Problems using OpenRecordSet with a prebuilt query

[dtsmith1984]

I have a form that i want to be able to reassign a specific piece of software to a different machine.

Form specs:
Software Title: cbotitle
CDKEY: cbocdkey
Old Assignment: cbooldassignment
New Assignment: cbonewassignment


I've built a query that pulls a list of all the computers the software is currently assigned to dynamically by gettings its criteria from the current form.

If i open the query manually after completing the form everythign works fine.

SQL for qryASSIGNCHANGE:

SELECT tblSOFTWARE.COMPUTER FROM tblSOFTWARE WHERE (((tblSOFTWARE.KEY)=[forms]![frmASSIGNSOFTWARE].[cbocdkey].[value]));

Then on my submit button I want to find the first value in the query that matches the cbooldassignment and change it to the new. I used findfirst because there could be more than one UNASSIGNED for example and i dont want it to change all of them to the new value.

Code for Submit Button:

Private Sub Command8_Click()
 
Dim db As DAO.Database
Dim rcd As DAO.Recordset
Dim newassignment As String
Dim oldassignment As String
 
newassignment = Me.cbonewassignment.Value
oldassignment = Me.cbooldassignment.Value
 
Set db = CurrentDb
Set rcd = db.OpenRecordset("qryASSIGNCHANGE")
 
rcd.FindFirst ([COMPUTER] = oldassignment)
rcd.Edit
rcd![COMPUTER] = newassignment
rcd.Update
rcd.Close
 
cbotitle.Value = Null
cbocdkey.Value = Null
cbooldassignment.Value = Null
cbonewassignment.Value = Null
 
End Sub

I Keep getting Error 3061: Too few parameters. Expected 1

I have tried all kinds of things even specifying the parameter with querydefs but couldnt get that to work either. Does anyone know what my problem could be and what I might need to do differently?

[JKing]

I have a form that i want to be able to reassign a specific piece of software to a different machine.

Form specs:
Software Title: cbotitle
CDKEY: cbocdkey
Old Assignment: cbooldassignment
New Assignment: cbonewassignment


I've built a query that pulls a list of all the computers the software is currently assigned to dynamically by gettings its criteria from the current form.

If i open the query manually after completing the form everythign works fine.

SQL for qryASSIGNCHANGE:

SELECT tblSOFTWARE.COMPUTER FROM tblSOFTWARE WHERE (((tblSOFTWARE.KEY)=[forms]![frmASSIGNSOFTWARE].[cbocdkey].[value]));

Then on my submit button I want to find the first value in the query that matches the cbooldassignment and change it to the new. I used findfirst because there could be more than one UNASSIGNED for example and i dont want it to change all of them to the new value.

Code for Submit Button:

Private Sub Command8_Click()
 
Dim db As DAO.Database
Dim rcd As DAO.Recordset
Dim newassignment As String
Dim oldassignment As String
 
newassignment = Me.cbonewassignment.Value
oldassignment = Me.cbooldassignment.Value
 
Set db = CurrentDb
Set rcd = db.OpenRecordset("qryASSIGNCHANGE")
 
rcd.FindFirst ([COMPUTER] = oldassignment)
rcd.Edit
rcd![COMPUTER] = newassignment
rcd.Update
rcd.Close
 
cbotitle.Value = Null
cbocdkey.Value = Null
cbooldassignment.Value = Null
cbonewassignment.Value = Null
 
End Sub

I Keep getting Error 3061: Too few parameters. Expected 1

I have tried all kinds of things even specifying the parameter with querydefs but couldnt get that to work either. Does anyone know what my problem could be and what I might need to do differently?

Try adding quotes in the findfirst line.

Private Sub Command8_Click()
 
Dim db As DAO.Database
Dim rcd As DAO.Recordset
Dim newassignment As String
Dim oldassignment As String
 
newassignment = Me.cbonewassignment.Value
oldassignment = Me.cbooldassignment.Value
 
Set db = CurrentDb
Set rcd = db.OpenRecordset("qryASSIGNCHANGE")
 
rcd.FindFirst ("[COMPUTER] = '" & oldassignment & "'")
rcd.Edit
rcd![COMPUTER] = newassignment
rcd.Update
rcd.Close
 
cbotitle.Value = Null
cbocdkey.Value = Null
cbooldassignment.Value = Null
cbonewassignment.Value = Null
 
End Sub

Hope this helps.
If you're still receiving the error let me know which line is the offending line.

Jared

[dtsmith1984]

Try adding quotes in the findfirst line.

Private Sub Command8_Click()
 
