Problems using OpenRecordSet with a prebuilt query

**JKing** · Nov 10 '07, 04:11 PM

Originally posted by 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: cbooldassignmen t
New Assignment: cbonewassignmen t

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 :

Code:

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 cbooldassignmen t 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:

Code:

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.

[CODE=vb]
Private Sub Command8_Click( )

Dim db As DAO.Database
Dim rcd As DAO.Recordset
Dim newassignment As String
Dim oldassignment As String

newassignment = Me.cbonewassign ment.Value
oldassignment = Me.cbooldassign ment.Value

Set db = CurrentDb
Set rcd = db.OpenRecordse t("qryASSIGNCHA NGE")

rcd.FindFirst ("[COMPUTER] = '" & oldassignment & "'")
rcd.Edit
rcd![COMPUTER] = newassignment
rcd.Update
rcd.Close

cbotitle.Value = Null
cbocdkey.Value = Null
cbooldassignmen t.Value = Null
cbonewassignmen t.Value = Null

End Sub[/CODE]

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

Jared

**dtsmith1984** · Nov 12 '07, 03:32 AM

Originally posted by JKing

Try adding quotes in the findfirst line.

[CODE=vb]
Private Sub Command8_Click( )

Dim db As DAO.Database
Dim rcd As DAO.Recordset
Dim newassignment As String
Dim oldassignment As String

newassignment = Me.cbonewassign ment.Value
oldassignment = Me.cbooldassign ment.Value

Set db = CurrentDb
Set rcd = db.OpenRecordse t("qryASSIGNCHA NGE")

rcd.FindFirst ("[COMPUTER] = '" & oldassignment & "'")
rcd.Edit
rcd![COMPUTER] = newassignment
rcd.Update
rcd.Close

cbotitle.Value = Null
cbocdkey.Value = Null
cbooldassignmen t.Value = Null
cbonewassignmen t.Value = Null

End Sub[/CODE]

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** · Nov 12 '07, 05:01 AM

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** · Nov 12 '07, 02:51 PM

Originally posted by 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.

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

Code:

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** · Nov 12 '07, 06:30 PM

Originally posted by dtsmith1984

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

Code:

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.

Code:

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

Or

Code:

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

Or

Code:

strSQL = "SELECT tblSOFTWARE.COMPUTER FROM tblSOFTWARE WHERE (tblSOFTWARE.KEY) = [B]" & Me.cbocdkey & "[/B]"

Gave 3 examples hope they work for you.

**dtsmith1984** · Nov 12 '07, 08:15 PM

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

Code:

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

Thank you so much for the help.

**jrayjr** · Nov 12 '07, 09:33 PM

Originally posted by dtsmith1984

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

Code:

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

Thank you so much for the help.

No problem, Glad its working for you.

**Wildster** · Jun 19 '08, 01:52 PM

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

Code:

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** · Jun 19 '08, 02:15 PM

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:

Code:

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** · Jun 20 '08, 12:10 PM

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

Code:

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** · Jun 20 '08, 02:08 PM

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

Attached Files

ScreenHunter_51.jpg (11.9 KB, 317 views)

**Wildster** · Jun 24 '08, 08:29 AM

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

Problems using OpenRecordSet with a prebuilt query

Problems using OpenRecordSet with a prebuilt query

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment