Problem with DAO recordset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using a combo box to lookup an account number in a table. The following
code is in the AfterUpdate event for the combo box:

Dim rsDistProfile As DAO.Recordset

If Not IsNull(Me.cboAccountNo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rsDistProfile = Me.RecordsetClone
rsDistProfile.FindFirst "[acct_no] = """ & Me.cboAccountNo & """"
If rsDistProfile.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rsDistProfile.Bookmark
Endif
Set rsDistProfile = Nothing

Finding the record is no problem. What I don't understand is why can't I
get values from the other fields in the record? When I try something like
this:

If rsDistProfile.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rsDistProfile.Bookmark
Me.contact_name = rsDistProfile!contact
 
This line:
Me.Bookmark = rsDistProfile.Bookmark
moves the form to the record you found in the clone set.

Since they both match now, the line:
Me.contact_name = rsDistProfile!contact
does nothing: because the form is already at this record, changing the name
to the name in the same record does nothing useful.
 
Allen,

Thanks for your response. I should have mentioned that the text boxes I'm
trying to populate are unbound. I'm doing this for a specific
"business-rules" restriction. Is there another way to populate these text
boxes?

Thanks again,

Patrick

Allen Browne said:
This line:
Me.Bookmark = rsDistProfile.Bookmark
moves the form to the record you found in the clone set.

Since they both match now, the line:
Me.contact_name = rsDistProfile!contact
does nothing: because the form is already at this record, changing the name
to the name in the same record does nothing useful.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Patrick said:
I'm using a combo box to lookup an account number in a table. The
following
code is in the AfterUpdate event for the combo box:

Dim rsDistProfile As DAO.Recordset

If Not IsNull(Me.cboAccountNo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rsDistProfile = Me.RecordsetClone
rsDistProfile.FindFirst "[acct_no] = """ & Me.cboAccountNo & """"
If rsDistProfile.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rsDistProfile.Bookmark
Endif
Set rsDistProfile = Nothing

Finding the record is no problem. What I don't understand is why can't I
get values from the other fields in the record? When I try something like
this:

If rsDistProfile.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rsDistProfile.Bookmark
Me.contact_name = rsDistProfile!contact
.
.
.
End If

I get the following error: Run-time error 3265. Item not found in this
collection.

?????

Thanks in advance for any help.

Patrick
Click to expand...
Click to expand...
 
Okay: which line gives the 3265 error?

If it is:
Me.contact_name = rsDistProfile!contact
then either there is no control named contact_name on the form, or there is
no field named contact in the recordset.

When it fails and you go into debug, you can pause the mouse over both to
discover which one is invalid.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Patrick said:
Allen,

Thanks for your response. I should have mentioned that the text boxes I'm
trying to populate are unbound. I'm doing this for a specific
"business-rules" restriction. Is there another way to populate these text
boxes?

Thanks again,

Patrick

Allen Browne said:
This line:
Me.Bookmark = rsDistProfile.Bookmark
moves the form to the record you found in the clone set.

Since they both match now, the line:
Me.contact_name = rsDistProfile!contact
does nothing: because the form is already at this record, changing the
name
to the name in the same record does nothing useful.

Patrick said:
I'm using a combo box to lookup an account number in a table. The
following
code is in the AfterUpdate event for the combo box:

Dim rsDistProfile As DAO.Recordset

If Not IsNull(Me.cboAccountNo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rsDistProfile = Me.RecordsetClone
rsDistProfile.FindFirst "[acct_no] = """ & Me.cboAccountNo &
""""
If rsDistProfile.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rsDistProfile.Bookmark
Endif
Set rsDistProfile = Nothing

Finding the record is no problem. What I don't understand is why can't
I
get values from the other fields in the record? When I try something
like
this:

If rsDistProfile.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rsDistProfile.Bookmark
Me.contact_name = rsDistProfile!contact
.
.
.
End If

I get the following error: Run-time error 3265. Item not found in
this
collection.

?????

Thanks in advance for any help.

Patrick
Click to expand...
Click to expand...
Click to expand...
 
Back
Top