Clueless query

[pbaldy]

The Nz() function will handle when a type hasn't been used before and the DMax() returns Null.

[QuantifyRisk]

This is the code that I eventually got to work:

Me.AgencyCode = Nz(DMax("AgencyCode", "AgencyInformation", "AgencyType='Flood Only'"), 0) + 1

Thank you all for your help.

[QuantifyRisk]

So I have a change that needs to be made. I'm adding 1 to the previous number for 5 of the 7 choices in a combo box. This is what I have:

Me.AgencyCode = Nz(DMax("AgencyCode", "NewAgentChecklist", "AgencyType<>'" & "Dave"' OR " & "AgencyType<>'" & "Bob"'"), 0) + 1

Any ideas? Thanks.

[June7]

Maybe:

Me.AgencyCode = Nz(DMax("AgencyCode", "NewAgentChecklist", "AgencyType<>'Dave' And AgencyType<>'Bob'"), 0) + 1

What do you want in AgencyCode if AgencyType is "Bob" or "Dave"? What happened to "Flood Only" parameter?

[pbaldy]

It makes no sense to me to be hard-coding the criteria, but with all the unnecessary concatenation you've gotten things mixed up. Try

Me.AgencyCode = Nz(DMax("AgencyCode", "NewAgentChecklist", "AgencyType<>'Dave' And AgencyType<>'Bob'"), 0) + 1

Note the change from Or to And, which I think you will have found to be necessary.

[QuantifyRisk]

Maybe:

Me.AgencyCode = Nz(DMax("AgencyCode", "NewAgentChecklist", "AgencyType<>'Dave' And AgencyType<>'Bob'"), 0) + 1

What do you want in AgencyCode if AgencyType is "Bob" or "Dave"? What happened to "Flood Only" parameter?

Flood Only has been upgraded to everyone but Bob and Dave. It's a real pain in the... I need the code to look at all the codes that are not Bob/Dave and take the highest one and add one to it. Dave and Bob selections will do nothing so the AgencyCode field will be entered manually.

[QuantifyRisk]

So, I tried it, and it works. However, it only works if you click on the AgencyCode box first, then select the AgencyType. So I added thsi before the code and it runs beautifully:
Me.AgencyCode=Dirty

Thanks guys!