If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
VBA DAO Parameters, etc.
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 |
#3
|
|||
|
|||
VBA DAO Parameters, etc.
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 |
#4
|
|||
|
|||
VBA DAO Parameters, etc.
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 |
#5
|
|||
|
|||
VBA DAO Parameters, etc.
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 |
#6
|
|||
|
|||
VBA DAO Parameters, etc.
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 |
#7
|
|||
|
|||
VBA DAO Parameters, etc.
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 |
#8
|
|||
|
|||
VBA DAO Parameters, etc.
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 |
Thread Tools | |
Display Modes | |
|
|