OpenForm Action Cancelled

  • Thread starter Thread starter Jan Il
  • Start date Start date
J

Jan Il

Hi all - Access 2002 XP, W2K SP4

I have a form for which I have a unbound combo box with a command button
with the following code, that opens a Record form with the repective data
selected in the combo box.

Private Sub cmdCardAcctNum_Click()
On Error GoTo Err_cmdCardAcctNum_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCreditCardReg"

stLinkCriteria = "[AcctNo]=" & "'" & Me![cmbCardAcctNum] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdCardAcctNum_Click:
Exit Sub

Err_cmdCardAcctNum_Click:
MsgBox Err.Description
Resume Exit_cmdCardAcctNum_Click

End Sub

When I make a selection from the combo box and then click the command
button, I am getting an error message that says: "OpenForm action was
cancelled". No reason why, or any other information in the error message.

This happens for all 6 of the command buttons on the form. One of the
command buttons just opens the Record form with all the records, so there's
no combo box or other action control that is used in conjunction with it. I
have used this process many times before, and never gotten any error
message. I have checked all the control and form names and they are correct
as written as far as I can see.

I have tried to look this error up and so far have not found any explanation
for it. I would truly appreciate it if someone could explain what this type
of message means so that I will have a better idea of how to troubleshoot
the problem.

Jan :)
Smiles are meant to be shared,
that's why they're so contagious.
 
Jan Il said:
Hi all - Access 2002 XP, W2K SP4

I have a form for which I have a unbound combo box with a command
button with the following code, that opens a Record form with the
repective data selected in the combo box.

Private Sub cmdCardAcctNum_Click()
On Error GoTo Err_cmdCardAcctNum_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCreditCardReg"

stLinkCriteria = "[AcctNo]=" & "'" & Me![cmbCardAcctNum] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdCardAcctNum_Click:
Exit Sub

Err_cmdCardAcctNum_Click:
MsgBox Err.Description
Resume Exit_cmdCardAcctNum_Click

End Sub

When I make a selection from the combo box and then click the command
button, I am getting an error message that says: "OpenForm action was
cancelled". No reason why, or any other information in the error
message.

This happens for all 6 of the command buttons on the form. One of the
command buttons just opens the Record form with all the records, so
there's no combo box or other action control that is used in
conjunction with it. I have used this process many times before, and
never gotten any error message. I have checked all the control and
form names and they are correct as written as far as I can see.

I have tried to look this error up and so far have not found any
explanation for it. I would truly appreciate it if someone could
explain what this type of message means so that I will have a better
idea of how to troubleshoot the problem.
Click to expand...

Hi, Jan!

I've seen this happen when some error occurs in opening the form.
Normally I'd ask first about the validity of your criteria string -- for
example, if there's no field named "AcctNo" in the form's recordsource,
or if it's a number field and not text (since you're wrapping it with
quotes). But since you tell me that you get the same error when you
click a button that just opens the form with no criteria, that seems to
rule out the "bad criteria" option.

First question: can you open the form itself without error by
double-clicking on it in the database window?

Second question: if you can't, can you open the form's recordsource
(query or table) by double-clicking on it in the database window?
 
Hi Dirk! :-)
Jan Il said:
Hi all - Access 2002 XP, W2K SP4

I have a form for which I have a unbound combo box with a command
button with the following code, that opens a Record form with the
respective data selected in the combo box.

Private Sub cmdCardAcctNum_Click()
On Error GoTo Err_cmdCardAcctNum_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCreditCardReg"

stLinkCriteria = "[AcctNo]=" & "'" & Me![cmbCardAcctNum] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdCardAcctNum_Click:
Exit Sub

Err_cmdCardAcctNum_Click:
MsgBox Err.Description
Resume Exit_cmdCardAcctNum_Click

End Sub

When I make a selection from the combo box and then click the command
button, I am getting an error message that says: "OpenForm action was
cancelled". No reason why, or any other information in the error
message.

This happens for all 6 of the command buttons on the form. One of the
command buttons just opens the Record form with all the records, so
there's no combo box or other action control that is used in
conjunction with it. I have used this process many times before, and
never gotten any error message. I have checked all the control and
form names and they are correct as written as far as I can see.

I have tried to look this error up and so far have not found any
explanation for it. I would truly appreciate it if someone could
explain what this type of message means so that I will have a better
idea of how to troubleshoot the problem.
Click to expand...

Hi, Jan!

