When Combobox is blank, textboxes should be blank

[RichW]

I have a form that has a combobox and several text boxes; all are linked to a
table.

When a user selects one of the items listed in the combobox, the text fields
then display the data associated with the item.

The combobox has the following code in its After Update event procedure:

Private Sub cboName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ColumnName] = '" & Me![cboName] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

Problem: When the form is first opened the combobox is blank, but the text
boxes display the data contained in the first row of the associated table.

How I want it to work: When the form is first opened, the combobox (like it
currently does) and all text boxes are blank.

All help is appreciated.

Thank you,

 

[Jack Leach]

You can build your condition to include a DoCmd.GoToNewRec for the
afterupdate, which will clear your checkboxes. Include it in the Open event
of the form as well so it defaults to a new record.

As a side note, you might want to
a) declare rs as a DAO.Recordset rather than Object, and
b) test the rs.NoMatch property after FindFirst for your condition, I
wouldn't always count on being at EOF. That's why there's .NoMatch

hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Jack Leach]

DoCmd.GoToNewRec

Sorry, that's DoCmd.GoToRecord, , acNewRec

apparently I didn't get enough sleep last night

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

You can build your condition to include a DoCmd.GoToNewRec for the
afterupdate, which will clear your checkboxes. Include it in the Open event
of the form as well so it defaults to a new record.

As a side note, you might want to
a) declare rs as a DAO.Recordset rather than Object, and
b) test the rs.NoMatch property after FindFirst for your condition, I
wouldn't always count on being at EOF. That's why there's .NoMatch

hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

I have a form that has a combobox and several text boxes; all are linked to a
table.

When a user selects one of the items listed in the combobox, the text fields
then display the data associated with the item.

The combobox has the following code in its After Update event procedure:

Private Sub cboName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ColumnName] = '" & Me![cboName] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

Problem: When the form is first opened the combobox is blank, but the text
boxes display the data contained in the first row of the associated table.

How I want it to work: When the form is first opened, the combobox (like it
currently does) and all text boxes are blank.

All help is appreciated.

Thank you,

 

[RichW]

Jack,

Thank you very much, the DoCmd.GoToRecord, , acNewRec works excellent; the
form now opens with the blank combobox and blank textboxes. Thank you!

Unfortunately, I do not understand your instructions regarding changes to
the rs. Because I am a fairly new user, I do not understand what rs and EOF
are.

I attempted to make changes to the I could not get it to work. Could you
please show me how I shoud insert your recommendations into my existing code?
The code follows:

Private Sub cboName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ColumnName] = '" & Me![cboName] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

Thanks again,
RichW

 

[Jack Leach]

Recordsets come in two types... DAO or ADO. DAO (Data Access Object) is the
native recordset for use with Access. It's the only one I've ever used. ADO
(Active Data Object) is used when dealing with other data sources outside an
Access database (of which I don't know much about).

A Recordset is a type of Object. There are thousands upon thousands of
objects. Forms, Tables, Queries, Modules, Controls, Recorsets,
Collections... they are all objects (in fact, press F2 from the VBA editor to
see the Object Browser). So, where it is possible to declare a variable as
an Object and set a Recordset to it (because a recordset IS an object), you
are much better off telling your code that you are explicitly using a
Recordset Object. Otherwise you leave it up to VBA to decide what type of
object you are trying to use, and thats something programmers do only at
great need.

So as far as declaring it as "DAO.Recordset" rather than just "Recordset" -
this further disambugates and lets VBA know that you specifically want the
DAO Recordset model. ADO and DAO have a great many differences, so we want
to make sure that it knows which one we're using.

The NoMatch property of a (DAO) Recordset is used to test whether a match
was found from a FindFirst action. So to put all this together, here's what
a much cleaner portion of that code generally looks like:


Private Sub cboName_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset

Set rs = Me.Recordset.Clone
rs.FindFirst "[ColumnName] = '" & Me![cboName] & "'"
If rs.NoMatch Then
'no recs found, go to a new record
DoCmd.GoToRecord, , acNewRec
Else
'match was found, set the bookmark
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End Sub


One more note: I added a "Set rs = Nothing" line at the end of the
procedure. Any Object type variable is set using the "Set" method. Anything
that you Set, Set it to Nothing when you're done. This keeps your instance
of "rs" from hanging around in the grey matter of your project and causing
problems later. It doesn't happen often, but often enough to explicitly
clear the variable. The general rule is "close what you have opened and
destroy what you have created". This keeps all your code on the up-and-up.

Hopefully this wasn't too confusing...

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

Jack,

Thank you very much, the DoCmd.GoToRecord, , acNewRec works excellent; the
form now opens with the blank combobox and blank textboxes. Thank you!

Unfortunately, I do not understand your instructions regarding changes to
the rs. Because I am a fairly new user, I do not understand what rs and EOF
are.

I attempted to make changes to the I could not get it to work. Could you
please show me how I shoud insert your recommendations into my existing code?
The code follows:

Private Sub cboName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ColumnName] = '" & Me![cboName] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

Thanks again,
RichW

 

[Jack Leach]

One more thing... I never did explain what EOF is.

A recordset is a set of records, at any number of records, including none.
Each recordset has a BOF and EOF property. This is the Beginning Of File and
End Of File.

a recordset with records...

'----
BOF
Record1
Record2
Record3
EOF
'----


a recordset with no records
'----
BOF
EOF
'----

When you first open a recordset, it is defaulted to BOF (not the first
record). You then need to move to the first record before processing.

rs.MoveFirst

EOF is generally found while looping a recordset...

Set rs = CurrentDb.OpenRecordset("tablename")
If rs.RecordCount <> 0 Then
rs.MoveFirst
While Not rs.EOF
'do some stuff here
rs.MoveNext
Wend
End If
rs.Close
Set rs = Nothing


Note that before performing some move method on a recordset you should check
to make sure it has records:

If rs.RecordCount <> 0

otherwise you will get an error if you try to Move anywhere if there's
nowhere to move.

Another method to check for records is to see if both BOF and EOF are true
(the only time they will both be true is if there is no records):

If Not (rs.BOF AND rs.EOF) Then
rs.MoveFirst
...
...
End If


So anyway, that's what EOF is. Assuming that FindFirst will end up on EOF
if there's no records is probably correct, but maybe not always. That's why
I suggested NoMatch - it will always be true if no records were found.

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[RichW]

Jack,

I truly appreciate the incredibly detailed response you have provided. Not
only have you provide me with the corrected coding, but you have provided an
education of the related aspects. Thank you.

Best regards,
RichW

 

[Jack Leach]

you have provided an

education of the related aspects.

That's the idea!

glad to help

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)