DLookup on subform (Access 2000 SP2)

Topic

New Reply

I’m trying to pull in a customer item from another table to match with our item on a subform. Problem #1 – we need the subform to have the ability to add new records, so joining the two tables doesn’t work. Problem #2, if I try a DLookup, it works fine in the subform, but when opening the main form/subform, the field where the customer item number is flashes “#Error” Here’s the lookup:

DLookUp(“CustomerItemNumber”,”T_CustomerItem”,”[CustomerID] = Forms!F_MickeyTest!CustID AND [ItemNumber] = FormsMickeyTest.Item “)

I’m linking the mainform/subform fields as CustID;SalesRep;StartDate. Both the CustID and IItem are text fields that match the CustomerID and ItemNumber. Any suggestions?

Reply | Quote

Replies

A subform is not part of the Forms collection. You must always refer to a subform through its parent form (the main form). Say that the parent form is named frmMain. The expression would become:

=DLookUp(“CustomerItemNumber”,”T_CustomerItem”,”[CustomerID] = Forms!frmMain!F_MickeyTest!CustID AND [ItemNumber] = Forms!frmMain!F_MickeyTest!Item”)

(I assume that the omission of ! and F_ between Forms and MickeyTest in the last part was just a typo.)

Reply | Quote

Hans, thanks for responding so quickly and yes, the missing ! was a typo as a result of trying to disguise the real name of the field with my usual “mickey” references.

I tried referencing the main form for the CustId, but unfortunately, the Item is not available on the main form, as the usage is to set up the main customer information and then put in the detail (sort of like the Orders on Northwind). Everything was working fine until the end user requested this additional field. That’s why I was hoping a DLookup would work because otherwise I fear I’ll be querying myself to death trying to anticipate how items will be entered for a particular customer.

Reply | Quote

You don’t need CustID on the main form. If you look closely at the expression I posted, you will see that it refers to CustID on the subform, but through the main form:

Forms!frmMain!F_MickeyTest!CustID

Reply | Quote

Sorry, Hans – I guess either I need another or a couple more hours of . I’ll try this and let you know how it goes.

Reply | Quote

Sorry, Hans – I guess either I need another or a couple more hours of . I’ll try this and let you know how it goes.

Reply | Quote

Sorry, Hans, this didn’t work. I still got the “#error” message. The odd thing is when I tried to do a update query on the customer item, I got the “use updateable query” message until I manually imported the table, which is residing in a SQL database. Importing the table sadly did not improve the DLookup() problem. We don’t want to be importing this table every time the user opens the application, so I’d like the DLookup() to work but am unsure what to do next. The following is the real code:

DLookUp(“CustomerItemNumber”,”dbo_ZNW_CustItem”,”[CustomerID] = Forms!F_CustQuotesNew!F_CustQuotesNewSubTest!CustID AND [ItemNumber] = Forms!F_CustQuotesNew!F_CustQuotesNewSubTest!Item”)

Maybe I need quotes around the fields or something?

Reply | Quote

Is F_CustQuotesNewSubTest the name of the form ?
You have to use the name of the subform control and not the name of the subform. These are not necessary the same.

Reply | Quote

Is F_CustQuotesNewSubTest the name of the form ?
You have to use the name of the subform control and not the name of the subform. These are not necessary the same.

Reply | Quote

Francois has a good point – you MUST use the name of the subform as a control on the main form; check by opening the main form in design view and clicking once on the subform (not twice!), then looking at the Name property in the Other tab of the Properties window.

If that doesn’t help, try this:

=DLookUp(“CustomerItemNumber”,”dbo_ZNW_CustItem”,”[CustomerID] = ” & Chr(34) & Forms!F_CustQuotesNew!F_CustQuotesNewSubTest!CustID & Chr(34) & ” AND [ItemNumber] = ” & Chr(34) & Forms!F_CustQuotesNew!F_CustQuotesNewSubTest!Item & Chr(34))

again with the name of the subform as a control on the main form.

Reply | Quote

To Hans and Francois,

It finally worked! After reading your responses a few hundred times I finally noticed that in my attempts to test out the subform, the Dlookup was looking for the name of the subform (F_CustQuotesNewSub) and I had the reference to the subform as F_CustQuotesNewSubTest . So I renamed the F_CustQuotesNewSubTest form to F_CustQuotesNewSub, and when I opened up the main form, all the customer items showed up. Boy, do I feel silly. Anyway, thanks so much for your quick replies (and patience).

Reply | Quote

To Hans and Francois,

It finally worked! After reading your responses a few hundred times I finally noticed that in my attempts to test out the subform, the Dlookup was looking for the name of the subform (F_CustQuotesNewSub) and I had the reference to the subform as F_CustQuotesNewSubTest . So I renamed the F_CustQuotesNewSubTest form to F_CustQuotesNewSub, and when I opened up the main form, all the customer items showed up. Boy, do I feel silly. Anyway, thanks so much for your quick replies (and patience).

Reply | Quote

Francois has a good point – you MUST use the name of the subform as a control on the main form; check by opening the main form in design view and clicking once on the subform (not twice!), then looking at the Name property in the Other tab of the Properties window.

If that doesn’t help, try this:

=DLookUp(“CustomerItemNumber”,”dbo_ZNW_CustItem”,”[CustomerID] = ” & Chr(34) & Forms!F_CustQuotesNew!F_CustQuotesNewSubTest!CustID & Chr(34) & ” AND [ItemNumber] = ” & Chr(34) & Forms!F_CustQuotesNew!F_CustQuotesNewSubTest!Item & Chr(34))

again with the name of the subform as a control on the main form.

Reply | Quote

Sorry, Hans, this didn’t work. I still got the “#error” message. The odd thing is when I tried to do a update query on the customer item, I got the “use updateable query” message until I manually imported the table, which is residing in a SQL database. Importing the table sadly did not improve the DLookup() problem. We don’t want to be importing this table every time the user opens the application, so I’d like the DLookup() to work but am unsure what to do next. The following is the real code:

DLookUp(“CustomerItemNumber”,”dbo_ZNW_CustItem”,”[CustomerID] = Forms!F_CustQuotesNew!F_CustQuotesNewSubTest!CustID AND [ItemNumber] = Forms!F_CustQuotesNew!F_CustQuotesNewSubTest!Item”)

Maybe I need quotes around the fields or something?

Reply | Quote

You don’t need CustID on the main form. If you look closely at the expression I posted, you will see that it refers to CustID on the subform, but through the main form:

Forms!frmMain!F_MickeyTest!CustID

Reply | Quote

Hans, thanks for responding so quickly and yes, the missing ! was a typo as a result of trying to disguise the real name of the field with my usual “mickey” references.

I tried referencing the main form for the CustId, but unfortunately, the Item is not available on the main form, as the usage is to set up the main customer information and then put in the detail (sort of like the Orders on Northwind). Everything was working fine until the end user requested this additional field. That’s why I was hoping a DLookup would work because otherwise I fear I’ll be querying myself to death trying to anticipate how items will be entered for a particular customer.

Reply | Quote