I've seen this happen when some error occurs in opening the form.
Normally I'd ask first about the validity of your criteria string -- for
example, if there's no field named "AcctNo" in the form's recordsource,
or if it's a number field and not text (since you're wrapping it with
quotes). But since you tell me that you get the same error when you
click a button that just opens the form with no criteria, that seems to
rule out the "bad criteria" option.

First question: can you open the form itself without error by
double-clicking on it in the database window?
Click to expand...

Yes, I an open the form itself by double clicking without any error. Also,
there is a field named AcctNo and it is a number field.
Second question: if you can't, can you open the form's recordsource
(query or table) by double-clicking on it in the database window?
Click to expand...

I can do this as well....no errors

It seems to be related to just the filter form. The code for 5 of the
command buttons is basically the same as what I posted here, with just the
change in criteria. The sixth one, that just brings up the form with all
data, has this code:

Private Sub CmdCreditCardReg_Click()
On Error GoTo Err_CmdCreditCardReg_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCreditCardReg"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CmdCreditCardReg_Click:
Exit Sub

Err_CmdCreditCardReg_Click:
MsgBox Err.Description
Resume Exit_CmdCreditCardReg_Click

End Sub

As you can see, it does not call for any specific criteria.

Here is the query SQL for the Record form:

SELECT MyCreditCardRegister.CreditCardID, MyCreditCardRegister.CreditCardNo,
MyCreditCardRegister.TransactionDate, MyCreditCardRegister.CardCompany,
MyCreditCardRegister.PurchasedFrom, MyCreditCardRegister.PurchaseAmt,
MyCreditCardRegister.PaymentAmt, MyCreditCardRegister.TransactionType,
MyCreditCardRegister.Comment, MyCreditCardRegister.TransactionSign,
MyCreditCardRegister.AcctNo, MyCreditCardRegister.BeginBal,
[Forms]![frmCreditCardRecFilter]![TxtDate1] AS Expr1,
[Forms]![frmCreditCardRecFilter]![TxtDate2] AS Expr2
FROM MyCreditCardRegister
WHERE (((MyCreditCardRegister.TransactionDate) Between
[Forms]![frmCreditCardRecFilter]![TxtDate1] And
[Forms]![frmCreditCardRecFilter]![TxtDate2])) OR
((([Forms]![frmCreditCardRecFilter]![TxtDate1]) Is Null)) OR
((([Forms]![frmCreditCardRecFilter]![TxtDate2]) Is Null))
ORDER BY MyCreditCardRegister.TransactionDate DESC;

Thank you for your time and help, I really appreicate it. :-)

Jan :)
Smiles are meant to be shared,
that's why they're so contagious.
 
Jan Il said:
[DG wrote]
First question: can you open the form itself without error by
double-clicking on it in the database window?
Click to expand...

Yes, I an open the form itself by double clicking without any error.
Also, there is a field named AcctNo and it is a number field.
Second question: if you can't, can you open the form's recordsource
(query or table) by double-clicking on it in the database window?
Click to expand...

I can do this as well....no errors

It seems to be related to just the filter form. The code for 5 of the
command buttons is basically the same as what I posted here, with
just the change in criteria. The sixth one, that just brings up the
form with all data, has this code:

Private Sub CmdCreditCardReg_Click()
On Error GoTo Err_CmdCreditCardReg_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCreditCardReg"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CmdCreditCardReg_Click:
Exit Sub

Err_CmdCreditCardReg_Click:
MsgBox Err.Description
Resume Exit_CmdCreditCardReg_Click

End Sub

As you can see, it does not call for any specific criteria.

Here is the query SQL for the Record form:

SELECT MyCreditCardRegister.CreditCardID,
MyCreditCardRegister.CreditCardNo,
MyCreditCardRegister.TransactionDate,
MyCreditCardRegister.CardCompany, MyCreditCardRegister.PurchasedFrom,
MyCreditCardRegister.PurchaseAmt, MyCreditCardRegister.PaymentAmt,
MyCreditCardRegister.TransactionType, MyCreditCardRegister.Comment,
MyCreditCardRegister.TransactionSign, MyCreditCardRegister.AcctNo,
MyCreditCardRegister.BeginBal,
[Forms]![frmCreditCardRecFilter]![TxtDate1] AS Expr1,
[Forms]![frmCreditCardRecFilter]![TxtDate2] AS Expr2
FROM MyCreditCardRegister
WHERE (((MyCreditCardRegister.TransactionDate) Between
[Forms]![frmCreditCardRecFilter]![TxtDate1] And
[Forms]![frmCreditCardRecFilter]![TxtDate2])) OR
((([Forms]![frmCreditCardRecFilter]![TxtDate1]) Is Null)) OR
((([Forms]![frmCreditCardRecFilter]![TxtDate2]) Is Null))
ORDER BY MyCreditCardRegister.TransactionDate DESC;
Click to expand...

