Opening a SQL query with DAO

[meadows43]

Hi,

I've managed to get myself confused (not hard to do). I am trying to create a SQL query with criteria taken from the current record in another recordset and open it. I get the error "Run time error '3061' Too few parameters. Expected 2." This occurs on the line

Set rst3 = dbs.OpenRecordset(strSQL, dbOpenSnapshot)


Here's the function

Public Function RemoveOnhandIng()
Dim dbs As Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim rst3 As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset("SortedShoppingList", dbOpenDynaset)
Set rst2 = dbs.OpenRecordset("Pantry Contents", dbOpenDynaset)
With rst1
.MoveFirst
.Edit
End With

Dim listQuantity As String
Dim pantryQuantity As String

Dim mySearch As String
mySearch = "[Ingredient Num]=" & rst1![Ingredient Num]

Do While Not rst1.EOF

listQuantity = rst1!Quantity
If DLookup("[Ingredient Num]", "[Pantry Contents]", "[Ingredient Num] =" & rst1![Ingredient Num]) Then
'pantryQuantity = DLookup("[Quantity]", "[Pantry Contents]", "[Ingredient Num] =" & rst1![Ingredient Num])
With rst2
.FindNext mySearch
.Edit
End With
pantryQuantity = rst2!Quantity
strSQL = "SELECT ConversionFactor FROM MeasurementConversions WHERE FromUnit = '" & rst1![Unit Num] & "' AND ToUnit = '" & rst2![Unit Num] & "'"

Set rst3 = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

If Not (rst3.EOF) Then
rst3.MoveLast
End If

If rst3.RecordCount <> 0 Then

If (rst1![Unit Num] <> rst2![Unit Num]) Then
listQuantity = ConvertUnits(rst1![Unit Num], rst2![Unit Num], rst1!Quantity)
End If
End If

End Function

 

[WayneRyan]

Meadows,

I have no idea what you are trying to do, but this must be closer ...

The following will traverse all records in the table
SortedShoppingList, look up a field in table
[Pantry Contents], and the using those two look up
something in table MeasureConversions and update
the original table (SortedShoppingList).

Public Function RemoveOnhandIng() 
Dim dbs As Database 
Dim rst1 As DAO.Recordset 
Dim rst2 As DAO.Recordset 
Dim rst3 As DAO.Recordset 
Dim strSQL As String 

Set dbs = CurrentDb 

Set rst1 = dbs.OpenRecordset("SortedShoppingList", dbOpenDynaset) 

'
' Traverse all of Recordset 1
'
While Not rst1.EOF and Not rst1.BOF
   strSQL = "Select * " & _
            "From [Pantry Contents] " & _
            "Where [Ingredient Num] =" & rst1![Ingredient Num] & ";"
   Set rst2 = dbs.OpenRecordset(strSQL, dbOpenDynaset)
   strSQL = "SELECT ConversionFactor " & _
            "FROM MeasurementConversions " & _
            "WHERE FromUnit = '" & rst1![Unit Num] & "' AND ToUnit = '" & rst2![Unit Num] & "'" 
   Set rst3 = dbs.OpenRecordset(strSQL, dbOpenSnapshot) 
   rst1.Edit
   rst1!SomeField = rst3!SomeField
   rst1.Update
   rst1.MoveNext
   Wend
End Function

Wayne

 

[meadows43]

Sorry, with rst3 I need to know if the query returns any results. If it does, that means that there is a conversion factor for those two units of measurement in the MeasuerementConversions table. I do not want the units to get passed to the ConvertUnits function if there is no conversion factor listed for those two units in the MeasuermentConversions table. So, with rst3 I am just trying to check if the strSQL query returns any results. I hope this makes sense.

Thanks,
CM

 

[WayneRyan]

CM,

The code I posted is just a general structure for nested
recordsets. If I understood more what you were trying to
do, I could have been closer.

To see if there are any records:

Set rst3 = dbs.OpenRecordset(strSQL)

If rst3.BOF and rst3.EOF Then
   MsgBox("There are no records")
Else
   MsgBox("There are records")
End If

Or you can do a:

rst3.MoveFirst
rst3.MoveLast

And then you can use rst3.RecordCount, but you must do the
above lines.

Wayne

 

[meadows43]

Thanks. I still get the error, though, on the line

Set rst3 = dbs.OpenRecordset(strSQL)

I think something is wrong with my SQL String.

 

[WayneRyan]

CM,

What's in it?

Do you know how to use the debugger?

Wayne

 

[meadows43]

Wayne,

I have used the debugger to watch the value of my strSQL variable, and it appears to be contain the correct criteria, but I still get the error "Too few parameters. Expected 2." The SQL String should be "SELECT ConversionFactor FROM MeasurementConversions WHERE FromUnit = '" & rst1![Unit Num] & "' AND ToUnit = '" & rst2![Unit Num] & "'" The MeasurementConversions table has a primary key that has two fields, FromUnit and ToUnit. I need to query that table to see if there is a record with rst![Unit Num] as FromUnit and rst2![Unit Num] as ToUnit. To help explain what the code is trying to do, rst1 is a recordset based on the SortedShoppingList table, and rst2 is based on the Pantry contents table. I need to find out if each ingredient in the SortedShoppingList table is in the Pantry Contents table. If yes, then I need to find out if the Quantity for that ingredient in the Pantry Contents table is greater than the Quantity in the SortedShoppingList table. I need the ConvertUnits function because the Unit of Measurement in the Pantry Contents table may be different than the one in the SortedShoppingList table. The reason I need the third recordset (based on the runtime SQL query) is because I cannot pass the ConvertUnits function a FromUnit and ToUnit that do not have a ConversionFactor in the MeasurementConversions table (i.e. there is no record in that table with rst1![Unit Num] as FromUnit and rst2![Unit Num] as ToUnit).

Thanks,
Chris

 

[WayneRyan]

Chris,

That error comes when SQL is not happy with your query.
One of the names is wrong.

What happens if you paste the following into a new query?

New query, design view, right-click and choose SQL view,
then paste:

SELECT ConversionFactor 
FROM    MeasurementConversions
WHERE FromUnit = 'AAA'  AND 
            ToUnit = 'BBB'

You can substitute the appropriate values for AAA and BBB

Wayne

 

[meadows43]

Wayne,

I took your advice and tried typing my query in the SQL view window, and got the same results. I then realized I had the field names wrong. I changed it and now it works fine. Well, now I know what that error means!

Thanks for all your help.

Chris