Goal is to update a list box when I scan through the records via record navigation controls.
To list all types of accounts I have at a bank given the unique id of the bank (Bank_ID)
so what I have is a tab control with 4 tabs on a form.
The 4th tab is a subform that is bound to a table4 - Bank_ID is fk
One field on the 4th tab is a primary key to table3 bound to 3rd tab - Bank_ID (is a pk)
Now on the 4th tab as I scan the records, the bank_id changes.
What I would like to display on 4th tab is the bank name, city and state found in table3 bound to tab3.
To achieve that I created a bound text box with the following control source:
Code:
=(select bank_name from tbl_Owner_Bank_Info where Bank_ID= [Bank_ID] )
This results in #Name? in this text box. I tried various forms of this select with no luck.
If I use a Dlookup it works fine....
Code:
=DLookUp("[Bank_Name]","[tbl_Owner_Bank_Info]","[Bank_ID]=" & [Bank_ID]) & ", " & DLookUp("[Bank_Details_City]","[tbl_Owner_Bank_Info]","[Bank_ID]=" & [Bank_ID]) & ", " & DLookUp("[Bank_Details_State]","[tbl_Owner_Bank_Info]","[Bank_ID]=" & [Bank_ID])
and it updates the values in the text box as I scan through the records of bank accounts at various banks....but it's noticably slow...so I'd prefer to use the sql option (bound box or LBox) or some other method.
Now on to the list box....
So how can I achieve the functionality of the DLookup but show it in a listbox.
I have played around with LB and various forms of the sql and events and got it to display the correct data but NOT refresh automatically as I scan through the records.
It's the refresh that is holding me up...
I tried adding an on change event for the "Bank_ID" field - hoping that as I scan the records this will trigger a change in it's value and refresh the LB.
Code:
Private Sub Bank_ID_Change()
Set List18 = [Bank_ID]
List18.Requery
End Sub
any suggestions how I can cause the LB to refresh?
thanks