Error in code - never been there before - set RS as openrecordset(

[BlueWolvering]

Hello,
I have used this code before countless times to find records in a
table/query within in VBA. (Using Access 2003).

Here is the code
Dim MyDb As Database, RS As Recordset
TargetQueryName = "q_VINRESULTS_find_FuelCard"
Set MyDb = CurrentDb
-->Set RS = MyDb.OpenRecordset(TargetQueryName)

RS.MoveFirst
If IsNull(RS.Fields(0).Value) Then
MsgBox "This fuel card is currently unassigned.", vbCritical,
"Unassigned Fuel Card"

Else
VINnum = RS.Fields(0).Value
DoCmd.OpenForm "f_VinResults"
End If

The arrow indicates the line on which the code fails. here is the error
message.
<<<Run-time error '3061':

Too few parameters. Expected 2.>>>

Here is the SQL for the query.
SELECT t_FuelCardInventory.VIN, t_FuelCardInventory.FuelCardNo,
t_FuelCardInventory.FuelCardProvider
FROM t_FuelCardInventory
WHERE (((t_FuelCardInventory.FuelCardNo)=[Forms]![f_SearchPanel]![FCN1]) AND
((t_FuelCardInventory.FuelCardProvider)=[Forms]![f_SearchPanel]![FCP1]));


The goal of this code is to take Fuel Card No and Fuel Card Provider from
the form, find out which VIN they are assigned to, and then shove that Vin
into VINum on the form.

Thanks

 

[raskew via AccessMonster.com]

Hi -

In your snippet, can't see where you've dimensioned TargetQueryName. Don't
know that that's the problem but it might be worth a shot.

Bob

Hello,
I have used this code before countless times to find records in a
table/query within in VBA. (Using Access 2003).

Here is the code
Dim MyDb As Database, RS As Recordset
TargetQueryName = "q_VINRESULTS_find_FuelCard"
Set MyDb = CurrentDb
-->Set RS = MyDb.OpenRecordset(TargetQueryName)

RS.MoveFirst
If IsNull(RS.Fields(0).Value) Then
MsgBox "This fuel card is currently unassigned.", vbCritical,
"Unassigned Fuel Card"

Else
VINnum = RS.Fields(0).Value
DoCmd.OpenForm "f_VinResults"
End If

The arrow indicates the line on which the code fails. here is the error
message.
<<<Run-time error '3061':

Too few parameters. Expected 2.>>>

Here is the SQL for the query.
SELECT t_FuelCardInventory.VIN, t_FuelCardInventory.FuelCardNo,
t_FuelCardInventory.FuelCardProvider
FROM t_FuelCardInventory
WHERE (((t_FuelCardInventory.FuelCardNo)=[Forms]![f_SearchPanel]![FCN1]) AND
((t_FuelCardInventory.FuelCardProvider)=[Forms]![f_SearchPanel]![FCP1]));

The goal of this code is to take Fuel Card No and Fuel Card Provider from
the form, find out which VIN they are assigned to, and then shove that Vin
into VINum on the form.

Thanks

 

[BlueWolvering]

Somehow, that worked. At least for the minute.

I guess it really does not want a variant there.

Thanks

Hi -

In your snippet, can't see where you've dimensioned TargetQueryName. Don't
know that that's the problem but it might be worth a shot.

Bob

Hello,
I have used this code before countless times to find records in a
table/query within in VBA. (Using Access 2003).

Here is the code
Dim MyDb As Database, RS As Recordset
TargetQueryName = "q_VINRESULTS_find_FuelCard"
Set MyDb = CurrentDb
-->Set RS = MyDb.OpenRecordset(TargetQueryName)

RS.MoveFirst
If IsNull(RS.Fields(0).Value) Then
MsgBox "This fuel card is currently unassigned.", vbCritical,
"Unassigned Fuel Card"

Else
VINnum = RS.Fields(0).Value
DoCmd.OpenForm "f_VinResults"
End If

The arrow indicates the line on which the code fails. here is the error
message.
<<<Run-time error '3061':

Too few parameters. Expected 2.>>>

Here is the SQL for the query.
SELECT t_FuelCardInventory.VIN, t_FuelCardInventory.FuelCardNo,
t_FuelCardInventory.FuelCardProvider
FROM t_FuelCardInventory
WHERE (((t_FuelCardInventory.FuelCardNo)=[Forms]![f_SearchPanel]![FCN1]) AND
((t_FuelCardInventory.FuelCardProvider)=[Forms]![f_SearchPanel]![FCP1]));

The goal of this code is to take Fuel Card No and Fuel Card Provider from
the form, find out which VIN they are assigned to, and then shove that Vin
into VINum on the form.

Thanks

 

[BlueWolvering]

SORRY THAT DID NOT SOLVE THE PROBLEM AT ALL.

Anyother thoughts?

Somehow, that worked. At least for the minute.

I guess it really does not want a variant there.

Thanks

Hi -

In your snippet, can't see where you've dimensioned TargetQueryName. Don't
know that that's the problem but it might be worth a shot.

Bob

Hello,
I have used this code before countless times to find records in a
table/query within in VBA. (Using Access 2003).

Here is the code
Dim MyDb As Database, RS As Recordset
TargetQueryName = "q_VINRESULTS_find_FuelCard"
Set MyDb = CurrentDb
-->Set RS = MyDb.OpenRecordset(TargetQueryName)

RS.MoveFirst
If IsNull(RS.Fields(0).Value) Then
MsgBox "This fuel card is currently unassigned.", vbCritical,
"Unassigned Fuel Card"

Else
VINnum = RS.Fields(0).Value
DoCmd.OpenForm "f_VinResults"
End If

The arrow indicates the line on which the code fails. here is the error
message.
<<<Run-time error '3061':

Too few parameters. Expected 2.>>>

Here is the SQL for the query.
SELECT t_FuelCardInventory.VIN, t_FuelCardInventory.FuelCardNo,
t_FuelCardInventory.FuelCardProvider
FROM t_FuelCardInventory
WHERE (((t_FuelCardInventory.FuelCardNo)=[Forms]![f_SearchPanel]![FCN1]) AND
((t_FuelCardInventory.FuelCardProvider)=[Forms]![f_SearchPanel]![FCP1]));

The goal of this code is to take Fuel Card No and Fuel Card Provider from
the form, find out which VIN they are assigned to, and then shove that Vin
into VINum on the form.

Thanks

 

[John Spencer]

The error is caused because the Jet dateabase engine does not recognize the
form references when you are calling the query from VBA. The Access query
service (which is not triggered by VBA) turns those references into values
before pushing the query to the JET database engine

You could probably do what you want using the DLookup function.

Dim vResult as Variant
Dim strWhere as String

'Note Assumption is that FuelCardNo and FuelCardProvider are text fields
'If they are number fields then change the triple quotes to one quote and
'drop the four quotes at the end
strWhere = "FuelCardNo=""" & Forms!f_SearchPanel!FCN1 & _
""" AND FuelCardProvider=""" & Forms!f_sSearchPanel!FCP1 & """"

vResult = DLookup("VIN","t_FuelCardInventory", strWhere)

If IsNull(vResult) Then
MsgBox ...
Else
VinNum = vResult
...
End If

End if

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..