Recordset won't open - Error 3061 2

[LouiseJ]

Could any please explain why the following isn't working?

It seems to fail on the Where clause of the SQL when attempting to open the recordset. However in debug [Forms]![frmMainBuildBOM]![ProductID] has a value. The code is executed from frmMainBuildBOM - ie the form is open.

Error message reads:
'error 3061 Too few parameters. Expected 1'

Thanks



Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String


strSQL = "SELECT tblConponent.ConponentID, tblConponent.ParentProductID, tblConponent.Quantity, tblProduct.ProductName, tblConponent.Notes " & vbCrLf & _
"FROM tblConponent INNER JOIN tblProduct ON tblConponent.ConponentID = tblProduct.ProductID " & vbCrLf & _
"WHERE (((tblConponent.ParentProductID)= [Forms]![frmMainBuildBOM]![ProductID]));"



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

 

[Remou]

When building SQL strings in VBA, you need actual values rather than references to a form:

strSQL = "SELECT tblConponent.ConponentID, " & _
   "tblConponent.ParentProductID, tblConponent.Quantity, " & _
   "tblProduct.ProductName, tblConponent.Notes " & _
"FROM tblConponent INNER JOIN tblProduct " & _
"ON tblConponent.ConponentID = tblProduct.ProductID " & _
"WHERE tblConponent.ParentProductID = " & _
[Forms]![frmMainBuildBOM]![ProductID]

http://lessthandot.com

 

[LouiseJ]

So no way round it then?

I need to test if a sub form is showing any records as a user moves through the records of the main form. Any suggestions?

Thanks

 

[Remou]

I though I had shown the answer? Put the reference to the form outside the quotes.

http://lessthandot.com

 

[MajP]

I need to test if a sub form is showing any records as a user moves through the records of the main form. Any suggestions

Public Function getSubCount(subfrm As Access.Form) As Integer
getSubCount = subfrm.Recordset.RecordCount
End Function
call it from the mainforms on current event
Private Sub Form_Current()
if getSubCount(Me.subFormControl.Form) = 0...
End Sub

 

[LouiseJ]

Sorry I missed that Remou.

Thanks Majp.

I appreciate the help from both of you.