Batch Complete Action

[richard]

Hi

I have a form [Sample Results] with a command button which runs a report
"Certificate Details", currently there is a question posed before the report
is run "have you checked the batch is complete", if the user clicks 'yes'
then the report is run, if 'no' then the user is returned to the form.
What I now need is for the report to run with no prompt if the batch is
complete(batch is complete when all records have a value in three fields), if
the batch is not complete then the user is prompted with 'the batch is not
complete do you wish to continue' (YES/NO)
I have tried to run a query that selects the appropriate records and then to
try an if statement in the VBA of the click event of the button, but kept
getting an error message saying 'object required'.
Then turned select query to an append query and put the selected data into a
table and then tried the if then else statement on the table but still
getting the same error message.
Below is the code from the command button on the form. The code may be a
mess as it is a WIP, but any help with direction or with the problems with
the code would be wonderful

Private Sub PrintCert_Click()
On Error GoTo Err_PrintCert_Click

DoCmd.OpenQuery "qrybatchcompletetestforcert", acViewNormal, acReadOnly
DoCmd.OpenTable "tbltestforcert", acViewNormal
DoCmd.Close acQuery, "qrybatchcompletetestforcert"
If Table!tbltestforcert!Expr1 = 1 Then
RetValue = MsgBox("Are you aware the batch is not yet complete?", vbYesNo)
If RetValue = 6 Then
DoCmd.OpenReport "Certificate Details", acPreview
Else
PrintCert.SetFocus
End If
Else
DoCmd.OpenReport "Certificate Details", acViewPreview
End If
Exit_PrintCert_Click:
Exit Sub

Err_PrintCert_Click:
MsgBox Err.Description
Resume Exit_PrintCert_Click

End Sub

 

[Douglas J. Steele]

What is your code supposed to accomplish? If qrybatchcompletetestforcert is
an Action query (SELECT INTO, UPDATE, DELETE, etc.), you're better off
executing it, rather than using RunQuery:

CurrentDb.QueryDefs("qrybatchcompletetestforcert").Execute, dbFailOnError

Why, though, are you opening the table? If you're trying to read a value
from the table, you're better off using DLookup (or opening a recordset):

If DLookup("Expr1", "tbltestforcert") = 1 Then

That assumes, of course, that there's only a single row tbltestforcert

 

[richard]

Thanks for the prompt response.
The code is supposed to do the following

1 Open query (this is now an append query as detailed previously)
2 Open Table to view the data now stored due to the append query
3 then depending on the value returned from the field [expr1](this field is
created by the query) either run the report or ask the user a question

The reason for opening the table was that I believed the field could not be
seen by the code unless the table was open, same for the query.
The purpose of the query was to determine whether or not the batch was
complete. The query will only return records if the batch is incomplete, it
will return nothing if the batch is complete.
The table could have more than one record as there may be more than one
record within a batch that is defined as not complete.

I am not sure this is the right way to go about what I am tyrying to achieve
but half the fun is in the trying.(sometimes)

Any more thoughts following this post please advise

Thanks

Richard

What is your code supposed to accomplish? If qrybatchcompletetestforcert is
an Action query (SELECT INTO, UPDATE, DELETE, etc.), you're better off
executing it, rather than using RunQuery:

CurrentDb.QueryDefs("qrybatchcompletetestforcert").Execute, dbFailOnError

Why, though, are you opening the table? If you're trying to read a value
from the table, you're better off using DLookup (or opening a recordset):

If DLookup("Expr1", "tbltestforcert") = 1 Then

That assumes, of course, that there's only a single row tbltestforcert


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi

I have a form [Sample Results] with a command button which runs a report
"Certificate Details", currently there is a question posed before the
report
is run "have you checked the batch is complete", if the user clicks 'yes'
then the report is run, if 'no' then the user is returned to the form.
What I now need is for the report to run with no prompt if the batch is
complete(batch is complete when all records have a value in three fields),
if
the batch is not complete then the user is prompted with 'the batch is not
complete do you wish to continue' (YES/NO)
I have tried to run a query that selects the appropriate records and then
to
try an if statement in the VBA of the click event of the button, but kept
getting an error message saying 'object required'.
Then turned select query to an append query and put the selected data into
a
table and then tried the if then else statement on the table but still
getting the same error message.
Below is the code from the command button on the form. The code may be a
mess as it is a WIP, but any help with direction or with the problems with
the code would be wonderful

