Repeat Entries

N

Nylex

I have a stock and Invoice system that is working very accurately since
asking for help twice on this site and both times they were great

When entering the purchases I am trying to expand the system so that we can
print labels before the stock is put on the shelf

I have a label table with SerialNumber and SalePrice in it

I need to put into the file as many entries as we purchased so the the Avery
Wizard will automatically print the labels for us
Dim ItemNos as Integer
ItemNos = Me![Quantity]
For i = 1 To ItemNos
DoCmd.OpenForm "Temp PopUp", , , , acFormAdd, acDialog
Forms![Temp PopUp]![SerialNumber] = Me![SerialNumber] (Stops Here)
Forms![Temp PopUp]![SalePrice] = Me![SalePrice]
DoCmd.Save
DoCmd.Close
Next i
Exit Sub
Form Opens and then the Error comes up on the next lines


I can open the file but then is stops
 
K

Ken Snell \(MVP\)

When you open a form in Dialog mode, your code pauses at that point and
waits for the opened form to either be closed or made invisible, then the
code continues. Assuming that you're closing the dialog form, your code then
cannot find the form for the code step on which it errors.

What are you wanting to do in the popup form -- are you adding data to a
table? Provide us with more information and we should be able to help you do
what you seek.

--

Ken Snell
<MS ACCESS MVP>


Nylex said:
I have a stock and Invoice system that is working very accurately since
asking for help twice on this site and both times they were great

When entering the purchases I am trying to expand the system so that we
can
print labels before the stock is put on the shelf

I have a label table with SerialNumber and SalePrice in it

I need to put into the file as many entries as we purchased so the the
Avery
Wizard will automatically print the labels for us
Dim ItemNos as Integer
ItemNos = Me![Quantity]
For i = 1 To ItemNos
DoCmd.OpenForm "Temp PopUp", , , , acFormAdd, acDialog
Forms![Temp PopUp]![SerialNumber] = Me![SerialNumber] (Stops Here)
Forms![Temp PopUp]![SalePrice] = Me![SalePrice]
DoCmd.Save
DoCmd.Close
Next i
Exit Sub
Form Opens and then the Error comes up on the next lines


I can open the file but then is stops
Click to expand...
 
N

Nylex

I need to make from 1 to up to 20 entries in the Table

That way the Avery Wizard will print out 20 labels so that 1 can be put on
each item in stock - when they are sold we make out a delivery docket and the
label is removed from the stock and put on the delivery docket

This way we hope to get rid of errors that happen when staff write down the
wrong codes and the customer gets charged the wrong items


Ken Snell (MVP) said:
When you open a form in Dialog mode, your code pauses at that point and
waits for the opened form to either be closed or made invisible, then the
code continues. Assuming that you're closing the dialog form, your code then
cannot find the form for the code step on which it errors.

What are you wanting to do in the popup form -- are you adding data to a
table? Provide us with more information and we should be able to help you do
what you seek.

--

Ken Snell
<MS ACCESS MVP>


Nylex said:
I have a stock and Invoice system that is working very accurately since
asking for help twice on this site and both times they were great

When entering the purchases I am trying to expand the system so that we
can
print labels before the stock is put on the shelf

I have a label table with SerialNumber and SalePrice in it

I need to put into the file as many entries as we purchased so the the
Avery
Wizard will automatically print the labels for us
Dim ItemNos as Integer
ItemNos = Me![Quantity]
For i = 1 To ItemNos
DoCmd.OpenForm "Temp PopUp", , , , acFormAdd, acDialog
Forms![Temp PopUp]![SerialNumber] = Me![SerialNumber] (Stops Here)
Forms![Temp PopUp]![SalePrice] = Me![SalePrice]
DoCmd.Save
DoCmd.Close
Next i
Exit Sub
Form Opens and then the Error comes up on the next lines


I can open the file but then is stops
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

Then let's forget the dialog form if all you want to do is add records to a
table; it's easier to do that by running a query from code.

