vb6 to access 2003 vba

[James D. Houston]

I've been using vb6 to develop front ends for Access databases for some time
now. Now I have a client who would like to have their app written using
Access VBA, which I've never used before. The first thing I need is a form
that will let the user enter a name and search a table to see if the name is
already there. In VB6 I'd put the code in a command button's click event:

sFName = txtFirstName.Text
sLName = txtLastName.Text

If sFName <> vbNullString Then
sName = sFName & " " & sLName
Else
sName = vbNullString
sLName = txtLastName.Text
End If

GetPlayer sName, sLName

But when I try to run this code in Access, I get an error message saying
that: "You can't reference a property or method of a control unless the
control has the focus"

So how do I get the value of a control without setting the focus to that
control?

Another problem I'm having: I'd like to make sure the text boxes are blank
when I load the form. In vb6 I'd put something like this in the form's
initialize event:

Dim ctl As Control

For Each Control In Form_Form1.Controls
Set ctl = Form_Form1.ActiveControl
If ctl.ControlType = acTextBox Then
ctl.Text = vbNullString
End If
Next

I get the error message: "The expression you entered requires the control to
be in the active window"

Can anyone tell me how to do this in Access VBA? Thaniks in advance.

Jim

 

[Duane Hookom]

In Access/VBA use the Value property to get the value of a control. Since
this is the default property of a bound control, you can use just:
sFName = Me.txtFirstName

I'm not sure why you need to initialize the form controls since the values
should be blank or whatever value is the Default Value. The exception would
be bound controls which I assume you would not want to change.

 

[missinglinq via AccessMonster.com]

As Duane said, use .Value instead of .Text when referring to a control that
doesn't have focus.

If by "I'd like to make sure the text boxes are blank" you meant you want the
form ready for entering a new record, use this code"


Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
End Sub

 

[Larry Linson]

Like classic VB, you'll be using more than VBA code to develop the
application. The biggest difference between an Access database application
and a VB front-end is that, almost always, we use bound forms and controls
in Access, and data-binding is less common in VB. In fact, Access is so
oriented to database, that you can (except for error handling) write many
applications with no more code, or not much more, than the Wizards generate.

The reason we usually use bound Forms and Controls is that they work very
well, smoothly, and (some would say) intuitively in Access.

To do what you want, create a Form bound to the Table or Query that has the
data you want to display. Then, to the Header of the form, add a Combo Box,
set its Row Source to the Field you want to search, and then follow the
prompts on the wizard to let it create the proper code behind that Combo Box
to select the proper Record. At this point, you've done what you ask,
without writing any code at all.

I like to modify the error handling that the Wizards generate, and currently
use free software from MZTOOLS to generate the comments and the error
handling (which I set up in its library). So, at this point, I'd go into the
AfterUpdate procedure for the Combo Box and click my toolbar to add comments
and error handling.

The VB crowd at Microsoft was never very "kind" in their comments about
Access (except for the Jet databases, which they called Access databases)
because they never wanted you guys to know how simple, straightforward, and
easy it is to create normal business database applications in Access, lest
they lose some of their developers.

First, you need to become proficient in using Access, because (as with VB)
much of what you'll be doing will be to automate what you can do manually
with Access, so the casual user doesn't have to know Access. If you find
yourself thinking, "I can do this easier by using unbound forms and writing
code," then it's time to post here so we can, maybe, help you past whatever
stumbling block you're facing... it's (almost) always possible to do your
data handling with bound Forms and Controls once you grok The Access Way.

Larry Linson
Microsoft Access MVP

 

[David W. Fenton]

I've been using vb6 to develop front ends for Access databases for
some time now. Now I have a client who would like to have their
app written using Access VBA, which I've never used before.

Forget everything you know about VB6.

Approach Access as though you are a completely novice with no
programming expereince.

Populating a form in Access requires ABSOLUTELY NO CODING.

Learn how to use Access in point-and-click mode, then figure out
what default Access behaviors you don't like and learn to code
around those.