Dlookup works in subform but not in Main Form

[GregShah]

I have a repair and compacted version of my DB that I am happy to share. What is the best way for me to share this with the forum?




Sorry for my lack of experience. I normally have worked out my details from reading. This ones been a challenge.

Greg

[Micron]

Click Go Advanced. Select 'Attachments' at the top right of the toolbar. You navigate to, then select the attachment and upload. There is a max file size that usually requires you to zip the db first.

[GregShah]

Test DB.zipTried to upload a version of my DB for you to see.

Not sure if I did this properly.

[GregShah]

It looks like my Test DB uploaded.

Quick explanation. Table CID Base Price List is the master list of every item along with the base price. Current Multipler is the quarterly price adjustment. Historic Multiplier tracks the changes by quarter. The Job table will be a list of active jobs. The PO Details is the list of the quantity, item number for the active jobs.

Only one query has a real purpose. This query adjusts the base price to the current quarterly price based on the multiplier. The other queries were a fail attempt to get my form to work.

My goal is to have a form/subform. The form will contain the info pertaining to the main job number. The subform will include quantity, a combo box to select the CID number and then the current pricing. If entered correctly, this form/sub will match my customr's PO. I can then track item quantities ordered over the course of the year, most ordered items, etc.

My help will be GREATLY appreciated.

Greg

[GregShah]

Here is a copy of my DB.

Greg

[Micron]

There is no subform in your posted db??

I created a form and subform. In the subform, I used dlookup to find the current price of the item. When I just open the subform alone, the current price is displayed accurately. However, when I open the main form, I get a error #Name? Everything else displays properly.

[GregShah]

I do not have a working version although I have tried several options. The only fields I am trying to include are the quantity, the CID number selected using a combo box and finally the current price extracted from the currentprice query. I could get the combo box to work but I could not tie the current price to the selected CID.

Greg

[GregShah]

I tried to use the dlookup function to pull in the current price. The first two arguments were =dlookup("current price",[currentprice],"[cid]= and this is where I got into trouble. I could not direct the function to pull the current price for the CID matching the CID selected in the combo box.

I kept deleting the subform when I could not get it to function correctly.

[RuralGuy]

It is almost always better to use an AutoNumber as your table's Primary Key (PK). If you do then Access can assist in creating your relationships and besides, an index of numeric fields runs faster than text ones. You can always make any field unique.

[GregShah]

Not a problem. Right now, I used the CID number as the primary key because it is a unique number. Changing the key to autonumber is not a problem.

[RuralGuy]

I think you will find that helps you design the database better.

[Micron]

Well, I'm not sure where this is going. It's one thing to ask for help with what you have. It's another thing to remove it then ask someone to create it for you, especially without a crystal clear understanding of the requirements. If you create your forms as best as you can, maybe we can come up with a solution. Start with representative data in your tables, and if the forms are to be based on queries instead of tables, ensure those queries return the data you expect. Then build the forms. You'll need to review your thread to ensure you've made it quite clear as to what you want to happen. Not saying you haven't, but this has gone onto a second forum page and one cannot review all the posts while composing an answer.

[GregShah]

Great Point. I keep trying to solve which keeps evolving my DB. I am going to open a new thread with a clean copy of my DB along with a form with four different dlookup versions, none of which are working for me. Please refer to Trouble with Dlook up in Subform.