DLookup Question

[AandH]

Hello. I am using the DLookup function on my form to pull data from a table in the database, however, the data that is pulls does not show up on the databases "main table" from the form. How can I make the DLookup data show up on the main table?

Thanks!

 

[TheAceMan1]

How are ya AandH . . .

[blue]however, the data that is pulls does not show up on the databases "main table" from the form.[/blue]

You can't lookup data where it doesn't exist!. You'll have to be more specific about what yout after. Also, post what you've tried!

BTW: Welcome to [blue]Tek-Tips![/blue] . . . Be sure to go over one of the links at the bottom of my post . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[AandH]

Thanks for your response TheAceMan1...below is what I have in a field on the dispatch orders form to lookup the city of the Shipper that I select form a combo box that I have on the form. I have two tables in my database..Shipper and Dispatch Orders. Is there anyway to get this data that I am pulling from the shipper table to show up in my dispatch orders table that is pulling information from the form that I created that has this DLookup function in it? Thanks!

=DLookUp("[City]","[Shipper]","Shipper=" & "[Shipper1]")

 

[genomon]

You could code it into an SQL INSERT or UPDATE statement and use the DoCmd object to perform the operation on your table.

"Teamwork means never having to take all the blame yourself."

 

[AandH]

Hello genoman,
I would love to try the SQL insert or UPDATE statement, however, I am new to access and I am not where to put the code...do I put it in the DLookup statement or do I put it in the dispatch table design itself?

Thanks!

 

[genomon]

Neither.

Dim strSQL As String
strSQL = "INSERT somefield INTO sometable VALUES("
strSQL = strSQL & somevalue & ")"
DoCmd.RunSQL strSQL

This code can go into any event that works for you. If you don't understand events, you should take a look at MS help first...

"Teamwork means never having to take all the blame yourself."

 

[AandH]

Thanks, if I am understanding you correctly, I can put this code in one of the events for the field in my form that has the DLookup function in it? So lets say in the "On Update" event, this code will run and input the data into the table I specify in the code?

 

[ProgramError]

try

=DLookUp("[City]","[Shipper]","Shipper ='" & "[Shipper1]" & "'")



Ian Mayor (UK)
Program Error
Your lack of planning is not my emergency!

 

[AandH]

Hello...I replaced my DLookup with the DLookup you supplied and the function does not work anymore. I am still at a loss on how to make the DLookup data show up on my main forms table.

thanks!

 

[ProgramError]

DLookup("field", "table", "comparefield = '" & Me.txtbox & "'")

this is the syntax for dlookup. Note the single speech marks which change anynumeric value in the textbox to a string. You will not need then if its a string in the textbox.

But after reading more of the thread I don't think this will produce what you need and I may have misled you.

What you need to do is filter the opened main form based on the value in the combobox. Put
=forms!nameofcomboboxform.comboboxname
in the filter property of the main form.

This may also interest you.

http://www.datapigtechnologies.com/flashfiles/buildcustomfilter.html

Ian Mayor (UK)
Program Error
Your lack of planning is not my emergency!

 

[AandH]

Hello Program Error...thanks you for your suggestions...I was able to get it working!I really appreciate your help.
The datapigtechnologies link also helped me out greatly.