[Solved] MS Access dlookup Equivalent??

Creating and using forms
Post Reply
ponlerd
Posts: 56
Joined: Sun Nov 15, 2009 6:22 pm
Location: Bangkok, Thailand

[Solved] MS Access dlookup Equivalent??

Post by ponlerd »

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
Last edited by ponlerd on Mon Mar 22, 2010 5:00 pm, edited 1 time in total.
OOo 3.2 Ubuntu 9.04 / OOo 3.2 Windows7 / Migrating from Access
ponlerd
Posts: 56
Joined: Sun Nov 15, 2009 6:22 pm
Location: Bangkok, Thailand

Re: MS ACCESS dlookup Equivalent??

Post by ponlerd »

Sorry for the long question, next time i'm going to keep it short
But now I got a solution that works..

For MS Access users
From SQL statement
SELECT fieldname FROM tablename WHERE statement

converts into

lookup = dlookup("fieldname","tablename","statement") one line and you're done

in openoffice base, you do the same thing but with a lot of lines

Code: Select all

dim Form
Form = ThisComponent.DrawPage.Forms.getByIndex(0)

'Get the active connection to create a statement object
dim Statement
Statement=Form.ActiveConnection.createStatement()

MyQuery = Statement.ExecuteQuery("SELECT fieldname FROM tablename WHERE statement")
'Get result from query
dim lookup
MyQuery.Next()  
lookup = MyQuery.GetString(1)

End Sub
I'm sure someone can come up with a general purpose function , but I prefer it this way, because i keep forgetting how the function works.
OOo 3.2 Ubuntu 9.04 / OOo 3.2 Windows7 / Migrating from Access
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: [Solved] MS Access dlookup Equivalent??

Post by RPG »

Hello

Your description of dlookup makes that I must think to code I had made for me own. I did used the same method as you. But the last time I did have study also the combobox and the service of the combobox. And this makes I think there is a better method.
I do use now this sub when record is changed in a subform. It is also important to know that the controls are in a gridcontrol.
Maybe there is better event to use. When you activated the code in an other way then the code must maybe a little adjust.

The combobox in a gridcontrol seemes to have little different properties then the combobox outside a gridcontrol. In this case the enable properties let you protec the field for changing.

For people who need information for SQL is this link

Code: Select all

sub dLookup(oEv as object)
dim oForm
dim oId,oGrid,oCombobox
oForm=oEv.source
oGrid=oForm.getbyname("Tabelobject 1")
oId=oGrid.getbyname("NumericField2")

oCombobox=oGrid.getbyname("ComboBox")
oCombobox.ListSource="SELECT distinct ""Naam"" FROM ""client"" WHERE ""ID"" = " & oId.value 
oCombobox.refresh
oCombobox.Text=oCombobox.StringItemList(0)
oCombobox.commit
end sub
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] MS Access dlookup Equivalent??

Post by Villeroy »

lookup = dlookup("fieldname","tablename","statement") one line and you're done

in openoffice base, you do the same thing but with a lot of lines
You can use the full arsenal of spreadsheet functions, including VLOOKUP.
Method callFunction of Service c.s.s.sheet.FunctionAccess supports input of data arrays instead of cell ranges.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
ponlerd
Posts: 56
Joined: Sun Nov 15, 2009 6:22 pm
Location: Bangkok, Thailand

Re: [Solved] MS Access dlookup Equivalent??

Post by ponlerd »

Hi, i really want to thank for the contribution of everyone on the forum, maybe especially RPG and Villeroy(I see you two all over the place).
For the dlookup code that i'm using , i got part of it from Roberto Benitez, and a bit from somewhere else (sorry don't remember). At the moment I still cannot build a code by myself.... but i'm trying hard. Actually, i have a strong passion for opensource and i really want to post a lot of how tos for ms access users that want to migrate. So they wouldn't have to google for days like me........

Your method of using the combobox is also a good idea, instead of looking up the value, then update a textbox, using a combobox can be more efficient, i'll give it a try.

For Villeroy's method, i'll also try, but i need to admit that my knowledge of the openoffice suite macro programming is still very little, sometimes i still can't follow your solutions without researching a lot, but i really thank you anyways for the directions you give to everyone.
OOo 3.2 Ubuntu 9.04 / OOo 3.2 Windows7 / Migrating from Access
Post Reply