showing results of a SQL statement in code

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
 
G

George Nicholson

Consider assigning your SQL to a querydef. When there is an error, display a
message box, just as you are now: "The following records have errors..:" and
then open the query for the user to view.

On second thought, consider changing your SQL to a saved query (whose SQL
property you can change via code if you need/want to, but what you have
seems pretty static), then you won't have to putz around with creating a new
querydef in code. Then just open & display the saved query once you
determine that it has records.

HTH,
 
K

Karen

Thanks George!

Just one thing, what if I want to have it check to see if there are errors,
display the query if there are, allow the user to make the corrections right
there in the query (all of this works up to here) and then once the
corrections are made in the query view preview the report so they can print
it.

Again, thanks for the help on the first part.
Karen


George Nicholson said:
Consider assigning your SQL to a querydef. When there is an error, display a
message box, just as you are now: "The following records have errors..:" and
then open the query for the user to view.

On second thought, consider changing your SQL to a saved query (whose SQL
property you can change via code if you need/want to, but what you have
seems pretty static), then you won't have to putz around with creating a new
querydef in code. Then just open & display the saved query once you
determine that it has records.

HTH,
--
George Nicholson

Remove 'Junk' from return address.



Karen said:
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
 

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

help with a loop 4
help with code - DAO Recordset 7

Top