Puzzled by error message

[Joan]

Hi,

I have an update query that runs when the user clicks a button after they
have entered values (that records will be updated to) in several textboxes.
Below is the code behind the click event of the button. In my code, I try
to clear the textboxes after the query is ran in order for the user to add
different values and run another update query. But these lines seem to
trigger the following error messages and I can't figure out why.

"This expression is typed incorrectly or it is too compex 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"

Then when I click OK, another message appears from underneath the first:
"Object invalid or no longer set."

Could someone please explain why I am getting these messages?

Joan

My code:

Private Sub cmdEnter_Click()
On Error GoTo Err_cmdEnter_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "qrySendPUpdate"
DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
DoCmd.SetWarnings True
Me!txtInvoiceNum = " "
'Me!txtCertMail = ""
'Me!dteRegPapers = ""
MsgBox "Dog records on specified Invoice have been updated with the
Certified Mail number and date that were entered.", vbOKOnly

Exit_cmdEnter_Click:
Exit Sub

Err_cmdEnter_Click:
MsgBox Err.Description
Resume Exit_cmdEnter_Click

End Sub

 

[Dirk Goldgar]

Hi,

I have an update query that runs when the user clicks a button after
they have entered values (that records will be updated to) in
several textboxes. Below is the code behind the click event of the
button. In my code, I try to clear the textboxes after the query is
ran in order for the user to add different values and run another
update query. But these lines seem to trigger the following error
messages and I can't figure out why.

"This expression is typed incorrectly or it is too compex 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"

Then when I click OK, another message appears from underneath the
first: "Object invalid or no longer set."

Could someone please explain why I am getting these messages?

Joan

My code:

Private Sub cmdEnter_Click()
On Error GoTo Err_cmdEnter_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "qrySendPUpdate"
DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
DoCmd.SetWarnings True
Me!txtInvoiceNum = " "
'Me!txtCertMail = ""
'Me!dteRegPapers = ""
MsgBox "Dog records on specified Invoice have been updated with
the Certified Mail number and date that were entered.", vbOKOnly

Exit_cmdEnter_Click:
Exit Sub

Err_cmdEnter_Click:
MsgBox Err.Description
Resume Exit_cmdEnter_Click

End Sub

Joan -

I'm not sure, but I wonder if maybe you're clearing the text boxes,
which I assume consitute parameters to the query, before the query has
finished. I assume that the query works fine with the current code if
you comment out the lines that clear the text boxes? I'm just guessing
here, but it *may* be due to the way OpenQuery works. Try this
variation instead, which doesn't use OpenQuery:

'----- start of revised code -----
Private Sub cmdEnter_Click()
On Error GoTo Err_cmdEnter_Click

Dim stDocName As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter

stDocName = "qrySendPUpdate"

Set db = CurrentDb
Set qdf = db.QueryDefs(stDocName)

For Each prm in qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

qdf.Execute, dbFailOnError

Me!txtInvoiceNum = " "
'Me!txtCertMail = ""
'Me!dteRegPapers = ""
MsgBox "Dog records on specified Invoice have been " & _
"updated with the Certified Mail number and date " & _
"that were entered.", _
vbOKOnly

Exit_cmdEnter_Click:

Set qdf = Nothing
Set db = Nothing
Exit Sub

Err_cmdEnter_Click:
MsgBox Err.Description
Resume Exit_cmdEnter_Click

End Sub

'----- end of revised code -----

Let me know what happens. As I said, I'm just guessing as to the cause
of your problem.

 

[Joan]

Hi Dirk,
Thanks for replying to my post.
I tried the variation that you sent and ran my form but then I get a compile
error: "Wrong number of arguments or invalid property assignment." When I
debugg, the word "Execute" is highlighted. If I take out the dbFailOnError
part then I get the same messages as before.
What do you think?

Joan

 

[Dirk Goldgar]

