Run time error on autonumber field

[GaryCanuck]

Hi all,
I'm running access 2007. I have a form with two subforms that make up a client index. On one of the subforms I have an autonumber field where the field is populated with the value on the double click event. It works as desired most of the time but under certain circumstances it produces a runtime error. They both have to do with creating a new record (using the row with the star). Errors happen when:
1) the double click is the first thing you do in the new record
2) if you click in the new record row (even without typing anything) and then double click in an old row

Both seem peculiar to me. I guessing they're related to nulls somehow but I'm not sure what to do about it, or where to start. Any ideas? Thanks as always. The double click event code is below.

  NexKey = Format(1 + Nz(DMax("Val(Right([Open#],3))", "File", "[Client#] = Forms!Client![Client#]")), "000")
  [Open#].Value = [Client#].Value & NexKey
  [OpenDate].Value = Format(Date, "mm/d/yyyy")

 

[PHV]

it produces a runtime error
Any chance you could post the error message(s) ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GaryCanuck]

In situation 1) above no error message pops up but the Client# half of the Open#.Value doesn't appear. In situation 2) the error message is "Runtime error 3464: Data type mismatch in criteria expression".

I apologize for not including this info in the first post.

 

[genomon]

Open is a reserved word, and the # char is reserved for file manipulation. Try renaming your fields with fldNum instead of #. Just a shot in the dark here. I would think that the brackets would cause the fields to be interpreted as such, but it may be worth a try.

Ni neart go cur le cheile.

 

[PHV]

What about this ?

NexKey = Format(1 + Nz(DMax("Val(Right([Open#],3))", "File", "[Client#]=Forms!Client![Client#]"), 0), "000")
Me![Client#] = Forms!Client![Client#]
Me![Open#] = Me![Client#] & NexKey
Me!OpenDate = Format(Date, "mm/d/yyyy")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GaryCanuck]

Thank you both for your suggestions. However, I have tried both and the problems still persist. It seems to have to do with null values but I have an "Nz" so I'm not sure how that could be. Is there any information that I could post that would clarify the situation? If so let me know. Any further suggestions would be appreciated.