Trouble with DLookup on Subform

[GregShah]

I have attached a simplified version of my DB with this post.



I have a table CID Base Price list. The CID is the primary key and is the item number of my structures. This table also has the base price of this item which gets adjusted quarterly based on the type of structure. I use a query called Currentprice to make this price adjustment for each CID.

The other tables involved are a table called Job which assigns a number to our project. Attached to the Job table is a table called PODetail. This lists the quantity and CIDT for whatever items my customer is ordering. I added the T to cid because I thought using the same name could be causing my grief.

Finally, I tried to create a form/subform which will allow me to open a job number and then enter the quantity, CID in a subform. I want the subform to lookup the current price in my query. I cannot get this function working. I listed four different formats I tried to know avail.

Can anyone see where I am going wrong?

Thanks for the effort,

Greg

[pbaldy]

Numerous problems, solutions include:

=DLookUp("[current price]","currentprice","cid='" & [Forms]![job]![podetail subform].[Form].[CIDT] & "'")

=DLookUp("[current price]","currentprice","cid='" & [CIDT] & "'")

[Gina Maylone]

I'm confused, where are the multiple prices supposed to come from? Why are their 4 price columns? Sorry if I'm missing something...

[GregShah]

Thank you. I could not find a good source for the proper formatting even though I looked on several sites. I appreciate your help. I was really getting discouraged.

Thanks again.

[pbaldy]

No problem. These would have helped:

DLookup Usage Samples

Forms Refer to Form and Subform properties and controls

[Gina Maylone]

No problem, PBaldy has the correct language for you. :-)

[Gina Maylone]

No problem, PBaldy has the correct language for you. :-)

Sorry just realized that wasn't for me!

[GregShah]

New issue with my form/subform. I used three different methods to add the CID to my subform. I need to select one method, either a combo box based on the table CID base price list or the query Currentprice. The third method was just entering the CID as a ten digit number. When I eliminate one or two of these fields, the current price no longer displays on my subform. Not sure why?

[pbaldy]

If having a combo based on the query is an option, I'd get rid of the DLookup's and use this:

BaldyWeb - Autofill

It's more efficient than repeated trips to the data.

[GregShah]

Even more confusion on my end. I deleted the four attempts I made to use dlookup and then save the form and subform. Now the current price is no longer displayed. I thought it was related only to the combo boxes I used to identify the CID. The other boxes all return errors. Why would deleting these derail the dlookup?

[pbaldy]

Hard to say without seeing the db, but presumably the DLookup referred to a deleted control or field. Did you consider the option in post 9?

[GregShah]

I considered but I need to use the current price in a calculated field to determine the qty*currentprice to verify the total on the PO. Not sure if this is possible using option in #9.

[pbaldy]

Normally I'd be saving the current price with the transaction, so dropping it into a bound field should allow it to be used in further calculations.

[Micron]

Difficult to know exactly what another person would want, but I played with this for some time. Check it out - too many changes to the forms to recall all of them. You'll see some controls are disabled to prevent user from changing source data, such a pricing. Generally, a datasheet is not appropriate for data input. If you want to keep close to what you had, you should be able to work with the attached. Note that I started by copying your two forms and renamed stuff, but couldn't keep it straight as to which I was looking at (the form captions threw me off) so I dumped the originals. The lookup is now
=DLookUp("[Base Price]","[CID Base Price List]","[CID] = '" & [Forms]![Job2]![PODetail Subform].[Form]![Combo6] & "'")
Hopefully you will pick up some hints for going forward by reviewing the design changes.

Test DB2.zip