AC2K: SQL in VBA issue

[Telsa]

I'm trying to create a recordset based on the [base] of a [partid]. I have a table and a query I'm using to establish the base. But the SQL that Access writes in the Query objects (where it works) doesn't seem to work in VBA. I get a "Join Expression Not Supported" error. Here is what I have tried..

strSQL = "SELECT * FROM qryDetailCRC "
Set db = CurrentDb()
Set recSubmittal = db.OpenRecordset(strSQL)

strProdNum = Me.PartsID

strPartsBase = Left(recSubmittal!PartNo, 5)

strSQL = "SELECT * FROM qryDetailCRC " & _
" INNER JOIN tblSubmittal ON qryDetailCRC.ID = tblSubmittal.PartsID" & _
" WHERE qryDetailCRC.Base ='" & strPartsBase & "';"
Set recSubmittal = db.OpenRecordset(strSQL)

What I want is all the parts that have the same base as the part in the current record (the button on the form starts the ball rolling). I don't have the Base in the table the form populates, but it is in the query and the PartsID matches the queries id field.

Any clues how I can make this work?

Mary :)

[paulw]

I can't explain why that isn't working, but couldn't you just make it a saved, parameterised query and execute it directly?

Just an idea.

Paul.

P.S. Your code should work tho'

[Telsa]

Thanks for your confirmation! I rewrote it and this way seems to work.

strProdNum = Me.PartsID

strPartsBase = DLookup("[base]", "parts", "[ID] = " & strProdNum)

strSQL = "SELECT * FROM qryDetailCRC "
strSQL = strSQL & " INNER JOIN tblSubmittal ON qryDetailCRC.ID = tblSubmittal.PartsID"
strSQL = strSQL & " WHERE qryDetailCRC.Base = "
strSQL = strSQL & Chr(34) & strPartsBase & Chr(34) & ";"

Set db = CurrentDb()
Set recSubmittal = db.OpenRecordset(strSQL)


Mary :)

[paulw]

That is odd!

Sure you weren't getting extraneous spaces in you Left(recSubmittal!PartNo, 5)? Use Trim$ - c'mon don't work round it, figure it out - I'm intrigued now;)

Cheers,

Paul.

[Telsa]

LOL!

Actually, what I was aiming for was the Base field in my Parts database... I was just mind numb at the moment when I was putting this together. I forgot about my handy, dandy Dlookup feature which has saved me a lot of grief in other parts of this database.

The 5 character grab from that function is what makes up the Base when it first gets added to the table anyway! That way the user doesn't have to input it, it'll do it for her anyway... reduce chance of typos.

I've even reduced the sql line to one simple line by removing the join section.

Mary :)

[Edited by Telsa on 11-09-2000 at 09:19 PM]