Private Sub PrintCert_Click()
On Error GoTo Err_PrintCert_Click

DoCmd.OpenQuery "qrybatchcompletetestforcert", acViewNormal, acReadOnly
DoCmd.OpenTable "tbltestforcert", acViewNormal
DoCmd.Close acQuery, "qrybatchcompletetestforcert"
If Table!tbltestforcert!Expr1 = 1 Then
RetValue = MsgBox("Are you aware the batch is not yet complete?", vbYesNo)
If RetValue = 6 Then
DoCmd.OpenReport "Certificate Details", acPreview
Else
PrintCert.SetFocus
End If
Else
DoCmd.OpenReport "Certificate Details", acViewPreview
End If
Exit_PrintCert_Click:
Exit Sub

Err_PrintCert_Click:
MsgBox Err.Description
Resume Exit_PrintCert_Click

End Sub

 

[Douglas J. Steele]

Have you tried the changes I suggested?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the prompt response.
The code is supposed to do the following

1 Open query (this is now an append query as detailed previously)
2 Open Table to view the data now stored due to the append query
3 then depending on the value returned from the field [expr1](this field
is
created by the query) either run the report or ask the user a question

The reason for opening the table was that I believed the field could not
be
seen by the code unless the table was open, same for the query.
The purpose of the query was to determine whether or not the batch was
complete. The query will only return records if the batch is incomplete,
it
will return nothing if the batch is complete.
The table could have more than one record as there may be more than one
record within a batch that is defined as not complete.

I am not sure this is the right way to go about what I am tyrying to
achieve
but half the fun is in the trying.(sometimes)

Any more thoughts following this post please advise

Thanks

Richard

What is your code supposed to accomplish? If qrybatchcompletetestforcert
is
an Action query (SELECT INTO, UPDATE, DELETE, etc.), you're better off
executing it, rather than using RunQuery:

CurrentDb.QueryDefs("qrybatchcompletetestforcert").Execute,
dbFailOnError

Why, though, are you opening the table? If you're trying to read a value
from the table, you're better off using DLookup (or opening a recordset):

If DLookup("Expr1", "tbltestforcert") = 1 Then

That assumes, of course, that there's only a single row tbltestforcert


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi

I have a form [Sample Results] with a command button which runs a
report
"Certificate Details", currently there is a question posed before the
report
is run "have you checked the batch is complete", if the user clicks
'yes'
then the report is run, if 'no' then the user is returned to the form.
What I now need is for the report to run with no prompt if the batch is
complete(batch is complete when all records have a value in three
fields),
if
the batch is not complete then the user is prompted with 'the batch is
not
complete do you wish to continue' (YES/NO)
I have tried to run a query that selects the appropriate records and
then
to
try an if statement in the VBA of the click event of the button, but
kept
getting an error message saying 'object required'.
Then turned select query to an append query and put the selected data
into
a
table and then tried the if then else statement on the table but still
getting the same error message.
Below is the code from the command button on the form. The code may be
a
mess as it is a WIP, but any help with direction or with the problems
with
the code would be wonderful

Private Sub PrintCert_Click()
On Error GoTo Err_PrintCert_Click

DoCmd.OpenQuery "qrybatchcompletetestforcert", acViewNormal, acReadOnly
DoCmd.OpenTable "tbltestforcert", acViewNormal
DoCmd.Close acQuery, "qrybatchcompletetestforcert"
If Table!tbltestforcert!Expr1 = 1 Then
RetValue = MsgBox("Are you aware the batch is not yet complete?",
vbYesNo)
If RetValue = 6 Then
DoCmd.OpenReport "Certificate Details", acPreview
Else
PrintCert.SetFocus
End If
Else
DoCmd.OpenReport "Certificate Details", acViewPreview
End If
Exit_PrintCert_Click:
Exit Sub

Err_PrintCert_Click:
MsgBox Err.Description
Resume Exit_PrintCert_Click

End Sub

 

[richard]

Ok

Here goes

Problem with the execute code, I get a 'Compile Error: Wrong number of
arguments or invalid property assignment. When I click on OK the .execute of
the code is highlighted in blue.
I have then tried the following statement which worked as I obviously just
opened and closed the 'append' query 'batchcompletetestforcert'

DoCmd.OpenQuery ("qrybatchcompletetestforcert"), acViewNormal
DoCmd.Close acQuery, "qrybatchcompletetestforcert"
If DLookup("Expr1", "tbltestforcert") = 1 Then

RetValue = MsgBox("Are you aware the batch is not yet complete?", vbYesNo)
If RetValue = 6 Then
DoCmd.OpenReport "Certificate Details", acPreview
Else
PrintCert.SetFocus
End If
Else
DoCmd.OpenReport "Certificate Details", acViewPreview
End If
Exit_PrintCert_Click:
Exit Sub

Err_PrintCert_Click:
MsgBox Err.Description
Resume Exit_PrintCert_Click

End Sub


The code for the table appears to work whether one record or more is
appended into the table.

Have you tried the changes I suggested?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the prompt response.
The code is supposed to do the following

1 Open query (this is now an append query as detailed previously)
2 Open Table to view the data now stored due to the append query
3 then depending on the value returned from the field [expr1](this field
is
created by the query) either run the report or ask the user a question

The reason for opening the table was that I believed the field could not
be
seen by the code unless the table was open, same for the query.
The purpose of the query was to determine whether or not the batch was
complete. The query will only return records if the batch is incomplete,
it
will return nothing if the batch is complete.
The table could have more than one record as there may be more than one
record within a batch that is defined as not complete.

I am not sure this is the right way to go about what I am tyrying to
achieve
but half the fun is in the trying.(sometimes)

Any more thoughts following this post please advise

Thanks

Richard

What is your code supposed to accomplish? If qrybatchcompletetestforcert
is
an Action query (SELECT INTO, UPDATE, DELETE, etc.), you're better off
executing it, rather than using RunQuery:

CurrentDb.QueryDefs("qrybatchcompletetestforcert").Execute,
dbFailOnError

Why, though, are you opening the table? If you're trying to read a value
from the table, you're better off using DLookup (or opening a recordset):

If DLookup("Expr1", "tbltestforcert") = 1 Then

That assumes, of course, that there's only a single row tbltestforcert


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi

I have a form [Sample Results] with a command button which runs a
report
"Certificate Details", currently there is a question posed before the
report
is run "have you checked the batch is complete", if the user clicks
'yes'
then the report is run, if 'no' then the user is returned to the form.
What I now need is for the report to run with no prompt if the batch is
complete(batch is complete when all records have a value in three
fields),
if
the batch is not complete then the user is prompted with 'the batch is
not
complete do you wish to continue' (YES/NO)
I have tried to run a query that selects the appropriate records and
then
to
try an if statement in the VBA of the click event of the button, but
kept
getting an error message saying 'object required'.
Then turned select query to an append query and put the selected data
into
a
table and then tried the if then else statement on the table but still
getting the same error message.
Below is the code from the command button on the form. The code may be
a
mess as it is a WIP, but any help with direction or with the problems
with
the code would be wonderful

Private Sub PrintCert_Click()
On Error GoTo Err_PrintCert_Click

DoCmd.OpenQuery "qrybatchcompletetestforcert", acViewNormal, acReadOnly
DoCmd.OpenTable "tbltestforcert", acViewNormal
DoCmd.Close acQuery, "qrybatchcompletetestforcert"
If Table!tbltestforcert!Expr1 = 1 Then
RetValue = MsgBox("Are you aware the batch is not yet complete?",
vbYesNo)
If RetValue = 6 Then
DoCmd.OpenReport "Certificate Details", acPreview
Else
PrintCert.SetFocus
End If
Else
DoCmd.OpenReport "Certificate Details", acViewPreview
End If
Exit_PrintCert_Click:
Exit Sub

Err_PrintCert_Click:
MsgBox Err.Description
Resume Exit_PrintCert_Click

End Sub

 

[Douglas J. Steele]

Which line of code raised the Compile error?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Ok

Here goes

Problem with the execute code, I get a 'Compile Error: Wrong number of
arguments or invalid property assignment. When I click on OK the .execute
of
the code is highlighted in blue.
I have then tried the following statement which worked as I obviously just
opened and closed the 'append' query 'batchcompletetestforcert'