Hmm, there's nothing there that looks terribly wrong to me, though I
don't think the query needs to include these two lines:
[Forms]![frmCreditCardRecFilter]![TxtDate1] AS Expr1,
[Forms]![frmCreditCardRecFilter]![TxtDate2] AS Expr2
Click to expand...

I'd guess they're the result of applying criteria to those fields in the
query designer and not unchecking the "Show" box. I don't see how
having them included in the query is doing you any harm.

Is "frmCreditCardRecFilter" the name of the form that has these buttons
on it to open "frmCreditCardReg"?

Is there any code behind "frmCreditCardReg"? Especially, is there any
code in the Open event?
 
Hi Dirk,

In line....
[DG wrote]
First question: can you open the form itself without error by
double-clicking on it in the database window?
Click to expand...

Yes, I an open the form itself by double clicking without any error.
Also, there is a field named AcctNo and it is a number field.
Second question: if you can't, can you open the form's recordsource
(query or table) by double-clicking on it in the database window?
Click to expand...

I can do this as well....no errors

It seems to be related to just the filter form. The code for 5 of the
command buttons is basically the same as what I posted here, with
just the change in criteria. The sixth one, that just brings up the
form with all data, has this code:

Private Sub CmdCreditCardReg_Click()
On Error GoTo Err_CmdCreditCardReg_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCreditCardReg"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CmdCreditCardReg_Click:
Exit Sub

Err_CmdCreditCardReg_Click:
MsgBox Err.Description
Resume Exit_CmdCreditCardReg_Click

End Sub

As you can see, it does not call for any specific criteria.

Here is the query SQL for the Record form:

SELECT MyCreditCardRegister.CreditCardID,
MyCreditCardRegister.CreditCardNo,
MyCreditCardRegister.TransactionDate,
MyCreditCardRegister.CardCompany, MyCreditCardRegister.PurchasedFrom,
MyCreditCardRegister.PurchaseAmt, MyCreditCardRegister.PaymentAmt,
MyCreditCardRegister.TransactionType, MyCreditCardRegister.Comment,
MyCreditCardRegister.TransactionSign, MyCreditCardRegister.AcctNo,
MyCreditCardRegister.BeginBal,
[Forms]![frmCreditCardRecFilter]![TxtDate1] AS Expr1,
[Forms]![frmCreditCardRecFilter]![TxtDate2] AS Expr2
FROM MyCreditCardRegister
WHERE (((MyCreditCardRegister.TransactionDate) Between
[Forms]![frmCreditCardRecFilter]![TxtDate1] And
[Forms]![frmCreditCardRecFilter]![TxtDate2])) OR
((([Forms]![frmCreditCardRecFilter]![TxtDate1]) Is Null)) OR
((([Forms]![frmCreditCardRecFilter]![TxtDate2]) Is Null))
ORDER BY MyCreditCardRegister.TransactionDate DESC;
Click to expand...

Hmm, there's nothing there that looks terribly wrong to me, though I
don't think the query needs to include these two lines:
[Forms]![frmCreditCardRecFilter]![TxtDate1] AS Expr1,
[Forms]![frmCreditCardRecFilter]![TxtDate2] AS Expr2
Click to expand...

I'd guess they're the result of applying criteria to those fields in the
query designer and not unchecking the "Show" box. I don't see how
having them included in the query is doing you any harm.

Is "frmCreditCardRecFilter" the name of the form that has these buttons
on it to open "frmCreditCardReg"?
Click to expand...

Yes...."frmCreditCardReg" is the Record form.
Is there any code behind "frmCreditCardReg"? Especially, is there any
code in the Open event?
Click to expand...

No...nothing. That is what is so strange, there is nothing that should not
work, but, 'something' isn't. There is nothing really out of the ordinary
in the Filter form.

Jan :)
 
Jan Il said:
Yes...."frmCreditCardReg" is the Record form.

No...nothing. That is what is so strange, there is nothing that
should not work, but, 'something' isn't. There is nothing really out
of the ordinary in the Filter form.
Click to expand...

Hmm. Would you care to send me a copy of this database to look at?
You'll want to delete the data from the copy, of course, and then
compact and zip it before sending. You know where to find me.
 
Hi Dirk,
Hmm. Would you care to send me a copy of this database to look at?
You'll want to delete the data from the copy, of course, and then
compact and zip it before sending. You know where to find me.
Click to expand...

