Re: VB Programmer struggling to learn Access

#1

In VB you will find that you have to write a lot code to accomplish this
task.

Quote:

In VB I'd have a form level ADO connection

Yup, and that is a pain to have to do. In access, you just use the sql
directly in the combo box. You can use the combo box wizard, or even build a
query and use that. No code needed, and it is automatic load for you.

Quote:

populate the combo box in the Form_Load event

Yuk, again more code. We don't have to use code to load up the combo box.
Note that we have both the on-open event, and the on-load event in access
forms, and you don't want to confuse the two.

Quote:

then populate the various text boxes in the combo box click event.

Again more code! Further, you really don't want to copy that data. You have
relational database here. VB tends to encourage you to "copy" the data form
other places when you should be using a relationships and databases
concepts.

There *are* cases when you do actually want to copy data. You can use the
after update event of the combo box *if* you need to copy the data. (but I
don't see the need here)

So, to copy the data, you could use something like:

me!Instructor = Me!cboCours.Column(2)

However, why not just "display" the additional data as a lookup. That is the
essence of a relational database. You only need to STORE the course id, and
all other information you thus need can be looked up. If you included the
columns in your combo, then you can simply place some un-bound text boxes on
your screen like:

=([cboCourse].[Column](2))

Thus, you don't need to write ANY code to accomplish this whole process.
Just build the combo box with the combo wizard, and then place some un-bound
controls on your form with the above as the data source for those controls.

Ms-access forms and controls have about double the number of events as
compared to the simple VB forms and VB controls. This means that the
learning curve for ms-access is a good deal higher then VB. However, once
you get the hang of the complex form model, then the result of this longer
learning curve is a huge increase in software development speed. Software
can be developed for about 1/3 the cost as compared to VB (ie: a $8,000
project in access will cost $24,000 in VB, a $12,000 project in access will
cost $36,000). However, these gains only occur *after* the complexity is
mastered.

In general, you will find very little code is needed to simple database
tasks. You whole process can be done with no code at all.

Another nice way to do a lookup is to use a sub form. In your case, I don't
think you need this, but you *should* read the following article to get some
more ideas:

http://www.attcanada.net/%7ekallal.m...000000005.html

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
kallal (AT) msn (DOT) com
http://www.attcanada.net/~kallal.msn

#2

"Software
can be developed for about 1/3 the cost as compared to VB (ie: a $8,000
project in access will cost $24,000 in VB, a $12,000 project in access will
cost $36,000). However, these gains only occur *after* the complexity is
mastered."

I certainly don't want to say otherwise, but I can't help wondering why so
many people are using VB with all this coding for often no reason..

I still develop with ACC97 / ODE97 for runtime / Sagekey scripts / Wise
Installer and this package rocks! 90% people don't have a clue what
programming language I am using anyway.

#3

Hi Helmar,

Wow.
I had a real eye-opener lately, too ...which tells me just how spoiled I am
by Access.
My son is learning C++ at computer school, and it just sounds horrible!

You've had quite a few responses already, yet I'm surprised that nobody has
mentioned that you could simply create a default form (using the wizard)
based on tblCourses, or even a query which is based on tblCourses. After the
form is created, add a combo-box (also based on tblCourses... again using
the wizard), and choose the "Find a record on my form based on the value I
selected in my combo-box" option.
Selecting a value from the combobox finds and displays the details of that
record.

Here is the code generated by the wizard:
*****************************************
Sub Combo6_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[ArtistID] = " & Me![Combo6]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub
*****************************************

Simple. Easy. Done

You're gonna love Access.

Don

"Helmar Herman" <BenAndMarsha (AT) mail (DOT) com> wrote

Quote:

I'm an experienced VB programmer trying to do some volunteer work for
a non-profit organization that has an Access application.

I'm having difficulty creating a simple data entry form. I think it
may be that my VB mindset is wrong for Access.

For example, I've created a new table called "Courses" that contains
course information (Course name, Instructor, start and end date,
active flag, etc.)

In my form, I placed a drop-down combo box that contains a list of
records from Courses where the active flag is "True". The Recordset
points to a Query I created for active courses.

That part works fine. The combo box is populated with active courses.

But I want to add text boxs or labels for such fields as Instructor,
start and end dates, etc. that are automatically filled in when the
user selects a particular course from the combo box.

In VB I'd have a form level ADO connection, populate the combo box in
the Form_Load event, and then populate the various text boxs in the
combo box click event.

I'd appreciate any help.

#4

Many VB programmers do work other than database, and that is where VB
"shines". Then, because they know VB, when they get a database job, they
look and find that VB can "do database" so they use it because it is what
they know.

Many of them never really learn anything of substance about database, so
they end up using the tables as though they were sequential files and
really, honestly believe that they have a database application. These folks
are unlikely to do a VB database application in ONLY 3 times the elapsed
time and with 3 times the effort of an experienced Access developer doing an
equivalent application.

Others do learn about queries, and joins, and such, but just never get out
of the VB language. They create real database applications, with a smaller
"footprint" than the comparable Access application, and they may well
develop the application in only 3 times the time and effort of the
experienced Access developer doing the equivalent.

Others stick with VB for various reasons -- I discussed using VB or Access
in a presentation that you can download from
http://appdevissues.tripod.com/downloads/. And, rather than go through the
same stuff again, I'll just suggest you download the PowerPoint file and
take a look.

"Saintor" <saintor1 (AT) REMOVETHIShotmail (DOT) com> wrote

Quote:

"Software
can be developed for about 1/3 the cost as compared to VB (ie: a $8,000
project in access will cost $24,000 in VB, a $12,000 project in access
will
cost $36,000). However, these gains only occur *after* the complexity is
mastered."

I certainly don't want to say otherwise, but I can't help wondering why so
many people are using VB with all this coding for often no reason..

I still develop with ACC97 / ODE97 for runtime / Sagekey scripts / Wise
Installer and this package rocks! 90% people don't have a clue what
programming language I am using anyway.

#5

I certainly don't want to get into one of the my language VS the other guys
language (those arguments stopped being cool many years ago in high
school!).

However, the real problem is that ms-access is often viewed as a 2nd rate
tool, and one for only amateurs.

In fact, it is a very good tool. It is also likely one of the most
productive tools out there. The tight interaction of the forms and report
builder can't be beat.

The real problem comes when you need to assemble a team of developers. Then
things like source code control, and things like the developers using
objects as a means of the development process to "connect" the resulting
parts of the application thus tends to leave out ms-access as a tool of
choice. Further, those first rate developers from those highly skilled teams
thus generally don't use ms-access. These developers don't use access
because the jobs and development environment for the type of projects they
*usually* work on don't warrant using ms-access. We are talking primality
non database windows type applications. In that envorment, we are talking
about teams, and OO stuff all the way.

So, much of access's lack of respect is due to the above. Plus the fact that
ms-access is shipped with office and not visual studio development tools
also contriubtes to this lack of respect.

Access is genearlly FAR better then is VB for most small business
applications.

Further, most would agree that distribution of ms-access applications is not
very good. However, with the ODE for a97 along with sagekey scripts then you
have one killer development platform.

Probably the best combo ever made for one person database development.

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
kallal (AT) msn (DOT) com
http://www.attcanada.net/~kallal.msn

#6

"Helmar Herman" <BenAndMarsha (AT) mail (DOT) com> wrote

Quote:

I'm an experienced VB programmer trying to do some volunteer work for
a non-profit organization that has an Access application.

I'm having difficulty creating a simple data entry form. I think it
may be that my VB mindset is wrong for Access.

For example, I've created a new table called "Courses" that contains
course information (Course name, Instructor, start and end date,
active flag, etc.)

In my form, I placed a drop-down combo box that contains a list of
records from Courses where the active flag is "True". The Recordset
points to a Query I created for active courses.

That part works fine. The combo box is populated with active courses.

But I want to add text boxs or labels for such fields as Instructor,
start and end dates, etc. that are automatically filled in when the
user selects a particular course from the combo box.

In VB I'd have a form level ADO connection, populate the combo box in
the Form_Load event, and then populate the various text boxs in the
combo box click event.

I'd appreciate any help.

Hi Helmar

Access forms are usually "bound", which means that (a) the form is based on
a selected table or query, and (b) the controls on the form are bound to
selected fields within that table or query. (a) is defined by the form's
Rowsource property (Recordsource? - I don't have Access here to check). (b)
is specified by the control's Controlsource property. Adding suitable
controls to a form, & setting the relevant properties, is the only thing you
need to do, to create a view/add/edit/delete form for any table or query in
Access.

What you need on top of that, is code behind the combo box to reposition the
form to the selected course. You'll need to use the combo box's AfterUpdate
event to: (1) see which course the user chose; (2) find that course in the
form's RecordSetClone property (whcih is actually a recordset), then (3) set
the form's Bookmark property to the bookmark property of the found record in
the recordsetclone. I won't attempt "air code" for this, cos I don't have
Access here to check. It sounds complicated, but it's only a few lines of
code.

The result of all this, is that you will have a view/add/edit/delete-capable
form where the user can move to a given course by selecting it from the
combo box. This would be in addition to the various other methods that
Access gives you *automatically*, for moving to records in forms; for
example, the recordselector buttons at the bottom left of the form.

The problem with "populating the various text boxs in the combo box click
event", in Access, is this. If those boxes are *bound* (as described above),
populating them from VBA code will actually update those fields in the
underlying table or query. And if they are *unbound*, you will have to write
your own code to duplicate the many things that Access does for you
*automatically* for bound forms & fields: eg. updating the table
automatically. So unbound controls are not the way for you to go, here.

HTH,
TC

#7

Albert (and other repliers),

Thanks for your replies to my question. They've been helpfull.

As to the MS Access vs Visual Basic vs C++ issue.........

I've just retired from over 30 years of software development.

There has always been "Class wars" about programming languages and
operating systems (there are 3 on IBM Mainframes, "MVS", "VM", and
"VSE".)

I started in 1968 on an IBM 1401 with AutoCoder, went on to 360/370's
in assembler (and a little COBOL), then to PC's with BASIC, Visual
Basic and now MS Access.

The commercial applications I created in Visual Basic (which currently
sell for $15,000 to over $100,000) were process based, not
form/DataBase based. They crunched data and used the database only
incidentially. User interface was a minor part of the projects. As
such, VB or C++ (which I never warmed to or used) were more
appropriate than MS Access.

For applications that are visually and database oriented, MS Access
seems appropriate, as speed of development is the overriding concern.

I guess my opinions paraphrase the old saying, "It's a poor workman
who blames his tools". If you have a bag of tools, pick the one
appropriate for the particular job at hand. One is not necessarily
"Better" than another.

Helmar B. Herman
BenAndMarsha (AT) mail (DOT) com

On Mon, 28 Jul 2003 01:37:58 GMT, "Albert D. Kallal" <kallal (AT) msn (DOT) com>
wrote:

Quote:

I certainly don't want to get into one of the my language VS the other guys
language (those arguments stopped being cool many years ago in high
school!).

However, the real problem is that ms-access is often viewed as a 2nd rate
tool, and one for only amateurs.

In fact, it is a very good tool. It is also likely one of the most
productive tools out there. The tight interaction of the forms and report
builder can't be beat.

The real problem comes when you need to assemble a team of developers. Then
things like source code control, and things like the developers using
objects as a means of the development process to "connect" the resulting
parts of the application thus tends to leave out ms-access as a tool of
choice. Further, those first rate developers from those highly skilled teams
thus generally don't use ms-access. These developers don't use access
because the jobs and development environment for the type of projects they
*usually* work on don't warrant using ms-access. We are talking primality
non database windows type applications. In that envorment, we are talking
about teams, and OO stuff all the way.

So, much of access's lack of respect is due to the above. Plus the fact that
ms-access is shipped with office and not visual studio development tools
also contriubtes to this lack of respect.

Access is genearlly FAR better then is VB for most small business
applications.

Further, most would agree that distribution of ms-access applications is not
very good. However, with the ODE for a97 along with sagekey scripts then you
have one killer development platform.

Probably the best combo ever made for one person database development.

#8

Gee, what a marvellous wealth of experience you have.

And..yes.....very simply the right horse for the right course.

--
Albert D. Kallal
Edmonton, Alberta Canada
kallal (AT) msn (DOT) com
http://www.attcanada.net/~kallal.msn

#9

RE/

Quote:

I certainly don't want to say otherwise, but I can't help wondering why so
many people are using VB with all this coding for often no reason..

- Divisibility among large project teams
- IT likes VB, tends to look down on MS Access
- Once the app is deployed, the user doesn't have to worry about IT pushing down
a newer version of MS Access and breaking their app
- Users with big budgets either don't know about or don't care about the 1:3
cost ratio.

Having said all that, I'm still doing 100% MS Access....because my nich is that
of the guy who can do "quick-but-not-too-dirty".
-----------------------
PeteCresswell

#10

As I said in my previous post, each language has its strengths.

One of my commercial VB applications reads through a printer PCL file,
parses it, stashes it in whatever database the user has (SQL Server,
ORACLE, or Access), and then converts the PCL to an Adobe Acrobat file
for eMail or Web access.

MS Access would not have been appropriate for this data-crunching
application.

Another application performs mail-merges of various sorts and eMails
via a variety of eMail servers (Exchange, Notes, SMTP, etc.). This
also wouldn't have been appropriate for MS Access. Once again I used
VB.

I'm finding that for my volunteer work for Non-profits, MS Access is
the language of choice, because they need a visually intensive
database application.

Helmar B. Herman
BenAndMarsha (AT) mail (DOT) com

On Tue, 29 Jul 2003 00:22:58 GMT, "(Pete Cresswell)" <x@y.z> wrote:

Quote:

RE/
I certainly don't want to say otherwise, but I can't help wondering why so
many people are using VB with all this coding for often no reason..

- Divisibility among large project teams
- IT likes VB, tends to look down on MS Access
- Once the app is deployed, the user doesn't have to worry about IT pushing down
a newer version of MS Access and breaking their app
- Users with big budgets either don't know about or don't care about the 1:3
cost ratio.

Having said all that, I'm still doing 100% MS Access....because my nich is that
of the guy who can do "quick-but-not-too-dirty".
-----------------------
PeteCresswell