Automatic numbering of records

[diacos]

I have a form containing five fields. The first field is the registration no of vehicles. The second field is a serial of a journey. Note that each vehicle makes several journeys. The third and fourth fields are data for the journey and the fifth field contains a number which represents the no of occurrences of the same journey of the same vehicle.

Let’s say the data in the corresponding table of the form might look like:


RegNo SNJourney COL3 COL4 Freq

AAA1 1 100 100 1
AAA1 2 200 200 1
AAA2 1 300 300 1
Etc

Now, the frequency of each journey, ie the value of the last column, may contain a number greater than one. My problem is how to automatically create the same records and the number of these new records to be equal to the number of Frequency and in addition, how these new records could have different but consecutive serial numbers?

For example,

RegNo SNJourney COL3 COL4 Freq
AAA1 1 100 100 1
AAA1 2 200 200 3
AAA1 3 200 200 3 *
AAA1 4 200 200 3 *

*These records must be created automatically

So, I would like Access to do the following: if I type 3 in the frequency field then the current record must be copied twice so as to have a total of three records but with consecutive SNJourney.

Thank you very much for your time and patience.

 

[CautionMP]

Here is a thought, Declare and set the value of each field in your record to a variable. Then create an If..Then statement that states IF varFrequency > 1 THEN run a LOOP statement that creates a new record using the values you stored in the variables with the last statement being varFrequency = varFrequency - 1 and LOOP UNTIL varFrequency = 0. If you use this method remember to intially set the varFrequency = Frequency -1 because you already have the first record in your table. Hope this helps.

 

[diacos]

CautionMP

Thanks for your tip which sounds reasonble.

However, since my vba coding is very poor I would much appreciate it if you could provide me some more help with the coding.

 

[CautionMP]

Dim db As Database
Dim rec As Recordset
Dim sRegNo As String
Dim iSNJourney As Interger
Dim iCol3 As Interger
Dim iCol4 As Interger
Dim iFreq As Interger

sRegNo = Me.RegNo 'Sets var = form control
iSNJourney = Me.ISJourney 'Sets var = form control
iCol3 = Me.Col3 'Sets var = form control
ICol4 = Me.Col4 'Sets var = form control
iFreq = Me.Freq 'Sets var = form control

Set db = CurrentDB
Set rec = db.OpenRecordset(TableName)

Do
  If iFreq > 1 Then
    rec.AddNew
    rec!RegNo = sRegNo
    rec!SNJourney = iSNJourney + 1
    rec!Col3 = iCol3
    rec!Col4 = iCol4
    rec!Freq = iFreq
    rec.Update
  iFreq = iFreq -1
  End If
Loop Until iFreq = 0
[/Code}
If you use this method your form does not need to bound to your table, by setting the "On Click" event for a "Submit button on your form this code should add the appropriate number of records to your table.