[Solved] MS Access dlookup Equivalent??
Posted: Mon Mar 08, 2010 7:03 pm
Hi everyone, I have a very MS Access like question where I wish I could do the same on oooBase. So here how it goes.
I have a form to do an invoice, linked to a table with lots of textboxes, like this.
Customer name (Combo box in MSA, Listbox in Base) >> to select the customer
Name (txtbox)
Address (txtbox)
Telephone (txtbox)
etc..
Now in MSA , i would select a customer name in the combobox(where the bound field is the customer code) , after the customer has been selected, I would write some VBA to
1.dlookup the Address, telephone and whatever I need and then
2.set the value of the textbox to the Address, telephone , i have "dlook"ed up
pretty easy in MSA
Some might be asking why I would want to store all the data that could be queried from the Customer's table anyway. The reason is that sometimes customers change their names, address, telephone, and I want to be able to look at the old invoices and see the history of changes in the invoices.
Now in Base, to do the same thing in MSA, this is what I do
1.In the same form , other than the textboxes above, here is how my form looks like
Customer name (Listbox in Base)
Name (txtbox) LookupName(listbox : SELECT Name, CustomerCode FROM tblCustomers , bound field =1 (CustomerCode)
Address (txtbox) LookupAddress(listbox : SELECT Address, CustomerCode FROM tblCustomers , bound field =1 (CustomerCode)
Telephone (txtbox) LookupTel(listbox : SELECT Telephone, CustomerCode FROM tblCustomers , bound field =1 (CustomerCode)
etc.
So now all of the "Lookup" listboxes now have the value of the Name, Address, Telephone that I need to store into the textboxes
2. Then I write a macro so that after the Customer name Listbox(the top one) is selected, I set the values of the textboxes = corresponding "Lookup "Listboxes.
Actually what i'm doing is instead of doing the dlookup in VBA, I'm making a listbox to "lookup" the values and the put them in the textboxes.
Which does the work fine.
But,
Is there a better way to do this? I mean something in a macro in the fashion of
running a SQL query to get the values i need , then set the textbox value
all in code?
Thanks for all help!
Ponlerd
I have a form to do an invoice, linked to a table with lots of textboxes, like this.
Customer name (Combo box in MSA, Listbox in Base) >> to select the customer
Name (txtbox)
Address (txtbox)
Telephone (txtbox)
etc..
Now in MSA , i would select a customer name in the combobox(where the bound field is the customer code) , after the customer has been selected, I would write some VBA to
1.dlookup the Address, telephone and whatever I need and then
2.set the value of the textbox to the Address, telephone , i have "dlook"ed up
pretty easy in MSA
Some might be asking why I would want to store all the data that could be queried from the Customer's table anyway. The reason is that sometimes customers change their names, address, telephone, and I want to be able to look at the old invoices and see the history of changes in the invoices.
Now in Base, to do the same thing in MSA, this is what I do
1.In the same form , other than the textboxes above, here is how my form looks like
Customer name (Listbox in Base)
Name (txtbox) LookupName(listbox : SELECT Name, CustomerCode FROM tblCustomers , bound field =1 (CustomerCode)
Address (txtbox) LookupAddress(listbox : SELECT Address, CustomerCode FROM tblCustomers , bound field =1 (CustomerCode)
Telephone (txtbox) LookupTel(listbox : SELECT Telephone, CustomerCode FROM tblCustomers , bound field =1 (CustomerCode)
etc.
So now all of the "Lookup" listboxes now have the value of the Name, Address, Telephone that I need to store into the textboxes
2. Then I write a macro so that after the Customer name Listbox(the top one) is selected, I set the values of the textboxes = corresponding "Lookup "Listboxes.
Actually what i'm doing is instead of doing the dlookup in VBA, I'm making a listbox to "lookup" the values and the put them in the textboxes.
Which does the work fine.
But,
Is there a better way to do this? I mean something in a macro in the fashion of
running a SQL query to get the values i need , then set the textbox value
all in code?
Thanks for all help!
Ponlerd