DoCmd.OpenQuery ("qrybatchcompletetestforcert"), acViewNormal
DoCmd.Close acQuery, "qrybatchcompletetestforcert"
If DLookup("Expr1", "tbltestforcert") = 1 Then

RetValue = MsgBox("Are you aware the batch is not yet complete?", vbYesNo)
If RetValue = 6 Then
DoCmd.OpenReport "Certificate Details", acPreview
Else
PrintCert.SetFocus
End If
Else
DoCmd.OpenReport "Certificate Details", acViewPreview
End If
Exit_PrintCert_Click:
Exit Sub

Err_PrintCert_Click:
MsgBox Err.Description
Resume Exit_PrintCert_Click

End Sub


The code for the table appears to work whether one record or more is
appended into the table.

Have you tried the changes I suggested?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the prompt response.
The code is supposed to do the following

1 Open query (this is now an append query as detailed previously)
2 Open Table to view the data now stored due to the append query
3 then depending on the value returned from the field [expr1](this
field
is
created by the query) either run the report or ask the user a question

The reason for opening the table was that I believed the field could
not
be
seen by the code unless the table was open, same for the query.
The purpose of the query was to determine whether or not the batch was
complete. The query will only return records if the batch is
incomplete,
it
will return nothing if the batch is complete.
The table could have more than one record as there may be more than one
record within a batch that is defined as not complete.

I am not sure this is the right way to go about what I am tyrying to
achieve
but half the fun is in the trying.(sometimes)

Any more thoughts following this post please advise

Thanks

Richard
:

What is your code supposed to accomplish? If
qrybatchcompletetestforcert
is
an Action query (SELECT INTO, UPDATE, DELETE, etc.), you're better off
executing it, rather than using RunQuery:

CurrentDb.QueryDefs("qrybatchcompletetestforcert").Execute,
dbFailOnError

Why, though, are you opening the table? If you're trying to read a
value
from the table, you're better off using DLookup (or opening a
recordset):

If DLookup("Expr1", "tbltestforcert") = 1 Then

That assumes, of course, that there's only a single row tbltestforcert


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi

I have a form [Sample Results] with a command button which runs a
report
"Certificate Details", currently there is a question posed before
the
report
is run "have you checked the batch is complete", if the user clicks
'yes'
then the report is run, if 'no' then the user is returned to the
form.
What I now need is for the report to run with no prompt if the batch
is
complete(batch is complete when all records have a value in three
fields),
if
the batch is not complete then the user is prompted with 'the batch
is
not
complete do you wish to continue' (YES/NO)
I have tried to run a query that selects the appropriate records and
then
to
try an if statement in the VBA of the click event of the button, but
kept
getting an error message saying 'object required'.
Then turned select query to an append query and put the selected
data
into
a
table and then tried the if then else statement on the table but
still
getting the same error message.
Below is the code from the command button on the form. The code may
be
a
mess as it is a WIP, but any help with direction or with the
problems
with
the code would be wonderful

Private Sub PrintCert_Click()
On Error GoTo Err_PrintCert_Click

DoCmd.OpenQuery "qrybatchcompletetestforcert", acViewNormal,
acReadOnly
DoCmd.OpenTable "tbltestforcert", acViewNormal
DoCmd.Close acQuery, "qrybatchcompletetestforcert"
If Table!tbltestforcert!Expr1 = 1 Then
RetValue = MsgBox("Are you aware the batch is not yet complete?",
vbYesNo)
If RetValue = 6 Then
DoCmd.OpenReport "Certificate Details", acPreview
Else
PrintCert.SetFocus
End If
Else
DoCmd.OpenReport "Certificate Details", acViewPreview
End If
Exit_PrintCert_Click:
Exit Sub

Err_PrintCert_Click:
MsgBox Err.Description
Resume Exit_PrintCert_Click

End Sub

 

[richard]

:
CurrentDb.QueryDefs("qrybatchcompletetestforcert").Execute,dbFailOnError

The de-bugger is highlighting the .execute in blue

Which line of code raised the Compile error?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Ok

Here goes

Problem with the execute code, I get a 'Compile Error: Wrong number of
arguments or invalid property assignment. When I click on OK the .execute
of
the code is highlighted in blue.
I have then tried the following statement which worked as I obviously just
opened and closed the 'append' query 'batchcompletetestforcert'