Hi Dirk,
Thanks for replying to my post.
I tried the variation that you sent and ran my form but then I get a
compile error: "Wrong number of arguments or invalid property
assignment." When I debugg, the word "Execute" is highlighted. If I
take out the dbFailOnError part then I get the same messages as
before.
What do you think?

It should have been

qdf.Execute dbFailOnError

But if you wrote

qdf.Execute

and got the same message as before, I'm somewhat at a loss. Have you
tried stepping through the code in debug mode, to see exactly where the
error occurs? If not, please do it, then report the result.

 

[Joan]

Dirk,

I tried setting a breakon almost every line of code from the line prm.Value
= Eval(prm.Name) on. The parameter names and values change as they should.
The error message beginning "This expression is typed incorrectly or it is
too complex....." appears when the code breaks on the MsgBox line. However,
when I comment this line out, I still get the message only it appears when
the code breaks on the Set qdf = Nothing line. I also get the message:
"Object invalid or no longer set."

I set a watch on Eval(prm.Name), Me!txtInvoiceNum and dbFailOnError.
dbFailOnError's value is 128 all through the code. Me!txtInvoiceNum = ""
Changes from False to True after the Me!txtInvoiceNum = "" line and
Eval(prm.Name) 's value changes as it evaluates each textbox to be the name
of the textbox. After the code moves out of the For Next loop the value of
Eval(prm.Name) is <Object variable or With block variable not set>.

One other thing, I have a subform on my form, which when the user enters the
Invoice Number in the textbox, the Customer's Name, Address and Date of Sale
corresponding to that Invoice Number appears in the subform for the user to
see. When I run my form, press the update button, and the error message
appears, the textboxes on this subform have #Name? in them. I'm afraid that
I don't know what any of this means if anything. Maybe it gives you a clue
though.

Joan

 

[Dirk Goldgar]

Dirk,

I tried setting a breakon almost every line of code from the line
prm.Value = Eval(prm.Name) on. The parameter names and values change
as they should. The error message beginning "This expression is
typed incorrectly or it is too complex....." appears when the code
breaks on the MsgBox line. However, when I comment this line out, I
still get the message only it appears when the code breaks on the Set
qdf = Nothing line. I also get the message: "Object invalid or no
longer set."

I set a watch on Eval(prm.Name), Me!txtInvoiceNum and dbFailOnError.
dbFailOnError's value is 128 all through the code. Me!txtInvoiceNum =
"" Changes from False to True after the Me!txtInvoiceNum = "" line and
Eval(prm.Name) 's value changes as it evaluates each textbox to be
the name of the textbox. After the code moves out of the For Next
loop the value of Eval(prm.Name) is <Object variable or With block
variable not set>.

One other thing, I have a subform on my form, which when the user
enters the Invoice Number in the textbox, the Customer's Name,
Address and Date of Sale corresponding to that Invoice Number appears
in the subform for the user to see. When I run my form, press the
update button, and the error message appears, the textboxes on this
subform have #Name? in them. I'm afraid that I don't know what any
of this means if anything. Maybe it gives you a clue though.

It sounds to me as if the error may actually be happening as a result of
this line:

Me.txtInvoiceNum = " "

Here are some questions:

1. If you comment that line out, does the error still occur?

2. Is txtInvoiceNum a bound control?

3 As I understand it, txtInvoiceNum is the Link Master Field for your
subform. What type of field is the Link Child Field; that is, what's
its data type?

4. If you change the line I mentioned to this:

Me.txtInvoiceNum = Null

does the error still occur?

 

[Dirk Goldgar]

Dirk,
1) When I comment out the line: Me.txtInvoiceNum = "" the error
does not occur
2) txtInvoiceNum is not a bound control.
3) The Link Child Field, Invoice Number, is of long integer type.
4) And Yeah!!! No, the error does not occur when I change the above
mentioned line to Me.txtInvoiceNum = Null.
That's it! I was trying to set a long integer type to an empty
string. Thank you so much.

Excellent, Joan. You're very welcome.