Calculate Last Record Value + 1, Display Result on Form

[Modify_inc]

I’m trying to use the job number field, which is my primary key in my table, to auto assign the new job number on my Forms. Currently, I have to enter a new job number manually, and it has to be unique because that is the way I have it set. I can’t use autonumber because Access does not allow you to select what number you would like to start from, which would not play well with my current job numbers.



Basically I need Access to get the job number from the last record and add 1 to it, or just find the last / highest current job number and add 1 to it.

I created a query (qryFindJob#s) that list all of the Job Numbers, but I’m not sure how to add the query results to my Form to display the Job number + 1. I created a text box, typed a simple expression in the control box to see if I was on the right path (=[qryFindJob#s]) but I keep getting a #Name? error in the txt field.

Any suggestions?

Thanks
Mike

[June7]

Cannot reference table or query directly that way. Can use a domain aggregate function expression to pull data from table or query.

Generating custom unique identifier is a common topic. Search forum. Here are two:

https://www.accessforums.net/forms/c...ble-41863.html

https://www.accessforums.net/program...ple-46469.html

[Modify_inc]

Following those links I was able to successfully accomplish part of my goal. Any new records created now and in the future, seem to work using the code mentioned below.

But if I open any of the previous records, their Job Numbers are changed to the latest plus 1.

The Function that was recommended was the DMax. But from what I can tell, it will only find the highest job number out of ALL the job numbers.

I need it to find the highest job number for only the previous record, then add 1 to the next record, and so on. I'm just looking for a sequence of numbers to display on my form for each record so I don't have to type one in.

Typing it in isn't really an issue. It's that I have to look at the previous record and get the job number assigned to it, and then go back to my current record and enter the new job number (previous job number + 1)

Once again it appears Access makes this much harder than what I thought it would've been.

Here is the code I am testing for my Job Numbers:

Dim numNewRec As Double

' Determine the new Job number.
' Add 1 to the highest Rec_Num in the database.
numNewRec = DMax("[Job Number]", "tblClientInfo") + 1
RecNum.Value = numNewRec
[tblClientInfo.Job Number] = numNewRec


Thanks for any feedback and/or suggestions!

Mike

[June7]

Use conditional code:

If IsNull([Job Number]) Then
...
End If

I don't understand - why would you NOT want to find and increment the highest job number of all records?

[Modify_inc]

Use conditional code:

If IsNull([Job Number]) Then
...
End If


I don't understand - why would you NOT want to find and increment the highest job number of all records?

Thanks June for replying, I always appreciate your help.

It's because as I mentioned in my previous post, "But if I open any of the previous records, their Job Numbers are changed to the latest plus 1."

So basically using the code I mentioned earlier seems to work for all new jobs that I enter, but it will overwrite any of the older jobs with new job numbers when I view them. (Fortunately this only happens when I view them, and so does not effect all of my records)

I assume because the DMax function is taking ALL the job numbers found in the tblClientInfo table, as a whole.

It then finds the highest job number, say for example job number 1000.

It will then add 1 to the 1000, thus the variable numNewRec = 1001.

If I go to say, my first record/job, which should be job number 1, it will now be job number 1001. It basically takes the highest job number found in the table and adds 1 to it. It will process this code even for job numbers that are less than current or highest job number, which causes havoc on my job numbers.

And if I go to job number 2, it will now be job number 1002.

And it gets worse! If I skip around to say job number 500, it will become job number 1003. Pretty much whatever record I click on or view becomes the highest job number plus 1.

I need something that will just take the job number from the previous record and add 1 to the next record.

Thanks again!
Mike

[June7]

Did you try the conditional code suggested?

[Modify_inc]

Did you try the conditional code suggested?

I didn't at the time, but I have now and it works great for all my previous records, and new ones! Thanks!!

One thing that is odd though, is when I get to the last record in the table while in Form View, the Next Record button on the bottom Record Bar is not greyed out as it should be when you have reached the last record. And if I continue to click on it, it continues creating new records, as if I was clicking on the New Record button.

This behavior does not occur when viewing from the Datasheet View, so I suspect it has to do with me using the Form On Current Event for the code.

The only thing I did different other than using the If statement was I moved the code from an unbound field using a Double Click Event (used just for testing) to the Form On Current Event.

I'm assuming the Form On Current Event is best for this code, since it needs to update every time the form is opened and a new record is created/selected. But I am curious how am I suppose to fix the Next Record button from creating new records?

Here is my code:

Private Sub Form_Current()

Dim numNewRec As Double

' Determine the new record / Job number.
' Add 1 to the highest Rec_Num in the database.
If IsNull([Job Number]) Then
numNewRec = DMax("[Job Number]", "tblClientInfo") + 1
RecNum= numNewRec
[tblClientInfo.Job Number] = numNewRec
End If

End Sub

[June7]

It should not be greyed out if the form is set to AllowAdditions Yes. I usually disable the intrinsic navigation bar and control everything with custom buttons.