Auto increment a field in new record

[NachoMan]

Pat Hartman, Anyone,

I saw a previous post response from Pat Hartman which explained this a bit, but I'm still not tracking for some reason.

I want to model a report log system that is currently in use in my office. Presently, transcript reports are simply listed in a logbook (rptnumber, date, operator, etc...). There are three logbooks (Log A, B, C) which have a simple sequential numbering system. example: Log A: AC12345, AC12346, AC12347.

An operator may choose any of the three logbooks when adding a new transcript report and simply write in the next sequential logbook number.

I have a form called frmTranscriptDetails populated by a query qryTranscriptDetailsDE. The PK for the main table is TRNumber which is formatted in the same alphanumeric string from our log books (i.e AC12345).

This is what I'm trying to accomplish.

When an operator creates a new record, a form will pop up prompting him/her to choose one of the three log books. If the operator chooses Log A, and clicks the "newRecord" command button, a new record is created in the frmTranscriptDetails form that autoinserts the the next sequential number from Log A in the TRNumber field of frmTranscriptDetails.

Based on previous posts I understand that I should somehow use the DMAX function to get the TRNumber of the last record, but I'm not too clear exactly how. Any insight on this would be greatly appreciated.

 

[Jack Cowley]

I would separate the LogBook and the TRnumber into two fields. Then you can use code similar to this to get the last number from the table for the particular LogBook selected.

Me.TRNumber = Nz(DMax("[TRNumber]", "TableNameHere", "[LogBook] = '" & Me.LogBook & "'"), 0) + 1

I have assumed that you will have a control called LogBook on your form where the user selects the LogBook and the field in your table is called LogBook.

hth,
Jack

 

[Jack Cowley]

NachoMan -

I would only use one table but I would not have the log book number part of the TRNumber. The table would look like this:

tblMyLogBookTable
MyTableID (PK and autonumber)
LogBookID (FK)
TRNumber
...other fields...

tblLogBooks
LogBookID (PK and autonumber)
LogBookName

Now when the user starts a new record they select the LogBook from a combo box, which puts the ID for the LogBook in the table tblMyTable. When they exit the combo the code I posted earlier it looks up the last TRNumber for that particular LogBookID. So if the user selects LogBookID 1 (LogBookName = A) then the code will find the max number for that LogBookID add one to it and put it in the new record. If the last record for LogBookID 1 was 12345 then the number put into the record will be 12346.

Here is some sample code that should do the trick:

If Me.NewRecord Then
On Error Resume Next
Me.TRNumber = Nz(DMax("[TRNumber]", "TableNameHere", "[LogBook] = '" & Me.LogBook & "'"), 0) + 1
End If

Also, the LogBook table allows you at sometime in the future to easily add or delete log books. And you can concatenate the LogBook name and TRNumber so the user sees AC12346....

I hope this clears up my suggestion...

Jack

 

[NachoMan]

Thanks again for both posts.

Jack,
I inadvertently deleted my previous post (don't ask). Thanks for your responses. I got it working fine. I convinced the boss that we didn't need the different number series, though, so I didn't end up having to employ that strategy. I don't doubt that it will come in handy down the road though. Thanks again.

 

[Jack Cowley]

NachoMan -

I am glad you have your problem solved....

Jack

 

[Jon.N]

...And in a Multiuser environment?

I have a similar situation to NachoMan. Only in my case there is Project Log 1, Project Log 2 and so on. As the database becomes populated the users will create their own Project Logs: Project log 1, 2, 3 etc. The first record in Project Log 1 becomes Record A; the second is Record B … Record AA, AB and so on. I managed to sort everything out using queries. Every time a user creates a record they either assign it to an existing Project Log or they create a new project Log and queries calculate the record letter.

All is well or so I thought. The database is used in a multi-user environment, it is a split database. All records for all logs are in the same table. The record letter is assigned when the record is saved. This is because the spec. specifies the option to cancel creating a new record. (Why oh why didn’t I do what NatchoMan has done and got the manager to change his mind?) Testing as shown that if two users save a new record at the same time in the same log both of the new records can end up with same Record letter, i.e. we can end up with 2 Records labelled D.

Is there any way around this? Would the whole thing work fast enough if I used code rather than a query to calculate the letter?

Many thanks to all those who managed to read this spiel and as always any help is gratefully received.