Aye Cap'n...I'll crop it down per usual and send it on it's way to the
chartroom. :-)Thank you.. :-)

Jan :)
 
Aye Cap'n...I'll crop it down per usual and send it on it's way to the
chartroom. :-)
Click to expand...

Okay, Jan, I've had a look at it, and you're quite right, the answer was
a bit obscure. The key observation for me was that the register form's
recordsource query, qryCreditCardReg, works fine only when the filter
form is not open. Then, naturally, you get parameter prompts for the
values of Forms]![frmCreditCardRecFilter]![TxtDate1] and
[Forms]![frmCreditCardRecFilter]![TxtDate2], but if you put in dates or
leave them blank, the query proceeds to open with no problem. However,
if frmCreditCardRecFilter is *open* when you open the query, you get
this error message:

<quote>
This expression is typed incorrectly, or it is too complex to be
evaluated. For example, a numeric expression may contain too many
complicated elements. Try simplifying the expression by assigning parts
of the expression to variables.
</quote>

Well! That's not very helpful, but it told me that the problem was in
the query, not in the form itself. If there's an error in evaluating a
form's recordsource query, you'll get an error indicating that the
OpenForm action was cancelled.

The fact that you have an error when the filter form is open and not
when it isn't led me to suspect that the problem was with the evaluation
of the parameters -- the text box values being picked up from the form.
If Access guesses wrong at the data type of the parameters, it can lead
to an error in processing the query. So I opened qryCreditCardReg in
SQL View and explicitly identified the parameters as to data type,
changing the query's SQL like so:

------------ start of revised SQL -------------
PARAMETERS [Forms]![frmCreditCardRecFilter]![TxtDate1] DateTime,
[Forms]![frmCreditCardRecFilter]![TxtDate2] DateTime;
SELECT MyCreditCardRegister.CreditCardID,
MyCreditCardRegister.CreditCardNo, MyCreditCardRegister.TransactionDate,
MyCreditCardRegister.CardCompany, MyCreditCardRegister.PurchasedFrom,
MyCreditCardRegister.PurchaseAmt, MyCreditCardRegister.PaymentAmt,
MyCreditCardRegister.TransactionType, MyCreditCardRegister.Comment,
MyCreditCardRegister.TransactionSign, MyCreditCardRegister.AcctNo,
MyCreditCardRegister.BeginBal
FROM MyCreditCardRegister
WHERE (((MyCreditCardRegister.TransactionDate) Between
[Forms]![frmCreditCardRecFilter]![TxtDate1] And
[Forms]![frmCreditCardRecFilter]![TxtDate2])) OR
((([Forms]![frmCreditCardRecFilter]![TxtDate1]) Is Null)) OR
((([Forms]![frmCreditCardRecFilter]![TxtDate2]) Is Null))
ORDER BY MyCreditCardRegister.TransactionDate DESC;

------------ end of revised SQL -------------

After I saved the query with that SQL, the query and form worked
correctly.
 
Hi Dirk :-)
Aye Cap'n...I'll crop it down per usual and send it on it's way to the
chartroom. :-)
Click to expand...

Okay, Jan, I've had a look at it, and you're quite right, the answer was
a bit obscure. The key observation for me was that the register form's
recordsource query, qryCreditCardReg, works fine only when the filter
form is not open. Then, naturally, you get parameter prompts for the
values of Forms]![frmCreditCardRecFilter]![TxtDate1] and
[Forms]![frmCreditCardRecFilter]![TxtDate2], but if you put in dates or
leave them blank, the query proceeds to open with no problem. However,
if frmCreditCardRecFilter is *open* when you open the query, you get
this error message:

<quote>
This expression is typed incorrectly, or it is too complex to be
evaluated. For example, a numeric expression may contain too many
complicated elements. Try simplifying the expression by assigning parts
of the expression to variables.
</quote>

Well! That's not very helpful, but it told me that the problem was in
the query, not in the form itself. If there's an error in evaluating a
form's recordsource query, you'll get an error indicating that the
OpenForm action was cancelled.

The fact that you have an error when the filter form is open and not
when it isn't led me to suspect that the problem was with the evaluation
of the parameters -- the text box values being picked up from the form.
If Access guesses wrong at the data type of the parameters, it can lead
to an error in processing the query. So I opened qryCreditCardReg in
SQL View and explicitly identified the parameters as to data type,
changing the query's SQL like so:

