Solved: Populate text boxes from a listbox

[austenr]

Here is what I need to do. First of all the data is not normalized so don’t flame me I didn’t start this.
I have a list box which contains a First Name and Last Name. When you click on it this is what I want to so:
Run a VBA procedure that will pull 800 + items from multiple tables and populate them into text boxes.
Ordinarily, you could use a regular query to do this but in this instance you cant due to the field limitations on Access.
There are 5 tables I need data from and the only real variable is the name.
How can you do this in VBA? :dunno

[Imdabaum]

So you want to change the recordsource based on the value of the list box?

Can you bind the text fields to queries where the conditional parameter is the list box.selected value? What limitations are you finding if you have tried this?

If it needs to be in VBA, I would try something like this,


Dim rst as Recordset
Set rst = CurrentDB.OpenRecordset("Select [data],[data2],...etc FROM Table1
WHERE [name] = " & listboxvalue, dbopendynaset)

Me.text1 = rst.Fields("data1")
Me.text2 = rst.Fields("data2")
Me.text3 = rst.Fields("data3")

Set rst = CurrentDb.OpenRecordsetCurrentDB.OpenRecordset("Select [data],[data2],...etc FROM Table2
WHERE [name] = " & listboxvalue, dbopendynaset)

Me.Text4 = rst.Fields("data1")

etc...

Might need to setfocus to the field you are changing, but that should allow you to set all the values according to the lookups.


DLookup might be another solution.

[austenr]

So you want to change the recordsource based on the value of the list box?

Can you bind the text fields to queries where the conditional parameter is the list box.selected value? What limitations are you finding if you have tried this?

If it needs to be in VBA, I would try something like this,


Dim rst as Recordset
Set rst = CurrentDB.OpenRecordset("Select [data],[data2],...etc FROM Table1
WHERE [name] = " & listboxvalue, dbopendynaset)

Me.text1 = rst.Fields("data1")
Me.text2 = rst.Fields("data2")
Me.text3 = rst.Fields("data3")

Set rst = CurrentDb.OpenRecordsetCurrentDB.OpenRecordset("Select [data],[data2],...etc FROM Table2
WHERE [name] = " & listboxvalue, dbopendynaset)

Me.Text4 = rst.Fields("data1")

etc...

Might need to setfocus to the field you are changing, but that should allow you to set all the values according to the lookups.


DLookup might be another solution.

Yes I can bind the textboxes.

[Imdabaum]

Is that a viable solution? Or do you need a different method? If it's not what you're looking for, I can try and come up with something else.

[austenr]

Been a long time since i wrote an Access Sub. Where does the code go? I tried it in a standard module but when I put the cursor in the sub and hit F5, I got a popup box asking what Sub to run and it didn't list the sub I constructed from your code as a macro.

Yes your solution would work if I could figure out how to run it.

[Imdabaum]

Sorry we're migrating our dbms and I've been in training all week.

Put it on the AfterUpdate Event. Once the dropdown box has been updated it will fire and adjust the bindings on the other controls.