Add to List When Combo Box is Concatenated 3

[Lilliabeth]

I use the method here to add to list

http://www.tek-tips.com/faqs.cfm?fid=4283

I have a form where the person combo box is from a query so the display is strLastName comma space strFirstName. This looks really nice, but the code can't automatically fill in the form since there is one field in the combo and two fields in the form.

I am hoping some one will offer suggestions as to how I can either do this better, or automatically add data before the comma to the txtLastName control and data after the space to the txtFirstName control.

 

[HarleyQuinn]

You could have a look into the Split() function.

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[rubbernilly]

In the AfterUpdate method, you can populate the controls... although it sounds out of normalization to do so.

me.txtLastName = Left(me.cboBOx, instr(1,me.cboBox,",")-1)
me.txtFirstName = right(me.cboBox, len(me.cboBox)-instrrev(me.cboBox,","))

Although, you might be further ahead to rework the combobox to have two more columns:

FName
LName
Last, First

And set the first two columns to have a width of 0" to hide them. Then you can set the First and Last Name controls on the form based on the columns of the combo box. It might be easier that way - less code and less calculation.

HTH

 

[rubbernilly]

Ooh, yeah, the Split function would work here to.

Good on ya', Harley!

 

[HarleyQuinn]

Glad to help, thanks for the stars

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.