K
Karen
I'm working on a form where I need to check to see if the entries in a table
are valid (that the code exists in another table or that the batchsize
entered is between the values of two fld in another table). I have the
following code behind a 'Print' button. I want the result to be either the
report prints (when there are no errors) or I want a list of the records
with 'errors' to be shown. The following code prints the report if there's
not an 'error' and the message box and the docmd.openquery work if there is
an 'error'.
Now what I'd like to do is change the code somehow so that once the user has
corrected the errors (while viewing the query results) and closed the query
view, the report will print. I thought about a loop of some sort but what I
tried left me in a continuous loop. Can anyone help me with this?
----
Private Sub previewpreps_Click()
On Error GoTo Err_previewpreps_Click
Dim rst As DAO.Recordset
Dim MyWksp As Workspace
Dim MyDB As DAO.Database
Dim MySQL As String
Set MyWksp = DBEngine.Workspaces(0)
Set MyDB = MyWksp.Databases(0)
MySQL = "SELECT [tblrgts for prnt preps].fldcode, [tblrgts for prnt
preps].fldbatchsize, tblReagents.fldcode FROM ([tblrgts for prnt preps] LEFT
JOIN tblReagents ON [tblrgts for prnt preps].fldcode = tblReagents.fldcode)
LEFT JOIN tblPrep ON [tblrgts for prnt preps].fldcode = tblPrep.fldcode
WHERE (((tblReagents.fldcode) Is Null)) OR ((([tblrgts for prnt
preps].fldbatchsize) Not Between [tblprep].[fldbatchmin] And
[tblprep].[fldbatchmax]))"
Set rst = MyDB.OpenRecordset(MySQL, dbOpenForwardOnly)
If Not rst.EOF Then
MsgBox ("The following records have errors:")
docmd.openquery ("qry multi print preps")
Else
Dim stDocName As String
stDocName = "rpt batch print preps"
DoCmd.OpenReport stDocName, acPreview
End If
Exit_previewpreps_Click:
Exit Sub
Err_previewpreps_Click:
MsgBox Err.Description
Resume Exit_previewpreps_Click
End Sub
----
Thanks for your help.
Karen
are valid (that the code exists in another table or that the batchsize
entered is between the values of two fld in another table). I have the
following code behind a 'Print' button. I want the result to be either the
report prints (when there are no errors) or I want a list of the records
with 'errors' to be shown. The following code prints the report if there's
not an 'error' and the message box and the docmd.openquery work if there is
an 'error'.
Now what I'd like to do is change the code somehow so that once the user has
corrected the errors (while viewing the query results) and closed the query
view, the report will print. I thought about a loop of some sort but what I
tried left me in a continuous loop. Can anyone help me with this?
----
Private Sub previewpreps_Click()
On Error GoTo Err_previewpreps_Click
Dim rst As DAO.Recordset
Dim MyWksp As Workspace
Dim MyDB As DAO.Database
Dim MySQL As String
Set MyWksp = DBEngine.Workspaces(0)
Set MyDB = MyWksp.Databases(0)
MySQL = "SELECT [tblrgts for prnt preps].fldcode, [tblrgts for prnt
preps].fldbatchsize, tblReagents.fldcode FROM ([tblrgts for prnt preps] LEFT
JOIN tblReagents ON [tblrgts for prnt preps].fldcode = tblReagents.fldcode)
LEFT JOIN tblPrep ON [tblrgts for prnt preps].fldcode = tblPrep.fldcode
WHERE (((tblReagents.fldcode) Is Null)) OR ((([tblrgts for prnt
preps].fldbatchsize) Not Between [tblprep].[fldbatchmin] And
[tblprep].[fldbatchmax]))"
Set rst = MyDB.OpenRecordset(MySQL, dbOpenForwardOnly)
If Not rst.EOF Then
MsgBox ("The following records have errors:")
docmd.openquery ("qry multi print preps")
Else
Dim stDocName As String
stDocName = "rpt batch print preps"
DoCmd.OpenReport stDocName, acPreview
End If
Exit_previewpreps_Click:
Exit Sub
Err_previewpreps_Click:
MsgBox Err.Description
Resume Exit_previewpreps_Click
End Sub
----
Thanks for your help.
Karen