Various Error

W

wafs1801

I've update a access database from 97 to 2002 and how
every time I run the module, I get a different error.

If I run from the form I get compile error: Only comments
may appear after end sub, end function, or end property,
but there are no comments.

If I go inside the module and start the program at option
compare database the error is file not found and stops at
the shell line.

If I go to the main code (Sub write_Zebra_Print_cmds1sap()
and run the debug, I get no errors.

Is there anyone out there that could expain why this is
happening?? Code to follow

Option Compare Database
Option Explicit

Function CallCopyZebraFile()
Call CopyZebraFile
End Function

Function CallWZPC1SAP()
Call Write_Zebra_Print_Cmds1SAP
End Function

Function CallWZPCSAP()
Call Write_Zebra_Print_CmdsSAP
End Function
Sub CopyZebraFile()

Shell ("c:\zebra.exe")

End Sub
Sub Write_Zebra_Print_Cmds1SAP()
Dim dbs As Database
Dim rtb As Recordset
Dim rcdz As Recordset
Dim rcdh As Recordset

Set dbs = CurrentDb()
Set rtb = dbs.OpenRecordset("SAPRollNo")
Set rcdz = dbs.OpenRecordset("SAPZebraOutput",
DB_OPEN_TABLE)
Set rcdh = dbs.OpenRecordset("SAPRollHistory",
DB_OPEN_TABLE)

Dim varPlant As String
Dim varYear As Integer
Dim varMonth As String
Dim varRollSeries As String * 4
Dim varCutPosition As String
Dim varExtrusionCode As Integer

'Get the data from the current table and generate Zebra
transactions to the Zebra table.

varPlant = rtb![Plant]
varYear = rtb![Year]
varMonth = rtb![Month]
varRollSeries = rtb![RollSeries]
varCutPosition = rtb![CutPosition]
varExtrusionCode = rtb![LineType]

rcdz.AddNew
rcdz![ZebraOutput] = "^XA^PRD^LH0,0^LT0^FWN^FS"
rcdz.UPDATE
rcdz.AddNew
'rcdz![ZebraOutput] = "^FO 100, 20,^A0,110,110^FD"
+ Trim(varPlant) + Trim(Str$(varYear)) + Trim(varMonth) +
Trim(Str$(varRollSeries)) + Trim(varCutPosition) + Trim
(Str$(varExtrusionCode)) + "^FS"
rcdz![ZebraOutput] = "^FO 100, 70,^A0,110,110^FD" +
Trim(varPlant) + Trim(Str$(varYear)) + Trim(varMonth) +
varRollSeries + Trim(varCutPosition) + Trim
(Str$(varExtrusionCode)) + "^FS"
rcdz.UPDATE
rcdz.AddNew
'rcdz![ZebraOutput] = "^FO 120,
110,^BY3^B3N,N,80,N^FD" + Trim(varPlant) + Trim
(Str$(varYear)) + Trim(varMonth) + Trim
(Str$(varRollSeries)) + Trim(varCutPosition) + Trim
(Str$(varExtrusionCode)) + "^FS"
rcdz![ZebraOutput] = "^FO 120,
160,^BY3^B3N,N,80,N^FD" + Trim(varPlant) + Trim
(Str$(varYear)) + Trim(varMonth) + varRollSeries + Trim
(varCutPosition) + Trim(Str$(varExtrusionCode)) + "^FS"
rcdz.UPDATE
rcdz.AddNew
rcdz![ZebraOutput] = "^PQ1^XZ"
rcdz.UPDATE

rcdh.AddNew
'rcdh.[RollNbr] = Trim(varPlant) + Trim
(Str$(varYear)) + Trim(varMonth) + Trim
(Str$(varRollSeries)) + Trim(varCutPosition) + Trim
(Str$(varExtrusionCode))
rcdh![RollNbr] = Trim(varPlant) + Trim
(Str$(varYear)) + Trim(varMonth) + varRollSeries + Trim
(varCutPosition) + Trim(Str$(varExtrusionCode))
rcdh![Date] = Now()
rcdh.UPDATE

End Sub
Sub Write_Zebra_Print_CmdsSAP()
Dim db As Database
Dim rtb As Recordset
Dim rcdz As Recordset
Dim rcdh As Recordset

Set db = CurrentDb()
Set rtb = db.OpenRecordset("SAPRollNo")
Set rcdz = db.OpenRecordset("SAPZebraOutput",
DB_OPEN_TABLE)
Set rcdh = db.OpenRecordset("SAPRollHistory",
DB_OPEN_TABLE)

Dim x As Integer
Dim y As Integer
Dim CutPositionInteger As Integer

Dim varPlant As String
Dim varYear As Integer
Dim varMonth As String
Dim varRollSeries As Integer
Dim varCutPosition As String
Dim varExtrusionCode As Integer
Dim varRollSeriesEnd As Integer
Dim varCutPositionEnd As String
Dim varCutPositionAlpha As String
Dim varRollSeries4Char As String * 4
Dim TotalStickers As Long
Dim MBText As String
Dim OutputText As String

'Get the data from the current table and generate Zebra
transactions to the Zebra table.

varPlant = rtb![Plant]
varYear = rtb![Year]
varMonth = rtb![Month]
varRollSeries = rtb![RollSeries]
varCutPosition = rtb![CutPosition]
varExtrusionCode = rtb![LineType]
varRollSeriesEnd = rtb![RollSeriesEnd]
varCutPositionEnd = rtb![CutPositionEnd]

'convert end cut position alpha to numeric for loop below.
If varCutPositionEnd = "A" Then
CutPositionInteger = 1
ElseIf varCutPositionEnd = "B" Then
CutPositionInteger = 2
ElseIf varCutPositionEnd = "C" Then
CutPositionInteger = 3
ElseIf varCutPositionEnd = "D" Then
CutPositionInteger = 4
Else
MsgBox "Cut position outside of range from A to D."
End If

'it is possible for the user to enter 0000 as
varRollSeries and 9999
'as varRollSeriesEnd. That would print 9999 to 39,996
stickers (cut
'position A to D). This probably isn't a real good
thing, so warn
'user. An arbitrary limit of 10 difference between
varRollSeries
'and varRollSeriesEnd is used.
If Abs(varRollSeries - varRollSeriesEnd) > 10 Then
TotalStickers = Abs(varRollSeries - varRollSeriesEnd)
* CutPositionInteger
MBText = "Do you really want to print " &
TotalStickers & " stickers?"
If MsgBox(MBText, vbYesNo + vbExclamation +
vbDefaultButton2 + vbApplicationModal, "Sticker Print
Warning") = vbNo Then
MsgBox "No stickers will print. You will need to
close the DOS window that appears and reselect stickers."
Exit Sub
End If
End If

For y = varRollSeries To varRollSeriesEnd
'need leading zeroes in the roll series (xxxx) for
printing, but it is an
'integer for use in this loop, so provide the alpha
to print:
Select Case y
Case Is < 10 'need to add 3 zeros
varRollSeries4Char = "000" & y
Case Is < 100 'need to add 2 zeros
varRollSeries4Char = "00" & y
Case Is < 1000 'need to add 1 zero
varRollSeries4Char = "0" & y
Case Is > 999
varRollSeries4Char = y
End Select

For x = 1 To CutPositionInteger
'set current cut position to alpha for zebra
printer output
Select Case x
Case 1
varCutPositionAlpha = "A"
Case 2
varCutPositionAlpha = "B"
Case 3
varCutPositionAlpha = "C"
Case 4
varCutPositionAlpha = "D"
Case Else
MsgBox "Error in converting cut position
numeric to alpha. Maximum cut position is D."
End Select
'write to zebra table for output to printer
OutputText = Trim(varPlant) + Trim(Str$(varYear))
+ Trim(varMonth) + varRollSeries4Char + Trim
(varCutPositionAlpha) + Trim(Str$(varExtrusionCode))
rcdz.AddNew
rcdz![ZebraOutput] = "^XA^PRD^LH0,0^LT0^FWN^FS"
rcdz.UPDATE
rcdz.AddNew
rcdz![ZebraOutput] = "^FO 100, 70,^A0,110,110^FD"
+ OutputText + "^FS"
rcdz.UPDATE
rcdz.AddNew
rcdz![ZebraOutput] = "^FO 120,
160,^BY3^B3N,N,80,N^FD" + OutputText + "^FS"
rcdz.UPDATE
rcdz.AddNew
rcdz![ZebraOutput] = "^PQ1^XZ"
rcdz.UPDATE
'write to sticker table for history of stickers
printed
rcdh.AddNew
rcdh![RollNbr] = OutputText
rcdh![Date] = Now()
rcdh.UPDATE
Next
Next

End Sub
 
D

Dan Artuso

I won't try to wade through all that code but I'll offer a few suggestions.
Compile everything and then you'll see where the compile errors are.
If it's telling you that you have code that is outside any procedure, then find
it and get rid of it. In any case, Access should highlight these lines

As for the File Not Found, isn't that self-explanatory?

The main thing is to do a full compile and let Access show you where the errors are.

--
HTH
Dan Artuso, Access MVP


wafs1801 said:
I've update a access database from 97 to 2002 and how
every time I run the module, I get a different error.

If I run from the form I get compile error: Only comments
may appear after end sub, end function, or end property,
but there are no comments.

If I go inside the module and start the program at option
compare database the error is file not found and stops at
the shell line.

If I go to the main code (Sub write_Zebra_Print_cmds1sap()
and run the debug, I get no errors.

Is there anyone out there that could expain why this is
happening?? Code to follow

Option Compare Database
Option Explicit

Function CallCopyZebraFile()
Call CopyZebraFile
End Function

Function CallWZPC1SAP()
Call Write_Zebra_Print_Cmds1SAP
End Function

Function CallWZPCSAP()
Call Write_Zebra_Print_CmdsSAP
End Function
Sub CopyZebraFile()

Shell ("c:\zebra.exe")

End Sub
Sub Write_Zebra_Print_Cmds1SAP()
Dim dbs As Database
Dim rtb As Recordset
Dim rcdz As Recordset
Dim rcdh As Recordset

Set dbs = CurrentDb()
Set rtb = dbs.OpenRecordset("SAPRollNo")
Set rcdz = dbs.OpenRecordset("SAPZebraOutput",
DB_OPEN_TABLE)
Set rcdh = dbs.OpenRecordset("SAPRollHistory",
DB_OPEN_TABLE)

Dim varPlant As String
Dim varYear As Integer
Dim varMonth As String
Dim varRollSeries As String * 4
Dim varCutPosition As String
Dim varExtrusionCode As Integer

'Get the data from the current table and generate Zebra
transactions to the Zebra table.

varPlant = rtb![Plant]
varYear = rtb![Year]
varMonth = rtb![Month]
varRollSeries = rtb![RollSeries]
varCutPosition = rtb![CutPosition]
varExtrusionCode = rtb![LineType]

rcdz.AddNew
rcdz![ZebraOutput] = "^XA^PRD^LH0,0^LT0^FWN^FS"
rcdz.UPDATE
rcdz.AddNew
'rcdz![ZebraOutput] = "^FO 100, 20,^A0,110,110^FD"
+ Trim(varPlant) + Trim(Str$(varYear)) + Trim(varMonth) +
Trim(Str$(varRollSeries)) + Trim(varCutPosition) + Trim
(Str$(varExtrusionCode)) + "^FS"
rcdz![ZebraOutput] = "^FO 100, 70,^A0,110,110^FD" +
Trim(varPlant) + Trim(Str$(varYear)) + Trim(varMonth) +
varRollSeries + Trim(varCutPosition) + Trim
(Str$(varExtrusionCode)) + "^FS"
rcdz.UPDATE
rcdz.AddNew
'rcdz![ZebraOutput] = "^FO 120,
110,^BY3^B3N,N,80,N^FD" + Trim(varPlant) + Trim
(Str$(varYear)) + Trim(varMonth) + Trim
(Str$(varRollSeries)) + Trim(varCutPosition) + Trim
(Str$(varExtrusionCode)) + "^FS"
rcdz![ZebraOutput] = "^FO 120,
160,^BY3^B3N,N,80,N^FD" + Trim(varPlant) + Trim
(Str$(varYear)) + Trim(varMonth) + varRollSeries + Trim
(varCutPosition) + Trim(Str$(varExtrusionCode)) + "^FS"
rcdz.UPDATE
rcdz.AddNew
rcdz![ZebraOutput] = "^PQ1^XZ"
rcdz.UPDATE

rcdh.AddNew
'rcdh.[RollNbr] = Trim(varPlant) + Trim
(Str$(varYear)) + Trim(varMonth) + Trim
(Str$(varRollSeries)) + Trim(varCutPosition) + Trim
(Str$(varExtrusionCode))
rcdh![RollNbr] = Trim(varPlant) + Trim
(Str$(varYear)) + Trim(varMonth) + varRollSeries + Trim
(varCutPosition) + Trim(Str$(varExtrusionCode))
rcdh![Date] = Now()
rcdh.UPDATE

End Sub
Sub Write_Zebra_Print_CmdsSAP()
Dim db As Database
Dim rtb As Recordset
Dim rcdz As Recordset
Dim rcdh As Recordset

Set db = CurrentDb()
Set rtb = db.OpenRecordset("SAPRollNo")
Set rcdz = db.OpenRecordset("SAPZebraOutput",
DB_OPEN_TABLE)
Set rcdh = db.OpenRecordset("SAPRollHistory",
DB_OPEN_TABLE)

Dim x As Integer
Dim y As Integer
Dim CutPositionInteger As Integer

Dim varPlant As String
Dim varYear As Integer
Dim varMonth As String
Dim varRollSeries As Integer
Dim varCutPosition As String
Dim varExtrusionCode As Integer
Dim varRollSeriesEnd As Integer
Dim varCutPositionEnd As String
Dim varCutPositionAlpha As String
Dim varRollSeries4Char As String * 4
Dim TotalStickers As Long
Dim MBText As String
Dim OutputText As String

'Get the data from the current table and generate Zebra
transactions to the Zebra table.

varPlant = rtb![Plant]
varYear = rtb![Year]
varMonth = rtb![Month]
varRollSeries = rtb![RollSeries]
varCutPosition = rtb![CutPosition]
varExtrusionCode = rtb![LineType]
varRollSeriesEnd = rtb![RollSeriesEnd]
varCutPositionEnd = rtb![CutPositionEnd]

'convert end cut position alpha to numeric for loop below.
If varCutPositionEnd = "A" Then
CutPositionInteger = 1
ElseIf varCutPositionEnd = "B" Then
CutPositionInteger = 2
ElseIf varCutPositionEnd = "C" Then
CutPositionInteger = 3
ElseIf varCutPositionEnd = "D" Then
CutPositionInteger = 4
Else
MsgBox "Cut position outside of range from A to D."
End If

'it is possible for the user to enter 0000 as
varRollSeries and 9999
'as varRollSeriesEnd. That would print 9999 to 39,996
stickers (cut
'position A to D). This probably isn't a real good
thing, so warn
'user. An arbitrary limit of 10 difference between
varRollSeries
'and varRollSeriesEnd is used.
If Abs(varRollSeries - varRollSeriesEnd) > 10 Then
TotalStickers = Abs(varRollSeries - varRollSeriesEnd)
* CutPositionInteger
MBText = "Do you really want to print " &
TotalStickers & " stickers?"
If MsgBox(MBText, vbYesNo + vbExclamation +
vbDefaultButton2 + vbApplicationModal, "Sticker Print
Warning") = vbNo Then
MsgBox "No stickers will print. You will need to
close the DOS window that appears and reselect stickers."
Exit Sub
End If
End If

For y = varRollSeries To varRollSeriesEnd
'need leading zeroes in the roll series (xxxx) for
printing, but it is an
'integer for use in this loop, so provide the alpha
to print:
Select Case y
Case Is < 10 'need to add 3 zeros
varRollSeries4Char = "000" & y
Case Is < 100 'need to add 2 zeros
varRollSeries4Char = "00" & y
Case Is < 1000 'need to add 1 zero
varRollSeries4Char = "0" & y
Case Is > 999
varRollSeries4Char = y
End Select

For x = 1 To CutPositionInteger
'set current cut position to alpha for zebra
printer output
Select Case x
Case 1
varCutPositionAlpha = "A"
Case 2
varCutPositionAlpha = "B"
Case 3
varCutPositionAlpha = "C"
Case 4
varCutPositionAlpha = "D"
Case Else
MsgBox "Error in converting cut position
numeric to alpha. Maximum cut position is D."
End Select
'write to zebra table for output to printer
OutputText = Trim(varPlant) + Trim(Str$(varYear))
+ Trim(varMonth) + varRollSeries4Char + Trim
(varCutPositionAlpha) + Trim(Str$(varExtrusionCode))
rcdz.AddNew
rcdz![ZebraOutput] = "^XA^PRD^LH0,0^LT0^FWN^FS"
rcdz.UPDATE
rcdz.AddNew
rcdz![ZebraOutput] = "^FO 100, 70,^A0,110,110^FD"
+ OutputText + "^FS"
rcdz.UPDATE
rcdz.AddNew
rcdz![ZebraOutput] = "^FO 120,
160,^BY3^B3N,N,80,N^FD" + OutputText + "^FS"
rcdz.UPDATE
rcdz.AddNew
rcdz![ZebraOutput] = "^PQ1^XZ"
rcdz.UPDATE
'write to sticker table for history of stickers
printed
rcdh.AddNew
rcdh![RollNbr] = OutputText
rcdh![Date] = Now()
rcdh.UPDATE
Next
Next

End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Export to PDF 0
type mismatch error in macro in excel 0
Wildcard Filtering on ADO Recordset 1
Access VBA to format Excel 1
Closing Excell from Access 3
Overwriting data 10
Repeated call to InternetExplorer.Application seems to hang the PC 4
Multiple Delimiters in Array 9

Top