Dlookup function just pick the first record ?

[mayskys1999]

Hello to All Expert.
I'm new in here and need your help.
I have 2 tables Models and ProdReport. I have form name ProductsReport. I made Dlookup function in Model drop down to seclect model from table Models. I would like to have right Revision in Models table will pick up to insert in field Firmware section in the form ProuctsReport.
Here is my work.

Private Sub Model_AfterUpdate()

Firmware = DLookup("[Revision]", "[Models]", _
    "[ModelID]= [ModelID]")

End Sub

the probem is it just pick the first on table Models only.
I don't know how to fix it.
Plesae help !!!
Thanks in advance.

I put attachment for your review.
Thanks again

[Seth Schrock]

Sorry, I don't open attachments from people that I don't know. And please read Before Posting (VBA or SQL) Code

You have a problem with your WHERE argument. I can make a guess that you have a control named ModelID and that is what you are wanting the record to match. If that is the case then you need your code to be:

Firmware = DLookup("Revision", "Models", "ModelID = " & Me.ModelID)

If I'm wrong with my guess, then you just need to place your control name that contains the value you need in place of the Me.ModelID.

DLookup returns the first record that matches the criteria. In your current code, ModelID will always equal ModelID because these are referring to the same field in the same table. Hopefully that makes sense.

[mayskys1999]

I tried your but still showing error

[Seth Schrock]

I need to know what the error message is. Both the number and the message.

Also read How to ask good questions. This will tell you what information is expected when you ask a question.

[zmbd]

mayskys1999:
As Seth indicated, No one here will normally open a file from unknown user. This is matter of good computing practice and security NOT a reflection upon any one person. The reality is that many people have infections on their PC and don't know that fact.

Because you are a new user, and before you run afoul of the rules:
Please keep in mind: Bytes is not a code nor homework service. Please read the FAQ and posting guidelines before re-posting your question.
Before Posting (VBA or SQL) Code.
How to ask "good" questions -- READ BEFORE SUBMITTING A QUESTION!.
POSTING_GUIDELI NES: Please Read Carefully Before Posting to a Forum.


Note the very first link referring to VBA.
Before you do anything else, please complete the requirements listed in the first section.
DO a debug compile on your code until you get no errors. The compiler stops on the first error encountered; thus, requiring that you REPEAT the compile step until there are no errors.

Post back here once you have fixed everything.
If you have issues fixing something, more than likely you will need to start a new thread unless the error is DIRECTLY related to this question.

[TheSmileyCoder]

Lets look at your code:

Firmware = DLookup("[Revision]", "[Models]", _
    "[ModelID]= [ModelID]")

I presume each and every record in your table will fullfill the requirement that ModelID=ModelID .

I presume that what meant is for one of the ModelID's to be a value you provide?
Make sure your modelID combobox has a sensible name for example cmb_SelectModel ID. The cmb short for combobox.

Now modify your code:

Private Sub [B]cmb_SelectModelID[/B]_AfterUpdate()
  Firmware = DLookup("[Revision]", "[Models]", _
    "[ModelID]= "[B] & Me.cmb_SelectModelID[/B])
End Sub

Best of luck with your project.

[zmbd]

Smiley,
that's what Seth said:

(...) If I'm wrong with my guess, then you just need to place your control name that contains the value you need in place of the Me.ModelID. (...) ModelID will always equal ModelID (...)

[mayskys1999]

Thanks for your help.
I inserted your code but function still get error. Run-time error '2471. and next line is showing the expression you enter as a query parameter produced this error: 'CCC"
Could you please help me again.
thanks alot

[zmbd]

Please read my post #5
Please do as asked therein.

Also take a look at Post #11 in Access 97 to 2010 conversion problem
As you have the same pet-peeve within your DLOOKUP function.
Pull that criteria string out of the function in a similar manner as I indicate in the post #11 of the referred to thread.
This way you can take a look at what the string is resolving to!

[mayskys1999]

Thanks for your comment.
I'm new and try to learn. Please forgive any mistake I did.
Actualy I don't know what I have to do to make it work. I tried so many ways but my project doesn't work.

[Seth Schrock]

What exactly are you wanting the criteria to do? For example, are you trying to find the Revision based on a the value selected in a control or you using a variable that contains the value you are searching by?

[mayskys1999]

I would like to do like this.
whenever I pick up a model from cmb_SelectModel ID_AfterUpdate( )
It will take right Revision from Models table to put in Firmware of ProductsReport form.
Thanks for your support.

[Seth Schrock]

Okay, that is helpful to know. For ease, we will do as Z suggested and make the criteria be a string like so:

dim strWhere as string

strWhere = "ModelID = " & Me.cmb_SelectModelID
Debug.Print strWhere

Firmware = DLookup("Revision", "Models", strWhere)

Now make sure that the immediate window is open (Ctrl + G will open it if it isn't already) and then change the value in cmb_SelectModel ID to trigger the AfterUpdate event. If this works then great. If not, we need to see the value that got entered in the immediate window.

Something that I just noticed, your original post says that it is in the Model_AfterUpda te event, not the cmb_SelectModel ID_AfterUpdate event. Please verify which one is correct.

[mayskys1999]

I used your code to my project.
I try to use cmb_SelectModel ID_AfterUpdate.
the immediate window showing ModelID = CCC when I picked the third model.

[Seth Schrock]

Okay. In table Models, what is the data type for ModelID?