Coding SQL in VBA

[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 = " & strProdNum & _
" 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?

 

[MichaelRed]

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


I re-stated the strSQL - just because I am more used to this format, the only real change is the addition of the quote characters (Chr(34) - in the fourth line. This - of coursr - is an assumption that the field is a text field and not numeric, although the name statement could be 'interperted' to mean either string/text or numeric.


MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Shep]

Your problem is that your are trying to join a table to a string variable.

Borrowing from Michael's response (I also prefer this layout of code) I assume you want something like this...

strSQL = "SELECT * FROM qryDetailCRC "
strSQL = strSQL & "INNER JOIN tblSubmittal ON "
strSQL = strSQL & "qryDetailCRC.ID = tblSubmittal.ID "
strSQL = strSQL & "WHERE qryDetailCRC.Base = '"
strSQL = strSQL & strPartsBase & "'"

Regards,

Shep

 

[Telsa]

Yup, That helped! Also, I made a few modifications and added Dlookup to make the relationship of base to partid.

Here's what I have ended up with...

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)

Don't seem to get error with this... even though I am getting error of "Requested member of collection does not exist."

I need try to figure what it means by that.

Mary )