Using an Access form to Mass Update a table

[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.

 

[XPS350]

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.

Try:
strSQL = strSQL & "VALUES('" Me.PartNumber & "','" & Me.RevNumber &
"'," & me.SerialNumber+intCount & ")"

(I asume PartNumber and RevNumber to be text and SerialNumber nemeric)

Instead:
strSQL = strSQL & "VALUES(Me.PartNumber & Me.RevNumber &
me.SerialNumber+1)"


Groeten,

Peter
http://access.xps350.com

 

[John W. Vinson]

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.

The VALUES() syntax is designed to insert a single row with a literal value -
since your Me.PartNumber and so on are inside the parentheses, it's trying to
insert those *names* into the table, rather than the values in those form
controls!

This kind of operation is better handled with a single-pass append query,
using a handy little auxiliary table. Any database I build will have a table
named Num, with one long integer field N, prefilled with values from 0 to
10000 or so (you can use Excel to quickly create the data with Insert... Fill
Series and copy and paste into your table).

Try creating such a table and change your code to:

Dim strSQL As String
Dim intCount As Integer
Dim db As DAO.Database

Set db = CurrentDb
strSQL = "INSERT INTO qryPartSerialNumber " _
& "(PartNum, RevNumber,SerialNumber) " _
& "SELECT '" & Me.PartNumber & "', " & Me.RevNumber _
& ", N + " & Me.SerialNumber & " FROM Num " _
& "WHERE N <= " & Me.[Quantity] & ";"
db.Execute strSQL, dbFailOnError

The SQL string should end up looking like

INSERT INTO qryPartSerialNumber (PartNum, RevNumber, SerialNumber) SELECT
'12345A', 2, 3256 + N FROM Num WHERE N <= 5;

You can use

DMax("[SerialNumber]", "yourtablename")

instead of having to put the current largest serial number on the form if you
prefer.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[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.

The VALUES() syntax is designed to insert a single row with a literal value -
since your Me.PartNumber and so on are inside the parentheses, it's trying to
insert those *names* into the table, rather than the values in those form
controls!

This kind of operation is better handled with a single-pass append query,
using a handy little auxiliary table. Any database I build will have a table
named Num, with one long integer field N, prefilled with values from 0 to
10000 or so (you can use Excel to quickly create the data with Insert... Fill
Series and copy and paste into your table).

Try creating such a table and change your code to:

Dim strSQL As String
Dim intCount As Integer
Dim db As DAO.Database

Set db = CurrentDb
strSQL = "INSERT INTO qryPartSerialNumber " _
   & "(PartNum, RevNumber,SerialNumber) " _
   & "SELECT '" & Me.PartNumber & "', " & Me.RevNumber _
   & ", N + " & Me.SerialNumber & " FROM Num " _
   & "WHERE N <= " & Me.[Quantity] & ";"
db.Execute strSQL, dbFailOnError

The SQL string should end up looking like

INSERT INTO qryPartSerialNumber (PartNum, RevNumber, SerialNumber) SELECT
'12345A', 2, 3256 + N FROM Num WHERE N <= 5;

You can use

DMax("[SerialNumber]", "yourtablename")

instead of having to put the current largest serial number on the form ifyou
prefer.
--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Fo...al.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

Thank you so much for your input. I tried what you suggested. If I
create a table with those three fields it works great. Trying to
update my query doesn't work. I get a RunTime error 3113 Cannot
update "RevNumber". field is not updateable.

I checked the query. tblParts is tied to tblMasterSerial on partid
field. This is an autonumber field in the tblParts table.
tblMasterSerial has this field as a number field. I am sure I have to
do something with this field to update....just not sure what to do .

Any additional help would be appreciated.

Thank you.

 

[John W. Vinson]

Thank you so much for your input. I tried what you suggested. If I
create a table with those three fields it works great. Trying to
update my query doesn't work. I get a RunTime error 3113 Cannot
update "RevNumber". field is not updateable.

I checked the query. tblParts is tied to tblMasterSerial on partid
field. This is an autonumber field in the tblParts table.
tblMasterSerial has this field as a number field. I am sure I have to
do something with this field to update....just not sure what to do .

Please post the fieldnames and datatypes of the fields in your table, and the
SQL view of the query you're trying to run.

I was assuming you would be running an *APPEND* query to create new records in
the table, rather than an *UPDATE* query which changes values in records which
already exist - which did you use?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[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.

The VALUES() syntax is designed to insert a single row with a literal value -
since your Me.PartNumber and so on are inside the parentheses, it's trying to
insert those *names* into the table, rather than the values in those form
controls!

This kind of operation is better handled with a single-pass append query,
using a handy little auxiliary table. Any database I build will have a table
named Num, with one long integer field N, prefilled with values from 0 to
10000 or so (you can use Excel to quickly create the data with Insert.... Fill
Series and copy and paste into your table).

Try creating such a table and change your code to:

Dim strSQL As String
Dim intCount As Integer
Dim db As DAO.Database

Set db = CurrentDb
strSQL = "INSERT INTO qryPartSerialNumber " _
   & "(PartNum, RevNumber,SerialNumber) " _
   & "SELECT '" & Me.PartNumber & "', " & Me.RevNumber _
   & ", N + " & Me.SerialNumber & " FROM Num " _
   & "WHERE N <= " & Me.[Quantity] & ";"
db.Execute strSQL, dbFailOnError

The SQL string should end up looking like

INSERT INTO qryPartSerialNumber (PartNum, RevNumber, SerialNumber) SELECT
'12345A', 2, 3256 + N FROM Num WHERE N <= 5;

You can use

DMax("[SerialNumber]", "yourtablename")

instead of having to put the current largest serial number on the form if you
prefer.
--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Forums/en-US/accessdev/http://social...
and see alsohttp://www.utteraccess.com-Hide quoted text -

- Show quoted text -

Thank you so much for your input.  I tried what you suggested.  If I
create a table with those three fields it works great.  Trying to
update my query doesn't work.  I get a RunTime error 3113 Cannot
update "RevNumber".  field is not updateable.

I checked the query.  tblParts is tied to tblMasterSerial on partid
field.  This is an autonumber field in the tblParts table.
tblMasterSerial has this field as a number field.  I am sure I have to
do something with this field to update....just not sure what to do .

Any additional help would be appreciated.

Thank you.- Hide quoted text -

- Show quoted text -

I got that part working. I took out the part number because it
already exists. This is the code I am using that is working.

Dim strSQL As String
Dim intCount As Integer
Dim db As DAO.Database

Set db = CurrentDb
strSQL = "INSERT INTO qryPartSerialNumber " _
& "(PartID, RevNumber, SerialNumber) " _
& "SELECT " & Me.PartID & ", " & Me.RevNumber _
& ", N + " & Me.StartingSerial & " FROM Num " _
& "WHERE N <= " & Me.[Quantity] & ";"
db.Execute strSQL, dbFailOnError

My only problem now is that I want the form to insert based on
"startingserial" number then add to it. So if user types 123. I want
first entry to be 123 then add one for each added. So if 5 is typed
in quantity it would be:

123
124
125
126
127

Currently, when it runs I get:
124
125
126
127
128

Any thoughts?

 

[John W. Vinson]

My only problem now is that I want the form to insert based on
"startingserial" number then add to it. So if user types 123. I want
first entry to be 123 then add one for each added. So if 5 is typed
in quantity it would be:

123
124
125
126
127

Currently, when it runs I get:
124
125
126
127
128

Any thoughts?

Is there a record in table Num with a value of 0 for N? Or did you start with
1? The query assumes that there is a zero. Either add a 0 record and change
the query to

strSQL = "INSERT INTO qryPartSerialNumber " _
& "(PartID, RevNumber, SerialNumber) " _
& "SELECT " & Me.PartID & ", " & Me.RevNumber _
& ", N + " & Me.StartingSerial & " FROM Num " _
& "WHERE N < " & Me.[Quantity] & ";"
db.Execute strSQL, dbFailOnError

note the < rather than <= on the criteria; or leave the 0 out and change the
query to

strSQL = "INSERT INTO qryPartSerialNumber " _
& "(PartID, RevNumber, SerialNumber) " _
& "SELECT " & Me.PartID & ", " & Me.RevNumber _
& ", N + " & Me.StartingSerial & "-1 FROM Num " _
& "WHERE N <= " & Me.[Quantity] & ";"
db.Execute strSQL, dbFailOnError

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[clk]

My only problem now is that I want the form to insert based on
"startingserial" number then add to it.  So if user types 123.  I want
first entry to be 123 then add one for each added.  So if 5 is typed
in quantity it would be:

Currently, when it runs I get:
124
125
126
127
128

Any thoughts?

Is there a record in table Num with a value of 0 for N? Or did you start with
1? The query assumes that there is a zero. Either add a 0 record and change
the query to

strSQL = "INSERT INTO qryPartSerialNumber " _
   & "(PartID, RevNumber, SerialNumber) " _
   & "SELECT " & Me.PartID & ", " & Me.RevNumber _
   & ", N + " & Me.StartingSerial & " FROM Num " _
   & "WHERE N < " & Me.[Quantity] & ";"
db.Execute strSQL, dbFailOnError

note the < rather than <= on the criteria; or leave the 0 out and change the
query to

strSQL = "INSERT INTO qryPartSerialNumber " _
   & "(PartID, RevNumber, SerialNumber) " _
   & "SELECT " & Me.PartID & ", " & Me.RevNumber _
   & ", N + " & Me.StartingSerial & "-1 FROM Num " _
   & "WHERE N <= " & Me.[Quantity] & ";"
db.Execute strSQL, dbFailOnError

--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Fo...al.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

Thank you so much. I didn't start with 0....I started with 1. I
changed that and it all works now. Thanks again!!