VBA DAO Parameters, etc.

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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200901/1

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

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.




Rod Plastow wrote:
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

--
Message posted via http://www.accessmonster.com

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

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 wrote:
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

--
Message posted via http://www.accessmonster.com

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 as we are
probably at the limit of tollerance/relevance for this discussion group.

Rod

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.

RiceFarmer wrote:
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

--
Please Rate the posting if helps you.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200901/1

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

AccessVandal wrote:
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]

Rod


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200901/1