OpenRecordSet from Form Event, source is query, filtered based on value of form field (1 Viewer)

[davefwelch]

I've tried several different manners to get this to work, but keep getting the same error when the event is triggered (i.e., the code is run):

RunTime Error '3061':
Too few parameters. Expected 1.

I know this is happening because I'm not properly filtering my recordset, because if I instead alter the filter built into the query to not rely on the value of the form field, it works like a charm. Here's the code I'm using currently:

    Set rs = db.OpenRecordset("Select * from PWSIDmorGroups WHERE [PWSID] = " & Me.cmbPWSID.Value)

I just tried adding .Value to the cmbPWSID criteria, but keep getting the same error. I've even tried inserting a MsgBox into the code, to ensure that Me.cmbPWSID is returning a value that would cause the recordset to have records, and it surely does! Can anyone tell me what the heck is going on?

Alternately, I tried creating a query based on the table PWSIDmorGroups, and adding into that query a criteria based on the value of the combo box in the form, and still get the same error! HELLLLLP please

 

[boblarson]

Is PWSID numeric or text?

 

[davefwelch]

PWSID is text. Every record has a PWSID of the form ME0000000, where "0000000" can be any digit 0 - 9.

It's a new day. I think I will try using the column notation for the combo box, but I have a feeling that the problem has something to do with the timing of the query being filtered. I know that the error is coming from the fact that the OpenRecordset method, which is obviously expecting 1 argument, is not "seeing" any arguments, because for some reason the query "isn't there" due to the timing of the filter criteria. Because it comes from a field on the form from which this Sub is called. Here, the entire code of the Sub for you:

Private Sub cmbMonth_GotFocus()
    Me.cmbMonth = Month(Now()) - 1
    Dim db As Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("qryExpectedMOR")
    
    rs.MoveFirst
    Do
        Select Case rs!MORgroup
            Case 1
                Me.lblGroup1.FontBold = True
            Case 2
                Me.lblGroup2.FontBold = True
            Case 4
                Me.lblGroup4.FontBold = True
            Case 5
                Me.lblGroup5.FontBold = True
            Case 6
                Me.lblGroup6.FontBold = True
        End Select
        rs.MoveNext
    Loop Until rs.EOF
    rs.Close


End Sub

 

[davefwelch]

This is driving me insane. The error makes no sense based on what I know about VB and Access!!!! My recent attempt has the code looking like this:

Private Sub cmbMonth_GotFocus()
    Me.cmbMonth = Month(Now()) - 1
    Dim db As Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT * FROM qryExpectedMOR WHERE PWSID=" & Me.cmbPWSID.Column(0))
    
    rs.MoveFirst
    Do
        Select Case rs!MORgroup
            Case 1
                Me.lblGroup1.FontBold = True
            Case 2
                Me.lblGroup2.FontBold = True
            Case 4
                Me.lblGroup4.FontBold = True
            Case 5
                Me.lblGroup5.FontBold = True
            Case 6
                Me.lblGroup6.FontBold = True
        End Select
        rs.MoveNext
    Loop Until rs.EOF
    rs.Close


End Sub

Just keep getting the same error.
RunTime Error '3061':
Too few parameters. Expected 1.

I know this has something to do with circular references . . . or something like that. Because . . . when I modify the query itself to look at the value of the combo box on the form, the query runs fine on it's own. The problem only occurs when I try to open the query as a recordset. GRRRRRRRRRR

 

[spikepl]

You are trying to do too much too fast.

Replace

   Set rs = db.OpenRecordset("Select * from PWSIDmorGroups WHERE [PWSID] = " & Me.cmbPWSID.Value)

with

Dim strSQL as String
strSQL = "Select * from PWSIDmorGroups WHERE [PWSID] = " & Me.cmbPWSID 
debug.print strSQL
Set rs = db.OpenRecordset(strSQL)

and see what the output of the debug.print is. If you do not spot the error, then paste that output here.

 

[davefwelch]

Thanks for your help! At runtime, the value of the strSQL variable is:

Select * from PWSIDmorGroups WHERE [PWSID] = ME0000195

By all accounts, that SHOULD allow the db.OpenRecordset method to work, right? WTH is going on here!?!?!?

 

[spikepl]

You have a syntax error. How is the thing to know it was fed a string? Wrap the string in Chr(34) on each side.

 

[davefwelch]

Works like a charm. I thought that was the problem before sending my last response, but had only tried the " ' ' " fix, which had been unsuccessful. Thanks for your help!

Oh, and this is the successful strSQL:

strSQL = "Select * from PWSIDmorGroups WHERE [PWSID] = " & Chr(34) & Me.cmbPWSID & Chr(34)

 

[AC-Dev]

Hi, i have a similar problem with a recordset.
I am not the most advanced VBA programer, but this is what i have

