Help with Duplicate Price Tags

[Susan Allen Wyoming]

I have been working on trying to create duplicate price tags for the last two weeks.

I created a button with the following code behind it. It works without any errors, but I'm not getting too many price tags when I preview the report. I tested it with two items on order and I got over 5 pages of price tags.

I also am getting an error on an item called Marshall's Seedless Ash. This is a text field. I'm assuming its because of the apostraphe, but I don't know what to do because that is the name of the product.

Any thoughts? Please help!!!

''''''''''''''''''''''''''''''''''''
Private Sub cmdPriceLabels_Click()

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 labels for the products"_
& "listed on this Purchase Order. This procedure may take a few"_
& "moments.", vbOKCancel, "Label Generation Procedure")

If result = vbCancel Then
Exit Sub
Else

DoCmd.SetWarnings False
ordered = 0
Set dbs = CurrentDb

strSql = "Select
DescripName,ProdSize,ProdRetail,ProdID,OrderID,OrderTotal
FROM qryOrders" _
& "WHERE POID = " & Me.POID & ";"

Set rst = dbs.OpenRecordset(strSql, dbOpenSnapshot)

If rst.RecordCount > 0 Then
rst.MoveLast
rst.MoveFirst

Do Until rst.EOF
i = 1
ordered = rst!OrderTotal

For i = 1 To ordered
DoCmd.RunSQL ("INSERT INTO TempPriceLabels
(ProdID,OrderID,DescripName,ProdRetail,ProdSize) VALUES('" _
& rst!ProdID & "', "& rst!OrderID & "', & rst!DescripName & "', "&
rst!ProdRetail & "', rst!ProdSize & ");")
Next
rst.MoveNext
Loop

End If

MsgBox "The labels have been created. Opening Labels in Print
Preview"_
& ".", , "Labels Created"
stDocName = "rptPriceLabels"
DoCmd.OpenReport stDocName, acViewPreview

DoCmd.RunSQL ("DELETE * from TempPriceLabels;")
DoCmd.SetWarnings = True
End If

End Sub
'''''''''''''''''''''''''''''''''''''''

Thanks Sue

 

[Susan Allen Wyoming]

PS I am getting too many price tags. Not....not.

 

[chrismcbride]

Susan

The answer to your second problem is you need to clean your input text before you try to run the SQL statement. The following procedure can be pasted into a public module and called from within the SQL statement.

Public Function TranslateSQL(FieldValue As String) As String
Dim strText As String
Dim strTemp As String
Dim strTemp2 As String
Dim i As Integer

strText = FieldValue

For i = 1 To Len(strText)
If Mid(strText, i, 1) = Chr(39) Then
strTemp = Mid(strText, 1, i) & Chr(39)
strTemp2 = Right(strText, Len(strText) - i)
strText = strTemp & strTemp2
i = i + 1
End If
Next i
TranslateSQL = strText
End Function

Called like...

DoCmd.RunSQL ("INSERT INTO TempPriceLabels
(ProdID,OrderID,DescripName,ProdRetail,ProdSize) VALUES('" _
& TranslateSQL(rst!ProdID) & "', "& rst!OrderID & "', & TranslateSQL(rst!DescripName) & "', "&
rst!ProdRetail & "', rst!ProdSize & ");")

With respect to the number of price tags, you would need to be more specific as to what is wrong with the number. Alternatively you could try to place a breakpoint at the beginning of the Do Until loop. This will allow you to see the execution of the code and evaluate what is wrong with the logic.

HTH
Chris

 

[Susan Allen Wyoming]

Do I just put the function in my utilites? I have never done this before. I'm also having difficult with some of the syntax with respect to what is something I need to do to wrap the code onto the next line at what is actually code.

I'm really overwhelmed right now. Do you know anybody who can consult? I want to....and need to understand this code, but right now I have, on the average of 6 semi-trucks a day arriving at my store. I also manage a crew of 40 and have been working 80 hours a week trying to write this program. Oh, the joys of small business! I'm hoping that I can at least get it to work right and then I have to give this project up until the fall because we are getting really busy.

SUE

PS I totally respect anybody's privacy who does not want to be bothered, but if there is somebody out there that can help me, I would be eternally grateful.