Combo box based on a query to navigate to a specific record

[freedomverse]

I am trying to implement the instructions found on Allen Brown's website that
tell how to make a combo box that will navigate to a particular record in
your form. So I have my combo box but it gets it's information from a query
instead of directly from a form. This way I can view the names it retrieves
as "Last, First" I seem to be close but am getting an error every time. I
think it has something to do with how I am referancing my query. Here is my
code:

Private Sub Combo258_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.Combo258) Then
If Me.Dirty Then
Me.Dirty = False
End If
Set rs = Me.RecordsetClone
rs.FindFirst "[PeopleQuery].[Last&First] = " & Me.cboMoveTo
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

 

[fredg]

I am trying to implement the instructions found on Allen Brown's website that
tell how to make a combo box that will navigate to a particular record in
your form. So I have my combo box but it gets it's information from a query
instead of directly from a form. This way I can view the names it retrieves
as "Last, First" I seem to be close but am getting an error every time. I
think it has something to do with how I am referancing my query. Here is my
code:

Private Sub Combo258_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.Combo258) Then
If Me.Dirty Then
Me.Dirty = False
End If
Set rs = Me.RecordsetClone
rs.FindFirst "[PeopleQuery].[Last&First] = " & Me.cboMoveTo
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

Try it like this:

1) Include the RecordID field in the combo row source. Perhaps
something like this...

Select YourTable.RecordID, [LastName] & ", " & [FirstName] as FullName
from YourTable Order by [LastName] & ", " & [First Name];

Set the column count to 2.
set the Bound colun to 1.
Set the Column Width property to
0";1"

2) Then code the Combo Box AfterUpdate event:

Private Sub Combo258_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[RecordID] = " & Str(Nz(Me![Combo258], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This way you are searching for the record's unique Prime Key field
rather than a combined [Last&First] field which doesn't exist in the
table.

An easy way to create all of this is to use the combo wizard when you
add a combo box to the form. Choose the 3rd option, "Find a record
....etc." Include the RecordID and the LastName fields. Choose to hide
the RecordID when prompted. Save the combo box.
Then simply edit the rowsource and instead of using just [RecordID]
and [LastName], replace [LastName] with
FullName:[LastName] & ", " & [FirstName]

Your combo row source will now show as
134 Smith, Robert
with the 134 RecordID column being hidden when you open the combo.

Change your table and field names as needed.

 

[freedomverse]

I will try your suggestion in the morning. Looks more like the right way to
do it. I actually had it working on another form using the wizard as you
described and I would like to do that now but the wizard only gives me two
options this time; it doesn't give me that 3rd option.

If there is a way to get that option back on the table I would do that
instead. If not, I guess I'll just know a few more useful bits of code.

Regards,
Jared.

 

[fredg]

I will try your suggestion in the morning. Looks more like the right way to
do it. I actually had it working on another form using the wizard as you
described and I would like to do that now but the wizard only gives me two
options this time; it doesn't give me that 3rd option.

If there is a way to get that option back on the table I would do that
instead. If not, I guess I'll just know a few more useful bits of code.

Regards,
Jared.

You only get the 3rd option ** IF ** the form is bound to a table ot
query.
Evidently, your form is unbound (no recordsource).
How would the wizard know you might want to look up a value in a table
or query if there is no table or query to look it up in?

 

[freedomverse]

I think I am almost there... I took the query out of the picture and made my
rowsource my table. (I only created the query in the first place because I
didn't know that I could do what you explained in the previous post.) The
wizard showed the third option again. It was successful in creating my combo
box. I am getting an error; I'm sure a comma or period is out of place:

SELECT [People].[ContactID], [People].FullName:[LastName] & ", " &
[FirstName].FROM [People];

Any problems seen in that line?

 

[fredg]

I think I am almost there... I took the query out of the picture and made my
rowsource my table. (I only created the query in the first place because I
didn't know that I could do what you explained in the previous post.) The
wizard showed the third option again. It was successful in creating my combo
box. I am getting an error; I'm sure a comma or period is out of place:

SELECT [People].[ContactID], [People].FullName:[LastName] & ", " &
[FirstName].FROM [People];

Any problems seen in that line?

Wouldn't you also like to have the combo display the names sorted.

SELECT [People].[ContactID], [LastName] & ", " &
[FirstName] as Fullname FROM People Order By [LastName] & ", " &
[FirstName]

 

[freedomverse]

I got it!!!

SELECT [People].[ContactID], [People].[LastName] & ", " & [FirstName] FROM
People;

Thank you for your help! I really appreciate it!

Jared.