VBA DAO Parameters, etc.

[RiceFarmer via AccessMonster.com]

From a combobox on a form, iI am trying to access a record in another table.
I am using the afterupdate event of the tw combobox to create a query to
find the recordset. I will then use the fields in the recordset to perform
calsulations. I can't figure out how to look up a recordset. I keep getting
an error 3061 "too few parameters" when I go to the module to look up the
recordset. I know this is so simple...I hate to show my ignorance. Here is
the code.

Private Sub TW_AfterUpdate()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim sTW1 As Single
Dim sTW2 As Single
Dim sTW3 As Single
Dim sTW4 As Single
Dim sTW5 As Single
Dim sTW6 As Single
Dim sTW7 As Single
Dim sTW8 As Single
Dim intElevatorID As Integer
Dim intCropID As Integer

intElevatorID = Me.cboElevatorID.Value
Debug.Print intElevatorID
intCropID = Me.cboCropID.Value
Dim curTW12 As Currency
Set dbs = CurrentDb
Debug.Print dbs.Name
Set qdf = dbs.QueryDefs("qryGrainDiscounts")

It works to this point, but I've tried the next two lines, and neither works.
This is where I get my parameter error. I need something like the second
line that looks up a recordset based on the values in two different
comboboxes located on the form. The first line is commented out so i could
try the second line. Both give the same error.

