Solved

Dlookup help

Posted on 2008-09-29
6
298 Views
Last Modified: 2013-11-28
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

0
Comment
Question by:onaled777
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 84
ID: 22603639
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
 

Author Comment

by:onaled777
ID: 22604098
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
 
LVL 84
ID: 22604894
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:onaled777
ID: 22606377
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
 

Author Comment

by:onaled777
ID: 22614622
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
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 22762069
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

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Error 438 6 46
How to get data off Lotus Notes server 4 57
GA Ribbon creator 9 52
Tabbed form question 5 11
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question