automate the generation of a reference number incrementally by 1

[ant1983za]

Hi,

So I am relatively new to VBA etc so please be gentle...

I am trying to automate the generation of a reference number incrementally by 1.

In my main table (tblBooking) I have my primary key field autBookingID which is an autonumber.

In another table (tblBookingStops) I have the primary key as autBookingStopsID as the autonumber and then numBookingID linked to the above table (tblBooking). I have another field called txtGPSID so this is the number I would like to automate.

So for example:

tblBooking:
autBookingID: 1234

tblBookingStops:
autBookingStopsID: 5555
autBookingID: 1234
txtGPSID: 1234.1

autBookingStopsID: 5555
autBookingID: 1234
txtGPSID: 1234.2

autBookingStopsID: 5555
autBookingID: 1234
txtGPSID: 1234.3

I want to put the code that would populate the txtGPSID on a form in datasheet view and would like to put the code on the load event.

Hope that was enough to go by and that I was clear enough.

Thanks

Ant

 

[Rx_]

There are probably several ways to accomplish this.
So, you appear to be taking the autBookingID: 1234 and appending to it the incremented number that is shown in the txtGPSID.
Guessing the txtGPSID: 1234.1 ... 1234.5
The decimal is an indicator that would re-start, kind of a version number?

One of the problems might be in sorting this later if there are over 10 versions.
e.g. does .1, .2, .3 ... .11, .12, .13 sort into .1, .11, .12, .13, .2, .3... ?
For version where I expect a maximum of 20, my preference is to generate
.01, .02, .03, ... .11, .12, .13 These will numerically or lexicographical sort easier.

Go ahead and turn this field back from a text to a number if .01
Let us know if the numeric .01 format would be acceptable for your version generator.

If it is, then you would have the autobookind ID in one column and the intGPSID (just the .01, .02 ...) in another column.
The function would take the AutobookingID in a query and sort the intGPSID in acending order, and look at the last record (or indicate no record).
In the case of no record, the .01 would be returned.
In the case of n records returned, it would find the last number and add .01.

On your form, one text box would display the AutbookingID & the intGPSID.
Another method that I use is the DMax - here is a code snippet of it used to increment the first time whole number of 1 or the next incremented whole number.
Again, since you are using a decimal number (.1 or .01) the sorting for this function might suggest the .01, .02 version discussed rather than the .1, .2 version.

60    NextPadID_For_State = 1 ' if this State (now Area in state) has no pads - use as default 
70    If IsNull(DMax("PAD_Number", "Wells_PAD_Name", "ID_Area = " & CStr(Me.ID_Area))) Then  ' changed me.id_state to me_id_Area an area within a State
          ' no existing Pad number for this state (now areas in a state)
80        NextPadID_For_State = 1 ' if this State has no pads - use as default
90        Exit Function
100   Else
110           CurrentPad_MAXNumber = DMax("PAD_Number", "Wells_PAD_Name", "ID_Area = " & CStr(Me.ID_Area))
120       If (CurrentPad_MAXNumber) = 0 Then
130           Next_Pad_Number = 1
140       Else
150           Next_Pad_Number = CurrentPad_MAXNumber + 1
160       End If
170           NextPadID_For_State = Next_Pad_Number       ' return value
180   End If

 

[RainLover]

In my signature there is a link to some sample Databases.

If you go there you will find DMax plus 1. Use the single user the other one is complicated.

Download that and change it to suit yourself. You could increase by 0.0001 if you wanted to.

Post back if you have questions.

 

[Mihail]

Are you sure that you wish to add decimals to the autBookingID field's value ?
The ID field represent NOTHING for your data. This field help only Access to maintain the referential integrity. I think that you should not use this field as base for your numeration.

 

[ant1983za]

I cant view the links in your signatures. Dang.

 

[RainLover]

I cant view the links in your signatures. Dang.

Did you click on this. Sample Databases at SkyDrive I just tested and it works.

I will attach a file for you.