Find Record to Edit based on Selected ID

[Nene75]

Hi Everyone:

I have a form that contains a combo box. Combo Box has persons' concatenated LastName, FirstName, MiddleInitial to choose the record to edit. I would like the selected record to show in the form to make modifications. I was able to concatenate the 3 fields (LastName, FirstName, and MiddleInitial) but when the record is selected I am unable to retrieve the selected record to modify. I am able to retrieve the record if only LastName selected but not all three fields. How do I accomplish this to have the selected record open up?

Any help provided would be appreciated.

Thanks!

 

[ethorn10]

I would suggest "un-concatenating" them into three variables. I'm assuming you are selecting the record via a Select statement using SQL. If this is the case, just use those three variables in a WHERE clause.

should work.

 

[Nene75]

I am selecting the record using the following on an unbound combo box:

Row Source:

SELECT [tblPersonData].[LastName], [FirstName] & " " & [LastName] AS Fullname FROM tblPersonData;

AfterUpdate Event:

Private Sub FullName_AfterUpdate()
DoCmd.OpenForm "frmEditData", , , , , , Me.FullName.Value
End Sub

With the above I am retrieving blank form. The form is based on multitables with 5 subforms.

 

[trxnme]

Why not set the combo box to use a query based on the table? This will allow you to use concatenation to create a column with the full name in it, as well as allowing you to add a second column with that tables unique id in it. Then use the unique id as the 'bookmark' for the on update event. I've had combo boxes set to update a form from columns other than the primary by using the me.combobox.column method of passing the data. This also insures against getting the records of two people with the same surname, if you want all records with the same surname then use the surname column.

Also the form wil load quicker, apparently, if you use a query rather than a SQL string.

 

[Nene75]

trxnme:

I have combo box set to use the query based on the table where I concatenate the 3 fields. So that's what the row source shows in the above thread.

I didn't get the part you mentioned about ading a second column with the tables unique id in it? How do I do this>?