Dlookup multiple tables and set a text box to access 2007 results

Question

I will try to deal with my problem as best as possible and explain what I'm trying to achieve. Firstly, I have three tables:

**RFI** (stands for Request For Information)- Fields: rfi_id, Customer_id ..... **RFI_project** - Fields: rfipro_id, project_id, rfi_id *"....." represents other unnecessary fields* **Customer** - Fields: Customer_id, company ..... 

I have an access form with two comboboxes. In the first combo box, I select the name of the project in which the second text field changes to show these * rfi_id * where there is a match with the selected project name. Now I'm trying to do this - when I select * rfi_id * in the second combobox, I want it to appear in the text box in my form, where the value * rfi_id * matches the value in the combobox. This is a bit complicated due to the way the tables join ... this is what I'm essentially trying to display in a text field field in terms of SQL:

 SELECT Customer.company, RFI.Customer_id FROM Customer, RFI WHERE (((Customer.Customer_id)=[RFI].[Customer_id]) AND ((RFI.rfi_id)=[Forms]![Request for Info Form]![Combo90])) ORDER BY Customer.company; 

To do this, I tried the following to no avail. In the event after updating my second combobox, I inserted the following:

 companyTB = DLookup("company", "Customer", "Customer_id =" & DLookup("Customer_id", "RFI" And "rfi_id =" & [Forms]![Request for Info Form]![cmbRFI])) 

When I change the value of combobox, I get the error Runtime Error '13': Type of mismatch. I tried looking for what I did wrong, but apparently this is a very big mistake, and I can not find anything like it (or what I understand). I also tried this -

 companyTB = DLookup("company", "Customer", "Customer_id =" & DLookup("Customer_id", "RFI", "rfi_id =" & cmbRFI)) 

which gives me the following error: runtime error "3075": syntax error (missing statement) in the query expression. Anyway, would someone be nice to give me a breakdown of what I need to do to achieve this, or what I'm doing wrong (or maybe the best way to do this?). Forgive me for seeming stupid, I just started working with more in-depth access in the last 3 weeks or so. Thanks.

Answer 1

Your first DLookUp has the wrong syntax:

 companyTB = DLookup("company", "Customer", "Customer_id =" & DLookup("Customer_id", "RFI" And "rfi_id =" & [Forms]![Request for Info Form]![cmbRFI])) 

I broke it into three lines to make it easier to see. The second line has AND between "RFI" and "rfi_id" when it should have a comma.

 companyTB = DLookup("company", "Customer", "Customer_id =" & DLookup("Customer_id", "RFI", "rfi_id =" & cmbRFI)) 

The error you get in the second combo seems to be related to the result returned by cmbRFI. You can verify this by filling in the actual rfi_id, not the combo link, and setting the text box to cmbRFI and see what it returns. Combo can be tricky because the displayed column and the related column can be different.

It may be convenient to customize your combobox with multiple columns as shown in your query, so the row source could be:

 SELECT rfi.ID, Customer.company, RFI.Customer_id FROM Customer INNER JOIN RFI ON Customer.Customer_id=RFI.Customer_id ORDER BY Customer.company; 

(or three tables, if necessary),

Then

 Column count = 3 Column widths = 2cm;0;0 Bound column = 1 

Now you can access the second column in your text box:

 = cmbRFI.Column(1) 

Columns are numbered from scratch.

Before working with Access, you should always read in sql:

Fundamental Microsoft Jet SQL for Access 2000
Intermediate Microsoft Jet SQL for Access 2000
Advanced Microsoft Jet SQL for Access 2000

Answer 2

Your last Dlookup statement seems absolutely perfect, so I'm a little confused why it doesn't work, you can work around a problem like this:

 Combox2_AfterUpdate (Or whatever the event is called) Dim rs As Recordset Set rs = Currentdb.OpenRecordset("SELECT C.Company, R.Customer_ID " & _ "FROM Customer As C, RFI As R " & _ "WHERE C.Customer_ID = R.Customer_ID " & _ "AND R.RFI_ID =" & [Forms]![Request for Info Form]![Combo90] & " " & _ "ORDER BY C.Company") CompanyTB = rs!Company rs.Close Set rs = Nothing End Sub