code:

Dim rs As DAO.Recordset

Set rs = db.OpenRecordset("Employees", dbOpenDynaset)

With rs
FindFirst "Employee ID = strempID"
If Not .NoMatch Then
MsgBox .Fields(3).Value
End If
End With

i am trying to get the value from a regular, unfiltered table
(the msgbox is justto display the data to make sure it is correct)
i keep getting the error "object required"
i tried to implement the code that was suggested above (with tweaks to fit my program of course) and i still get the same error.

 

[boblarson]

First off this line:

FindFirst "Employee ID = strempID"


needs square brackets because of the space in the field name (it is best to NOT use spaces in field or object names nor use special characters either).

FindFirst "[Employee ID] = " & strempID

EDIT:

Oh, and I forgot you need to concatenate in the variable not include it in the quotes.

 

[AC-Dev]

First off this line:

FindFirst "Employee ID = strempID"


needs square brackets because of the space in the field name (it is best to NOT use spaces in field or object names nor use special characters either).

FindFirst "[Employee ID] = " & strempID

EDIT:

Oh, and I forgot you need to concatenate in the variable not include it in the quotes.

alright i fixed the namein the table and the code. as for the edit you made, im not sure what that is. can you please further clarify?

 

[boblarson]

alright i fixed the namein the table and the code. as for the edit you made, im not sure what that is. can you please further clarify?

When I first posted my response it looked like this:

FindFirst "[Employee ID] = strempID"


But the ending double quote was in the wrong place and there needed to be an ampersand (&) to concatenate in the variable:

FindFirst "[Employee ID] = " & strempID

 

[AC-Dev]

AHH ic yes i fixed that as well. same issue although. Am i going about the wrong way to do this?

 

[boblarson]

If you want to not filter the form but only go to the record, you are using the correct method. If you want to filter the form then you would need to set the filter instead. I think I just spotted your problem.

You have

With rs

and so there needs to be a period BEFORE the FindFirst part:

With rs
.FindFirst

 

[AC-Dev]

i have that in my code. it was accidently left out when i pasted the code here

 

[boblarson]

Well, last idea - where are you declaring strTempID and how are you populating it?

 

[AC-Dev]

strempID is populated by me in a table that is currently unlinked from all others (i mean like no relationships and junk)

here is the full code so you can get a better understanding of what i may be missing:

Private Sub btnMaintenance_Click()

On Error GoTo Err_cmdbtnMaintenance_Click
'Attached to On Click event of cmdOpenEmpForm

Dim strempID
Dim strPass
Dim strInPasswd
Dim rs As DAO.Recordset
Dim strSQL As String

strempID = InputBox("Enter Employee ID", "Restricted Form")
MsgBox strempID

'other attempted declaration
'Set rs = db.OpenRecordset("Employees", dbOpenDynaset)

strSQL = "Select Password from Employees WHERE [Employee_ID] = " & strempID
Debug.Print strSQL
Set rs = db.OpenRecordset(strSQL)
MsgBox strSQL

With rs
.FindFirst "[Employee_ID] =" & strempID
If Not .NoMatch Then
MsgBox .Fields(3).Value
End If
End With


'Check to see if there is any entry made to input box, or if
'cancel button is pressed. If no entry made then exit sub.
If strempID = "" Or strempID = Empty Then
MsgBox "No Input Provided", vbInformation, "Required Data"
Exit Sub
ElseIf strempID = "ACXXXXXX" Then
'ACXXXXXX is for testing purposes
strInPasswd = InputBoxDK("Enter Password", "Restricted Form")
MsgBox strPass

Else

MsgBox "Sorry, you do not have access to this form", _
vbOKOnly, "Invalid User"

Exit Sub
End If


If strInPasswd = "" Or strInPasswd = Empty Then
MsgBox "No Input Provided", vbInformation, "Required Data"

Exit Sub
ElseIf strInPasswd = strPass Then
DoCmd.OpenForm "Maintenance Form", acNormal

Else
MsgBox "Incorrect Password!", _
vbOKOnly, "OOPS!"
Exit Sub
End If
Exit_cmdBtnMaintenance_Click:
Exit Sub

Err_cmdbtnMaintenance_Click:
MsgBox Err.Description
Resume Exit_cmdBtnMaintenance_Click

End Sub

 

[boblarson]

If your [Employee_ID] is text and not numeric then you would need to change it to:

.FindFirst "[Employee_ID] =" & Chr(34) & strempID & Chr(34)

 

[AC-Dev]

If your [Employee_ID] is text and not numeric then you would need to change it to:

.FindFirst "[Employee_ID] =" & Chr(34) & strempID & Chr(34)

I actually changed it to a DLookUp function and it works fine now.
But thanks