Using a Form to Mass Update a Table

  • Thread starter Thread starter clk
  • Start date Start date
C

clk

Hi...I have a parts database. One part can have many serial numbers.
One to many relationship exists between Part table and Serial Number
table.

I have designed an unbound form with a drop-down field "Part Number".
They choose the part number, then fill in Serial Number, RevNumber and
Quantity.

I have added an update button with the following code. What I would
like is for the code to loop through and add the records for that item
based on the quantity. I found this code on forums and tweaked. It
is still not working.
Further explanation. If the quantity field is 5, I want it to insert
five rows under a specific part with rev number (always the same) and
serial number +1.

Main Form: Part Number: 12345A

Subform: Rev Number Serial Number
2 serial1
2 serial2
2 serial3
2 serial4
2 serial5


The code on my unbound form is:

Dim strSQL As String
Dim intCount As Integer

For intCount = 1 To Me![Quantity]

strSQL = "INSERT INTO qryPartSerialNumber (PartNum, RevNumber,
SerialNumber) "
strSQL = strSQL & "VALUES(Me.PartNumber & Me.RevNumber &
me.SerialNumber+1)"

DoCmd.RunSQL strSQL

Next intCount


Any help is greatly appreciated.
 
m:
Hi...I have a parts database. One part can have many serial
numbers. One to many relationship exists between Part table and
Serial Number table.

I have designed an unbound form with a drop-down field "Part
Number". They choose the part number, then fill in Serial Number,
RevNumber and Quantity.

I have added an update button with the following code. What I
would like is for the code to loop through and add the records for
that item based on the quantity. I found this code on forums and
tweaked. It is still not working.
Further explanation. If the quantity field is 5, I want it to
insert five rows under a specific part with rev number (always the
same) and serial number +1.

Main Form: Part Number: 12345A

Subform: Rev Number Serial Number
2 serial1
2 serial2
2 serial3
2 serial4
2 serial5


The code on my unbound form is:

Dim strSQL As String
Dim intCount As Integer

For intCount = 1 To Me![Quantity]

strSQL = "INSERT INTO qryPartSerialNumber (PartNum, RevNumber,
SerialNumber) "
strSQL = strSQL & "VALUES(Me.PartNumber & Me.RevNumber &
me.SerialNumber+1)"

DoCmd.RunSQL strSQL

Next intCount


Any help is greatly appreciated.
Click to expand...

If partNumber is a numeric type:
strSQL = strSQL & "VALUES(Me.PartNumber, _
Me.RevNumber, _
me.SerialNumber+1)"

If it is a string:
strSQL = strSQL & "VALUES("'" & Me.PartNumber & "'", _
Me.RevNumber, _
me.SerialNumber+1)"
 
Back
Top