Dim db As DAO.Database
Dim rcd As DAO.Recordset
Dim newassignment As String
Dim oldassignment As String
 
newassignment = Me.cbonewassignment.Value
oldassignment = Me.cbooldassignment.Value
 
Set db = CurrentDb
Set rcd = db.OpenRecordset("qryASSIGNCHANGE")
 
rcd.FindFirst ("[COMPUTER] = '" & oldassignment & "'")
rcd.Edit
rcd![COMPUTER] = newassignment
rcd.Update
rcd.Close
 
cbotitle.Value = Null
cbocdkey.Value = Null
cbooldassignment.Value = Null
cbonewassignment.Value = Null
 
End Sub

Hope this helps.
If you're still receiving the error let me know which line is the offending line.

Jared

Still receive the same error. Its the OpenRecordset line that gives me the error.
For some reason it's not getting the parameter from the form. If the form is open and filled out and i manually open the query it shows the results properly, if the form is not open the query will pop up and ask me for the value of the cdkey from the form. I just cannot figure out why it's not able to see the value from the form when i run this with a button onclick.

Is there any way to set the value from the form to a public variable i guess. So that the query is not actually looking for the form to get its parameter value but instead the variable and I could set the combo box on the form to update the variable onchange. ??

[JKing]

Try putting your query into a string and then passing the string into the openrecordset command.

If you are unsure how to do this post the sql for your query and I will help you with building the string.

[dtsmith1984]

Try putting your query into a string and then passing the string into the openrecordset command.

If you are unsure how to do this post the sql for your query and I will help you with building the string.

Ok, I now have this. Still getting "Too Few Parameters: Expected 1"

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim newassignment As String
Dim oldassignment As String
Dim cdkey As String
Dim strTestQuery As String
Dim strSQL As String
 
strTestQuery = "qryTEST"
newassignment = Me.cbonewassignment.Value
oldassignment = Me.cbooldassignment.Value
cdkey = Me.cbocdkey.Value
 
Set db = CurrentDb
 
strSQL = ("SELECT [COMPUTER] FROM tblSOFTWARE WHERE [KEY] = [cdkey]")
Set qdf = db.CreateQueryDef(strTestQuery, strSQL)
 
 
 
Set rst = qdf.OpenRecordset
rst.FindFirst ("[COMPUTER] = '" & oldassignment & "'")
rst.Edit
rst![COMPUTER] = newassignment
rst.Update
rst.Close

[jrayjr]

Ok, I now have this. Still getting "Too Few Parameters: Expected 1"

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim newassignment As String
Dim oldassignment As String
Dim cdkey As String
Dim strTestQuery As String
Dim strSQL As String
 
strTestQuery = "qryTEST"
newassignment = Me.cbonewassignment.Value
oldassignment = Me.cbooldassignment.Value
cdkey = Me.cbocdkey.Value
 
Set db = CurrentDb
 
strSQL = ("SELECT [COMPUTER] FROM tblSOFTWARE WHERE ([KEY]) = [cdkey]")
Set qdf = db.CreateQueryDef(strTestQuery, strSQL)
 
 
 
Set rst = qdf.OpenRecordset
rst.FindFirst ("[COMPUTER] = '" & oldassignment & "'")
rst.Edit
rst![COMPUTER] = newassignment
rst.Update
rst.Close

I think the problem is you need to break out of the SQL statement to pull in your cdkey array because its trying to get it from your table and its not there.

Try your SQL statment like this.

strSQL = "SELECT [COMPUTER] FROM tblSOFTWARE WHERE ([KEY]) = " & cdkey & ""
 

Or

strSQL = "SELECT tblSOFTWARE.COMPUTER FROM tblSOFTWARE WHERE (tblSOFTWARE.KEY) = " & cdkey & ""
 

Or

strSQL = "SELECT tblSOFTWARE.COMPUTER FROM tblSOFTWARE WHERE (tblSOFTWARE.KEY) = " & Me.cbocdkey & ""
 

Gave 3 examples hope they work for you.

[dtsmith1984]

Yes i was just about to post up here that i figured it out.. This is what i used.

strSQL = "SELECT [COMPUTER] FROM tblSOFTWARE WHERE [KEY] = '" & cdkey & "'"

Thank you so much for the help.

[jrayjr]

Yes i was just about to post up here that i figured it out.. This is what i used.

strSQL = "SELECT [COMPUTER] FROM tblSOFTWARE WHERE [KEY] = '" & cdkey & "'"

Thank you so much for the help.

No problem, Glad its working for you.