What values do you need to put into the table? What is the table name? What
is the RecordSource of the popup form that you're currently trying to use?
--

Ken Snell
<MS ACCESS MVP>




Nylex said:
I need to make from 1 to up to 20 entries in the Table

That way the Avery Wizard will print out 20 labels so that 1 can be put on
each item in stock - when they are sold we make out a delivery docket and
the
label is removed from the stock and put on the delivery docket

This way we hope to get rid of errors that happen when staff write down
the
wrong codes and the customer gets charged the wrong items


Ken Snell (MVP) said:
When you open a form in Dialog mode, your code pauses at that point and
waits for the opened form to either be closed or made invisible, then the
code continues. Assuming that you're closing the dialog form, your code
then
cannot find the form for the code step on which it errors.

What are you wanting to do in the popup form -- are you adding data to a
table? Provide us with more information and we should be able to help you
do
what you seek.

--

Ken Snell
<MS ACCESS MVP>


Nylex said:
I have a stock and Invoice system that is working very accurately since
asking for help twice on this site and both times they were great

When entering the purchases I am trying to expand the system so that we
can
print labels before the stock is put on the shelf

I have a label table with SerialNumber and SalePrice in it

I need to put into the file as many entries as we purchased so the the
Avery
Wizard will automatically print the labels for us


Dim ItemNos as Integer
ItemNos = Me![Quantity]
For i = 1 To ItemNos
DoCmd.OpenForm "Temp PopUp", , , , acFormAdd, acDialog
Forms![Temp PopUp]![SerialNumber] = Me![SerialNumber] (Stops Here)
Forms![Temp PopUp]![SalePrice] = Me![SalePrice]
DoCmd.Save
DoCmd.Close
Next i
Exit Sub


Form Opens and then the Error comes up on the next lines


I can open the file but then is stops
Click to expand...
Click to expand...
Click to expand...
 
N

Nylex

Table Name is "Labels"

The info to enter into this table is "SerialNo" and "SellPrice"

I am using a form to enter the purchases and they are entered by the
suppliers "SerialNo" and all the other information - The form is called
"Invoice Details Subform" - the last field is where the system calculates the
price to be sold at - field is named "SellPrice" - these are the two fields
that need to be added to the Table - In "Invoice Details Subform" there is a
field named "Quantity" and depending the number purchased then that decides
how many entrys need to be made. Can be up to 20 items - then 20 labels - one
for each item being placed in stock.


Ken Snell (MVP) said:
Then let's forget the dialog form if all you want to do is add records to a
table; it's easier to do that by running a query from code.

What values do you need to put into the table? What is the table name? What
is the RecordSource of the popup form that you're currently trying to use?
--

Ken Snell
<MS ACCESS MVP>




Nylex said:
I need to make from 1 to up to 20 entries in the Table

That way the Avery Wizard will print out 20 labels so that 1 can be put on
each item in stock - when they are sold we make out a delivery docket and
the
label is removed from the stock and put on the delivery docket

This way we hope to get rid of errors that happen when staff write down
the
wrong codes and the customer gets charged the wrong items


Ken Snell (MVP) said:
When you open a form in Dialog mode, your code pauses at that point and
waits for the opened form to either be closed or made invisible, then the
code continues. Assuming that you're closing the dialog form, your code
then
cannot find the form for the code step on which it errors.

What are you wanting to do in the popup form -- are you adding data to a
table? Provide us with more information and we should be able to help you
do
what you seek.

--

Ken Snell
<MS ACCESS MVP>


I have a stock and Invoice system that is working very accurately since
asking for help twice on this site and both times they were great

When entering the purchases I am trying to expand the system so that we
can
print labels before the stock is put on the shelf

I have a label table with SerialNumber and SalePrice in it

I need to put into the file as many entries as we purchased so the the
Avery
Wizard will automatically print the labels for us


Dim ItemNos as Integer
ItemNos = Me![Quantity]
For i = 1 To ItemNos
DoCmd.OpenForm "Temp PopUp", , , , acFormAdd, acDialog
Forms![Temp PopUp]![SerialNumber] = Me![SerialNumber] (Stops Here)
Forms![Temp PopUp]![SalePrice] = Me![SalePrice]
DoCmd.Save
DoCmd.Close
Next i
Exit Sub


Form Opens and then the Error comes up on the next lines


I can open the file but then is stops
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

Then this code should work, without the need for the dialog form (the code
assumes that SerialNo field is a text field):


Dim dbs AS DAO.Database
Dim ItemNos as Integer, i As Integer
Dim strSQL As String
On Error GoTo Err_Msg
ItemNos = Me![Quantity]
Set dbs = CurrentDb
For i = 1 To ItemNos
strSQL = "INSERT INTO Labels ( SerialNo, SellPrice ) " & _
"VALUES ( '" & Me![SerialNumber].Value & "', " & _
Me![SalePrice] & " );"
dbs.Execute strSQL, dbFailOnError
Next i

Exit_B:
On Error Resume Next
dbs.Close
Exit Sub

Err_Msg:
MsgBox "Error occurred when entering record # " & ItemNos & _
": Error # " & Err.Number & " -- " & Err.Description
Resume Exit_B
 
N

Nylex

Tks That worked fine - Only one error

In the form "Invoice Details Subform" there is a Combo Box that you enter
the "Serial No" into - this is where it gets all its information from
Products Table - but it stores into that field the table "ProductsID" not the
serial no - so the ProductID, not SerialNo went into the entries in the
Labels Table - the entries it made was the correct amount and the Selling
Price was correct on all entries


Nylex said:
I have a stock and Invoice system that is working very accurately since
asking for help twice on this site and both times they were great

When entering the purchases I am trying to expand the system so that we can
print labels before the stock is put on the shelf

I have a label table with SerialNumber and SalePrice in it

I need to put into the file as many entries as we purchased so the the Avery
Wizard will automatically print the labels for us
Dim ItemNos as Integer
ItemNos = Me![Quantity]
For i = 1 To ItemNos
DoCmd.OpenForm "Temp PopUp", , , , acFormAdd, acDialog
Forms![Temp PopUp]![SerialNumber] = Me![SerialNumber] (Stops Here)
Forms![Temp PopUp]![SalePrice] = Me![SalePrice]
DoCmd.Save
DoCmd.Close
Next i
Exit Sub
Form Opens and then the Error comes up on the next lines


I can open the file but then is stops
Click to expand...
 
K

Ken Snell \(MVP\)

Are you asking for assistance regarding this SerialNo issue? Not sure if you
fixed that yourself or you need help.

If the combo box contains the Serial Number in one of the columns of the
combo box (perhaps column 2?), then you can modify the code I provided
earlier to read that second column instead of the value of the combo box
(note: Column is a zero-based property, so column zero is the first column,
column 1 is the second column, etc.):


Dim dbs AS DAO.Database
Dim ItemNos as Integer, i As Integer
Dim strSQL As String
On Error GoTo Err_Msg
ItemNos = Me![Quantity]
Set dbs = CurrentDb
For i = 1 To ItemNos
strSQL = "INSERT INTO Labels ( SerialNo, SellPrice ) " & _
"VALUES ( '" & Me![SerialNumber].Column(1) & "', " & _
Me![SalePrice] & " );"
dbs.Execute strSQL, dbFailOnError
Next i

Exit_B:
On Error Resume Next
dbs.Close
Exit Sub

Err_Msg:
MsgBox "Error occurred when entering record # " & ItemNos & _
": Error # " & Err.Number & " -- " & Err.Description
Resume Exit_B


--

Ken Snell
<MS ACCESS MVP>


Nylex said:
Tks That worked fine - Only one error

In the form "Invoice Details Subform" there is a Combo Box that you enter
the "Serial No" into - this is where it gets all its information from
Products Table - but it stores into that field the table "ProductsID" not
the
serial no - so the ProductID, not SerialNo went into the entries in the
Labels Table - the entries it made was the correct amount and the Selling
Price was correct on all entries


Nylex said:
I have a stock and Invoice system that is working very accurately since
asking for help twice on this site and both times they were great

When entering the purchases I am trying to expand the system so that we
can
print labels before the stock is put on the shelf

I have a label table with SerialNumber and SalePrice in it

I need to put into the file as many entries as we purchased so the the
Avery
Wizard will automatically print the labels for us
Dim ItemNos as Integer
ItemNos = Me![Quantity]
For i = 1 To ItemNos
DoCmd.OpenForm "Temp PopUp", , , , acFormAdd, acDialog
Forms![Temp PopUp]![SerialNumber] = Me![SerialNumber] (Stops Here)
Forms![Temp PopUp]![SalePrice] = Me![SalePrice]
DoCmd.Save
DoCmd.Close
Next i
Exit Sub
Form Opens and then the Error comes up on the next lines


I can open the file but then is stops
Click to expand...
Click to expand...
 
N

Nylex

Tks Ken - it was still the same query for help

I have made the changes you have suggested and they worked perfectly - i
entered an Invoice and when I finished the Wizard printed out all the labels

Tks again - reading through all the help topics I would not have been able
to come up with the way you have done it
Tks again


Ken Snell (MVP) said:
Are you asking for assistance regarding this SerialNo issue? Not sure if you
fixed that yourself or you need help.

If the combo box contains the Serial Number in one of the columns of the
combo box (perhaps column 2?), then you can modify the code I provided
earlier to read that second column instead of the value of the combo box
(note: Column is a zero-based property, so column zero is the first column,
column 1 is the second column, etc.):


Dim dbs AS DAO.Database
Dim ItemNos as Integer, i As Integer
Dim strSQL As String
On Error GoTo Err_Msg
ItemNos = Me![Quantity]
Set dbs = CurrentDb
For i = 1 To ItemNos
strSQL = "INSERT INTO Labels ( SerialNo, SellPrice ) " & _
"VALUES ( '" & Me![SerialNumber].Column(1) & "', " & _
Me![SalePrice] & " );"
dbs.Execute strSQL, dbFailOnError
Next i

Exit_B:
On Error Resume Next
dbs.Close
Exit Sub

Err_Msg:
MsgBox "Error occurred when entering record # " & ItemNos & _
": Error # " & Err.Number & " -- " & Err.Description
Resume Exit_B


--

Ken Snell
<MS ACCESS MVP>


Nylex said:
Tks That worked fine - Only one error

In the form "Invoice Details Subform" there is a Combo Box that you enter
the "Serial No" into - this is where it gets all its information from
Products Table - but it stores into that field the table "ProductsID" not
the
serial no - so the ProductID, not SerialNo went into the entries in the
Labels Table - the entries it made was the correct amount and the Selling
Price was correct on all entries


Nylex said:
I have a stock and Invoice system that is working very accurately since
asking for help twice on this site and both times they were great

When entering the purchases I am trying to expand the system so that we
can
print labels before the stock is put on the shelf

I have a label table with SerialNumber and SalePrice in it

I need to put into the file as many entries as we purchased so the the
Avery
Wizard will automatically print the labels for us


Dim ItemNos as Integer
ItemNos = Me![Quantity]
For i = 1 To ItemNos
DoCmd.OpenForm "Temp PopUp", , , , acFormAdd, acDialog
Forms![Temp PopUp]![SerialNumber] = Me![SerialNumber] (Stops Here)
Forms![Temp PopUp]![SalePrice] = Me![SalePrice]
DoCmd.Save
DoCmd.Close
Next i
Exit Sub


Form Opens and then the Error comes up on the next lines


I can open the file but then is stops
Click to expand...
Click to expand...
Click to expand...
 
Top