combo box problems

[Guest]

I am making a combo box in which a entry person will choose a dealer name and
what is stored in a subform is a number. This combo box pulls up a record on
the main form based the value chosen in the combo box. This code seems to
work fine when the table the form is based on is a linked table to the back
end, but I can't make it work when it is a static table in the front end.
How can I modify this to make it work in the front end?

'Pulls up dealer contact information when a dealer name is chosen.

Private Sub DealerName_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LicenseID] = " & Str(Nz(Me![DealerName], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

 

[Guest]

There is something else going on here. There is no difference addressing
linked tables and local tables.
You don't say what is or isn't happening. Are you getting an error? If so,
what error and where in your code is it happening?

I do notice a couple of problems with your code. First, you are dimming the
database as an object. Although this will work, it would be better to dim it
as the object type you will be using.
Also, your FindFirst is mixed up. You are converting the value in
Me.DealerName to a string, and returning a zero in the Nz function if there
is no value in DealerName. That is okay, except that the syntax is set for a
numeric value.
One thing I would suggest is a couple of changes to your existing code:

Private Sub DealerName_AfterUpdate()
Dim rs As Database

' Find the record that matches the control.

Set rs = Me.Recordset.Clone
rs.FindFirst "[LicenseID] = '" & Str(Nz(Me![DealerName], 0)) & "'"
If rs.NoMatch Then
MsgBox "Record Not Found"
Else
Me.Bookmark = rs.Bookmark
End If
rs.Close
Set rs = Nothing
End Sub

 

[Guest]

Here's more info:
The entry person needs to pick a dealer name (which is a string), but the
License ID, which is the value stored, is a number. With my original code, I
am not getting an error at all -- the combo box simply does not work, so no
records are pulled up on the main form. I tried the code you suggested, but
I got a complile error: Method or data member not found, and it highlighted
the .FindFirst in the code.

There is something else going on here. There is no difference addressing
linked tables and local tables.
You don't say what is or isn't happening. Are you getting an error? If so,
what error and where in your code is it happening?

I do notice a couple of problems with your code. First, you are dimming the
database as an object. Although this will work, it would be better to dim it
as the object type you will be using.
Also, your FindFirst is mixed up. You are converting the value in
Me.DealerName to a string, and returning a zero in the Nz function if there
is no value in DealerName. That is okay, except that the syntax is set for a
numeric value.
One thing I would suggest is a couple of changes to your existing code:

Private Sub DealerName_AfterUpdate()
Dim rs As Database

' Find the record that matches the control.

Set rs = Me.Recordset.Clone
rs.FindFirst "[LicenseID] = '" & Str(Nz(Me![DealerName], 0)) & "'"
If rs.NoMatch Then
MsgBox "Record Not Found"
Else
Me.Bookmark = rs.Bookmark
End If
rs.Close
Set rs = Nothing
End Sub

I am making a combo box in which a entry person will choose a dealer name and
what is stored in a subform is a number. This combo box pulls up a record on
the main form based the value chosen in the combo box. This code seems to
work fine when the table the form is based on is a linked table to the back
end, but I can't make it work when it is a static table in the front end.
How can I modify this to make it work in the front end?

'Pulls up dealer contact information when a dealer name is chosen.

Private Sub DealerName_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LicenseID] = " & Str(Nz(Me![DealerName], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

 

[Guest]

The reason it was not working before is you are looking up the wrong value.
If the license id is a number, then you need to know the number associated
with the dealer name. The easiest way to do that is use a 2 column combo box
that has dealer name and license id as its row source with license id being
the bound column.

Why it is failing on the FindFirst, I don't know. From here, it looks okay.
Here is a revised version of that line using a combo box with license id
being the bound column:
rs.FindFirst "[LicenseID] = " & Me!cboDealer

The error you are getting usually indicates a missing or misspelled field or
control name.

Here's more info:
The entry person needs to pick a dealer name (which is a string), but the
License ID, which is the value stored, is a number. With my original code, I
am not getting an error at all -- the combo box simply does not work, so no
records are pulled up on the main form. I tried the code you suggested, but
I got a complile error: Method or data member not found, and it highlighted
the .FindFirst in the code.

