Append table with Increment based on value in textbox

  • Thread starter Thread starter ChoonBoy
  • Start date Start date
C

ChoonBoy

I have a form with a button named "Add" and 3 text boxes. txtTimes (long Int)
, txtprefix (text) and txtStartNos (long int)

1) txtprefix has a value of "CN"
2) txtStartNos has a value of 200
3) txtTimes has a value of 25. (to signify the number of rows to append)

When the button "Add" is clicked, I want the field (SeqNos) in table
(Tbl_Add) to be appended with the following based on the above 1,2 and 3

CN201
CN202
CN203
CN204
CN205
CN206
CN207
CN208
CN209 until CN225.

Is the above possible, please help.

Thanks
 
It's definitely doable, but it's normally recommended that you not store
"place holder" records: that you wait until there's data to associated with
the records before you store them.

If you're determined, though, something like the following should work:

Private Sub Add_Click()
Dim dbCurr As DAO.Database
Dim lngLoop As Long
Dim lngStart As Long
Dim lngTimes As Long
Dim lngValue As Long
Dim strMsg As String
Dim strSQL As String

If Len(Me.txtPrefix & vbNullString) = 0 Then
strMsg = strMsg & "You must supply a prefix." & vbCrLf
End If

If Len(Me.txtStartNos & vbNullString) = 0 Then
strMsg = strMsg & "You must supply a start number." & vbCrLf
Else
If IsNumeric(Me.txtStartNos) = False Then
strMsg = strMsg & "Start number must be numeric." & vbCrLf
End If
End If

If Len(Me.txtTimes & vbNullString) = 0 Then
strMsg = strMsg & "You must supply a Times number." & vbCrLf
Else
If IsNumeric(Me.txtTimes) = False Then
strMsg = strMsg & "Times must be numeric." & vbCrLf
Else
lngTimes = CLng(Me.txtTimes)
If lngTimes <= 0 Then
strMsg = strMsg & "Times must be positive." & vbCrLf
End If
End If
End If

If Len(strMsg) > 0 Then
MsgBox strMsg, vbOkOnly + vbCritical
Else
Set dbCurr = CurrentDb()
lngStart = CLng(Me.txtStartNos)
lngLoop = 1
For lngValue = lngStart To (lngStart + lngTimes)
strSQL = "INSERT INTO Tbl_Add (SeqNos) " & _
"VALUES (" & lngValue & ")"
dbCurr.Execute strSQL, dbFailOnError
Next lngValue
Set dbCurr = Nothing
End If

End Sub
 
ChoonBoy said:
I have a form with a button named "Add" and 3 text boxes. txtTimes (long
Int)
, txtprefix (text) and txtStartNos (long int)

1) txtprefix has a value of "CN"
2) txtStartNos has a value of 200
3) txtTimes has a value of 25. (to signify the number of rows to append)

When the button "Add" is clicked, I want the field (SeqNos) in table
(Tbl_Add) to be appended with the following based on the above 1,2 and 3

CN201
CN202
CN203
CN204
CN205
CN206
CN207
CN208
CN209 until CN225.

Is the above possible, please help.
Click to expand...


You mean you want to append 25 records (in this case) to the table, with
values in the field SeqNos from "CN201" to "CN225"? Are you sure you want
201 to 225, when txtStartNos is 200? It occurs to me that you might want
200 to 224.

You could do what you've asked using code like this:

'------ start of example code ------
Private Sub cmdAdd_Click()

On Error GoTo Err_Handler

Dim db As DAO.Database
Dim lngTimes As Long
Dim lngSeqNo As Long
Dim lngDuplicates As Long

If IsNull(Me.txtPrefix) _
Or IsNull(Me.txtStartNos) _
Or IsNull(Me.txtTimes) _
Then
MsgBox _
"You must fill in all the details before clicking this button",
_
vbExclamation,
"Missing Information"
Else

Set db = CurrentDb

lngSeqNo = Me.txtStartNos
lngTimes = Me.txtTimes

While lngTimes > 0

lngSeqNo = lngSeqNo + 1

db.Execute _
"INSERT INTO Tbl_Add (SeqNos) VALUES('" & _
Me.txtPrefix & Format(lngSeqNo, "000") & "')", _
dbFailOnError

NEXT_RECORD:
' Prepare for next iteration.
lngTimes = lngTimes - 1
Wend

End If

If lngDuplicates > 0 Then
MsgBox _
"Warning: " & lngDuplicates & " record(s) were not added " & _
"because their keys were already on file.", _
vbInformation,
"Duplicate Records Not Added"
End If

Exit_Point:
Exit Sub

Err_Handler:
If Err.Number = 3022 Then ' Duplicate key
lngDuplicates = lngDuplicates + 1
Resume NEXT_RECORD
Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End If

End Sub
'------ end of example code ------

That's air code, but it should at least give you the idea.
 
Thank you very much, your code works very well, and you are right I wanted
the 200 - 224 as per the label Start Nos.

Regards
 
ChoonBoy said:
Thank you very much, your code works very well, and you are right I wanted
the 200 - 224 as per the label Start Nos.
Click to expand...

In that case, you should move this line:

.... to come after this line:

That ought to give you the desired range of numbers.
 
Back
Top