Combo Box Find Record 1

[braith]

Hello Access wizards (no pun intended!),

Version is Access 2007.

Unbelievably, I can't seem to find a solution to this problem. Several sites had good information that seemed to come close, but I couldn't get any of the VBA variations to work.

I have a simple find combo box that lists records by last name then first name. The problem is that if more than one user in the DB has the same last name, the code will only return the first name on the list with that last name.

Here is the code in the AfterUpdate event:

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Last] = '" & Me![cmbFindRecord] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Looking at the code, it is understandable why it will only return the first instance of identical last names. But I have tried every combination I can think of for the last few days to make it capable of multiple criteria, and all either retun errors or just plain fails to run.

I realize that I can just use a wizard and be done with it, but the wizard-created macro seems to run slower and besides, I can't increase my VBA knowledge if I have wizards do everything and never look under the hood. Speaking of wizards, I did use an 03 version to have a wizard build what I need since the earlier wizards use VBA. I then lifted the code from the IDE and put it in the 07 version (after entering the row source), but it also failed to run. Came back with a 'type mismatch' error. Here is that code:

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[PersonnelID] = " & Str(Nz(Me![cmbFindRecord], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

I am not just looking for working code here, but could someone please give me a Reader's Digest explanation of what I am doing wrong?

Thanks.

 

[PHV]

Provided that the first column of cmbFindRecord is PersonnelID:
rs.FindFirst "PersonnelID=" & Nz(Me!cmbFindRecord.Column(0), 0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

Normally you have A user ID to uniquely identify a user. So I am assuming that you do and that it is a long value.

If that is the case then the trick is that the combo box recordsource should be a query that returns the unique ID and a concatenated field. Something like this

Select
UserID,
lastName & ", " & firstName AS FullName
From
YourTableName
Order By
LastName,
First Name

Now in your combo you have
column Count: 2
bound column: 1
column widths: 0";1"

This combo then shows
...
Smith, John
Smith, Paul
Smith, Robert
Stevens, Mary

the first column holds the unique ID but it is hidden because the width is 0"
When you pick "Stevens, Mary" the combo value is the value of the bound column which is the unique id (some number like 123).

So the code is simply
me.recordset.findfirst "UserID = " & me.cmbFindRecord

should resolve to something like:
UserID = 123

if you did not have a unique ID and a multicolumn combo (neither of these I would ever do) then your code is

me.recordset.findfirst "LastName = '" & me.cmbFindRecord.column(0) & "' AND FirstName = '" & me.cmbFindRecord.column(1) & "'"

the above should resolve to
LastName = 'Smith' AND FirstName = 'John'


I never use the bookmark property I see no need for it, and it is confusing in may instances and prone to errors.

This code will not work because if it is a string it has single quotes around the value, if it is numeric value no quotes.

rs.FindFirst "[PersonnelID] = " & Str(Nz(Me![cmbFindRecord], 0))

 

[braith]

MajP,

Your code works perfectly! In fact, I am amazed at the simplicity of it, and it definitely works better than the wizard-created macro. Your explanation drilled to the heart of the matter much better than the Sybex and SAMS volumes I have. Makes me wonder why previous versions of Access wizards employed the bookmark property to begin with...

Anyway, since I have my learning hat on, I would like to induldge you just a bit more. You stated that you would never have a multicolumn combo - why is that?

Reason is, in the organization I work for, personnel databases tend to adhere to formal LASTNAME | FIRSTNAME conventions, all caps. So, I rewrote your SELECT query as such and added an extra column:

SELECT [tblPersonnel].[PersonnelID], [tblPersonnel].[Last], [tblPersonnel].[First] FROM [tblPersonnel] ORDER BY [tblPersonnel].[Last], [tblPersonnel].[First];

Can there be future problems with this?

Anyway, I have saved your answer as a PDF to add to my library of Access programming. Maybe one day when I become more VBA-proficient I can pay it forward to others on this site.

A star for you, my friend.

Thanks again.

 

[MajP]

I believe the bookmark use may be a carry over from Access 97 and older. Back then you had a recordsetclone property and not a recordset property. So you had to find the record in the clone, get the bookmark, and then set it in the recordset which moved the recordset.
Seems overly complicated it you can just try to do a find first in the forms recordset which automatically moves it. And if it does not find it then nothing happens, so you do not even have to error check (as long as you pass the correct data type).
Some people argue that moving with the recordset clone is safer than moving the forms recordset. I think that is garbage. The recordsetclone still points to the same underlying table data.


Multi column comboboxes just seem strange to me. Once selected a combo only shows one column value. If I need to display multi values I concatenate it or use a listbox.
I am not using the concatenation because it is the unique ID in the hidden column.

So if I have a multi column combo

ROBERTS | MIKE
ROBERTS | TINA
SMITH | JOHN

if I select Mike Roberts my combo value would display only the bound column
Roberts

Instead
I would have a single concatenated field

ROBERTS, MIKE
ROBERTS, TINA
SMITH, JOHN

Then when I select Roberts, Mike it would display Roberts, Mike

I can concatenate into any format I want to display in the combo. It does not matter because you are using the unique ID for searches or saving to a table.

Select [Last] & " " & [First] as FullName
ROBERTS MIKE

Select [Last] & ", " & left([First],1) & "." as FullName
ROBERTS, M.

Select [EmployeeID] & ": " & [Last] & ", " & [First] as FullName
XY45P: ROBERTS, MIKE


So to be specific

SELECT [tblPersonnel].[PersonnelID], [tblPersonnel].[Last] & " " & [tblPersonnel].[First] AS FullName FROM [tblPersonnel] ORDER BY [tblPersonnel].[Last], [tblPersonnel].[First]

Column Count: 2
Bound Column: 1
Column Widths: 0"; somevalue

 

[braith]

Outstanding. It occurred to me that the combo box auto-fills as the user types in it, so the first concatenation option that you suggested works much better than the two-column format we are using. With your code, the combo box auto-fills both first AND last names when an end user types in the box, making the find operation much more versatile. Thank you for this; I have implemented it in the DB.

Last request - I discovered that if a user types in the combo box, then has a change of mind and backspaces through the text then attempts to exit the box, an error occurs offering the user to debug the code. Obviously, I don't want the user going into the code, so I added this GOTO handler:

Err_cmbFindRecord_Click:
cmbFindRecord = ""

This works well to return the combo box to an empty state with no errors. I realize that entire books have probably been written on proper error handling, but off the top of your head, do you see a potential problem with this?

Thanks again for all of your help.

 

[MajP]

likely the problem is that the value of the combo is null and then this would error

me.recordset.findfirst "UserID = " & me.cmbFindRecord

You can do this

if not isnull(me.cmbFindRecord) then
me.recordset.findfirst "UserID = " & me.cmbFindRecord
end if

or NZ
me.recordset.findfirst "UserID = " & NZ(me.cmbFindRecord,0)

 

[braith]

Yep, yep, yep - you nailed it. The IF statement is right on the money. Thanks for taking the time to teach me.

 

[PHV]

FYI just reread carefully my post timestamped 5 Dec 11 11:24 ...