'Set rst = qdf.OpenRecordset()
Set rst = dbs.OpenRecordset("SELECT tblGrainDiscountSchedule.* FROM
tblGrainDiscountSchedule WHERE (((tblGrainDiscountSchedule.ElevatorID)=[forms]
![frmWeightTickets]![cboElevatorID]) AND ((tblGrainDiscountSchedule.CropID)=
[forms]![frmWeightTickets]![cboCropID]));")

I have tried putting the values of the comboboxes into variables, and tried
refencing the comboboxes from the code.
I hope this is enough info. Is there a simpler way of doing this? Thanks.

 

[Rod Plastow]

Hi,

The 'Too few parameters. Expected ...' error often/usually occurs because
the SQL parser cannot interpret the [Forms]![formname]![controlname]
construct. From your sample code you have obviously copied and pasted the SQL
from the Access Query Designer. Does it work there? Remember to have the form
loaded and with values in the combo box when you try running the SQL from the
Query Designer.

You can do one of two things: either pursue using the explicit form
references in your SQL or capture the values in variables and use the
variable names in your SQL.

For the former try something like this (I hate all those parentheses so I've
removed them):

"WHERE tblGrainDiscountSchedule.ElevatorID = " & [forms]
![frmWeightTickets]![cboElevatorID] & " AND tblGrainDiscountSchedule.CropID
= " & [forms]![frmWeightTickets]![cboCropID]

For the latter:

intElevatorID = Me.cboElevatorID.Value
intCropID = Me.cboCropID.Value
"WHERE tblGrainDiscountSchedule.ElevatorID = " & intElevatorID & " AND
tblGrainDiscountSchedule.CropID = " & intCropID

A third option is to define parameters in the SQL and then explicitly assign
values to these parameters before running the SQL.

Hope this helps,

Rod

 

[RiceFarmer via AccessMonster.com]

Thanks for responding.

I chose the second of the choices, and it worked. Now, however, it seems to
have no record even though when I debug.print rst.fields.count, it shows the
correct number of fields. The recordset should only contain one recordthat
has 134 fields. When I try to load the values into variables, I get the
error 3021, "no current record". BOF is true. EOF is true. Absolute
position is -1.
Here is my code now. What am I missing?

intElevatorID = Me.cboElevatorID.Value
Debug.Print intElevatorID
intCropID = Me.cboCropID.Value
MsgBox "ElevID= " & Me.cboElevatorID.Value
Dim curTW12 As Currency
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryGrainDiscounts")
Set rst = dbs.OpenRecordset("SELECT tblGrainDiscountSchedule.* FROM
tblGrainDiscountSchedule WHERE tblGrainDiscountSchedule.ElevatorID = " &
intElevatorID & " AND tblGrainDiscountSchedule.CropID = " & intCropID)
Debug.Print rst.Fields.Count
Debug.Print rst.BOF
Debug.Print rst.EOF
Debug.Print rst.AbsolutePosition
Debug.Print rst.Name
sTW1 = rst!tw1
sTW2 = rst![tw2]

tw1 and tw2 are fields in the tblGrainDiscountSchedule, and should be in the
recordset.

Hi,

The 'Too few parameters. Expected ...' error often/usually occurs because
the SQL parser cannot interpret the [Forms]![formname]![controlname]
construct. From your sample code you have obviously copied and pasted the SQL
from the Access Query Designer. Does it work there? Remember to have the form
loaded and with values in the combo box when you try running the SQL from the
Query Designer.

You can do one of two things: either pursue using the explicit form
references in your SQL or capture the values in variables and use the
variable names in your SQL.

For the former try something like this (I hate all those parentheses so I've
removed them):

"WHERE tblGrainDiscountSchedule.ElevatorID = " & [forms]
![frmWeightTickets]![cboElevatorID] & " AND tblGrainDiscountSchedule.CropID
= " & [forms]![frmWeightTickets]![cboCropID]

For the latter:

intElevatorID = Me.cboElevatorID.Value
intCropID = Me.cboCropID.Value
"WHERE tblGrainDiscountSchedule.ElevatorID = " & intElevatorID & " AND
tblGrainDiscountSchedule.CropID = " & intCropID

A third option is to define parameters in the SQL and then explicitly assign
values to these parameters before running the SQL.

Hope this helps,

Rod

 

[Rod Plastow]

Hi,

Not so strange; Access is telling you through your debugging statements that
your table has 134 columns (ouch!) but zero rows that match your selection
criteria. Can you open the table directly to confirm this?

You need to ensure there is a record for the particular values of ElevatorId
and CropId.

Rod

 

[RiceFarmer via AccessMonster.com]

Rod,

I had one of those "duh !" moments for sure! You were right. I didn't have
the data for that
combination. You expressed discomfort at the thought of 134 columns. I
agree. Would there be a better way to populate variables to use in a Select
Case procedure, or would you need more info? I really do appreciate your
help!

Collier

 

[Rod Plastow]

Hi Collier,

I would need to know more about your data. '... better way to populate
variables to use in a Select Case procedure ...' intrigues me. What are you
doing?

If you want to take this further email me at (e-mail address removed) as we are
probably at the limit of tollerance/relevance for this discussion group.

Rod

 

[AccessVandal via AccessMonster.com]

Have you tried…..

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryGrainDiscounts")
qdf(0) = Forms!YourFormName!YourControl
Set rst = qdf.OpenRecordset(dbOpenSnapshot)
Debug.Print qdf(0)
Debug.Print rst!YourField/Column

There was a post regarding this. It was due to the query parameter but I’ve
lost that thread.

Thanks for responding.

I chose the second of the choices, and it worked. Now, however, it seems to
have no record even though when I debug.print rst.fields.count, it shows the
correct number of fields. The recordset should only contain one recordthat
has 134 fields. When I try to load the values into variables, I get the
error 3021, "no current record". BOF is true. EOF is true. Absolute
position is -1.
Here is my code now. What am I missing?

intElevatorID = Me.cboElevatorID.Value
Debug.Print intElevatorID
intCropID = Me.cboCropID.Value
MsgBox "ElevID= " & Me.cboElevatorID.Value
Dim curTW12 As Currency
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryGrainDiscounts")
Set rst = dbs.OpenRecordset("SELECT tblGrainDiscountSchedule.* FROM
tblGrainDiscountSchedule WHERE tblGrainDiscountSchedule.ElevatorID = " &
intElevatorID & " AND tblGrainDiscountSchedule.CropID = " & intCropID)
Debug.Print rst.Fields.Count
Debug.Print rst.BOF
Debug.Print rst.EOF
Debug.Print rst.AbsolutePosition
Debug.Print rst.Name
sTW1 = rst!tw1
sTW2 = rst![tw2]

tw1 and tw2 are fields in the tblGrainDiscountSchedule, and should be in the
recordset.

Hi,

[quoted text clipped - 29 lines]

Rod

 

[RiceFarmer via AccessMonster.com]

I'll give it a try. Gonna be busy for a few days. I'll let you know.
Thanks

Have you tried…..

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryGrainDiscounts")
qdf(0) = Forms!YourFormName!YourControl
Set rst = qdf.OpenRecordset(dbOpenSnapshot)
Debug.Print qdf(0)
Debug.Print rst!YourField/Column

There was a post regarding this. It was due to the query parameter but I’ve
lost that thread.

Thanks for responding.

[quoted text clipped - 32 lines]