Add muliple rows

[dragonmaster]

Hi all,

I have a table with customers, a table with products and a table with serialnumbers. When I add a new product I want to create the same rows with a different serialnumber or add new serialnumbers for an allready existing product. Basically what I am looking for is when I select a product I get prompted (or how ever possible) with the question how many serialnumbers I wish to create.
Example: I have product X. My last serialnumber is 100. I want to generate 25 serial numbers. I would be asked how many I want and it would automaticaly insert 25 new rows in my DB for product X with different serialnumbers 101, 102, 103 etc etc.

Is this possible and how would I go about in doing this.

Any help would be gratefully appreciated.

Thank you

Timo

 

[PHV]

Take a look at append query:
DoCmd.RunSQL "INSERT INTO theTable (field1,....,fieldN) VALUES (value1,...valueN)"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[gunnie]

I need to exactly the same things, the reply you have received does not seem to be the answer. Please advise if you have worked this out as I am pulling my hair out trying to find the same solution.

Thanks
craig

 

[PHV]

If you don't like the append queries then play with the AddNew method of the Recordset object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[gunnie]

Thanks for the response...
It's not that I dont like your append solution,but how would I use this to paste a number of rows based on the value in an uound field for quantity of one product with the same serial number.

Thanks again.

 

[PHV]

And what have you so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[gunnie]

At the moment I have a command button..

Private Sub Command122_Click()
Dim sClient As String
Dim sDevice_ID As String
Dim sCon_In As String
Dim sStatus As String
Dim sReceived As String
Dim sSN As String

sClient = Client
sDevice_ID = Device_ID
sCon_In = Con_In
sStatus = Status
sReceived = Received
sSN = SN

DoCmd.GoToRecord , , acNewRec

Client = sClient
Device_ID = sDevice_ID
Con_In = sCon_In
Status = sStatus
Received = sReceived
SN = sSN

End Sub

 

[PHV]

And the goal is ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[gunnie]

Well at the moment it just copies and pastes one record. I want to have another unbound field with the number of times I want the record to be pasted.

The field for entering the qty is not my problem but rather the code to loop it for n times.

So basically some sort of loop that will continue for the number of times specified on the qty unbound field.

 

[PHV]

Something like this ?
For i = 1 To Me![number of times]
DoCmd.GoToRecord , , acNewRec
Client = sClient
Device_ID = sDevice_ID
Con_In = sCon_In
Status = sStatus
Received = sReceived
SN = sSN
DoCmd.RunCommand acCmdSaveRecord
Next i

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[gunnie]

Thanks PH... that did the trick.

Here is the final code.

Private Sub Repeat_AfterUpdate()
Dim sClient As String
Dim sDevice_ID As String
Dim sCon_In As String
Dim sStatus As String
Dim sReceived As String
Dim sSN As String

sClient = NewClient
sDevice_ID = NewDevice
sCon_In = ConIN
sStatus = NewStatus
sReceived = Date
sSN = NewSerial

For i = 1 To Me![Repeat]
DoCmd.GoToRecord , , acNewRec

Client = sClient
Device_ID = sDevice_ID
Con_In = sCon_In
Status = sStatus
Received = sReceived
SN = sSN
Next i
End Sub