Urgent: Dividing Data from Combo Box

[fapa via AccessMonster.com]

Hi there

I have a combo box that displays an employees first and last name. When a
name is selected, im trying to get the Firstname to populate txtbox_firstname
and the Lastname to populate txtbox_lastname. Can someone please help.

The recordsource in the cmbo_names (combo box) =

SELECT tbl_Employees_records.PERSON_ID, [LASTNAME] & ", " & [FIRSTNAME] AS
FULLNAME
FROM tbl_Employees_records
ORDER BY [LASTNAME] & ", " & [FIRSTNAME];

therefore the names are displayed as: Patterson, Sarah (rather than being in
2 different columns) This is where i am having alot of trouble as i've tried .
... = [cmb_names].Column(1) but it just displays the full name, the divided
(firstname/lastname) as i'd like it

can someone PLEASE help?!

 

[Douglas J. Steele]

Easiest would be to include Firstname and Lastname as separate columns in
the combo box (you don't have to display them):

SELECT tbl_Employees_records.PERSON_ID, [LASTNAME] & ", " & [FIRSTNAME] AS
FULLNAME, [LASTNAME], [FIRSTNAME]
FROM tbl_Employees_records
ORDER BY [LASTNAME] & ", " & [FIRSTNAME];

You'd then refer to columns 2 and 3 to get them.

With your current setup, you could use the following in the AfterUpdate
event of the combo box:

Private Sub cmb_names_AfterUpdate()

Dim strFirstName As String
Dim strFullName As String
Dim strLastName As String

strFullName = Me!cmb_names.Column(1)
strFirstName = Trim$(Mid$(strFullName, InStr(strFullName, ",") + 1))
strLastName = Trim$(Left$(strFullName, InStr(strFullName, ",") - 1))

Me!txtFirstName = strFirstName
Me!txtLastName = strLastName

End Sub

 

[fapa via AccessMonster.com]

thank u, that worked well!

Easiest would be to include Firstname and Lastname as separate columns in
the combo box (you don't have to display them):

SELECT tbl_Employees_records.PERSON_ID, [LASTNAME] & ", " & [FIRSTNAME] AS
FULLNAME, [LASTNAME], [FIRSTNAME]
FROM tbl_Employees_records
ORDER BY [LASTNAME] & ", " & [FIRSTNAME];

You'd then refer to columns 2 and 3 to get them.

With your current setup, you could use the following in the AfterUpdate
event of the combo box:

Private Sub cmb_names_AfterUpdate()

Dim strFirstName As String
Dim strFullName As String
Dim strLastName As String

strFullName = Me!cmb_names.Column(1)
strFirstName = Trim$(Mid$(strFullName, InStr(strFullName, ",") + 1))
strLastName = Trim$(Left$(strFullName, InStr(strFullName, ",") - 1))

Me!txtFirstName = strFirstName
Me!txtLastName = strLastName

End Sub

Hi there

[quoted text clipped - 18 lines]

can someone PLEASE help?!