There is something else going on here. There is no difference addressing
linked tables and local tables.
You don't say what is or isn't happening. Are you getting an error? If so,
what error and where in your code is it happening?

I do notice a couple of problems with your code. First, you are dimming the
database as an object. Although this will work, it would be better to dim it
as the object type you will be using.
Also, your FindFirst is mixed up. You are converting the value in
Me.DealerName to a string, and returning a zero in the Nz function if there
is no value in DealerName. That is okay, except that the syntax is set for a
numeric value.
One thing I would suggest is a couple of changes to your existing code:

Private Sub DealerName_AfterUpdate()
Dim rs As Database

' Find the record that matches the control.

Set rs = Me.Recordset.Clone
rs.FindFirst "[LicenseID] = '" & Str(Nz(Me![DealerName], 0)) & "'"
If rs.NoMatch Then
MsgBox "Record Not Found"
Else
Me.Bookmark = rs.Bookmark
End If
rs.Close
Set rs = Nothing
End Sub

I am making a combo box in which a entry person will choose a dealer name and
what is stored in a subform is a number. This combo box pulls up a record on
the main form based the value chosen in the combo box. This code seems to
work fine when the table the form is based on is a linked table to the back
end, but I can't make it work when it is a static table in the front end.
How can I modify this to make it work in the front end?

'Pulls up dealer contact information when a dealer name is chosen.

Private Sub DealerName_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LicenseID] = " & Str(Nz(Me![DealerName], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

 

[Guest]

Yes, my combo box is 2 columns and license ID is bound. Thank you for
spending time on this -- I really appreciate it. I'll keep working and see
if I can come up with something.

The reason it was not working before is you are looking up the wrong value.
If the license id is a number, then you need to know the number associated
with the dealer name. The easiest way to do that is use a 2 column combo box
that has dealer name and license id as its row source with license id being
the bound column.

Why it is failing on the FindFirst, I don't know. From here, it looks okay.
Here is a revised version of that line using a combo box with license id
being the bound column:
rs.FindFirst "[LicenseID] = " & Me!cboDealer

The error you are getting usually indicates a missing or misspelled field or
control name.

Here's more info:
The entry person needs to pick a dealer name (which is a string), but the
License ID, which is the value stored, is a number. With my original code, I
am not getting an error at all -- the combo box simply does not work, so no
records are pulled up on the main form. I tried the code you suggested, but
I got a complile error: Method or data member not found, and it highlighted
the .FindFirst in the code.

There is something else going on here. There is no difference addressing
linked tables and local tables.
You don't say what is or isn't happening. Are you getting an error? If so,
what error and where in your code is it happening?

I do notice a couple of problems with your code. First, you are dimming the
database as an object. Although this will work, it would be better to dim it
as the object type you will be using.
Also, your FindFirst is mixed up. You are converting the value in
Me.DealerName to a string, and returning a zero in the Nz function if there
is no value in DealerName. That is okay, except that the syntax is set for a
numeric value.
One thing I would suggest is a couple of changes to your existing code:

Private Sub DealerName_AfterUpdate()
Dim rs As Database

' Find the record that matches the control.

Set rs = Me.Recordset.Clone
rs.FindFirst "[LicenseID] = '" & Str(Nz(Me![DealerName], 0)) & "'"
If rs.NoMatch Then
MsgBox "Record Not Found"
Else
Me.Bookmark = rs.Bookmark
End If
rs.Close
Set rs = Nothing
End Sub

:

I am making a combo box in which a entry person will choose a dealer name and
what is stored in a subform is a number. This combo box pulls up a record on
the main form based the value chosen in the combo box. This code seems to
work fine when the table the form is based on is a linked table to the back
end, but I can't make it work when it is a static table in the front end.
How can I modify this to make it work in the front end?

'Pulls up dealer contact information when a dealer name is chosen.

Private Sub DealerName_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LicenseID] = " & Str(Nz(Me![DealerName], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub