dlookup problems

[Guest_imported]

Hello, Im trying to use the dlookup function in a query. have got it working good when there is only one criteria eg, Supplier Part Name: DLookUp("[fldSupplierPartName]","tblSupplierParts","fldProductID =" & [tblOrdersAndQuantitys]![fldProductID])
Works fine
However i want the lookup to take into account another critera in tblSupplierParts. I want dlookup to lookup
the value from a textbox in a form as an And condition. i have tried using the following method.
Supplier Part Name: DLookUp("[fldSupplierPartName]","tblSupplierParts","fldProductID =" & [tblOrdersAndQuantitys]![fldProductID] And "fldSupplierID=" & [Forms]![frmPartOrder]![fldSupplierID])With this code it isn't producting the correct output. im just wondering if the way i have added this extra criteria appears correct.
Any suggestions greatly appreciated, Thanks.

 

[hsitz]

You need to move one of the quotes from after the 'AND' to before it, like so:

DLookUp("[fldSupplierPartName]","tblSupplierParts","fldProductID =" & [tblOrdersAndQuantitys]![fldProductID] " And fldSupplierID=" & [Forms]![frmPartOrder]![fldSupplierID])

Quotes are picky. If you don't understand why it wasn't working after seeing this fix, please let me know. -- Herb

 

[Guest_imported]

Thanks for the reply Herb. I have entered the method you recommended and i keep getting an error message saying invalid syntax. i have tried changing things around abit and still no luck.

 

[hsitz]

Very sorry, left out an ampersand:

DLookUp("[fldSupplierPartName]","tblSupplierParts","fldProductID =" & [tblOrdersAndQuantitys]![fldProductID] & " And fldSupplierID=" & [Forms]![frmPartOrder]![fldSupplierID])

The only other thing I can see that looks strange is the reference to [tblOrdersAndQuantitys]![fldProductID].

Are you sure that works in the first version you posted?

Typically, you reference controls on forms or fields in a form's recordset with something like the syntax:

forms![formname]![fieldname]

Is there some reason you're not referencing fldProductID that way? -- Herb

 

[Guest_imported]

Thanks for all the help mate. it works perfect now!
I think ill have to change the reference so that fldProductID refers to the actual form.
Once again Thanks!!!

 

[Guest_imported]

I am using DLookUp in Access 97. I have a combo box which links to a set of values in another table. Upon selecting a value from this field, I want another field within the form to be automatically updated. I have used DLookUp to achieve this and it is 50% working.

When I open the form with all fields blank and select a value from the combo box, the field with the DLookUp command updates. However, despite changing the values in the combo box, it will not update again.

Is this a limitation of the DLookUp statement? Is there another construct I could use?

SYNTAX:
=DLookUp"[Company_ProcessesEmployed]","[Company_Details]","[Company_ID] = Forms![Company_Link]![Company_ID]&quot

 

[ray00]

John:
Where did you put this Dlookup code? You need to have it 'On change' and 'after update' of the combo box event too. I use this type a lot to show records in a form based on a value selected in the combo box. in Access 2000, when I create the combo box with a wizard, it asks that do I want to look up records based on the values in the combo box. (obviously, the form has to be bound to some table/query). If I say 'yes', it creates the event automatically.

 

[Guest_imported]

I put the DLookUp code on the Field I want updated, as the "ControlSource" property. Like I said, upon the first selection of a value in the combo box above, the value in the field changes to the correct one. After this, no more selections within the combo box will cause the value in the desired field to change.

Do I need to change the syntax of the code if I put it on the "On Change" and "Afer Update" properties of the combo box? Where else will I need to put it?

 

[ray00]

Here is an ecample from my code.
I put this code in the "On change" event of my combo box which is cboSName.
txtLName is the name of the textbox that I want to populate with the look up function.

Dim varS As Variant
varS = DLookup("[Implementation Area Long Name]", "Implementation Area", "[Implementation Area Short Name]= '" & [Forms]![frmprimary]![cboSName] & "'&quot
Me!txtLName.SetFocus
txtLName.Text = varS
Hope this helps!