Combo box with a different row Source

[Triscuit]

I have searched for this for a while and I think I've come up with maybe an idea of how to do it.

I have a form for a Sample where one field takes in an auto-number userID for which ever person received the sample. The auto-number userID comes from a linked table name tblUsers that along with the PK auto-number userID has fields for the First Name, M Initial and Last Name.

When the user uses the form I would rather they see a concatenated version of the FName, MInitial and LName instead of a non descriptive auto-number userID.

On the data entry form I changed it to a combo box with the row source set as a query for the concatenated full name but this cannot be used because of the RI link to the tblUsers.userID and the Full name is not a number.

The most recent idea I've had is to use an update query where I update the record.

UPDATE
tblSample INNER JOIN tblUsers on tblSample.userID = tblUsers.userID
SET tblSample.userID = tblUsers.userID
WHERE tblUsers.txtFName AND " " AND tblUsers.txtMInitial AND " " AND tblUsers.LName = Me.cboFullName

where cboFullName is a combo box on the form with the rowsource set to a query concatenation of the user's Name.

Any ideas, I'm stumped.

 

[vbaInet]

Well, have you tried a simple UPDATE statement without any complicated joins?

An update statement doesn't return a recordset. The row source requires a recordset.

I don't see why the concatenated field wouldn't work?

 

[nathansav]

Use a multicolumn combo, with the ID in the first column, but the column width set to zero, make sure the bound column is 1

so you would have

Select staffID, [FirstName] & " " & [Last Name]
or something like that as the source,

column count of the combo as 2

column widths as 0;10

where 10 is the width of your box.

The ID will then be returned from the combo. So if the combo was called Combo1, you could use ".....Where [StaffID]=" & me.Combo1

 

[Triscuit]

Thank you nathansav!

Left the control source as the userID for the Sample.
Row source was changed to my query to include the userID then concatenated name.

Followed your column settings and works awesomely.

 

[nathansav]

Not a problem, you can move the bound column around, so have it at the end or something, just remember where it is and to set it, and you should have no issues.