Dmax error 2465 cannot locate field (1 Viewer)

hugomadail · Dec 6, 2019

Hello,

I have a form (frmClients) on which i'm trying to update a field using a button on click event, with the vba code:

Me.[Number] = Nz(DMax("[Number]", "QryClientsExt", "[LocationID] = " & [VarTemp]![Location] & " And [WorkplaceID] = " & Forms("frmClients").Workplace.Column(1)), 0) + 1

The idea is to add number to [Number] field on the button press, with both criteria (so incremental number will be different while checking both criteria)

adicional info: [Number] is a number field

When pressing the button, i get error 2465 cannot locate field '|1'

What am I doing wrong?

hugomadail · Dec 6, 2019

Update: problem seems to be with
[LocationID] = " & [VarTemp]![Location]
because I used formula without it and added number to field when button press.

But I need to use that part of the formula...
and I cant find why it doesnt work.

Also gave me another error - 3075 syntax error (missing operator)

isladogs · Dec 6, 2019

Several errors
1. Number is a reserved word and shouldn't be used as a field name or a control name.
2. VarTemp? I think you mean TempVar!Location
3. Is Workplace a combo or listbox? Column numbering is zero based so Column(1) is the 2nd column. Is that a number datatype as well? Try setting a variable (integer datatype?) to the value of WorkPlace.Column(1) and use that in your expression instead

theDBguy · Dec 6, 2019

Pardon me, I thought VarTemp was a table. If a TempVar, then I think that would have been TempVars!Location. But if a table, then see if it's possible to include the Location in the Form's record source.

hugomadail · Dec 6, 2019

Thanks for the reply isladogs

1. Number is a reserved word and shouldn't be used as a field name or a control name.

Changed to Reference, problem is still there.

2. VarTemp? I think you mean TempVar!Location

VarTemp works in other forms, that might be because TempVar in my language is read VarTemp

3. Is Workplace a combo or listbox? Column numbering is zero based so Column(1) is the 2nd column. Is that a number datatype as well?

Workplace is a combobox with query source where
Column 0 = Workplace (text in table)
Column 1 = WorkplaceID (number in table)
so yeah, has that in account and used column 1 to use WorkplaceID

Try setting a variable (integer datatype?) to the value of WorkPlace.Column(1) and use that in your expression instead

I dont know how to do that but before you explain it to me, I did some more testing and:

I think the problem is with [LocationID] = " & [VarTemp]![Location]

because:
- created a button with on click event
MsgBox "LocationID: " & [TempVar]![Location]
which shows no value when pressed
BUT
- also created textbox with source =VarTemp("Location")
which shows number 2 or 3 as intended.

I dont know why the textbox can show the vartemp value but the msgbox cant....

hugomadail · Dec 6, 2019

Thanks for the reply theDBguy

Pardon me, I thought VarTemp was a table. If a TempVar, then I think that would have been TempVars!Location. But if a table, then see if it's possible to include the Location in the Form's record source.

Yes its a TempVar! Well this is awkward because I created a button with

MsgBox "LocationID: " & [TempVars]![Location]
and yes, it returned value (=2) as its suposed to..

I cant believe it was all because of a missing S!!

Will correct formula and will feedback soon

isladogs · Dec 6, 2019

DBG
I never use TempVars but looked it up here and it said TempVar![Name] so I copied that!
https://docs.microsoft.com/en-us/office/vba/api/access.tempvars

hugomadail · Dec 6, 2019

If a TempVar, then I think that would have been TempVars!Location

So yeah, I changed my formula and it worked as it was supposed. thank you theDBguy

...it was all because of the missing S!! (*awkward moment*)

But I am awkwardly happy

Thank you both for your time.

The_Doc_Man · Dec 6, 2019

Sometimes all it takes is another pair of eyes. What happened to you is "writer's hypnosis" (which, as an amateur writer, I run into all of the time.) Your mind remembers clearly what you intended to write but that doesn't mean you actually wrote it that way.

That is why I make it a point to put aside something and start on something else to break the mindset that I get into. If you get something else on your brain for a while, you break the "hypnosis" of the previous thing and can sometimes see it for what it really was. You can then catch the error. And as we all know (from Bugs Bunny cartoons), the first step in making Haasenpfeffer is to "catch the wabbit."

theDBguy · Dec 6, 2019

hugomadail said:
So yeah, I changed my formula and it worked as it was supposed. thank you theDBguy

...it was all because of the missing S!! (*awkward moment*)

But I am awkwardly happy

Thank you both for your time.

Hi. Congratulations! Glad to hear you got it to work. Colin and I were happy to assist. Good luck with your project.

theDBguy · Dec 6, 2019

isladogs said:
DBG
I never use TempVars but looked it up here and it said TempVar![Name] so I copied that!
https://docs.microsoft.com/en-us/office/vba/api/access.tempvars

Hi Colin. I'll let them know about the typo. Thanks!

hugomadail · Dec 6, 2019

make it a point to put aside something and start on something else to break the mindset

As an amateur databaser, I should follow that technique more than I actually do LOL

Anyway, good advice, The_Doc_Man. thank you

Dmax error 2465 cannot locate field (1 Viewer)

hugomadail

Registered User.

hugomadail

Registered User.

isladogs

MVP / VIP

theDBguy

I’m here to help

hugomadail

Registered User.

hugomadail

Registered User.

isladogs

MVP / VIP

hugomadail

Registered User.

The_Doc_Man

Immoderate Moderator

theDBguy

I’m here to help

theDBguy

I’m here to help

hugomadail

Registered User.

Similar threads

Users who are viewing this thread