Dmax/Val function Problem

[MintChipMadness]

I am having trouble with some code. I know how to write the expression in access but I can't figure out how to translate it to VBA. I listed the code below. The Val portion extracts just the numbers from [Boxes].[OldBox#]. Then DMax finds the highest number and adds 1 to it. Lastly, the current record will be set to that number. Thank you for your help.



Me.[OldBox#]=DMax("Val([OldBox#])","[Boxes]")+1

[June7]

What is the trouble - error message, wrong result, nothing happens?

I tested that expression in the VBA editor immediate window and it worked.

[MintChipMadness]

It says Run-time error '3464': Data type mismatch in criteria expression.

[June7]

OldBox# is a text type and result is a number but the text field should have no problem accepting that value. I did another test and the field was populated with the result.

Since I can't replicate the issue, will need your latest version of db to analyse your code.

Don't want a letter suffix on the value?

[MintChipMadness]

Some of the old box numbers have letters in them. I use the Val function to make sure the boxes with letters are not higher than the numbers. I attached the database. The code is on the DataEntry Form. What I want to do is fill in the OldBox# for the a new record. The information comes from the DataEntry Query which is the record source of the form.

[June7]

This is annoying. The DMax/Val works in the Immediate window but bombs in the VBA procedure.

1. Change the DataEntry query to create a field with calculation to extract the number from OldBox#:
OldBoxNumber: Val([OldBoxNumber])

2. Change the code to:
Me.[OldBox#] = DMax("[OldBoxNumber]", "[DataEntry]") + 1

That all works but now get 'Action Failed' macro execution error from the Save button click. I don't use macros so will let you deal with that.

[MintChipMadness]

Thank you.I'll fix that save button. On my real database, I converted all the macros to VBA. Just curious why didn't he VAL function work nested in the Max function?

[June7]

I don't know why, that was source of my frustration. The nesting worked in the Immediate window test but not in the event procedure.

[MintChipMadness]

I figured it out without using the work around. I thought I tried it but I guess I didn't. I changed the domain from Boxes to DataEntry and it worked.

Me.[OldBox#] = DMax("Val([OldBox#])", "[DataEntry]") + 1