runtime error 2471 and 2465 (1 Viewer)

Help please.

I am using Access 2016 as part of Office 365 with a database in the accdb file format of 2007-2016.

On one form I can get dlookup to work but another I cannot. I know it is something to do with the variable types and the corresponding syntax i'm using, but I just cant find a solution.

When
My form field Forms![frmaddintschedcourse].[CourseTitle] is a defined as dropdown box with a control source from my table field which is defined as 'short text' because it ultimately points back to owning table's primary key which is also defined as short text... i.e. [tblcourse].[CourseTitle]

This code is working absolutely fine.
Me.Duration = DLookup("[CourseDuration]", "[tblcourse]", "[CourseTitle] = Forms![frmaddintschedcourse].[CourseTitle]")

BUT
My form field Forms![frmaddintbooking].[CourseTitle] is a defined as dropdown box with a control source from my table field which is defined as a number (long integer) because it ultimately point back to owning table's primary key which is an autonumber..... i.e [tblintschedcourse].[InternalScheduledCourseID]


This code
Me.CoursePrice = DLookup("[CoursePrice]", "[tblintschedcourse]", "[InternalScheduledCourseID] = Forms![frmaddintbooking].[CourseTitle]")

generates the error 2471 The expression you entered as a query parameter produced this error 'Forms![frmaddintbooking].[CourseTitle]'


When I change it to this code
Me.CoursePrice = DLookup("[CoursePrice]", "[tblintschedcourse]", "[InternalScheduledCourseID] = '" & Forms![frmaddintbooking].[CourseTitle] & "'")

it generates the error 2465 Microsoft Access can't find the field "|" referred to in your expression"


For clarification I have also tried this code
Me.CoursePrice = DLookup("[CoursePrice]", "[tblintschedcourse]", [InternalScheduledCourseID] = Forms![frmaddintbooking].[CourseTitle])

and it also generates the error 2465 as above

Any ideas please.

First of all, welcome to AWF.
I got a bit lost reading your explanation but I think you're saying it's a number field. Try this


BTW the [] brackets are all unnecessary as there are no spaces or special characters in your table or field names

Thankyou for your prompt reply.

Yes the table field is a number - defined as long integer

unfortunately

produced the same 2465 error

Is frmaddintbooking an actual form in your database and is it open when this code runs?

If so, change your code to this:

Me.CoursePrice = Forms![frmaddintbooking].[CourseTitle]

Let's find out exaclty what is in CourseTitle.

it generates the error 2465 Microsoft Access can't find the field "|" referred to in your expression"

Click to expand...

Almost invariably, that particular error (with the vertical bar in the text) tells you that you spelled something wrong or have misidentified its location because SQL could not find it based on your instructions.

Think of the string Forms![frmaddintbooking].[CourseTitle] as being similar to a directory path. Starting from the Forms object, look in form frmaddintbooking to find CourseTitle and get the contents of that object. This is the moral equivalent of a "file not found" error. Either the path to the object is wrong or you have spelled something wrong because there IS no such object. That is what it is telling you.

Now, one last question: You are using a Me.x type of reference on the left-hand side of the equation, so using a Forms!... reference path implies that the [CourseTitle] info isn't on the current form in question. Are you certain that at the time this code is running, frmaddintbooking is open? Because if it is not, then [CourseTitle] can't be found because it is not instantiated at the moment. If the form is not open, its controls do not exist with a value. They only exist in design mode and in such cases, the .Value property is neither expressly or implicitly available.

LawrenceHill1 said:

This code
Me.CoursePrice = DLookup("[CoursePrice]", "[tblintschedcourse]", "[InternalScheduledCourseID] = Forms![frmaddintbooking].[CourseTitle]")

Click to expand...

Looking at the field/control names, I think you're comparing a number ("[InternalScheduledCourseID] seems to a number"), with text ("[CourseTitle]" seems to be some text.) !

LawrenceHill1 said:

This code is working absolutely fine.
Me.Duration = DLookup("[CourseDuration]", "[tblcourse]", "[CourseTitle] = Forms![frmaddintschedcourse].[CourseTitle]")

Click to expand...

Here you compare text and text, so therefore it is okay!
You can try to replace Forms![frmaddintbooking].[CourseTitle] with a hardcoded number, to see if you still get an error, (and if you get an error if the error number change).