Code Runs in Query / QBE Grid, but when copied and pasted to VB, it gives an error!!

[BrianDP]

The problem is a query that is supposed to tell me if there any activity for a specific Jobno/Partno combination. If a both the fields equal the criteria, then it returns something and that's all that is necessary.

I've tried rebuilding the database but that didn't help.


---------------------------------------------------------------------
This is the code I copied directly from the SQL view:

SELECT oldlab.Partno, oldlab.Jobno FROM oldlab
WHERE (((oldlab.Partno)="60a13068") AND ((oldlab.Jobno)="23289"));

---------------------------------------------------------------------

Next, I copied this into a program I'm working on and made the following changes for compatibility in VBa..

Set rst = db.OpenRecordset("SELECT oldlab.Partno, oldlab.Jobno FROM oldlab
WHERE (((oldlab.Partno)='60a13068') AND ((oldlab.Jobno)='23289'));")

---------------------------------------------------------------------

If ANYONE has a clue that I cannot see here..

It runs from the query window, but when I copy it into a VBA attached to a button, then it kicks up a Type 13, Type Mismatch which would usually mean you have the quotes out of whack, or maybe a field type that isn't compatible, but the field types of Oldlab.Partno and oldlab.Jobno are both texts @50 Characters.

Thanks in Advance! I sure hope it's as simple as "Hey, Brian, you missed a quote right here!" But I don't think so. Let me know if this makes any sense.


Sincerely,

Brian Prenovost
Best Data Processing

[bobh]

I've had a Type Mismatch that ended up being I forgot to do a Set db =
CurrentDB().....
are you sure you have the variables set up in your vba sub

dim db as dao.database
dim rst as dao.recordset
set db = currentdb()
set rst = db.openrecordset(etc...................

bobh.

[David Hare-Scott]

There is nothing obviously wrong with the SQL ( but it never is obvious is
it ;-) ).

As a general principal assign your SQL string to a variable and make that
variable the argument of openrecordset. This :

- lets you inspect the actual value of the string which may identify
problems with the syntax of SQL concatenations, especially where they go
over one line,

- makes it easier to step through the code to see just where the error
happens.

The error may not be where you think. Have you stepped through the code in
this case, does the error throw on the openrecordset call?

BTW your SQL could be made easier to read and to verify by simplification.

Instead of

WHERE (((oldlab.Partno)="60a13068") AND ((oldlab.Jobno)="23289"));

you could use

WHERE Partno="60a13068" AND Jobno="23289"

but then you can't paste in SQL that Access has generated in the query
builder. YMMV


David

[Phil]

Build it up slowly, and see where it fails

Air Code

Dim SQLStg as String
Dim MyDb as Database
Dim Rs as RecordSet

Set MyDb = CurrentDb()

SQLStg = "Select oldlab.* FROM oldlab"

set Rs = Mydb.OpenRecordSet(SQLStg)
With Rs
. MoveLast
Debug.Print Rs.RecordCount
. Close
Set RS = Nothing
End With

If that's OK change the SQLStg to
SQLStg = "Select PartNo, JobNo FROM oldlab"
and run it again

Then build up to
SQLStg = "Select PartNo, JobNo FROM oldlab WHERE PartNo = '60a13068'"

Finally
SQLStg = "Select PartNo, JobNo FROM oldlab WHERE PartNo = '60a13068' AND
Jobno='23289'"

Phil

[The Frog]

Bang on. Probably an indexing or table design issue. We still don't
know why its structured the way it is.

--
Cheers

The Frog

[Bob Barrows]

BrianDP wrote:
> The problem is a query that is supposed to tell me if there any
> activity for a specific Jobno/Partno combination. If a both the
> fields equal the criteria, then it returns something and that's all
> that is necessary.
>
> I've tried rebuilding the database but that didn't help.
>
>
> ---------------------------------------------------------------------
> This is the code I copied directly from the SQL view:
>
> SELECT oldlab.Partno, oldlab.Jobno FROM oldlab
> WHERE (((oldlab.Partno)="60a13068") AND ((oldlab.Jobno)="23289"));
>
> ---------------------------------------------------------------------
>
> Next, I copied this into a program I'm working on and made the
> following changes for compatibility in VBa..
>
> Set rst = db.OpenRecordset("SELECT oldlab.Partno, oldlab.Jobno FROM
> oldlab
> WHERE (((oldlab.Partno)='60a13068') AND ((oldlab.Jobno)='23289'));")
>
> ---------------------------------------------------------------------
>
> If ANYONE has a clue that I cannot see here..
>
> It runs from the query window, but when I copy it into a VBA attached
> to a button, then it kicks up a Type 13, Type Mismatch which would
> usually mean you have the quotes out of whack, or maybe a field type
> that isn't compatible, but the field types of Oldlab.Partno and
> oldlab.Jobno are both texts @50 Characters.
>

I believe this has nothing to do with your query and everything to do with
your OpenRecordset statement.OpenRecordset returns a DAO recordset. If you
also have a Reference to the ADODB (ActivX Data Objects) library and failed
to be explicit when declaring your rst variable, it is likely you are
attempting to set a DAO recordset to an ADODB recordset, causinfg ... Type
Mismatch.

[The Frog]

Sorry, don't know why this posted here. Belongs in another thread.

--
Cheers

The Frog

[BrianDP]

Bob, Thanks for the reminder. Yes, I'm calling out the --

dim db as databse
dim rst as recordset

set db= currentdb()
set rst = dbopenrecordblahblahblah...

When I was a kid my Dad used to explain to me how there were certain little pieces of code that you tend to use over and over and OVER again, and after a while you get to learn to type those things VERY fast.. hahaha

-B