Solved DLookup

[mike60smart]

Hi Can anyone see where my error is with the following DLookup?
Any Help appreciated.

=DLookup("LastofLableNumber", "qryLabelStartNumbers", "CustomerID = _
" & [Forms]![frmCustomerOrders]![frmOrderItemSizesSubform]![txtCustomerID] _
& " AND [CropID] = " & [Forms]![frmCustomerOrders]![frmOrderItemSizesSubform]![txtCropID] _
& " AND [VarietyID] = " & [Forms]![frmCustomerOrders]![frmOrderItemSizesSubform]![txtVarietyID] & ")

 

[Gasman]

You do not say what the issue is?

Why not put it into a string variable, then you debug.print it to see what is produced?
If you get it correct, you can then use it in the function. You could even copy and paste here if you still cannot see the error.

A quick look does not show anything immediately to me, so that is what I would do. However if using subforms, I do not see the Form keyword?
All values must exist and be numeric for the above to work?

Why the external form references as well?

You have been doing this long enough to know about this page.

Forms: Refer to Form and Subform properties and controls

 

[arnelgp]

=DLookup("LastofLableNumber", "qryLabelStartNumbers", "CustomerID = " _
& [Forms]![frmCustomerOrders]![frmOrderItemSizesSubform]![txtCustomerID] _
& " AND [CropID] = " & [Forms]![frmCustomerOrders]![frmOrderItemSizesSubform]![txtCropID] _
& " AND [VarietyID] = " & [Forms]![frmCustomerOrders]![frmOrderItemSizesSubform]![txtVarietyID])

 

[mike60smart]

=DLookup("LastofLableNumber", "qryLabelStartNumbers", "CustomerID = " _
& [Forms]![frmCustomerOrders]![frmOrderItemSizesSubform]![txtCustomerID] _
& " AND [CropID] = " & [Forms]![frmCustomerOrders]![frmOrderItemSizesSubform]![txtCropID] _
& " AND [VarietyID] = " & [Forms]![frmCustomerOrders]![frmOrderItemSizesSubform]![txtVarietyID])

Hi arnelgp
Your suggestion gives me the following error which is the same as my original?:-

 

[mike60smart]

Hi Everyone

I changed the Lookup to the following and now just get a flashing #error in the unbound Control. Very strange.

=DLookUp("LableNumber","tblOrderItemSizes","CustomerID = " & [CustomerID])

 

[LarryE]

Hi Can anyone see where my error is with the following DLookup?
Any Help appreciated.

=DLookup("LastofLableNumber", "qryLabelStartNumbers", "CustomerID = _
" & [Forms]![frmCustomerOrders]![frmOrderItemSizesSubform]![txtCustomerID] _
& " AND [CropID] = " & [Forms]![frmCustomerOrders]![frmOrderItemSizesSubform]![txtCropID] _
& " AND [VarietyID] = " & [Forms]![frmCustomerOrders]![frmOrderItemSizesSubform]![txtVarietyID] & ")

Try:
DLookup("LastofLableNumber", "qryLabelStartNumbers", "[CustomerID] = ' " & [Forms]![frmCustomerOrders]![frmOrderItemSizesSubform]![txtCustomerID] & " AND [CropID] = ' " & [Forms]![frmCustomerOrders]![frmOrderItemSizesSubform]![txtCropID] & " AND [VarietyID] = ' " & [Forms]![frmCustomerOrders]![frmOrderItemSizesSubform]![txtVarietyID] & " ")

I placed spaces between the single and double quotes to make it easier to read

And are you sure "LastofLableNumber" is correct? Should it be "LastofLabelNumber"?

And should you use Me.Code=?

 

[Gasman]

Hi Everyone

I changed the Lookup to the following and now just get a flashing #error in the unbound Control. Very strange.

=DLookUp("LableNumber","tblOrderItemSizes","CustomerID = " & [CustomerID])

Not really, I would have thought you would refer to the Control, otherwise how does access know which one you are referring to?, the field or the control?

 

[XPS35]

It seems you are referring to a control on a subform (Forms]![frmCustomerOrders]![frmOrderItemSizesSubform]![txtCustomerID). That might be your problem. On a (continues?) subform there can be multiple such control. So which one are you refering to?
Without further context (where and how is de DLookup used? ) it is hard to give a solution.

 

[mike60smart]

Hi Everyone

I changed the Lookup to the following and now just get a flashing #error in the unbound Control. Very strange.

=DLookUp("LableNumber","tblOrderItemSizes","CustomerID = " & [CustomerID])

Well I just eliminated one error on my part I had spelt a name wrong.

The following now works in the Continuous Form where I want to display the value

=DLookUp("LabelNumber","tblOrderItemSizes","CustomerID = " & [txtCustomerID])

However when I change it to include the and elements I get the flashing error?

=DLookup("LabelNumber", "tblOrderItemSizes", "CustomerID = " & [txtCustomerID] & " AND CropID = " & [txtCropID] & " AND [VarietyID] =" & [txtVarietyID])

It seems you are referring to a control on a subform (Forms]![frmCustomerOrders]![frmOrderItemSizesSubform]![txtCustomerID). That might be your problem. On a (continues?) subform there can be multiple such control. So which one are you refering to?
Without further context (where and how is de DLookup used? ) it is hard to give a solution.

Hi Peter
I am always getting told off for not giving a full description of my problem.

I have a Main Form/Subform1/Subform2 settup where:-

The Main Form allows the user to enter a Job Date and a Job Number (CustomersOrderT)

Subform 1 SingleForm View with Data Entry set to Yes (OrderItemsT)
allows the user to select a Crop and a Variety

Subform 2 Continuous Form View (tblOrderItemSizes)
allows the user to select a Customer and enter a Quantity of the specified Crop and Variety together with the Start Label Number. ie 50050
For this specific record there is a Command Button that generates Labels - if the quantity ordered is 10 then there are 10 labels generated numbered 5005 to 5059.

What I am trying to do is display on Subform 2 the last Label Number used where the CustomerID, CropID and VarietyID match

Hope this is a bit clearer?

 

[LarryE]

Try using DMax if you want the largest LabelNumber and you also need to use single quotes in certain places when referring to field names where AND is used.
=DMax("LabelNumber", "tblOrderItemSizes", "CustomerID =' " & [txtCustomerID] & " AND CropID =' " & [txtCropID] & " AND [VarietyID] =' " & [txtVarietyID])

 

[mike60smart]

Try using DMax if you want the largest LabelNumber and you also need to use single quotes in certain places when referring to field names where AND is used.
=DMax("LabelNumber", "tblOrderItemSizes", "CustomerID =' " & [txtCustomerID] & " AND CropID =' " & [txtCropID] & " AND [VarietyID] =' " & [txtVarietyID])

Sorry but single quotes are only needed when referencing Text Data Types. All of these fields are numeric.

 

[gemma-the-husky]

@mike60smart

=DLookup("LastofLableNumber", "qryLabelStartNumbers", "CustomerID = _
" & [Forms]![frmCustomerOrders]![frmOrderItemSizesSubform]![txtCustomerID] _
& " AND [CropID] = " & [Forms]![frmCustomerOrders]![frmOrderItemSizesSubform]![txtCropID] _
& " AND [VarietyID] = " & [Forms]![frmCustomerOrders]![frmOrderItemSizesSubform]![txtVarietyID] & ")

I may be missing something but the continuations at the end of each line look strange to me. I normally have & _ , not the other way round.
I also would have a closing "on the first line, and not have the &"" at the end of the entire statement.

So this variation compiled syntactically for me

MsgBox DLookup("LastofLableNumber", "qryLabelStartNumbers", "CustomerID = " & _
[Forms]![frmCustomerOrders]![frmOrderItemSizesSubform]![txtCustomerID] & _
" AND [CropID] = " & [Forms]![frmCustomerOrders]![frmOrderItemSizesSubform]![txtCropID] & _
" AND [VarietyID] = " & [Forms]![frmCustomerOrders]![frmOrderItemSizesSubform]![txtVarietyID])

 

[LarryE]

Sorry but single quotes are only needed when referencing Text Data Types. All of these fields are numeric.

Yes of course. I was just going to correct that after thinking about it.
=DMax("LabelNumber", "tblOrderItemSizes", "CustomerID =" & [txtCustomerID] & " AND CropID =" & [txtCropID] & " AND [VarietyID] =" & [txtVarietyID])

 

[Gasman]

Well as you cannot be bothered to debug.print it, I'll step away.
Probably your references are all wrong.

 

[mike60smart]

@mike60smart



I may be missing something but the continuations at the end of each line look strange to me. I normally have & _ , not the other way round.
I also would have a closing "on the first line, and not have the &"" at the end of the entire statement.

So this variation compiled syntactically for me

MsgBox DLookup("LastofLableNumber", "qryLabelStartNumbers", "CustomerID = " & _
[Forms]![frmCustomerOrders]![frmOrderItemSizesSubform]![txtCustomerID] & _
" AND [CropID] = " & [Forms]![frmCustomerOrders]![frmOrderItemSizesSubform]![txtCropID] & _
" AND [VarietyID] = " & [Forms]![frmCustomerOrders]![frmOrderItemSizesSubform]![txtVarietyID])

Hi Dave
That works perfectly.
I have moved it from a DLookup in an Unbound Control to the AfterUpdate of the cboCustomer.

Many thanks to everyone for all of the posts

 

[Pat Hartman]

Although having the calculation as the control source seems convenient, it actually wasted a lot of your time debugging as did the refusal to use debug.print to print the string to determine where the syntax errors were.

Now that you have moved the expression to an event, take the final step to put the expression in a variable so in the future, you understand how to debug these expressions.

And finally, when you are referencing controls on a subform, you can only address the CURRENT record of the subform this way so you may not actually be getting the correct answer in all situations.

 

[mike60smart]

Pat I did not knowingly refuse to do a Debug.Print

My old brain was just focused on using a DLookup until Dave gave me the correct method.

 

[Pat Hartman]

Gasman answered your post 4 minutes after you made it. Probably a record for a Sunday I think it is almost seven hours later. I don't know if a debug.print would have helped you at that time but. Anyway, I wasn't involved earlier so I have no skin in this. I was merely suggesting that for future reference, when you have to create complicated strings, everything is so much easier if you create them into a variable so you can print the results to more easily find the errors

You should also take my comment on referencing data on subforms to heart. Do you ALWAYS know what record is current? I doubt it, so be careful.

 

[mike60smart]

Gasman answered your post 4 minutes after you made it. Probably a record for a Sunday I think it is almost seven hours later. I don't know if a debug.print would have helped you at that time but. Anyway, I wasn't involved earlier so I have no skin in this. I was merely suggesting that for future reference, when you have to create complicated strings, everything is so much easier if you create them into a variable so you can print the results to more easily find the errors

You should also take my comment on referencing data on subforms to heart. Do you ALWAYS know what record is current? I doubt it, so be careful.

Pat
Thanks for the comments but in this specific case the Op is only doing Data Input for a New Record.

 

[Pat Hartman]

Yes BUT, if the code is in the main form, there is no subform record when the main form code runs the first time because you cannot create a subform record until the mainform record (parent) is created.