DoCmd.OpenQuery ("qrybatchcompletetestforcert"), acViewNormal
DoCmd.Close acQuery, "qrybatchcompletetestforcert"
If DLookup("Expr1", "tbltestforcert") = 1 Then

RetValue = MsgBox("Are you aware the batch is not yet complete?", vbYesNo)
If RetValue = 6 Then
DoCmd.OpenReport "Certificate Details", acPreview
Else
PrintCert.SetFocus
End If
Else
DoCmd.OpenReport "Certificate Details", acViewPreview
End If
Exit_PrintCert_Click:
Exit Sub

Err_PrintCert_Click:
MsgBox Err.Description
Resume Exit_PrintCert_Click

End Sub


The code for the table appears to work whether one record or more is
appended into the table.

Have you tried the changes I suggested?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks for the prompt response.
The code is supposed to do the following

1 Open query (this is now an append query as detailed previously)
2 Open Table to view the data now stored due to the append query
3 then depending on the value returned from the field [expr1](this
field
is
created by the query) either run the report or ask the user a question

The reason for opening the table was that I believed the field could
not
be
seen by the code unless the table was open, same for the query.
The purpose of the query was to determine whether or not the batch was
complete. The query will only return records if the batch is
incomplete,
it
will return nothing if the batch is complete.
The table could have more than one record as there may be more than one
record within a batch that is defined as not complete.

I am not sure this is the right way to go about what I am tyrying to
achieve
but half the fun is in the trying.(sometimes)

Any more thoughts following this post please advise

Thanks

Richard
:

What is your code supposed to accomplish? If
qrybatchcompletetestforcert
is
an Action query (SELECT INTO, UPDATE, DELETE, etc.), you're better off
executing it, rather than using RunQuery:

CurrentDb.QueryDefs("qrybatchcompletetestforcert").Execute,
dbFailOnError

Why, though, are you opening the table? If you're trying to read a
value
from the table, you're better off using DLookup (or opening a
recordset):

If DLookup("Expr1", "tbltestforcert") = 1 Then

That assumes, of course, that there's only a single row tbltestforcert


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi

I have a form [Sample Results] with a command button which runs a
report
"Certificate Details", currently there is a question posed before
the
report
is run "have you checked the batch is complete", if the user clicks
'yes'
then the report is run, if 'no' then the user is returned to the
form.
What I now need is for the report to run with no prompt if the batch
is
complete(batch is complete when all records have a value in three
fields),
if
the batch is not complete then the user is prompted with 'the batch
is
not
complete do you wish to continue' (YES/NO)
I have tried to run a query that selects the appropriate records and
then
to
try an if statement in the VBA of the click event of the button, but
kept
getting an error message saying 'object required'.
Then turned select query to an append query and put the selected
data
into
a
table and then tried the if then else statement on the table but
still
getting the same error message.
Below is the code from the command button on the form. The code may
be
a
mess as it is a WIP, but any help with direction or with the
problems
with
the code would be wonderful

Private Sub PrintCert_Click()
On Error GoTo Err_PrintCert_Click

DoCmd.OpenQuery "qrybatchcompletetestforcert", acViewNormal,
acReadOnly
DoCmd.OpenTable "tbltestforcert", acViewNormal
DoCmd.Close acQuery, "qrybatchcompletetestforcert"
If Table!tbltestforcert!Expr1 = 1 Then
RetValue = MsgBox("Are you aware the batch is not yet complete?",
vbYesNo)
If RetValue = 6 Then
DoCmd.OpenReport "Certificate Details", acPreview
Else
PrintCert.SetFocus
End If
Else
DoCmd.OpenReport "Certificate Details", acViewPreview
End If
Exit_PrintCert_Click:
Exit Sub

Err_PrintCert_Click:
MsgBox Err.Description
Resume Exit_PrintCert_Click

End Sub

 

[Douglas J. Steele]

Go into the VB Editor, and select Tools | References from the menu.

If the reference for Microsoft DAO 3.6 Object Library isn't one of the
checked references at the top of the list, scroll through the list until you
find it, and check it to select it.

 

[richard]

Microsoft DAO 3.6 Object Library is one of the checked references

 

[Douglas J. Steele]

Are any of the other checked references marked as MISSING:? If so, uncheck
them and compile your application (under the Debug menu) to see whether you
really needed those references. (If you did, reselect them from the list)