[Wildster]

Hi,

I've got a similar problem to the one in this post which was solved.

My error returned is "Invalid Argument" and it points to the line where OpenRecordset is used.

My code is: -

Private Sub Command26_Click()
 
Dim test As Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim bFlag As Boolean
 
Set Rs = CurrentDb.OpenRecordset("SELECT Time_Slot, Count([Bookings])+1 AS Total FROM qryBookedHolidaySlots WHERE (((Time_Slot) Between Forms!tblBooking!Booking_StartDtm And Forms!tblBooking!Booking_EndDtm)) GROUP BY Time_Slot;", dbOpenStatic, dbReadOnly)
 
If Not Rs.EOF And Not Rs.EOF Then
   Do Until Rs.EOF
       If Booking_Type = "Hol" And Rs("Total") >= 4 Then
          Msg = Msg & "Time Slot :" & Rs("Time_Slot") & " Full" & vbCrLf
          bFlag = True
       End If
       Rs.MoveNext
   Loop
   Rs.Close
End If
Set Rs = Nothing
 
If bFlag = True Then
   MsgBox "The following time slots are fully booked:" & vbCrLf & vbCrLf & Msg, vbExclamation + vbOKOnly, "Bookings Conflict"
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
End If
 
End Sub

Any help would be very much appreciated as I've been stuck for 3 days trying to solve it and the help on the Microsoft website regarding OpenRecordset isn't helpful at all.

Thanks in advance

[Stewart Ross]

Hi Wildster. Problem is the references to form fields within your SQL string, which will not be recognised as valid when the recordset open takes place. To resolve this you need to include the value of the form fields, not their names.

A small complication here is that I am not sure what the type of the underlying data in the controls involved is. If it is times (as these are time slots) you may have to use explicit date/time delimiters - '#' before and after each reference to the value to make it work. If the control values are strings, you would need single quotes. If they are numbers, no delimiters will be required. The three alternatives are listed below, numeric then string then date/time:

NUMERIC VERSION
SELECT Time_Slot, Count([Bookings])+1 AS Total FROM qryBookedHolidaySlots WHERE (((Time_Slot) Between " & Forms!tblBooking!Booking_StartDtm & " And " & Forms!tblBooking!Booking_EndDtm & ")) GROUP BY Time_Slot;"
 
STRING VERSION
SELECT Time_Slot, Count([Bookings])+1 AS Total FROM qryBookedHolidaySlots WHERE (((Time_Slot) Between '" & Forms!tblBooking!Booking_StartDtm & "' And '" & Forms!tblBooking!Booking_EndDtm & "')) GROUP BY Time_Slot;"
 
TIME VERSION
SELECT Time_Slot, Count([Bookings])+1 AS Total FROM qryBookedHolidaySlots WHERE (((Time_Slot) Between #" & Forms!tblBooking!Booking_StartDtm & "# And #" & Forms!tblBooking!Booking_EndDtm & "#)) GROUP BY Time_Slot;"

-Stewart

[Wildster]

Thanks for the post, it makes sense what you're saying. Although I've completely stripped the query back to something very basic and it still doesn't like OpenRecordset. There is something else more underlying which is causing the error (the solution you gave probably solved what would have been a preceeding error once this one was corrected).

I've stripped the code back to: -

Dim db As DAO.Database
Dim Rs As DAO.Recordset
 
Set db = CurrentDb
 
Set Rs = db.OpenRecordset("SELECT * FROM tblBooking", dbOpenDynaset)

I'm getting runtime error 91: Object variable or With block variable not set.

The only two variables I'm using are db and Rs which I both have declared and set, is there something that I'm doing wrong? i.e. need to open the db first, declare something else etc

I've been stuck on this problem all week now and no matter how many help files, Microsoft support pages etc. I look at I can't seem to find a solution. If anyone can offer help then it would be very much appreciated. I've even simplified the sql query to something really simple to get OpenRecordset to work but no luck.

Thanks

[Stewart Ross]

OK. There's nothing wrong with your stripped down code - and just to be absolutely certain belt and brace wise I tried it myself in an Access 2003 test DB on a table renamed tblBooking for the purpose.

I wonder if there is a conflicting project reference somewhere which is interfering with the correct interpretation of your DAO recordset?

Could you check your project references (from the VB editor select Tools, References) and make sure that there is only one reference to the Microsoft DAO Object Library ticked, and that nothing else looks suspicious? I will attach a screenshot of my own project references so you know what to look for if it is not obvious.

-Stewart

[Wildster]

The references were the problem, thanks very much for your help