autonumber query with a difference (1 Viewer)

[sha7jpm]

I have a table with completed, active and inactive projects.

basically the projects that are completed or active do not need job numbers.

but the inactive projects once they turn active I would like to assign them a job number.

as this used to be manual the next number to be used is 2180.

BUT! I dont want it as an autonumber as that will give incorrect number to the older completed projects.

so... is there some code I can write to do the following:

last number used is 2179.

if button (or whatever event procedure) is clicked put "last number" +1 into job number textbox.


Therefore job number textbox now equals 2180. and that is saved on the datatable.

So the last number will have to update each time and be stored. To amke each new active project a new unique number.

any ideas anybody???

ta

John

 

[Rich]

DMax("MyNum","MyTable")+1 will give the number

 

[The_Doc_Man]

Not to be nit-picky, but .... (pick, pick, pick)

The syntax you want is probably more like

1 + DMax( "Nz([ProjNumb],0)", "myTable", {possible filtration criteria if any apply here})

The domain aggregates will probably ignore the nulls anyway, but this way makes it unambiguous to YOU and to any subsequent maintainers that you fully expect to sometimes encounter a null value.

 

[Rich]

Well if we want to get really picky, I prefer to use this
Function CheckNum() As String
Dim DB As DATABASE, ssMaxCheck As Recordset

Set DB = CurrentDb


Set ssMaxCheck = DB.OpenRecordset("SELECT Max(ChequeList.ChNo) AS MaxOfChNo FROM ChequeList;")
If Not IsNull(ssMaxCheck.Fields(0).Value) Then
ChequeNum = CStr(ssMaxCheck("MaxOfChNo") + 1)
Else:
Dim CurDB As DATABASE, BA As Recordset, SQLStmt As String
Set CurDB = DBEngine.Workspaces(0).Databases(0)
SQLStmt = "SELECT [StartingChequeNumber] FROM [MisInfo]"
Set BA = CurDB.OpenRecordset(SQLStmt, DB_OPEN_DYNASET)
If Not IsNull(BA.Fields(0).Value) And BA![StartingChequeNumber] > 0 Then
ChequeNum = CStr(BA![StartingChequeNumber])
Else
CheckNum = "00000100"
End If
BA.Close
CurDB.Close
End If
End Function

 

[sha7jpm]

thanks to you both!

cheers to you both for the tips,

I'll try both possible routes for this one....

its like some kind of syntax war!!!

ta

John

 

[sha7jpm]

error!

I am getting that it cannot find my table "new final table" where the job number is housed....

any ideas?

John


Private Sub DE_Job_number_Click()
On Error GoTo Err_DE_Job_Number_Click

DMax [DE Job number], [New Final Table] + 1

Exit_DE_Job_number_Click:
Exit Sub

Err_DE_Job_Number_Click:
MsgBox Err.Description
Resume Exit_DE_Job_number_Click


End Sub

 

[Pat Hartman]

BUT! I dont want it as an autonumber as that will give incorrect number to the older completed projects.

Who gave you that piece of incorrect information? You can import all of your existing job number values (provided they are unique) into a table where job number is defined as autonumber and Access will start autonumbering new jobNumbers at imported JobNumber + 1.

 

[sha7jpm]

Thanks

thanks for the info Pat,

I managed to get the syntax to work, it would have been good to have put in the previous job numbers, but they were only in paper format, so was decided not to update the table.

but I will need to do a similar exercise for a another number in the future and your advice willl be very handy as to what I had previously believed was possible.

cheers

John

 

[Pat Hartman]

Actually import is not precisely the correct term. I should have said append because you will need to use an append query to add rows with existing autonumbers.