Auto Fill between 2 numbers

[Idealsteve]

Hi

I have a order form for bus tickets which a person puts the number of the first bus ticket in the book they have ordered and the last number.

Is it possible to do something maybe an append query which will fill out all of the numbers in between the numbers they have put in, into a table.

Thanks
Steve

 

[pbaldy]

I would probably use the AddNew method of a recordset inside a For/Next loop that used your first and last numbers.

 

[apr pillai]

Here is some sample VBA Code for you to try it out on your Form:

Private Sub cmdGenRec_DblClick(Cancel As Integer)
Dim SN As Long, EN As Long, msg As String
Dim C As Long, db As Database, rst As Recordset
On Error GoTo cmdGenRec_DblClick_Err
Set db = CurrentDb
Set rst = db.OpenRecordset("myTable")
SN = Nz(Me![StartNumber], 0)
EN = Nz(Me![EndNumber], 0)
If EN <= 0 Then
   MsgBox "End Number = " & EN & " Invalid. Program Aborted."
   Exit Sub
End If
If SN < 0 Then
   MsgBox "Start Number = " & SN & " Invalid. Program Aborted."
   Exit Sub
End If

For C = SN + 1 To EN - 1 Step 1
  With rst
    .AddNew
    .Fields("myField").Value = C
    .Update
  End With
Next
  rst.Close

msgbox "Records created. " ,,"cmdGenRec_DblClick()"

cmdGenRec_DblClick_Exit:
Set rst = Nothing
Set db = Nothing
Exit Sub

cmdGenRec_DblClick_Err:
MsgBox Err & ": " & Err.Description, , "cmdGenRec_DblClick()"
Resume cmdGenRec_DblClick_Exit
End Sub

1. Create two Text Boxes with the names StartNumber, EndNumber on your Form.
2. Create a Command Button with the name cmdGenRec on the Form.
3. Select the DoubleClick() Event Procedure from the Event Tab of the property sheet.
4. Copy and paste the above code overwriting the existing VBA Code body.
5. Create a sample table with the name myTable and a single field myField with Long Integer data type.
6. Save the Table.
7. Open your Form in Normal View.
8. Enter Start Number and End Number into the text boxes and Double-click on the Command Button.

 

[pbaldy]

Why look, it's the AddNew method of a recordset and a For/Next loop!