Populate multiple fields using an unbound Combo Box

[gsc]

Hello, I am not an experienced user. I have an unbound subform from which I have used a query to select Inv ID, First Name & Surname to use in a Combo box (all 3 columns) in the main form. By making it unbound I can get it to populate the first field Inv ID (Lead) but I need it to then populate First Name (Lead) & Surname (Lead) but can't seem to find the answer anywhere. I have attached a screen shot of the form if that helps.



Many thanks, in advance

gsc

[orange]

That's a pretty significant set of forms. Can you give us some background about your application and your tables and relationships?

[RuralGuy]

All you should need is the Key field and a query with a Join.

[gsc]

I must admit, that I thought it did have to be to do with the relationships but just don't seem to be able to get it right, I have attached the relationships that I have at present. I have two forms, on which hold names, addresses and availability for each active person then I have another form that shows the availity of each person on an individual form for the exam that they need people to do, which can be up to 8 individuals. The idea is that they can be allocated quickly and the sessions then emailed or sent out via mailmerge. I have used the availablity query to make a second query giving just the names without duplicates to make it quicker to manage. I have attached a copy of the relationships, sorry its another word doc.
Thank you

[John_G]

Hi -

Looking at you original post, I think your problem is not as difficult as it looks -

Your combo box has three columns. When you reference the combo after making a selection, e.g. to assign the value to a form field as in me!field1 = me!combo1 , you get the value in the first column. You can get the value of the other columns just as easily, but you have to explicitly state which column. For example, to get the value in the second column, you would use me!field2 = me!combo1.column(1). That is not a typo - the column reference is zero-based, so the first column is column(0). For clarity, you can use me!field1 = me!combo1.column(0) with no problem.

The best place to put code for populating your form fields is in the After Update event of the combo box.

I notice on your form you have 5 occurrences of the ID and Name fields - is there a reason for that? If that grouping is repeated 5 times within the (Exam schedule?) table, you may have a data normalization issue (hard to tell from the form).

HTH

John

[gsc]

Hi -

Looking at you original post, I think your problem is not as difficult as it looks -

Your combo box has three columns. When you reference the combo after making a selection, e.g. to assign the value to a form field as in me!field1 = me!combo1 , you get the value in the first column. You can get the value of the other columns just as easily, but you have to explicitly state which column. For example, to get the value in the second column, you would use me!field2 = me!combo1.column(1). That is not a typo - the column reference is zero-based, so the first column is column(0). For clarity, you can use me!field1 = me!combo1.column(0) with no problem.

The best place to put code for populating your form fields is in the After Update event of the combo box.

I notice on your form you have 5 occurrences of the ID and Name fields - is there a reason for that? If that grouping is repeated 5 times within the (Exam schedule?) table, you may have a data normalization issue (hard to tell from the form).

HTH

John

Hi John,
Thank you for your help. I have done this before, and have now tried it again but it doesn't recognise the field. The ID Fields and Name fields are in the original teable as up to 8 people can be assigned to one exam and it seemed the eaiest way to list them in the form, so I allocated a different field for each. I am sure there is something really simple I am not seeing or doing but can't see it for the trees, as they say.