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 works if there is an 'error'.
What I need help on is what to put there instead of the message box. I'd
like a popup or list or something that shows the fields from tblrgts for
prnt preps and the fields from tblReagents and tblprep where there are
'errors' (no match).
----
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 ("there's an error")
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 works if there is an 'error'.
What I need help on is what to put there instead of the message box. I'd
like a popup or list or something that shows the fields from tblrgts for
prnt preps and the fields from tblReagents and tblprep where there are
'errors' (no match).
----
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 ("there's an error")
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