Adding multiple records

  • Thread starter Thread starter WWV
  • Start date Start date
W

WWV

I have a table that does not have a primary key. The user keys in a unique
number as the key field when they add a record. They have the need to add
multiple records that have the same information except for the key field
number they key in. they would like to be "asked" How Many Records Do You
Want To Add? key in an amount (5 for example) then key the information
once and have the database add the 5 records with the same data incrementing
the unique number field by 1.
Any Ideas?
 
You really should set the field type to autonumber for the
unique number. It would make things a lot easier/simpler.

But to answer your question, I need more information. The
information the user is entering... is it on a form?
 
I have a table that does not have a primary key. The user keys in a unique
number as the key field when they add a record.
Click to expand...

So? why not make this field the Primary Key? It's unique; it's stable;
it's short - the criteria for a Primary Key. Don't fall for
Microsoft's implication that a Primary Key should be an Autonumber!
They have the need to add
multiple records that have the same information except for the key field
number they key in. they would like to be "asked" How Many Records Do You
Want To Add? key in an amount (5 for example) then key the information
once and have the database add the 5 records with the same data incrementing
the unique number field by 1.
Any Ideas?
Click to expand...

You'ld need some VBA code to open a the Form's RecordsetClone and
append records to it - a bit more code than I'm comfortable writing
from scratch here. But consider adapting this button code. It uses an
accessory table Num which contains a single long integer field N, with
values from 0 to 1000.

Private Sub cmdDup_Click()
' Comments : Create txtHowMany dups of current record
' Parameters:
' Created : 05/30/03 17:11 JWV
' Modified :
'
' --------------------------------------------------

Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim iIssue As Integer
On Error GoTo PROC_ERR

' be sure the cost has been calculated
If IsNull(Me.txtCost) Then Call cmdCalc_Click

' Use the Num table as an accessory to add new records
strSQL = "INSERT INTO Ads (InvoiceID, IssueNo, AdTypeID, Width, " _
& "Height, Cost, Color, TearSheet) " _
& "SELECT " & Me!InvoiceID & " AS InvoiceID, [N] + " & Me.IssueNo & "
AS IssueNo, " _
& Me.AdTypeID & " AS AdTypeID, " _
& Nz(Me!txtWidth, 0) & " AS Width, " _
& Nz(Me!txtHeight, 0) & " AS Height, " _
& Me.Cost & " AS Cost, _
& Me.TearSheet & " AS TearSheet " _
& "FROM Num WHERE Num.N < " & Me.txtHowMany & " ORDER BY N;"

Set db = CurrentDb()
Set qd = db.CreateQueryDef("", strSQL)
qd.Execute dbFailOnError
Set qd = Nothing
Set db = Nothing
Me.txtHowMany = 1
Me.Requery

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & _
" in cmdDup_Click in Form_sbfrmAds:" & vbCrLf & Err.Description
Resume PROC_EXIT

End Sub
 
Sorry, There is a Primary Key called GC. This is Gift Certificate number
(they key it in). They wish to issue a number ie. 5 gift certs to a
corporate customer. Customer ordewrs 5 for $200.00 each with other info
added like sold to , address, etc. They enter the GC and other info on the
form and then have to repeat the process 4 more times (or how ever many they
need) They would like to enter the data onec and then have the DB increment
the GC number and duplicate the other fields (for how ever many they need).
Hope this helps (you and me)
Thanks in advance
 
Back
Top