Button to add new entry into table with incrementing number field (not autonumber) (1 Viewer)


Local time
Today, 13:41
Apr 16, 2020
Hello i am trying to achieve the following functions with a script without much success, would anyone be able to help?

This is my situation:

I have a table called tblClients from which i am selecting a client name through a combobox called: comboClientSelect
I have a query (qryJobsForSelectedClient) that selects the list of job numbers from a table called tblJobs that matches the client selected in the above combobox (there is a field in the tblJobs table called Client, with entries that match the client names in tblClients)
The job numbers are sequential integers (eg: 1, 2, 3, 4, etc), but these cannot be automunber because these numbers a unique per client (not the whole system), so for example ClientA can have a job number 2 and ClientB can also have a job number 2. Of course, each entry in tblJobs has unique identifier key which is an Autonumber (but this is only used by the behind-the-scenes functionality of the Access database, not the user interface).

What i am trying to achieve is the following:

A button (buttonNewJobNumber) in my form that does the following:
  1. Get the largest job number from the qryJobsForSelectedClient query
  2. Create a new entry in the tblJobs table where some of the fields are filled as follows:
    • Client = comboClientSelect
    • JobNumber = (largest number as determined in step 1 above) + 1
    • Description = Text as manually inputted in a text field called txtNewJobDescription - This field needs to be filled in (cannot be Null)
    • AllocatedHours = Text as manually inputted in a text field called txtNewAllocatedHours - This field needs to be filled in (cannot be Null)
    • (all other fields in tblJobs to just go to their default assigned value)
Any guidance would be much appreciated!!


once i caught a fish alive...
Local time
Today, 19:41
May 7, 2009
on the Click event of buttonNewJobNumber
Private Sub buttonNewJobNumber_Click()
If Trim(Me!comboClientSelect & "")="" Then
    Exit Sub
End If
If Me.NewRecord = True Then
    Me.txtJobNumber = Nz(DMax("JobNumber","qryJobsForSelectedClient", Client='" & Me!comboClientSelect & "'"),0) + 1
End If
End Sub

Users who are viewing this thread

Top Bottom