You can't do this with a query, but you can with code. Try this:
Create an unbound form. Add 4 unbound text boxes and 1 button.
Name the controls like this: "BegSerial", "EndSerial", "ManDate" and
"WO_Num" (without the quotes).
Name the button "cmdAdd"
In the code, in one place, you will need to change the name of the table
where you are adding the records. It is between two lines like this:
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Set the format of the control for the Manufacturing date to "Short Date".
This will check to see if you entered a date.
The following code is Untested!! You might want to add more error checking.
Watch for line wrap...
'*************************
Option Compare Database
Option Explicit
Private Sub BegSerial_AfterUpdate()
Me.BegSerial = Right("00000000" & Me.BegSerial, 8)
End Sub
Private Sub EndSerial_AfterUpdate()
Me.EndSerial = Right("00000000" & Me.EndSerial, 8)
End Sub
Private Sub cmdAdd_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim sTmp As String
Dim TableName As String
Dim intCount As Integer
Dim BSN As Long, ESN As Long ' BSN = beginning serial number
Dim k As Long
'***********************
'do some checks
'beginning serial number entered?
If CLng(Nz(Me.BegSerial, 0)) = 0 Then
MsgBox "Beginning Serial number required!!"
Me.BegSerial.SetFocus
Exit Sub
End If
'ending serial number entered?
If CLng(Nz(Me.EndSerial, 0)) = 0 Then
MsgBox "Ending Serial number required!!"
Me.EndSerial.SetFocus
Exit Sub
End If
'Manufacture Date entered?
If IsNull(Me.ManDate) Or Me.ManDate = "" Then
MsgBox "Manufacture Date required!!"
Me.ManDate.SetFocus
Exit Sub
End If
'Work Order number entered?
If CLng(Nz(Me.WO_Num, 0)) = 0 Then
MsgBox "Work number required!!"
Me.WO_Num.SetFocus
Exit Sub
End If
'is beginning SN < ending SN?
If CLng(Nz(Me.BegSerial, 0)) > CLng(Nz(Me.EndSerial, 0)) Then
sTmp = Me.BegSerial
Me.BegSerial = Me.EndSerial
Me.EndSerial = sTmp
End If
'***********************
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'change 'theTable' to your table name - ONLY HERE
TableName = "theTable"
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Set db = CurrentDb()
'open a recordset to check if there are existing SN between Beg and end
SN entered on form
strSQL = "Select * From '" & TableName & "'"
strSQL = strSQL & " Where SerialNumber"
strSQL = strSQL & " Between '" & CLng(Me.BegSerial) & "' And '" &
CLng(Me.EndSerial) & "'"
Set rs = db.OpenRecordset(strSQL)
If Not (rs.BOF And rs.EOF) Then
' SNs found! Send message and abort!!
MsgBox "Existing Serial numbers found between " & Me.BegSerial & " and
" & Me.EndSerial & "!! Check the serial numbers"
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Sub
End If
rs.Close
' now check for duplicate WO numbers
'if WO_Num is text datatype, use
Set rs = db.OpenRecordset("Select * From '" & TableName & "' Where WO_Num
= '" & Me.WO_Num & "'")
'if WO_Num is a number datatype, use
' Set rs = db.OpenRecordset("Select * From '" & TableName & "' Where
WO_Num = " & Me.WO_Num)
If Not (rs.BOF And rs.EOF) Then
'Duplicate Work Order number found
MsgBox "Duplicate Work Order number found!! Check the Work Order
number"
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Sub
End If
'OK, good to go
'convert SN string to long
BSN = CLng(Me.BegSerial)
ESN = CLng(Me.EndSerial)
intCount = 0
'~~~~~~~~~~~~~~~~~~
'this is where the new records are added
Set rs = db.OpenRecordset(TableName)
For k = BSN To ESN
rs.AddNew
rs!SERIALNUM = Right("00000000" & k, 8)
rs!ManDate = Me.ManDate
rs!WO_Num = Me.WO_Num
' more field can be added here
rs.Update
intCount = intCount + 1
Next k
'~~~~~~~~~~~~~~~~~~
rs.Close
Set rs = Nothing
Set db = Nothing
MsgBox "Done! Added " & intCount & " records"
'****************
'Clear the text boxes on the form
Me.BegSerial = Null
Me.EndSerial = Null
Me.ManDate = ""
Me.WO_Num = Null
' And don't forget to clear them here
End Sub
'*************************
HTH
