Error Message: The object doesn't contain the Automation object 'qryQ.'.

[Bipsy]

I have a query qryQ, which is designed from two tables, where the RateDate is sorted in descending order with only 1 row to be displayed with the Service Rate for the chosen Service (to be chosen from Form1).

The got focus event of ServRate of Form1 I have put the code as follows:

=DLookup([ServRate],[qryQ],[Service]=[OrdService])


However, when the cursor come to the Rate box in Form1 I get the following Error message.

The object doesn't contain the Automation object 'qryQ.'. I just cannot understand this. Read it carefully and notice the dot after Q and before '. Its very strange..


Hope someone can help me resolving this.

The idea is to get the latest prevailing rate at the data entry level.


Thanks.



bipsy

[Welshgasman]

You need to surround all within double quotes.
Why not google the syntax?

https://support.microsoft.com/en-us/...b-bed10dca5937

[Bipsy]

Done it Sir. The error message is not there now. However, the Service Rate is not appearing in the desired box.
I really appreciate your help.
Thank you.

[Welshgasman]

Well if the code is in an event, you would need
Me.ServRate = dlookup etc
Your syntax would be for the control source?

Again read all of that link.

[Bipsy]

Tried it, but doesn't work!! ServRate is unbound control. That is why it need the Rate from the qryQ's field ServRate.

[Welshgasman]

Show your dlookup code code

Within code tags please.

[Welshgasman]

If you look at that link, it concatenates the value of the control. You would need to do that in the code as well. Sorry, my bad.

[Bipsy]

On the GotFocuss Even I have the following expression.

"Me.ServRate"=DLookUp("ServRate","qryQ","Service"= "OrdService")

[June7]

Did you even look at the link provided by Welshgasman?

Me.ServRate should not be between quote marks.

What is OrdService - a field or a textbox or both? Is Service a text type field?

Me.ServRate = DLookUp("ServRate", "qryQ", "Service='" & Me.OrdService & "'")

Instead of VBA, put the DLookup expression in ServRate ControlSource property.

I doubt you even need this DLookup but I would have to review db. If you want to provide for analysis, follow instructions at bottom of my post.

[Bipsy]

Which particular link please Sir?
Thank you.

[June7]

The one in post #2.

[Bipsy]

OrdService is a textbox on Form1. The value of it will determine the which Service the qryQ will search for and its Rate, that Rate will be put on the unbound ServRate box.

In the Control source of the box I have put
Me.ServRate=DLookUp("ServRate","qryQ","Service"="O rdService")

Its is till not working.

Thanks for your help. Please bear with me for I am a very beginner.

[June7]

You did not follow my example nor answer questions. Again, review the info found in link referenced in post #2.

Is OrdService a control on form or is this literal text? If the latter:

Me.ServRate = DLookUp("ServRate", "qryQ", "Service='OrdService'")

[Bipsy]

ControlSource now has =[Me].[ServRate]=DLookUp("ServRate","qryQ","Service='" & [Me].[OrdService] & "'")

When the cursor comes on the textbox ServRate it brings #Name error.

[Bipsy]

It is a textbox on Form1.