dlookup function in a report 1

[axslearnr]

hi
i have three tables customer, product and vendor. i have a listbox which list all the products when i select the item and press command button it prints report.
in the report how to get the names of the customer 1& 2 and vendor 1 & 2 ? using sql query? i dont know how to use the dlook up function can anyone tell how to d it or the SQL query to retrieve.

customer
------
cust_id
cust_name

product
--------
product_id
product_name
cust-1_id
cust-2_id
vendor-1_id
vendor-2_id

vendor
-------

vendor_id
vendor_name

thanks

 

[BobJacksonNCI]

Suggest you perform an advanced search (see the top of this window) for examples. One thread that should come up is:
thread703-802900




HTH,
Bob

 

[dhookom]

Don't use the dlookup() function. Add the Customer and Vendor tables twice to a query with the Product table. You can then join the ID fields to you fields in the Product table. Use joins that select all of the records from the Product table.

Your base issue is that you haven't properly normalized your tables. I would not have either vendor or customer ids in the product table.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[axslearnr]

hi
thanks for the reply. i tried it did kind of all the records.since i select the product on the listbox.

I want to print the vendor name of the correspodning product on the report using the VBA code. how to do that?

With Reports!OOSReport2

.acHeader.OOSlbl.Caption = " & Me!Listctl.Column(0) & "
End With

its not working can you pls tell how to do?
thank you very much.

 

[axslearnr]

thank you very much

Can you please write the SQL query for that I will be very thankful to u i am not really very good at SQL.

or
else a way how to print vendor name on the report dynamically when the user selects the listbox item product.

 

[dhookom]

You don't have to write the sql. It's all drag and drop. I don't even write sql unless I have to. Just add the Products table and two copies each of the Customer and Vendor tables. Drag the Cust_ID from one table to cust-1_id and Cust_ID from the other to cust-2_id.

Double-click each join line to select the option to show all records from the Products table.

Do the same for Vendor tables.

Add name fields as needed to the grid for your report. When you drag the Cust_Name field to the grid, change the field expression to:
Cust_Name1:Cust_Name
or
Cust_Name2:Cust_Name

Do the same for the vendors.

AGAIN, your table structure is not correct so you will always have issues like this.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[axslearnr]

Thank you for your help. I really appreciate that. I have one more problem if u dont mind asking.

Whenever i select my list item and open the report and close it come back to the form the selected item is still there and when i press the command button by selecting another item it still opening up the same report .

cant i change deselect the item in the list box once i open the report i mean like clearing the selection and making a fresh list of item in it.

pls let me know

thank you

 

[dhookom]

You can set the On Close event of the report to use code like:
Forms!frmYourForm!lboMyList = Null

Duane
MS Access MVP
Find out how to get great answers faq219-2884.