Dlookup help
I am using this code to autopopulate fields in the form. the fields get the information from a seperate table to the main table. but when using this code in the after update i get an error message. runtime error "2766", The object does not contain the automation object "blah blah blah". but when i had the code
Me.Pattern_.Value = DLookup("[PatternCode]", "Pattern", Me.Style_.Value) it worked but would always bring up the first entry in the patterm table.
Me.Pattern_.Value = DLookup("[PatternCode]", "Pattern", Me.Style_.Value) it worked but would always bring up the first entry in the patterm table.
Private Sub Style__AfterUpdate()
Dim PDMDate As Variant
Dim appDate As Variant
Me.Pattern_.Value = DLookup("[PatternCode]", "Pattern", "Style_=" & Me.Style_.Value)
PDMDate = DLookup("[DateApproved]", "Pattern", Me.Style_)
appDate = DLookup("[PDMReceived]", "Pattern", Me.Style_)
Me.PDM_Date.Value = PDMDate
Me.Approval_Date.Value = appDate
End Sub
ASKER
Thanks for your responce. The code now works but I am back to the same problem were the code is now always bring up the first entry in the patterm table. The Name of the field control is Style#. I have other code that works with the style# name of the control on the form. Just can't seem to get this Dlookup working. Thanks again for your help.
Well, if "style #" is the actual name of your control, then you'd refer to it as such:
Me.Pattern_.Value = DLookup("PatternCode", "Pattern", "[Style #]='" & Me.[Style #].Value & "'")
And please - look into those nameing conventions. The hash mark ( # ) is used in Access to delimit dates, and should never be used in a control or field name.
Me.Pattern_.Value = DLookup("PatternCode", "Pattern", "[Style #]='" & Me.[Style #].Value & "'")
And please - look into those nameing conventions. The hash mark ( # ) is used in Access to delimit dates, and should never be used in a control or field name.
ASKER
I cant understand what is going on. I change to code to match your suggestion and get a erroe. So I decided to go in and change my field names to more convetional names. When i did that the look is pulling up the first value in the pattern table again. I will zip file and attach it to this question. This code is the last thing I need to get working to deliver this DB. Any help would be greatly appriciated.
Dwayne-Project-Version-1.6--9-23.zip
Dwayne-Project-Version-1.6--9-23.zip
ASKER
I came up with the answer. There was noting really wrong with the syntax of the Dlookup. It was all in the way I was naming the fields in my tables. When I renamed fields as suggested The Dlookup worked. I no know that u need to pay extra special attention to table design and naming conventions
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try removing the square brackets ... they're not needed.
Is "Style_" a text or numeric value? If it's Text, you'll need to enclose it in quotes:
Me.Pattern_.Value = DLookup("PatternCode", "Pattern", "Style_='" & Me.Style_.Value & "'")
And if "Style_" IS the actual name of you field/control, then I'd strong suggest you look into nameing conventions for your objects ...