[RESOLVED] [2005] Populate 2nd Combo box

[Jonny1409]

Hello,

I have 2 tables in my SQL Database, one which holds a list of a persons details, and another which holds the persons reference and access number.

For example, in table 1 (tbl_Personal) I have :

Ref-Forename-Surname-Age-DOB-etc.....
112-Joseph-Bloggs-29-21/12/1977
123-Lucy-Winter-27-05/06/1979

In table 2 (tbl_Access), I have :

Ref-AccessNo
112-258
123-369

On my form I have a combobox which brings in the persons surname from tbl_Personal.

If I select a person from this box, I want the box underneath to populate with the persons accessno from tbl_Access.

How do I do this please ?

Thanks,

[Shaggy Hiker]

Are you sure you want a combo box for that second one? It seems more appropriate to either use a label, or a textbox.

I'm sure there are ways to do this with databinding or something like that, but I would just use a straight SQL query returning a single value. The tough part a bout that is that it looks like you have a bunch of different things concatenated together in the pick list. Is the ref part of that a unique identifier, or do you need the rest of the info for the unique ID?

[Jonny1409]

I'm happy with either a label or textbox.

Nothing is actually concatenated though, that's just my formatting - the data in tbl_access is :

Ref Colum
112
123

AccessNo Column
258
369

and the data in tbl_persons is :

Ref Column
112
123

Forename Column
Joseph
Lucy

Surname Column
Bloggs
Winter

Age Column
29
27

DOB Column
29/12/1977
05/06/1979

[Kimmy4]

All you will need to do is select accessNo from tbl_Access where (tbl_Access)Ref = (tbl_Personal)Ref. Just use a table join and the selectedItem value of the combobox and you should be able to get your result.
Have you tried anything yet because if you have then if you provide it we can tell you where you're going wrong.

Hope this helps

[Jonny1409]

Thanks Kimmy, that's excellent.

Cheers.