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

[dlambert]

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!!
Thanks

 

[arnelgp]

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