Adding increment to a number field in a form.

[Robert2150]

I have a database that has a form for adding records.


The Key Field in the form is the "Property Number", which is a Number Field.
When I open the form to add a property I would like the form to go to a "New Record" and add 1 to the last "Property Number" field.
Any help would be appreciated, Thank You.

[isladogs]

The easiest way is just to use an Autonumber field for Property Number

However if you have a good reason for not doing so, instead use DMax("Property Number", "YourTableName")+1

[Micron]

Of what importance is the number (i.e. does it have to be absolutely consecutive; is it used as a foreign value in some other table)? And is this always going to be a single user db? If not, you have to consider what can happen when 2 or more concurrent users are doing the same thing at more or less the same time.

An option to moving to a new record when the form opens is to set it's Data Entry property to be True/Yes. If you're going directly to a new record mode, why use code to move it there when there is a property that takes care of this?

[Robert2150]

with
Private Sub Form_Load()
DMax("Property Number", "Property Information") +1
End Sub
I keep getting Compile error: Expected:=

[June7]

Right, the DMax has to be on one side of an expression.

Me.[Property Number] = Nz(DMax("Property Number", "Property Information"),0) + 1

[Missinglinq]

...And is this always going to be a single user db?

It's important that you answer this question! If this is going to be a db used by only one person, having this code in the Form_Load event is fine...but if this is going to be a multi-user db, it needs to be in the Form_BeforeUpdate event, which occurs at the last possible moment before the Record is saved. This greatly reduces the chance of two or more users making a Record that has the same number generated, which was Micron's concern.

Linq ;0)>

[Robert2150]

Right, the DMax has to be on one side of an expression.

Me.[Property Number] = Nz(DMax("Property Number", "Property Information"),0) + 1

WITH THE FOLLOWING:

Private Sub Form_Load()
Me.[Property Number] = Nz(DMax("Property Number", "Property Information"), 0) + 1
End Sub

I get the Run-time error 3075, Syntax error (missing operator) in query expression 'Max(Property Number)'.

[Missinglinq]

I think there's a closing parens in the wrong place...try

Nz(DMax("Property Number", "Property Information", 0)) + 1

instead of

Nz(DMax("Property Number", "Property Information"), 0) + 1


Linq ;0)>

[June7]

Nope, parens are correct. Colin and I both forgot [ ] (and Linq and Micron didn't catch it either - shame on all of us!) for the field name that has spaces. Oddly, the table name will work without.

Me.[Property Number] = Nz(DMax("[Property Number]", "Property Information"), 0) + 1

Note I did remember to use [ ] for the left side reference.

Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention and most of these bracketing errors just won't happen.

Oh, and the reason for Nz() is to deal with creating very first number for very first record.

[Micron]

I think there's a closing parens in the wrong place...try

Nz(DMax("Property Number", "Property Information", 0)) + 1

I dont't agree. You are wrapping the 'value if null' parameter inside the DMax.
What's curious is that the message is stating it's a query expression error. Would it say that when it's not a query expression? If not, then the guilty expression or code isn't being shown to us. Otherwise, Property Number isn't the correct field name in the domain. It would be reasonable to assume it is, but then again, how many times have we seen posters refer to "field name on a form" when they meant "control name ion a form" and those 2 were not the same?

EDIT: I think June7 caught it. I noticed the space, but thought the quotes would take care of it. See what happens when you correctly name things (i.e. without spaces)? You miss the obvious because it never happens to you.

[June7]

And no, I did not wrap the 'value if null' inside the DMax(). Your suggested change is placing the 'value if null' inside the DMax().

I did find the error message rather odd as well because of the missing D of DMax, but maybe just a post typo or maybe is actual error.

[Micron]

I wasn't referring to you. The time stamp between your post and mine is only a minute, so I couldn't have seen yours. Nor did your post use either the expression I copied, or red text.
My fault for not citing, but there were clues.

[isladogs]

OK its 10 mins after Micron's last reply & hopefully I've read everything before replying

I did miss the required [] in the DMax
I agree that the expression needs to be

Me.[Property Number]=Nz(DMax("[Property Number]", "Property Information"),0)+1

Having created a table & field with those names as a test, I can confirm that using:

Nz(DMax("Property Number", "Property Information"),0)+1

gives error 3075 exactly as written by the OP in post 7- it wasn't a typo

[June7]

Ooops, sorry Micron, yes, I should have recognized which post you were responding to.

[Micron]

@Ridders (to clarify to whom)

Me.[Property Number]=Nz(DMax("[Property Number]", "Property Information"),0)+1

Naw, I don't think so. If property number requires brackets (and I agree it does), then so does Property Information. Seeing as how you have the tables and such, see if a second set of brackets fixes the problem.