Insert records access 2003/2007

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,
I need assistance with the right way to insert records to table.
I'm getting as an input from a user form the number of duplicates and the
identifier that says which record to duplicate. I need add letter (a,b,c...)
according to the counter. For example: the user put identifier FAL-3245 and
counter 10.
The outcome should be 9 new records with same values of all fields except
the identifier field that should be FAL-3245a,FAL-3245b,FAL-3245c till
FAL-3245i.
Any suggestions?
Thanks a lot in advance
 
Hi All,
I need assistance with the right way to insert records to table.
I'm getting as an input from a user form the number of duplicates and the
identifier that says which record to duplicate. I need add letter (a,b,c...)
according to the counter. For example: the user put identifier FAL-3245 and
counter 10.
The outcome should be 9 new records with same values of all fields except
the identifier field that should be FAL-3245a,FAL-3245b,FAL-3245c till
FAL-3245i.
Any suggestions?
Thanks a lot in advance
Click to expand...
Create an unbound form (don't specify a table or query as the
recordsource)
add two textboxes: txtLastChar, for the last character to create in
the sequence (between a-z) and txtParentRecordID, which is the prefix
for the record.
Create a button, cmdAddRecords then attach the below code.... you'll
need a reference to the DAO library.

Private Sub cmdAddRecords_Click()
Dim strEndChar As String
Dim intCurrentChar As Integer
Dim intStartChar As Integer
Dim intEndChar As Integer 'ASCII value of last character to be
created. (a-z)
Dim r As DAO.Recordset

Set r = DBEngine(0)(0).OpenRecordset("tblTest", dbOpenTable,
dbAppendOnly)
intStartChar = Asc("a")
strEndChar = LCase(Me.txtLastChar)
intEndChar = Asc(strEndChar)

For intCurrentChar = intStartChar To intEndChar
r.AddNew
r.Fields(0) = Me.txtParentRecordID & "-" & Chr(intCurrentChar)
r.Update
Next intCurrentChar

r.Close
Set r = Nothing
MsgBox "Records for [a-" & strEndChar & "]", vbOKOnly
End Sub
 
Hi All,
I need assistance with the right way to insert records to table.
I'm getting as an input from a user form the number of duplicates and the
identifier that says which record to duplicate. I need add letter (a,b,c...)
according to the counter. For example: the user put identifier FAL-3245 and
counter 10.
The outcome should be 9 new records with same values of all fields except
the identifier field that should be FAL-3245a,FAL-3245b,FAL-3245c till
FAL-3245i.
Any suggestions?
Thanks a lot in advance
Click to expand...
Create an unbound form (don't specify a table or query as the
recordsource)
add two textboxes: txtLastChar, for the last character to create in
the sequence (between a-z) and txtParentRecordID, which is the prefix
for the record.
Create a button, cmdAddRecords then attach the below code.... you'll
need a reference to the DAO library.

Private Sub cmdAddRecords_Click()
Dim strEndChar As String
Dim intCurrentChar As Integer
Dim intStartChar As Integer
Dim intEndChar As Integer 'ASCII value of last character to be
created. (a-z)
Dim r As DAO.Recordset

Set r = DBEngine(0)(0).OpenRecordset("tblTest", dbOpenTable,
dbAppendOnly)
intStartChar = Asc("a")
strEndChar = LCase(Me.txtLastChar)
intEndChar = Asc(strEndChar)

For intCurrentChar = intStartChar To intEndChar
r.AddNew
r.Fields(0) = Me.txtParentRecordID & "-" & Chr(intCurrentChar)
r.Update
Next intCurrentChar

r.Close
Set r = Nothing
MsgBox "Records for [a-" & strEndChar & "]", vbOKOnly
End Sub
 
Thanks pietlinden,
It works perfectly. The only thing that maybe I didn't mentioned is that
there are more fields to the table and just the Identifier field need to be
update. All other data (fields) need to be duplicate as is.
The outcome of your code is new records with only data in the Identifier
field according to the input from the text boxes.
How do I add all ther rest to those new records?
Thanks in advance

[email protected] said:
Hi All,
I need assistance with the right way to insert records to table.
I'm getting as an input from a user form the number of duplicates and the
identifier that says which record to duplicate. I need add letter (a,b,c...)
according to the counter. For example: the user put identifier FAL-3245 and
counter 10.
The outcome should be 9 new records with same values of all fields except
the identifier field that should be FAL-3245a,FAL-3245b,FAL-3245c till
FAL-3245i.
Any suggestions?
Thanks a lot in advance
Click to expand...
Create an unbound form (don't specify a table or query as the
recordsource)
add two textboxes: txtLastChar, for the last character to create in
the sequence (between a-z) and txtParentRecordID, which is the prefix
for the record.
Create a button, cmdAddRecords then attach the below code.... you'll
need a reference to the DAO library.

Private Sub cmdAddRecords_Click()
Dim strEndChar As String
Dim intCurrentChar As Integer
Dim intStartChar As Integer
Dim intEndChar As Integer 'ASCII value of last character to be
created. (a-z)
Dim r As DAO.Recordset

Set r = DBEngine(0)(0).OpenRecordset("tblTest", dbOpenTable,
dbAppendOnly)
intStartChar = Asc("a")
strEndChar = LCase(Me.txtLastChar)
intEndChar = Asc(strEndChar)

For intCurrentChar = intStartChar To intEndChar
r.AddNew
r.Fields(0) = Me.txtParentRecordID & "-" & Chr(intCurrentChar)
r.Update
Next intCurrentChar

r.Close
Set r = Nothing
MsgBox "Records for [a-" & strEndChar & "]", vbOKOnly
End Sub
Click to expand...
 
Back
Top