Code Is Slow

D

DS

Is There any way to speed this code up. It takes about 4 Seconds for any of
the forms to pop up.
Thanks
DS

Dim rtn As Long
Dim Driver As String
Dim I As Integer
Dim X As Printer

''SECURITY
Me.TxtSecure = Nz(DLookup("[JobNameSecurityID]", "tblJobNames",
"JobNameID = " & Me.TxtJobID & ""), 0)
Me.TxtPriv = Nz(DLookup("[SDPrivID]", "tblSecurityDetails", "SecurityID
= " & Me.TxtSecure & " And SDPrivID = 39"), 0)

'SENDING
Me.TxtCount = Nz(DCount("CDSent", "tblCheckDetails", "CDCheckID =" &
Me.CheckID & " and CDSent = 0"), 0)
'PRINTERS
'CHECKS PRINTER AVAILABILITY
Me.TxtNumber = Nz(DMax("PDPrinterID", "tblPrintDetails", "PDCheckID = "
& Me.CheckID), 0)
If Me.TxtNumber >= 1 Then
On Error GoTo ErrorHandler
Do
Me.TxtEmpty = Nz(DCount("PDPrinterID", "tblPrintDetails",
"PDPrinterID = " & Me.TxtNumber & "AND PDCheckID = " & Me.CheckID), 0)
If IsNull(Me.TxtEmpty) Or _
Me.TxtEmpty = 0 Then
Me.TxtNumber = Me.TxtNumber - 1
ElseIf Me.TxtEmpty >= 1 Then
Me.TxtName = Nz(DLookup("PrinterName", "tblPrinters", "PrinterID
= " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
m_PrtN = Me.TxtName
If Len(Me.TxtName) = 0 Then
Exit Sub
End If

rtn = GetPrnDriverName(m_PrtN, Driver)
If rtn <> 0 Then
Driver = ""
End If

m_DrvN = Driver

m_hApi = BiOpenMonPrinter(TYPE_PRINTER, m_PrtN)
If m_hApi < 0 Then
Me.TxtError = 0
DoCmd.OpenForm "frmNoPrinter"
Forms!frmNoPrinter!Label2.Caption = Me.TxtName & " " &
"Offline"
Exit Sub
ElseIf m_hApi > 0 Then
End If

BiCloseMonPrinter (m_hApi)
ElseIf m_hApi = 0 Then
End If

Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
Loop Until Me.TxtNumber = 0
Me.TxtError = 1

If Me.TxtPriv > 0 And _
Me.TxtCount > 0 And _
Me.TxtError > 0 Then
'SEND AND LEAVE
Dim PO As Integer
Dim SENDSQL As String
Dim DEADSQL As String
DoCmd.OpenForm "frmSending"
Me.TxtNumber = Nz(DMax("PDPrinterID", "tblPrintDetails", "PDCheckID
= " & Me.CheckID), 0)
On Error GoTo ErrorHandler
Do
Me.TxtEmpty = Nz(DCount("PDPrinterID", "tblPrintDetails",
"PDPrinterID = " & Me.TxtNumber & "AND PDCheckID = " & Me.CheckID), 0)
If IsNull(Me.TxtEmpty) Or _
Me.TxtEmpty = 0 Then
Me.TxtNumber = Me.TxtNumber - 1
ElseIf Me.TxtEmpty >= 1 Then
Me.TxtCon = Nz(DLookup("PDCon", "tblPrintDetails",
"PDPrinterID = " & Me.TxtNumber), 0)
If Me.TxtCon = -1 Then
Me.TxtItemID = Nz(DLookup("PDItemID",
"tblPrintDetails", "PDPrinterID = " & Me.TxtNumber), 0)
Me.TxtGo = Nz(DCount("PDItemID", "tblPrintDetails",
"PDItemID <> " & Me.TxtItemID & " And PDPrinterID = " & Me.TxtNumber & "And
PDCheckID = " & Me.CheckID), 0)
If Me.TxtGo >= 1 Then
Me.TxtName = Nz(DLookup("PrinterName",
"tblPrinters", "PrinterID = " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
DoCmd.OpenReport "rptPrepOrder", , , "PDCheckID
= " & Me.CheckID & " And PDPrinterID = " & Me.TxtNumber & ""
Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
ElseIf Me.TxtGo = 0 Then
Me.TxtNumber = Me.TxtNumber - 1
Me.TxtName = Nz(DLookup("PrinterName",
"tblPrinters", "PrinterID = " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
DoCmd.OpenReport "rptPrepOrder", , , "PDCheckID
= " & Me.CheckID & " And PDPrinterID = " & Me.TxtNumber & ""
Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
End If
ElseIf Me.TxtCon = 0 Then
Me.TxtName = Nz(DLookup("PrinterName",
"tblPrinters", "PrinterID = " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
DoCmd.OpenReport "rptPrepOrder", , , "PDCheckID = "
& Me.CheckID & " And PDPrinterID = " & Me.TxtNumber & ""
Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
End If
End If
Loop Until Me.TxtNumber = 0
'SEND ITEMS
DoCmd.SetWarnings False
SENDSQL = "UPDATE tblCheckDetails SET [CDSent] = True " & _
"WHERE tblCheckDetails.[CDCheckID] =
Forms!frmOrderScreen![CheckID]; "
DoCmd.RunSQL (SENDSQL)

DEADSQL = "UPDATE tblPrintDetails SET [PDDead] = True " & _
"WHERE tblPrintDetails.[PDCheckID] =
Forms!frmOrderScreen![CheckID]; "
DoCmd.RunSQL (DEADSQL)

DoCmd.SetWarnings True
Me.ListPrep.Requery
End
ErrorHandler:
DoCmd.OpenForm "frmMsgWarning"
Forms!frmMsgWarning!TxtMsg = "INVALID PRINTER"
ElseIf Me.TxtPriv > 0 And _
Me.TxtCount = 0 And _
Me.TxtError >= 0 Then
DoCmd.OpenForm "frmMsgWarning"
Forms!frmMsgWarning!TxtMsg = "NOTHING TO SEND"
Else:
DoCmd.OpenForm "frmMsgWarning"
Forms!frmMsgWarning!TxtMsg = "SENDING DENIED"
End If
End If
 
M

Marshall Barton

I can't unravel all that, but all those domain aggregate
functions jump out as a performance hit. If you are going
to retrieve more then one value from a table, it's time to
seriously consider using a recordset (with a restrictive
Where clause) so you can get all the needed values in a
single data retrieval operation.
--
Marsh
MVP [MS Access]

Is There any way to speed this code up. It takes about 4 Seconds for any of
the forms to pop up.

Dim rtn As Long
Dim Driver As String
Dim I As Integer
Dim X As Printer

''SECURITY
Me.TxtSecure = Nz(DLookup("[JobNameSecurityID]", "tblJobNames",
"JobNameID = " & Me.TxtJobID & ""), 0)
Me.TxtPriv = Nz(DLookup("[SDPrivID]", "tblSecurityDetails", "SecurityID
= " & Me.TxtSecure & " And SDPrivID = 39"), 0)

'SENDING
Me.TxtCount = Nz(DCount("CDSent", "tblCheckDetails", "CDCheckID =" &
Me.CheckID & " and CDSent = 0"), 0)
'PRINTERS
'CHECKS PRINTER AVAILABILITY
Me.TxtNumber = Nz(DMax("PDPrinterID", "tblPrintDetails", "PDCheckID = "
& Me.CheckID), 0)
If Me.TxtNumber >= 1 Then
On Error GoTo ErrorHandler
Do
Me.TxtEmpty = Nz(DCount("PDPrinterID", "tblPrintDetails",
"PDPrinterID = " & Me.TxtNumber & "AND PDCheckID = " & Me.CheckID), 0)
If IsNull(Me.TxtEmpty) Or _
Me.TxtEmpty = 0 Then
Me.TxtNumber = Me.TxtNumber - 1
ElseIf Me.TxtEmpty >= 1 Then
Me.TxtName = Nz(DLookup("PrinterName", "tblPrinters", "PrinterID
= " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
m_PrtN = Me.TxtName
If Len(Me.TxtName) = 0 Then
Exit Sub
End If

rtn = GetPrnDriverName(m_PrtN, Driver)
If rtn <> 0 Then
Driver = ""
End If

m_DrvN = Driver

m_hApi = BiOpenMonPrinter(TYPE_PRINTER, m_PrtN)
If m_hApi < 0 Then
Me.TxtError = 0
DoCmd.OpenForm "frmNoPrinter"
Forms!frmNoPrinter!Label2.Caption = Me.TxtName & " " &
"Offline"
Exit Sub
ElseIf m_hApi > 0 Then
End If

BiCloseMonPrinter (m_hApi)
ElseIf m_hApi = 0 Then
End If

Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
Loop Until Me.TxtNumber = 0
Me.TxtError = 1

If Me.TxtPriv > 0 And _
Me.TxtCount > 0 And _
Me.TxtError > 0 Then
'SEND AND LEAVE
Dim PO As Integer
Dim SENDSQL As String
Dim DEADSQL As String
DoCmd.OpenForm "frmSending"
Me.TxtNumber = Nz(DMax("PDPrinterID", "tblPrintDetails", "PDCheckID
= " & Me.CheckID), 0)
On Error GoTo ErrorHandler
Do
Me.TxtEmpty = Nz(DCount("PDPrinterID", "tblPrintDetails",
"PDPrinterID = " & Me.TxtNumber & "AND PDCheckID = " & Me.CheckID), 0)
If IsNull(Me.TxtEmpty) Or _
Me.TxtEmpty = 0 Then
Me.TxtNumber = Me.TxtNumber - 1
ElseIf Me.TxtEmpty >= 1 Then
Me.TxtCon = Nz(DLookup("PDCon", "tblPrintDetails",
"PDPrinterID = " & Me.TxtNumber), 0)
If Me.TxtCon = -1 Then
Me.TxtItemID = Nz(DLookup("PDItemID",
"tblPrintDetails", "PDPrinterID = " & Me.TxtNumber), 0)
Me.TxtGo = Nz(DCount("PDItemID", "tblPrintDetails",
"PDItemID <> " & Me.TxtItemID & " And PDPrinterID = " & Me.TxtNumber & "And
PDCheckID = " & Me.CheckID), 0)
If Me.TxtGo >= 1 Then
Me.TxtName = Nz(DLookup("PrinterName",
"tblPrinters", "PrinterID = " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
DoCmd.OpenReport "rptPrepOrder", , , "PDCheckID
= " & Me.CheckID & " And PDPrinterID = " & Me.TxtNumber & ""
Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
ElseIf Me.TxtGo = 0 Then
Me.TxtNumber = Me.TxtNumber - 1
Me.TxtName = Nz(DLookup("PrinterName",
"tblPrinters", "PrinterID = " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
DoCmd.OpenReport "rptPrepOrder", , , "PDCheckID
= " & Me.CheckID & " And PDPrinterID = " & Me.TxtNumber & ""
Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
End If
ElseIf Me.TxtCon = 0 Then
Me.TxtName = Nz(DLookup("PrinterName",
"tblPrinters", "PrinterID = " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
DoCmd.OpenReport "rptPrepOrder", , , "PDCheckID = "
& Me.CheckID & " And PDPrinterID = " & Me.TxtNumber & ""
Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
End If
End If
Loop Until Me.TxtNumber = 0
'SEND ITEMS
DoCmd.SetWarnings False
SENDSQL = "UPDATE tblCheckDetails SET [CDSent] = True " & _
"WHERE tblCheckDetails.[CDCheckID] =
Forms!frmOrderScreen![CheckID]; "
DoCmd.RunSQL (SENDSQL)

DEADSQL = "UPDATE tblPrintDetails SET [PDDead] = True " & _
"WHERE tblPrintDetails.[PDCheckID] =
Forms!frmOrderScreen![CheckID]; "
DoCmd.RunSQL (DEADSQL)

DoCmd.SetWarnings True
Me.ListPrep.Requery
End
ErrorHandler:
DoCmd.OpenForm "frmMsgWarning"
Forms!frmMsgWarning!TxtMsg = "INVALID PRINTER"
ElseIf Me.TxtPriv > 0 And _
Me.TxtCount = 0 And _
Me.TxtError >= 0 Then
DoCmd.OpenForm "frmMsgWarning"
Forms!frmMsgWarning!TxtMsg = "NOTHING TO SEND"
Else:
DoCmd.OpenForm "frmMsgWarning"
Forms!frmMsgWarning!TxtMsg = "SENDING DENIED"
End If
End If
Click to expand...
 
D

DS

Sounds good! But how on earth would I do it!
Thanks
DS
Marshall Barton said:
I can't unravel all that, but all those domain aggregate
functions jump out as a performance hit. If you are going
to retrieve more then one value from a table, it's time to
seriously consider using a recordset (with a restrictive
Where clause) so you can get all the needed values in a
single data retrieval operation.
--
Marsh
MVP [MS Access]

Is There any way to speed this code up. It takes about 4 Seconds for any
of
the forms to pop up.

Dim rtn As Long
Dim Driver As String
Dim I As Integer
Dim X As Printer

''SECURITY
Me.TxtSecure = Nz(DLookup("[JobNameSecurityID]", "tblJobNames",
"JobNameID = " & Me.TxtJobID & ""), 0)
Me.TxtPriv = Nz(DLookup("[SDPrivID]", "tblSecurityDetails",
"SecurityID
= " & Me.TxtSecure & " And SDPrivID = 39"), 0)

'SENDING
Me.TxtCount = Nz(DCount("CDSent", "tblCheckDetails", "CDCheckID =" &
Me.CheckID & " and CDSent = 0"), 0)
'PRINTERS
'CHECKS PRINTER AVAILABILITY
Me.TxtNumber = Nz(DMax("PDPrinterID", "tblPrintDetails", "PDCheckID =
"
& Me.CheckID), 0)
If Me.TxtNumber >= 1 Then
On Error GoTo ErrorHandler
Do
Me.TxtEmpty = Nz(DCount("PDPrinterID", "tblPrintDetails",
"PDPrinterID = " & Me.TxtNumber & "AND PDCheckID = " & Me.CheckID), 0)
If IsNull(Me.TxtEmpty) Or _
Me.TxtEmpty = 0 Then
Me.TxtNumber = Me.TxtNumber - 1
ElseIf Me.TxtEmpty >= 1 Then
Me.TxtName = Nz(DLookup("PrinterName", "tblPrinters",
"PrinterID
= " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
m_PrtN = Me.TxtName
If Len(Me.TxtName) = 0 Then
Exit Sub
End If

rtn = GetPrnDriverName(m_PrtN, Driver)
If rtn <> 0 Then
Driver = ""
End If

m_DrvN = Driver

m_hApi = BiOpenMonPrinter(TYPE_PRINTER, m_PrtN)
If m_hApi < 0 Then
Me.TxtError = 0
DoCmd.OpenForm "frmNoPrinter"
Forms!frmNoPrinter!Label2.Caption = Me.TxtName & " " &
"Offline"
Exit Sub
ElseIf m_hApi > 0 Then
End If

BiCloseMonPrinter (m_hApi)
ElseIf m_hApi = 0 Then
End If

Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
Loop Until Me.TxtNumber = 0
Me.TxtError = 1

If Me.TxtPriv > 0 And _
Me.TxtCount > 0 And _
Me.TxtError > 0 Then
'SEND AND LEAVE
Dim PO As Integer
Dim SENDSQL As String
Dim DEADSQL As String
DoCmd.OpenForm "frmSending"
Me.TxtNumber = Nz(DMax("PDPrinterID", "tblPrintDetails",
"PDCheckID
= " & Me.CheckID), 0)
On Error GoTo ErrorHandler
Do
Me.TxtEmpty = Nz(DCount("PDPrinterID", "tblPrintDetails",
"PDPrinterID = " & Me.TxtNumber & "AND PDCheckID = " & Me.CheckID), 0)
If IsNull(Me.TxtEmpty) Or _
Me.TxtEmpty = 0 Then
Me.TxtNumber = Me.TxtNumber - 1
ElseIf Me.TxtEmpty >= 1 Then
Me.TxtCon = Nz(DLookup("PDCon", "tblPrintDetails",
"PDPrinterID = " & Me.TxtNumber), 0)
If Me.TxtCon = -1 Then
Me.TxtItemID = Nz(DLookup("PDItemID",
"tblPrintDetails", "PDPrinterID = " & Me.TxtNumber), 0)
Me.TxtGo = Nz(DCount("PDItemID",
"tblPrintDetails",
"PDItemID <> " & Me.TxtItemID & " And PDPrinterID = " & Me.TxtNumber &
"And
PDCheckID = " & Me.CheckID), 0)
If Me.TxtGo >= 1 Then
Me.TxtName = Nz(DLookup("PrinterName",
"tblPrinters", "PrinterID = " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
DoCmd.OpenReport "rptPrepOrder", , ,
"PDCheckID
= " & Me.CheckID & " And PDPrinterID = " & Me.TxtNumber & ""
Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
ElseIf Me.TxtGo = 0 Then
Me.TxtNumber = Me.TxtNumber - 1
Me.TxtName = Nz(DLookup("PrinterName",
"tblPrinters", "PrinterID = " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
DoCmd.OpenReport "rptPrepOrder", , ,
"PDCheckID
= " & Me.CheckID & " And PDPrinterID = " & Me.TxtNumber & ""
Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
End If
ElseIf Me.TxtCon = 0 Then
Me.TxtName = Nz(DLookup("PrinterName",
"tblPrinters", "PrinterID = " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
DoCmd.OpenReport "rptPrepOrder", , , "PDCheckID =
"
& Me.CheckID & " And PDPrinterID = " & Me.TxtNumber & ""
Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
End If
End If
Loop Until Me.TxtNumber = 0
'SEND ITEMS
DoCmd.SetWarnings False
SENDSQL = "UPDATE tblCheckDetails SET [CDSent] = True " &
_
"WHERE tblCheckDetails.[CDCheckID] =
Forms!frmOrderScreen![CheckID]; "
DoCmd.RunSQL (SENDSQL)

DEADSQL = "UPDATE tblPrintDetails SET [PDDead] = True " &
_
"WHERE tblPrintDetails.[PDCheckID] =
Forms!frmOrderScreen![CheckID]; "
DoCmd.RunSQL (DEADSQL)

DoCmd.SetWarnings True
Me.ListPrep.Requery
End
ErrorHandler:
DoCmd.OpenForm "frmMsgWarning"
Forms!frmMsgWarning!TxtMsg = "INVALID PRINTER"
ElseIf Me.TxtPriv > 0 And _
Me.TxtCount = 0 And _
Me.TxtError >= 0 Then
DoCmd.OpenForm "frmMsgWarning"
Forms!frmMsgWarning!TxtMsg = "NOTHING TO SEND"
Else:
DoCmd.OpenForm "frmMsgWarning"
Forms!frmMsgWarning!TxtMsg = "SENDING DENIED"
End If
End If
Click to expand...
Click to expand...
 
M

Marshall Barton

I'm not going to rewrite all that code for you, so you
should read up on queries and recordsets.

I can't even figure out what you are trying to do beyond
finding a record that doesn't have "No Print" for a printer
name. If that's really all you are doing, can't you create
a query that does that instead of looping? The query would
probably Join tblPrinters and tblPrintDetails and use
criteria to find the needed data in one lookup.

If you need to make it run faster, then your design needs to
be redone. Think about using database operations (queries)
instead of all that code.
--
Marsh
MVP [MS Access]

Sounds good! But how on earth would I do it!

"Marshall Barton" wrote
I can't unravel all that, but all those domain aggregate
functions jump out as a performance hit. If you are going
to retrieve more then one value from a table, it's time to
seriously consider using a recordset (with a restrictive
Where clause) so you can get all the needed values in a
single data retrieval operation.

Is There any way to speed this code up. It takes about 4 Seconds for any
of
the forms to pop up.

Dim rtn As Long
Dim Driver As String
Dim I As Integer
Dim X As Printer

''SECURITY
Me.TxtSecure = Nz(DLookup("[JobNameSecurityID]", "tblJobNames",
"JobNameID = " & Me.TxtJobID & ""), 0)
Me.TxtPriv = Nz(DLookup("[SDPrivID]", "tblSecurityDetails",
"SecurityID
= " & Me.TxtSecure & " And SDPrivID = 39"), 0)

'SENDING
Me.TxtCount = Nz(DCount("CDSent", "tblCheckDetails", "CDCheckID =" &
Me.CheckID & " and CDSent = 0"), 0)
'PRINTERS
'CHECKS PRINTER AVAILABILITY
Me.TxtNumber = Nz(DMax("PDPrinterID", "tblPrintDetails", "PDCheckID =
"
& Me.CheckID), 0)
If Me.TxtNumber >= 1 Then
On Error GoTo ErrorHandler
Do
Me.TxtEmpty = Nz(DCount("PDPrinterID", "tblPrintDetails",
"PDPrinterID = " & Me.TxtNumber & "AND PDCheckID = " & Me.CheckID), 0)
If IsNull(Me.TxtEmpty) Or _
Me.TxtEmpty = 0 Then
Me.TxtNumber = Me.TxtNumber - 1
ElseIf Me.TxtEmpty >= 1 Then
Me.TxtName = Nz(DLookup("PrinterName", "tblPrinters",
"PrinterID
= " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
m_PrtN = Me.TxtName
If Len(Me.TxtName) = 0 Then
Exit Sub
End If

rtn = GetPrnDriverName(m_PrtN, Driver)
If rtn <> 0 Then
Driver = ""
End If

m_DrvN = Driver

m_hApi = BiOpenMonPrinter(TYPE_PRINTER, m_PrtN)
If m_hApi < 0 Then
Me.TxtError = 0
DoCmd.OpenForm "frmNoPrinter"
Forms!frmNoPrinter!Label2.Caption = Me.TxtName & " " &
"Offline"
Exit Sub
ElseIf m_hApi > 0 Then
End If

BiCloseMonPrinter (m_hApi)
ElseIf m_hApi = 0 Then
End If

Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
Loop Until Me.TxtNumber = 0
Me.TxtError = 1

If Me.TxtPriv > 0 And _
Me.TxtCount > 0 And _
Me.TxtError > 0 Then
'SEND AND LEAVE
Dim PO As Integer
Dim SENDSQL As String
Dim DEADSQL As String
DoCmd.OpenForm "frmSending"
Me.TxtNumber = Nz(DMax("PDPrinterID", "tblPrintDetails",
"PDCheckID
= " & Me.CheckID), 0)
On Error GoTo ErrorHandler
Do
Me.TxtEmpty = Nz(DCount("PDPrinterID", "tblPrintDetails",
"PDPrinterID = " & Me.TxtNumber & "AND PDCheckID = " & Me.CheckID), 0)
If IsNull(Me.TxtEmpty) Or _
Me.TxtEmpty = 0 Then
Me.TxtNumber = Me.TxtNumber - 1
ElseIf Me.TxtEmpty >= 1 Then
Me.TxtCon = Nz(DLookup("PDCon", "tblPrintDetails",
"PDPrinterID = " & Me.TxtNumber), 0)
If Me.TxtCon = -1 Then
Me.TxtItemID = Nz(DLookup("PDItemID",
"tblPrintDetails", "PDPrinterID = " & Me.TxtNumber), 0)
Me.TxtGo = Nz(DCount("PDItemID",
"tblPrintDetails",
"PDItemID <> " & Me.TxtItemID & " And PDPrinterID = " & Me.TxtNumber &
"And
PDCheckID = " & Me.CheckID), 0)
If Me.TxtGo >= 1 Then
Me.TxtName = Nz(DLookup("PrinterName",
"tblPrinters", "PrinterID = " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
DoCmd.OpenReport "rptPrepOrder", , ,
"PDCheckID
= " & Me.CheckID & " And PDPrinterID = " & Me.TxtNumber & ""
Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
ElseIf Me.TxtGo = 0 Then
Me.TxtNumber = Me.TxtNumber - 1
Me.TxtName = Nz(DLookup("PrinterName",
"tblPrinters", "PrinterID = " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
DoCmd.OpenReport "rptPrepOrder", , ,
"PDCheckID
= " & Me.CheckID & " And PDPrinterID = " & Me.TxtNumber & ""
Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
End If
ElseIf Me.TxtCon = 0 Then
Me.TxtName = Nz(DLookup("PrinterName",
"tblPrinters", "PrinterID = " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
DoCmd.OpenReport "rptPrepOrder", , , "PDCheckID =
"
& Me.CheckID & " And PDPrinterID = " & Me.TxtNumber & ""
Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
End If
End If
Loop Until Me.TxtNumber = 0
'SEND ITEMS
DoCmd.SetWarnings False
SENDSQL = "UPDATE tblCheckDetails SET [CDSent] = True " &
_
"WHERE tblCheckDetails.[CDCheckID] =
Forms!frmOrderScreen![CheckID]; "
DoCmd.RunSQL (SENDSQL)

DEADSQL = "UPDATE tblPrintDetails SET [PDDead] = True " &
_
"WHERE tblPrintDetails.[PDCheckID] =
Forms!frmOrderScreen![CheckID]; "
DoCmd.RunSQL (DEADSQL)

DoCmd.SetWarnings True
Me.ListPrep.Requery
End
ErrorHandler:
DoCmd.OpenForm "frmMsgWarning"
Forms!frmMsgWarning!TxtMsg = "INVALID PRINTER"
ElseIf Me.TxtPriv > 0 And _
Me.TxtCount = 0 And _
Me.TxtError >= 0 Then
DoCmd.OpenForm "frmMsgWarning"
Forms!frmMsgWarning!TxtMsg = "NOTHING TO SEND"
Else:
DoCmd.OpenForm "frmMsgWarning"
Forms!frmMsgWarning!TxtMsg = "SENDING DENIED"
End If
End If
Click to expand...
Click to expand...
Click to expand...
 
D

DS

Well Here it is in a nutshell...!
First I check to see if the person is allowed to print.
Next I check to see if there are any records to print.
Next I check to see if any of the printers that are going to print are not
offline.
Finally if all is well I print to all of the printers that need printing to.

I'm looping through the records in 2 areas...
1st to find if printers are online
2nd to actually print the reports

Thanks
DS

Marshall Barton said:
I'm not going to rewrite all that code for you, so you
should read up on queries and recordsets.

I can't even figure out what you are trying to do beyond
finding a record that doesn't have "No Print" for a printer
name. If that's really all you are doing, can't you create
a query that does that instead of looping? The query would
probably Join tblPrinters and tblPrintDetails and use
criteria to find the needed data in one lookup.

If you need to make it run faster, then your design needs to
be redone. Think about using database operations (queries)
instead of all that code.
--
Marsh
MVP [MS Access]

Sounds good! But how on earth would I do it!

"Marshall Barton" wrote
I can't unravel all that, but all those domain aggregate
functions jump out as a performance hit. If you are going
to retrieve more then one value from a table, it's time to
seriously consider using a recordset (with a restrictive
Where clause) so you can get all the needed values in a
single data retrieval operation.


DS wrote:
Is There any way to speed this code up. It takes about 4 Seconds for
any
of
the forms to pop up.

Dim rtn As Long
Dim Driver As String
Dim I As Integer
Dim X As Printer

''SECURITY
Me.TxtSecure = Nz(DLookup("[JobNameSecurityID]", "tblJobNames",
"JobNameID = " & Me.TxtJobID & ""), 0)
Me.TxtPriv = Nz(DLookup("[SDPrivID]", "tblSecurityDetails",
"SecurityID
= " & Me.TxtSecure & " And SDPrivID = 39"), 0)

'SENDING
Me.TxtCount = Nz(DCount("CDSent", "tblCheckDetails", "CDCheckID =" &
Me.CheckID & " and CDSent = 0"), 0)
'PRINTERS
'CHECKS PRINTER AVAILABILITY
Me.TxtNumber = Nz(DMax("PDPrinterID", "tblPrintDetails", "PDCheckID
=
"
& Me.CheckID), 0)
If Me.TxtNumber >= 1 Then
On Error GoTo ErrorHandler
Do
Me.TxtEmpty = Nz(DCount("PDPrinterID", "tblPrintDetails",
"PDPrinterID = " & Me.TxtNumber & "AND PDCheckID = " & Me.CheckID), 0)
If IsNull(Me.TxtEmpty) Or _
Me.TxtEmpty = 0 Then
Me.TxtNumber = Me.TxtNumber - 1
ElseIf Me.TxtEmpty >= 1 Then
Me.TxtName = Nz(DLookup("PrinterName", "tblPrinters",
"PrinterID
= " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
m_PrtN = Me.TxtName
If Len(Me.TxtName) = 0 Then
Exit Sub
End If

rtn = GetPrnDriverName(m_PrtN, Driver)
If rtn <> 0 Then
Driver = ""
End If

m_DrvN = Driver

m_hApi = BiOpenMonPrinter(TYPE_PRINTER, m_PrtN)
If m_hApi < 0 Then
Me.TxtError = 0
DoCmd.OpenForm "frmNoPrinter"
Forms!frmNoPrinter!Label2.Caption = Me.TxtName & " "
&
"Offline"
Exit Sub
ElseIf m_hApi > 0 Then
End If

BiCloseMonPrinter (m_hApi)
ElseIf m_hApi = 0 Then
End If

Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
Loop Until Me.TxtNumber = 0
Me.TxtError = 1

If Me.TxtPriv > 0 And _
Me.TxtCount > 0 And _
Me.TxtError > 0 Then
'SEND AND LEAVE
Dim PO As Integer
Dim SENDSQL As String
Dim DEADSQL As String
DoCmd.OpenForm "frmSending"
Me.TxtNumber = Nz(DMax("PDPrinterID", "tblPrintDetails",
"PDCheckID
= " & Me.CheckID), 0)
On Error GoTo ErrorHandler
Do
Me.TxtEmpty = Nz(DCount("PDPrinterID", "tblPrintDetails",
"PDPrinterID = " & Me.TxtNumber & "AND PDCheckID = " & Me.CheckID), 0)
If IsNull(Me.TxtEmpty) Or _
Me.TxtEmpty = 0 Then
Me.TxtNumber = Me.TxtNumber - 1
ElseIf Me.TxtEmpty >= 1 Then
Me.TxtCon = Nz(DLookup("PDCon", "tblPrintDetails",
"PDPrinterID = " & Me.TxtNumber), 0)
If Me.TxtCon = -1 Then
Me.TxtItemID = Nz(DLookup("PDItemID",
"tblPrintDetails", "PDPrinterID = " & Me.TxtNumber), 0)
Me.TxtGo = Nz(DCount("PDItemID",
"tblPrintDetails",
"PDItemID <> " & Me.TxtItemID & " And PDPrinterID = " & Me.TxtNumber &
"And
PDCheckID = " & Me.CheckID), 0)
If Me.TxtGo >= 1 Then
Me.TxtName = Nz(DLookup("PrinterName",
"tblPrinters", "PrinterID = " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
DoCmd.OpenReport "rptPrepOrder", , ,
"PDCheckID
= " & Me.CheckID & " And PDPrinterID = " & Me.TxtNumber & ""
Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
ElseIf Me.TxtGo = 0 Then
Me.TxtNumber = Me.TxtNumber - 1
Me.TxtName = Nz(DLookup("PrinterName",
"tblPrinters", "PrinterID = " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
DoCmd.OpenReport "rptPrepOrder", , ,
"PDCheckID
= " & Me.CheckID & " And PDPrinterID = " & Me.TxtNumber & ""
Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
End If
ElseIf Me.TxtCon = 0 Then
Me.TxtName = Nz(DLookup("PrinterName",
"tblPrinters", "PrinterID = " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
DoCmd.OpenReport "rptPrepOrder", , , "PDCheckID
=
"
& Me.CheckID & " And PDPrinterID = " & Me.TxtNumber & ""
Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
End If
End If
Loop Until Me.TxtNumber = 0
'SEND ITEMS
DoCmd.SetWarnings False
SENDSQL = "UPDATE tblCheckDetails SET [CDSent] = True "
&
_
"WHERE tblCheckDetails.[CDCheckID] =
Forms!frmOrderScreen![CheckID]; "
DoCmd.RunSQL (SENDSQL)

DEADSQL = "UPDATE tblPrintDetails SET [PDDead] = True "
&
_
"WHERE tblPrintDetails.[PDCheckID] =
Forms!frmOrderScreen![CheckID]; "
DoCmd.RunSQL (DEADSQL)

DoCmd.SetWarnings True
Me.ListPrep.Requery
End
ErrorHandler:
DoCmd.OpenForm "frmMsgWarning"
Forms!frmMsgWarning!TxtMsg = "INVALID PRINTER"
ElseIf Me.TxtPriv > 0 And _
Me.TxtCount = 0 And _
Me.TxtError >= 0 Then
DoCmd.OpenForm "frmMsgWarning"
Forms!frmMsgWarning!TxtMsg = "NOTHING TO SEND"
Else:
DoCmd.OpenForm "frmMsgWarning"
Forms!frmMsgWarning!TxtMsg = "SENDING DENIED"
End If
End If
Click to expand...
Click to expand...
Click to expand...
 
M

Marshall Barton

That helps, but the secret's in the tables. What fields
(type, primary and foreign keys, example values) are in the
tblPrinters and tblPrintDetails tables?

As I tried to say before, the goal is to construct a query
the retrieves all the desired printer information in one
fell swoop.
--
Marsh
MVP [MS Access]

Well Here it is in a nutshell...!
First I check to see if the person is allowed to print.
Next I check to see if there are any records to print.
Next I check to see if any of the printers that are going to print are not
offline.
Finally if all is well I print to all of the printers that need printing to.

I'm looping through the records in 2 areas...
1st to find if printers are online
2nd to actually print the reports

I'm not going to rewrite all that code for you, so you
should read up on queries and recordsets.

I can't even figure out what you are trying to do beyond
finding a record that doesn't have "No Print" for a printer
name. If that's really all you are doing, can't you create
a query that does that instead of looping? The query would
probably Join tblPrinters and tblPrintDetails and use
criteria to find the needed data in one lookup.

If you need to make it run faster, then your design needs to
be redone. Think about using database operations (queries)
instead of all that code.

Sounds good! But how on earth would I do it!

"Marshall Barton" wrote
I can't unravel all that, but all those domain aggregate
functions jump out as a performance hit. If you are going
to retrieve more then one value from a table, it's time to
seriously consider using a recordset (with a restrictive
Where clause) so you can get all the needed values in a
single data retrieval operation.


DS wrote:
Is There any way to speed this code up. It takes about 4 Seconds for
any
of
the forms to pop up.

Dim rtn As Long
Dim Driver As String
Dim I As Integer
Dim X As Printer

''SECURITY
Me.TxtSecure = Nz(DLookup("[JobNameSecurityID]", "tblJobNames",
"JobNameID = " & Me.TxtJobID & ""), 0)
Me.TxtPriv = Nz(DLookup("[SDPrivID]", "tblSecurityDetails",
"SecurityID
= " & Me.TxtSecure & " And SDPrivID = 39"), 0)

'SENDING
Me.TxtCount = Nz(DCount("CDSent", "tblCheckDetails", "CDCheckID =" &
Me.CheckID & " and CDSent = 0"), 0)
'PRINTERS
'CHECKS PRINTER AVAILABILITY
Me.TxtNumber = Nz(DMax("PDPrinterID", "tblPrintDetails", "PDCheckID
=
"
& Me.CheckID), 0)
If Me.TxtNumber >= 1 Then
On Error GoTo ErrorHandler
Do
Me.TxtEmpty = Nz(DCount("PDPrinterID", "tblPrintDetails",
"PDPrinterID = " & Me.TxtNumber & "AND PDCheckID = " & Me.CheckID), 0)
If IsNull(Me.TxtEmpty) Or _
Me.TxtEmpty = 0 Then
Me.TxtNumber = Me.TxtNumber - 1
ElseIf Me.TxtEmpty >= 1 Then
Me.TxtName = Nz(DLookup("PrinterName", "tblPrinters",
"PrinterID
= " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
m_PrtN = Me.TxtName
If Len(Me.TxtName) = 0 Then
Exit Sub
End If

rtn = GetPrnDriverName(m_PrtN, Driver)
If rtn <> 0 Then
Driver = ""
End If

m_DrvN = Driver

m_hApi = BiOpenMonPrinter(TYPE_PRINTER, m_PrtN)
If m_hApi < 0 Then
Me.TxtError = 0
DoCmd.OpenForm "frmNoPrinter"
Forms!frmNoPrinter!Label2.Caption = Me.TxtName & " "
&
"Offline"
Exit Sub
ElseIf m_hApi > 0 Then
End If

BiCloseMonPrinter (m_hApi)
ElseIf m_hApi = 0 Then
End If

Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
Loop Until Me.TxtNumber = 0
Me.TxtError = 1

If Me.TxtPriv > 0 And _
Me.TxtCount > 0 And _
Me.TxtError > 0 Then
'SEND AND LEAVE
Dim PO As Integer
Dim SENDSQL As String
Dim DEADSQL As String
DoCmd.OpenForm "frmSending"
Me.TxtNumber = Nz(DMax("PDPrinterID", "tblPrintDetails",
"PDCheckID
= " & Me.CheckID), 0)
On Error GoTo ErrorHandler
Do
Me.TxtEmpty = Nz(DCount("PDPrinterID", "tblPrintDetails",
"PDPrinterID = " & Me.TxtNumber & "AND PDCheckID = " & Me.CheckID), 0)
If IsNull(Me.TxtEmpty) Or _
Me.TxtEmpty = 0 Then
Me.TxtNumber = Me.TxtNumber - 1
ElseIf Me.TxtEmpty >= 1 Then
Me.TxtCon = Nz(DLookup("PDCon", "tblPrintDetails",
"PDPrinterID = " & Me.TxtNumber), 0)
If Me.TxtCon = -1 Then
Me.TxtItemID = Nz(DLookup("PDItemID",
"tblPrintDetails", "PDPrinterID = " & Me.TxtNumber), 0)
Me.TxtGo = Nz(DCount("PDItemID",
"tblPrintDetails",
"PDItemID <> " & Me.TxtItemID & " And PDPrinterID = " & Me.TxtNumber &
"And
PDCheckID = " & Me.CheckID), 0)
If Me.TxtGo >= 1 Then
Me.TxtName = Nz(DLookup("PrinterName",
"tblPrinters", "PrinterID = " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
DoCmd.OpenReport "rptPrepOrder", , ,
"PDCheckID
= " & Me.CheckID & " And PDPrinterID = " & Me.TxtNumber & ""
Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
ElseIf Me.TxtGo = 0 Then
Me.TxtNumber = Me.TxtNumber - 1
Me.TxtName = Nz(DLookup("PrinterName",
"tblPrinters", "PrinterID = " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
DoCmd.OpenReport "rptPrepOrder", , ,
"PDCheckID
= " & Me.CheckID & " And PDPrinterID = " & Me.TxtNumber & ""
Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
End If
ElseIf Me.TxtCon = 0 Then
Me.TxtName = Nz(DLookup("PrinterName",
"tblPrinters", "PrinterID = " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
DoCmd.OpenReport "rptPrepOrder", , , "PDCheckID
=
"
& Me.CheckID & " And PDPrinterID = " & Me.TxtNumber & ""
Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
End If
End If
Loop Until Me.TxtNumber = 0
'SEND ITEMS
DoCmd.SetWarnings False
SENDSQL = "UPDATE tblCheckDetails SET [CDSent] = True "
&
_
"WHERE tblCheckDetails.[CDCheckID] =
Forms!frmOrderScreen![CheckID]; "
DoCmd.RunSQL (SENDSQL)

DEADSQL = "UPDATE tblPrintDetails SET [PDDead] = True "
&
_
"WHERE tblPrintDetails.[PDCheckID] =
Forms!frmOrderScreen![CheckID]; "
DoCmd.RunSQL (DEADSQL)

DoCmd.SetWarnings True
Me.ListPrep.Requery
End
ErrorHandler:
DoCmd.OpenForm "frmMsgWarning"
Forms!frmMsgWarning!TxtMsg = "INVALID PRINTER"
ElseIf Me.TxtPriv > 0 And _
Me.TxtCount = 0 And _
Me.TxtError >= 0 Then
DoCmd.OpenForm "frmMsgWarning"
Forms!frmMsgWarning!TxtMsg = "NOTHING TO SEND"
Else:
DoCmd.OpenForm "frmMsgWarning"
Forms!frmMsgWarning!TxtMsg = "SENDING DENIED"
End If
End If
Click to expand...
Click to expand...
Click to expand...
 
D

DS

I'm looking into getting rid of the CheckDetails Me.TxtCount and just using
Me.TxtEmpty because thats the same info, I'll keep you abreast.
Thanks
DS
Marshall Barton said:
That helps, but the secret's in the tables. What fields
(type, primary and foreign keys, example values) are in the
tblPrinters and tblPrintDetails tables?

As I tried to say before, the goal is to construct a query
the retrieves all the desired printer information in one
fell swoop.
--
Marsh
MVP [MS Access]

Well Here it is in a nutshell...!
First I check to see if the person is allowed to print.
Next I check to see if there are any records to print.
Next I check to see if any of the printers that are going to print are not
offline.
Finally if all is well I print to all of the printers that need printing
to.

I'm looping through the records in 2 areas...
1st to find if printers are online
2nd to actually print the reports

I'm not going to rewrite all that code for you, so you
should read up on queries and recordsets.

I can't even figure out what you are trying to do beyond
finding a record that doesn't have "No Print" for a printer
name. If that's really all you are doing, can't you create
a query that does that instead of looping? The query would
probably Join tblPrinters and tblPrintDetails and use
criteria to find the needed data in one lookup.

If you need to make it run faster, then your design needs to
be redone. Think about using database operations (queries)
instead of all that code.


DS wrote:
Sounds good! But how on earth would I do it!

"Marshall Barton" wrote
I can't unravel all that, but all those domain aggregate
functions jump out as a performance hit. If you are going
to retrieve more then one value from a table, it's time to
seriously consider using a recordset (with a restrictive
Where clause) so you can get all the needed values in a
single data retrieval operation.


DS wrote:
Is There any way to speed this code up. It takes about 4 Seconds for
any
of
the forms to pop up.

Dim rtn As Long
Dim Driver As String
Dim I As Integer
Dim X As Printer

''SECURITY
Me.TxtSecure = Nz(DLookup("[JobNameSecurityID]", "tblJobNames",
"JobNameID = " & Me.TxtJobID & ""), 0)
Me.TxtPriv = Nz(DLookup("[SDPrivID]", "tblSecurityDetails",
"SecurityID
= " & Me.TxtSecure & " And SDPrivID = 39"), 0)

'SENDING
Me.TxtCount = Nz(DCount("CDSent", "tblCheckDetails", "CDCheckID ="
&
Me.CheckID & " and CDSent = 0"), 0)
'PRINTERS
'CHECKS PRINTER AVAILABILITY
Me.TxtNumber = Nz(DMax("PDPrinterID", "tblPrintDetails",
"PDCheckID
=
"
& Me.CheckID), 0)
If Me.TxtNumber >= 1 Then
On Error GoTo ErrorHandler
Do
Me.TxtEmpty = Nz(DCount("PDPrinterID", "tblPrintDetails",
"PDPrinterID = " & Me.TxtNumber & "AND PDCheckID = " & Me.CheckID), 0)
If IsNull(Me.TxtEmpty) Or _
Me.TxtEmpty = 0 Then
Me.TxtNumber = Me.TxtNumber - 1
ElseIf Me.TxtEmpty >= 1 Then
Me.TxtName = Nz(DLookup("PrinterName", "tblPrinters",
"PrinterID
= " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
m_PrtN = Me.TxtName
If Len(Me.TxtName) = 0 Then
Exit Sub
End If

rtn = GetPrnDriverName(m_PrtN, Driver)
If rtn <> 0 Then
Driver = ""
End If

m_DrvN = Driver

m_hApi = BiOpenMonPrinter(TYPE_PRINTER, m_PrtN)
If m_hApi < 0 Then
Me.TxtError = 0
DoCmd.OpenForm "frmNoPrinter"
Forms!frmNoPrinter!Label2.Caption = Me.TxtName & "
"
&
"Offline"
Exit Sub
ElseIf m_hApi > 0 Then
End If

BiCloseMonPrinter (m_hApi)
ElseIf m_hApi = 0 Then
End If

Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
Loop Until Me.TxtNumber = 0
Me.TxtError = 1

If Me.TxtPriv > 0 And _
Me.TxtCount > 0 And _
Me.TxtError > 0 Then
'SEND AND LEAVE
Dim PO As Integer
Dim SENDSQL As String
Dim DEADSQL As String
DoCmd.OpenForm "frmSending"
Me.TxtNumber = Nz(DMax("PDPrinterID", "tblPrintDetails",
"PDCheckID
= " & Me.CheckID), 0)
On Error GoTo ErrorHandler
Do
Me.TxtEmpty = Nz(DCount("PDPrinterID", "tblPrintDetails",
"PDPrinterID = " & Me.TxtNumber & "AND PDCheckID = " & Me.CheckID), 0)
If IsNull(Me.TxtEmpty) Or _
Me.TxtEmpty = 0 Then
Me.TxtNumber = Me.TxtNumber - 1
ElseIf Me.TxtEmpty >= 1 Then
Me.TxtCon = Nz(DLookup("PDCon", "tblPrintDetails",
"PDPrinterID = " & Me.TxtNumber), 0)
If Me.TxtCon = -1 Then
Me.TxtItemID = Nz(DLookup("PDItemID",
"tblPrintDetails", "PDPrinterID = " & Me.TxtNumber), 0)
Me.TxtGo = Nz(DCount("PDItemID",
"tblPrintDetails",
"PDItemID <> " & Me.TxtItemID & " And PDPrinterID = " & Me.TxtNumber &
"And
PDCheckID = " & Me.CheckID), 0)
If Me.TxtGo >= 1 Then
Me.TxtName = Nz(DLookup("PrinterName",
"tblPrinters", "PrinterID = " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
DoCmd.OpenReport "rptPrepOrder", , ,
"PDCheckID
= " & Me.CheckID & " And PDPrinterID = " & Me.TxtNumber & ""
Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
ElseIf Me.TxtGo = 0 Then
Me.TxtNumber = Me.TxtNumber - 1
Me.TxtName = Nz(DLookup("PrinterName",
"tblPrinters", "PrinterID = " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
DoCmd.OpenReport "rptPrepOrder", , ,
"PDCheckID
= " & Me.CheckID & " And PDPrinterID = " & Me.TxtNumber & ""
Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
End If
ElseIf Me.TxtCon = 0 Then
Me.TxtName = Nz(DLookup("PrinterName",
"tblPrinters", "PrinterID = " & Me.TxtNumber), "")
If Not Me.TxtName = "No Print" Then
Set Application.Printer =
Application.Printers(Me.TxtName.Value)
DoCmd.OpenReport "rptPrepOrder", , ,
"PDCheckID
=
"
& Me.CheckID & " And PDPrinterID = " & Me.TxtNumber & ""
Me.TxtNumber = Me.TxtNumber - 1
Set Application.Printer = Nothing
Else
End If
End If
End If
Loop Until Me.TxtNumber = 0
'SEND ITEMS
DoCmd.SetWarnings False
SENDSQL = "UPDATE tblCheckDetails SET [CDSent] = True
"
&
_
"WHERE tblCheckDetails.[CDCheckID] =
Forms!frmOrderScreen![CheckID]; "
DoCmd.RunSQL (SENDSQL)

DEADSQL = "UPDATE tblPrintDetails SET [PDDead] = True
"
&
_
"WHERE tblPrintDetails.[PDCheckID] =
Forms!frmOrderScreen![CheckID]; "
DoCmd.RunSQL (DEADSQL)

DoCmd.SetWarnings True
Me.ListPrep.Requery
End
ErrorHandler:
DoCmd.OpenForm "frmMsgWarning"
Forms!frmMsgWarning!TxtMsg = "INVALID PRINTER"
ElseIf Me.TxtPriv > 0 And _
Me.TxtCount = 0 And _
Me.TxtError >= 0 Then
DoCmd.OpenForm "frmMsgWarning"
Forms!frmMsgWarning!TxtMsg = "NOTHING TO SEND"
Else:
DoCmd.OpenForm "frmMsgWarning"
Forms!frmMsgWarning!TxtMsg = "SENDING DENIED"
End If
End If
Click to expand...
Click to expand...
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

Code Is Slow 3
Is This Good? 13
Loop Problem 5
RECORDSET 2
A Lot of Code 10
Complex If Statement 6
Please help changing the way this report is filtered and opened... 2
3rd grade code returns extra zero instead of decimal or hides deci 2

Top