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.
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

Open in new window

onaled777Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Generally if a comment by one of the Experts led you to your answer you would accept that as the answer. You might look into the grading guidelines in the Help file for more assistance.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Is "Style_" the actual name of your field?

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 ...

0
 
onaled777Author Commented:
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.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
onaled777Author Commented:
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
0
 
onaled777Author Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.