Data Type Mismatch

[DZ]

If your formfields state they are number types and not
regular text, try this instead:

Dim vDoc_No As Long

should be

Dim vDoc_No As Variant

It's strange, I know, but it could be your problem.

-----Original Message-----
I've got a Word2002 form with five fields/bookmarks and an Access database
with the same five fields (bkDoc_No/Doc_No,

bkCase_Name/Case_Name, bkAAG/AAG,

 

[Michael]

Thanks for writing in. Unfortunately, I made this change and now it comes up
with the error:
Object variable or With variable not set

It happens when the code reaches:
vDoc_No = ActiveDocument.FormFields("bkDoc_No").Result

And, you're right, the bkDoc_No field is defined as a Number field though I
switched it back to Regular Text for a lark - it made no difference.

 

[Michael]

Whoops! My fault. I was working with an older version that I'd been toying
with trying to see what was wrong. Actually, changing to Variant made no
difference. Thanks though.

Any other ideas, anybody?

Michael

 

[dz]

It sounds like something is missing in your code. When
you debug within VBA, there are no errors? Then when you
run the code, and the error msg comes up, does it give you
the option to debug? If so, what line of code does it
highlight?

 

[Michael]

Yes, sorry that was a mistake. I accidentally made your change to an older
version where I had made some other changes, played around with it, and
screwed things up trying to find the problem. After I realized what I'd
done, I went back to the version I put up here, changed the vDoc_No to
Variant and, as it turned out, it made no difference.

I still get the data type mismatch error with vDoc_No as every type I've
tried (i.e. String, Integer, Log, and Variant). When I choose debug, it
highlights:
vRecordSet.Open "SELECT * FROM Table1 WHERE Table1!Doc_No = " & Chr(34) &
vDoc_No & Chr(34), vConnection, adOpenKeyset, adLockOptimistic

And, you're right, the bkDoc_No field is defined as a Number field though I
switched it back to Regular Text for a lark - no change.

Michael

 

[Michael]

Ugh! This:

vRecordSet.Open "SELECT * FROM Table1 WHERE Table1! Doc_No = " & Chr(34) &
vDoc_No & Chr(34), vConnection, adOpenKeyset, adLockOptimistic

is the offending line of code, all right. And if I'd typed it out instead
of using the Chr's because I thought they made it look neater, I would have
spotted it sooner.

vRecordSet.Open "SELECT * FROM Table1 WHERE Table1! Doc_No = '" & vDoc_No &
"'", vConnection, adOpenKeyset, adLockOptimistic

Being as how it is a number field, those single quotes around the variable
name have to go. Sorry for dragging others into my hair-pulling competition,
but it was stumping me, too, for a long time. Thanks for your posts DZ.

Michael

P.S. I'll probably have a new question which I WONT be able to answer
shortly.