------------ start of revised SQL -------------
PARAMETERS [Forms]![frmCreditCardRecFilter]![TxtDate1] DateTime,
[Forms]![frmCreditCardRecFilter]![TxtDate2] DateTime;
SELECT MyCreditCardRegister.CreditCardID,
MyCreditCardRegister.CreditCardNo, MyCreditCardRegister.TransactionDate,
MyCreditCardRegister.CardCompany, MyCreditCardRegister.PurchasedFrom,
MyCreditCardRegister.PurchaseAmt, MyCreditCardRegister.PaymentAmt,
MyCreditCardRegister.TransactionType, MyCreditCardRegister.Comment,
MyCreditCardRegister.TransactionSign, MyCreditCardRegister.AcctNo,
MyCreditCardRegister.BeginBal
FROM MyCreditCardRegister
WHERE (((MyCreditCardRegister.TransactionDate) Between
[Forms]![frmCreditCardRecFilter]![TxtDate1] And
[Forms]![frmCreditCardRecFilter]![TxtDate2])) OR
((([Forms]![frmCreditCardRecFilter]![TxtDate1]) Is Null)) OR
((([Forms]![frmCreditCardRecFilter]![TxtDate2]) Is Null))
ORDER BY MyCreditCardRegister.TransactionDate DESC;

------------ end of revised SQL -------------

After I saved the query with that SQL, the query and form worked
correctly.
Click to expand...

Well... I copy/pasted the revised SQL into the query for the Record Form,
and then tried to open the forms. I tried all the command buttons one by
one.......and even tried them using the date period selections and
.....well.....the results at this point is...




....they all worked perfectly!! :-))

Although, I am not sure I understand the problem behind the parameters with
this particular form, or query.

Thank you very much for your time and assistance, I really do appreciate it.
:-)

Jan :)
Smiles are meant to be shared,
that's why they're so contagious.
 
Jan Il said:
Well... I copy/pasted the revised SQL into the query for the Record
Form, and then tried to open the forms. I tried all the command
buttons one by one.......and even tried them using the date period
selections and ....well.....the results at this point is...




...they all worked perfectly!! :-))
Click to expand...

You are such a tease, Jan!
Although, I am not sure I understand the problem behind the
parameters with this particular form, or query.
Click to expand...

I don't really understand the innermost workings myself. But think of
it this way. As far as Access is concerned, those references in your
query criteria to the form's text boxes are parameters, for which the
values have to be filled in when the query is processed. In order to
compare the parameter values with the values in the table, Access has to
know, or guess, what type of data is represented by each parameter --
are they text, or numbers, or dates, or what?

If the text boxes on your filter form were bound to fields, Access would
know from the type of field. But since they're unbound, Access has to
guess. Usually Access is pretty good at guessing this, especially if
you have the control's Format property set -- as you do -- to one of the
date formats. This time, though, it appears that the guess Access made
was wrong. So I included the PARAMETERS clause in the SQL statement to
explicitly declare the parameters and tell it what data type they are.

Incidentally, you can also define parameters in the query design view,
by clicking Query -> Parameters... and entering them there. That
results in SQL that looks the same as what I posted.
 
Hi Dirk,
"> >
You are such a tease, Jan!
Click to expand...

Moi..? ? ?

;o)
I don't really understand the innermost workings myself. But think of
it this way. As far as Access is concerned, those references in your
query criteria to the form's text boxes are parameters, for which the
values have to be filled in when the query is processed. In order to
compare the parameter values with the values in the table, Access has to
know, or guess, what type of data is represented by each parameter --
are they text, or numbers, or dates, or what?

If the text boxes on your filter form were bound to fields, Access would
know from the type of field. But since they're unbound, Access has to
guess. Usually Access is pretty good at guessing this, especially if
you have the control's Format property set -- as you do -- to one of the
date formats. This time, though, it appears that the guess Access made
was wrong. So I included the PARAMETERS clause in the SQL statement to
explicitly declare the parameters and tell it what data type they are.
Click to expand...

I see. Well...that is what really confused me. As you know, I have used
these types of forms many times before, and the format and information is
usually pretty straight forward. I tried all the normal troubleshooting and
all seemed in proper order, so nothing was making much sense. Obviously,
this time Access needed a bit more clarity in what I wanted. I thought it
know me well enough by now. ;-))
Incidentally, you can also define parameters in the query design view,
by clicking Query -> Parameters... and entering them there. That
results in SQL that looks the same as what I posted.
Click to expand...

Yes....I see that. I have only used that once or twice before, so far, so
I'll have to make a note on that to keep at hand for future reference. So
much to do......so little mind left.... <g>

Thank you for all your help and explaining what the problem involved. I'll
have to make sure and be more aware in that area when setting parameters.
:-)

Jan :)
 
Back
Top