Drop down box to select customer

[Biggles]

Can someone tell me what I am doing wrong. This code
works in one database, but not an another. I have an
unbound combo box with a row source of a SELECT statement
in the form header. When I select one of the customers,
the detail portion of the form does not change. This code
is in the after update control of the field.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[txtCUST_NO] = '" & Me![txtCUST_NO] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Sean

 

[Marshall Barton]

Can someone tell me what I am doing wrong. This code
works in one database, but not an another. I have an
unbound combo box with a row source of a SELECT statement
in the form header. When I select one of the customers,
the detail portion of the form does not change. This code
is in the after update control of the field.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[txtCUST_NO] = '" & Me![txtCUST_NO] & "'"
Me.Bookmark = rs.Bookmark
End Sub

If the txtCUST_NO field in the table is a numeric type, then
you should not use the quotes around the value.

This would be self evident if you'd check NoMatch before
setting the bookmark:

Private Sub Combo9_AfterUpdate()
' Find the record that matches the control.
With Me.RecordsetClone
.FindFirst "[txtCUST_NO] = " & Me![txtCUST_NO]
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

 

[Biggles]

Thanks, but the field is text. I will not do any calc's

 

[Marshall Barton]

Thanks, but the field is text. I will not do any calc's
on it, so I made it a text field.

Are you sure the field in the form's record source and the
combo box are both named txtCUST_NO?

I still think you should check for NoMatch.

Try using Me.RecordsetClone instead of Me.Recordset.Clone

Other than that, I'm out of ideas.
--
Marsh
MVP [MS Access]

-----Original Message-----

Can someone tell me what I am doing wrong. This code
works in one database, but not an another. I have an
unbound combo box with a row source of a SELECT statement
in the form header. When I select one of the customers,
the detail portion of the form does not change. This code
is in the after update control of the field.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[txtCUST_NO] = '" & Me![txtCUST_NO] & "'"
Me.Bookmark = rs.Bookmark
End Sub

If the txtCUST_NO field in the table is a numeric type, then
you should not use the quotes around the value.

This would be self evident if you'd check NoMatch before
setting the bookmark:

Private Sub Combo9_AfterUpdate()
' Find the record that matches the control.
With Me.RecordsetClone
.FindFirst "[txtCUST_NO] = " & Me! [txtCUST_NO]
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub