Duplicates

[Susan Allen Wyoming]

I'm not sure if I should be in the Forms forum or report forum....but here goes.

I'm trying to view/print duplicate price tags for products based on the quantity on order.

In otherwords, I have one line item in the Orders Form that has 10 items on order and need to print 10 price tags for that item.

The Orders Form is a subform linked to the parent form Purchase Order. I would like to create a button on my Purchase Order that will preview/print all of the records for the subform x the number I have ordered.

I desperately need help....as garden center owner, all of my products are arriving by the truckload....daily and I have no price tags. If I have no price tags, my customers get upset that they don't know how much things cost and then they don't shop my store, Home Depot beats me up, I go bankrupt, my parents who are 70 go bankrupt and I have to concede to Microsoft Access.

Any help would be much appreciated!

Susan Allen

 

[KevinM]

Create your report for your price tags.
Include on the report the ID field that matches the ID field in your Orders table (i.e Product ID), it can be set to invisible.

Create a command button on your main form to print/preview the report.

You then need to add a WHERE clause in the Docmd.OpenReport....

Use this....

"[ProductID]=[Forms]![MainFormName]![SubformName]![ProductID]"

Incl double quotes

hth

 

[Susan Allen Wyoming]

I'm not sure I understand why I need the where clause? Is it to link the form and the report or to create the duplicates.

I have tried creating a table through a make table query and then deleting the contents each time I print new price tags, but the code is frightening. I'm willing to do this if I could only understand the code.

I have also tried creating a function that asks for a value when I enter the report and will print duplicates. But if I enter, lets say a 10....it will print 10 duplicates for all of my records regardless of how many I really need for each item.

Does anybody have any insight?

Many thanks in advance.

Sue

 

[chrismcbride]

I do something quite like this in that I need to print product id lables to add to each product received in a particular order. Of course there are instances when more than one of each product is ordered. So this is what I do.

1. Create a temporary table that will hold the information that will be printed on each lable/price tag. Create a separate field for each separate piece of information required.

2. Use the Label Wizard to make a Label Report based on the Temporary table.

3. On your Purchase Order form, place your button that will fire your procedure.

4. Then you need a bit of code. I have included the code I use to make my labels. There are some comments to help you on your way...

''''''''''''''''''''''''''''''''''''''''''
' Create variables and object variables
' needed for procedure
''''''''''''''''''''''''''''''''''''''''''

Dim dbs As Database
Dim rst As Recordset
Dim strSql As String
Dim ordered As Integer
Dim i As Integer
Dim stDocName As String
Dim result As Integer


result = MsgBox("You have chosen to make lables for the products listed on this Purchase " _
& "Order. This procedure may take a few moments based on the number of " _
& "Products contained. You will be notified when the procedure is " _
& "completed.", vbOKCancel, "Lable Generation Procedure")
If result = vbCancel Then
Exit Sub
Else
''''''''''''''''''''''''''''''''''''''''''''
' SetWarnings turns off the Access prompts
''''''''''''''''''''''''''''''''''''''''''''
DoCmd.SetWarnings False
ordered = 0
''''''''''''''''''''''''''''''''''''''''''''
' Set a object reference to the Current DB
' We will use properties and methods of the
' Database Object in the procedure.
''''''''''''''''''''''''''''''''''''''''''''
Set dbs = CurrentDb
''''''''''''''''''''''''''''''''''''''''''''
' Set a value for the SQL string. Some
' hoop need to be lept through in order to
' get the syntax correct. These mostly have
' to do with embedding text or date values
' inside the SQL string.
''''''''''''''''''''''''''''''''''''''''''''
strSql = "SELECT units_ordered, prod_code, trans_no FROM tblTransaction " _
& "WHERE po_no = " & Me.po_no & ";"
'''''''''''''''''''''''''''''''''''''''''''''
' Set an Recordset object variable to the
' SQL string using the OpenRecordset method
' of the Database Object.
''''''''''''''''''''''''''''''''''''''''''''

Set rst = dbs.OpenRecordset(strSql, dbOpenSnapshot)

If rst.recordCount > 0 Then
rst.MoveLast
rst.MoveFirst
'''''''''''''''''''''''''''''''''''''''''''''
' Loop through all the records returned by
' the SQL string. In this case all the
' Transaction records where the Purchase
' Order Number = Me.po_no (or the current
' form value for Purchase Order Number)
''''''''''''''''''''''''''''''''''''''''''''
Do Until rst.EOF
i = 1
ordered = rst!units_ordered
' ''''''''''''''''''''''''''''''''''''''''''
' Set another loop that has total iterations
' = to the originally ordered number for the
' current transaction record.
' For each iteration, you use the doCmd
' method to runSQL. This will insert all
' the values required for each label into
' the temporary table.
''''''''''''''''''''''''''''''''''''''''''''

For i = 1 To ordered
DoCmd.RunSQL ("INSERT INTO tblTempLables (prod_code, trans_no) VALUES ('" _
& rst!prod_code & "', " & rst!trans_no & ");")
Next
rst.MoveNext
Loop

End If
MsgBox "The Lables have been created. Opening Lables Report in Print Preview" _
& ".", , "Lables Created"
stDocName = "rptLables_forReq"
DoCmd.OpenReport stDocName, acViewPreview
''''''''''''''''''''''''''''''''''''''''''''
' Lastly, run a Delete to erase all the
' records from the temporary table so that
' they will be gone the next time you want
' to run the procedure.
'''''''''''''''''''''''''''''''''''''''''''''
DoCmd.RunSQL ("DELETE * from tblTempLables;")
DoCmd.SetWarnings True
End If


'''''''''''''END''''''''''''''''''''''''''

HTH
Chris