Solved Another Run-time Error '2465' (1 Viewer)

[Cliff67]

Hello All,

long time no post. So a lot has happened since I last posted here.

I've changed jobs and I'm now in another industry altogether. I'm still putting my Access skills to use here too.

I'm using Access 365 which has been a learning curve so maybe that is the issue

Now down to the issue at hand - I get the dreaded 2465 error

the code is quite simple behind a sub form "BeforeInsert" event

it looks like this

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim HoldIt As String

HoldIt = Format((DMax("ID", "Tbl_Equipment") + 1), ["T" & '0000'])
    [Forms]![frm_Equipment_register]![Frm_Sub_Equipment].Form![PatTrackID] = HoldIt
    'Me.PatTrackID = HoldIt
    
End Sub

the error is being thrown at the Holdit = Format line.

All it is to do is take the maximum ID number from the table Tbl_Equipment add 1 to the value and format it to look like "T0012" and put it in the subform "Frm_Sub_Equipment field called PatTrackID

for some reason that escapes me Access cannot resolve the field name, any help would be gladly taken

 

[arnelgp]

try:

HoldIt = Format((Nz(DMax("ID", "Tbl_Equipment"), 0) + 1, "T0000")

 

[plog]

Programming 101: Divide, isolate and conquer

You have 2 function calls on the line you think it is failing on. Format() and DMax(). Does the DMax() work by itself? Does the Format() work if you hardcode a value into it?

Both of them take multiple arguments. When you find which function is causing this, simplify the arguments of the offending one or strip them out completely until you get that function to work. Once you do, add back the offending argument piece by piece until you find out what exactly caused the issue.

 

[The_Doc_Man]

Errors of the form "... can't find the field '|1' in ..." generally are complaining about the first field of an implied query. Normally I would point to the ID field of Tbl_Equipment, but I think the problem in this case MAY be the brackets around "T" & '0000' - because those brackets make Access think that is a field to be looked up - but it doesn't exist.

 

[Cliff67]

Thank you all for the replied, I'll try them tomorrow and get back to you all

 

[Cliff67]

try:

HoldIt = Format((Nz(DMax("ID", "Tbl_Equipment"), 0) + 1, "T0000")

Hi arnelgp

that worked a treat, thanks very much

 

[Cliff67]

Programming 101: Divide, isolate and conquer

You have 2 function calls on the line you think it is failing on. Format() and DMax(). Does the DMax() work by itself? Does the Format() work if you hardcode a value into it?

Both of them take multiple arguments. When you find which function is causing this, simplify the arguments of the offending one or strip them out completely until you get that function to work. Once you do, add back the offending argument piece by piece until you find out what exactly caused the issue.

Hi Plog

I tried separation them, the DMax Worked, the Format seemed to work but no idea why they don't work together. Adding arnelgp's NZ function worked.

thanks for the help

 

[Cliff67]

Errors of the form "... can't find the field '|1' in ..." generally are complaining about the first field of an implied query. Normally I would point to the ID field of Tbl_Equipment, but I think the problem in this case MAY be the brackets around "T" & '0000' - because those brackets make Access think that is a field to be looked up - but it doesn't exist.

Hi Doc_man
you may be right as arnelgp does not have the bracket around the "T" &'0000' bit and it works

thanks for the insite and the help

 

[Cliff67]

Thank you all for the help and information supplied. I'm marking this thread as solved.