help with a loop

[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

 

[Marshall Barton]

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

Instead of opening the query in datasheet view, use a form
that you open in dialog mode. This way, your code will
pause until the form is closed.

Do
Set rst = MyDB.OpenRecordset(MySQL, dbOpenForwardOnly)
If rst.RecordCount > 0 Then
DoCmd.OpenForm "form name", _
WindowMode:= acDialog
Else
DoCmd.OpenReport "rpt batch print preps", acPreview
Exit Do
End If
Loop Until <some escape condition>

 

[Karen]

okay, that works (thank you Marsh) ...but i don't like the way the form
displays. I wanted it to be a list of the records, what I get is a single
record and once I make the correction the next record is displayed. I have
it set to Continuous Forms.
Any ideas?

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

Instead of opening the query in datasheet view, use a form
that you open in dialog mode. This way, your code will
pause until the form is closed.

Do
Set rst = MyDB.OpenRecordset(MySQL, dbOpenForwardOnly)
If rst.RecordCount > 0 Then
DoCmd.OpenForm "form name", _
WindowMode:= acDialog
Else
DoCmd.OpenReport "rpt batch print preps", acPreview
Exit Do
End If
Loop Until <some escape condition>

 

[Marshall Barton]

Only one record in Continuous view???

The only thing I can think of is that either the form is not
tall enough (without Vertical scroll bar) to display more
records, or maybe the form's RecordSource query is only
returning one record?
--
Marsh
MVP [MS Access]

okay, that works (thank you Marsh) ...but i don't like the way the form
displays. I wanted it to be a list of the records, what I get is a single
record and once I make the correction the next record is displayed. I have
it set to Continuous Forms.

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

"Marshall Barton" wrote
Instead of opening the query in datasheet view, use a form
that you open in dialog mode. This way, your code will
pause until the form is closed.

Do
Set rst = MyDB.OpenRecordset(MySQL, dbOpenForwardOnly)
If rst.RecordCount > 0 Then
DoCmd.OpenForm "form name", _
WindowMode:= acDialog
Else
DoCmd.OpenReport "rpt batch print preps", acPreview
Exit Do
End If
Loop Until <some escape condition>

 

[George Nicholson]

Consider this: (note that the IsLoaded routine I've included may be
different that what you may be using as it allows you to specify objects
other than Forms).


Dim bolPrint as boolean
Dim bolCancelLoop as boolean
Dim iResponse as integer
Dim stDocName As String

............(the first section of your code)

Do Until bolCancelLoop = True
Set rst = MyDB.OpenRecordset(MySQL, dbOpenForwardOnly)

If Not rst.EOF Then
iResponse = MsgBox ("You have errors. Do you wish to correct
them?", vbYesNo)
If iResponse = vbYes then
docmd.openquery ("qry multi print preps")
' Make sure the query has a chance to open
Do Until IsLoaded("qry multi print preps", acQuery)
DoEvents
Loop
'Now, wait until the user closes it
Do Until IsLoaded("qry multi print preps", acQuery) = False
DoEvents
Loop
' Return to top of loop and check if errors still exist
Else
' User does not want to see/correct existing errors. Exit,
without printing.
bolCancelLoop = True
End If
Else
' We are at .EOF. Exit Loop, OK to print
bolPrint = True
bolCancelLoop = True
End If
Loop

If bolPrint = True then
stDocName = "rpt batch print preps"
DoCmd.OpenReport stDocName, acPreview
End If

**********************

Public Function IsLoaded(strObjName As String, Optional lngObjType As
acObjecttype = acForm) As Boolean
' Returns True if strName is Open (non-zero), False(0) otherwise.
' Should return 0, not an error, if the object doesn't exist
' Default Object is Form
On Error Resume Next
IsLoaded = (SysCmd(acSysCmdGetObjectState, lngObjType, strObjName) <> 0)
End Function

HTH,
--
George Nicholson

Remove 'Junk' from return address.

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