Open a Recordset-parameter query (Error 424)

H

hall95213

Registered User.
Local time
Today, 15:37
Joined
Mar 18, 2013
Messages
12
Why do I get an error on last line?

Function UserPasswordCk(strUser As String, strPassword As String, strMachName As String, strUserName As String)

Dim qdf As DAO.QueryDef
Set db = CurrentDb

'Get the parameter query (This line results in 424 error "Object Required")
Set qdf = dbs.QueryDefs("SltUser")
 
...because you assign CurrentDb to a variable named db, and then you try to use the QueryDefs collection of a different, undefined variable named dbs. What you need to do is "require variable declaration." I'm sure if you search this forum or Google you'll get a bunch of useful info.

Cheers,
 
I appreciate your help. In tinkering with it I may have created a different problem? Complete function below. When I try to execute Access can't find the function.

Private Function UserPasswordCk(strUser As String, strPassword As String, strMachName As String, strUserName As String) As Variant
Dim rst As DAO.Recordset, rsq As DAO.Recordset
Dim Temp As Variant
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

DoCmd.OpenQuery ("UpdUserMachName")
DoCmd.OpenQuery ("UpdUserUserName")

Set dbs = CurrentDb
'Set rsq = dbs.OpenRecordset("SltUser", dbOpenDynaset)

'Get the parameter query
Set qdf = dbs.QueryDefs("SltUser")

'Supply the parameter value
qdf.Parameters("EnterLoginUser") = strUser
qdf.Parameters("EnterLoginPassword") = strPassword
qdf.Parameters("EnterMachName") = strMachName
qdf.Parameters("EnterUserName") = strUserName

'Open a Recordset based on the parameter query
Set rsq = qdf.OpenRecordset()

With rsq
.FindFirst "CCodeGrp = User"
If .NoMatch Then
MsgBox ("User Name & Password combination invalid")
.MoveFirst
Else
strRights = Rights
If strRights = "Z" Then
DoCmd.OpenForm FrmPers
DoCmd.OpenForm FrmGlobalDeduct
DoCmd.OpenForm FrmVendorData
DoCmd.OpenForm FrmPayProcess
DoCmd.OpenForm FrmUser
ElseIf strRights = "F" Then
DoCmd.OpenForm FrmPers
DoCmd.OpenForm FrmVendorData
ElseIf strRights = "P" Then
DoCmd.OpenForm FrmGlobalDeduct
DoCmd.OpenForm FrmPayProcess
DoCmd.OpenForm FrmVendorData
End If
End If
End With
End Function
 
So how do you call the function? And what error do you get?
 
I have a button on a form. I try to execute "on click" event. I'm using Access 2010.

On Click: UserPasswordCk([Forms]![FrmLogin]![LoginUser], [Forms]![FrmLogin]![LoginPassword], [Forms]![FrmLogin]![MachName], [Forms]![FrmLogin]![UserName])
 
Are you handling the button click in VBA? If so, can you post that code? Also, you say you are getting an error. What is the error?
Also, what type of module is this function in? Microsoft Access Class Object (so a form or a report), Module, or Class Module?
Cheers,
 
I've used Access for years, but rarely do functions. I believe the function is a module function. When the button is clicked I execute a macro that "Run code": UserPasswordCk([Forms]![FrmLogin]![LoginUser],[Forms]![FrmLogin]![LoginPassword],[Forms]![FrmLogin]![MachName],([Forms]![FrmLogin]![UserName])

The error I get from this is 3021.

Thanks for your assistance!
 
You have not tested if there are any records, if there are no records the FindFirst will surely fail.. as the recordset is empty.. Try this code..
Code: 
Private Function UserPasswordCk(strUser As String, strPassword As String, strMachName As String, strUserName As String) As Variant
    Dim rst As DAO.Recordset, rsq As DAO.Recordset
    Dim Temp As Variant
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef

    DoCmd.OpenQuery ("UpdUserMachName")
    DoCmd.OpenQuery ("UpdUserUserName")

    Set dbs = CurrentDb
    'Set rsq = dbs.OpenRecordset("SltUser", dbOpenDynaset)

    'Get the parameter query
    Set qdf = dbs.QueryDefs("SltUser")

    'Supply the parameter value
    qdf.Parameters("EnterLoginUser") = strUser
    qdf.Parameters("EnterLoginPassword") = strPassword
    qdf.Parameters("EnterMachName") = strMachName
    qdf.Parameters("EnterUserName") = strUserName

    'Open a Recordset based on the parameter query
    Set rsq = qdf.OpenRecordset()
    [COLOR=Red][B]If rsq.RecordCount <> 0 Then[/B][/COLOR]
        With rsq
            .FindFirst "CCodeGrp = User"
            If .NoMatch Then
                MsgBox ("User Name & Password combination invalid")
                .MoveFirst
            Else
                strRights = [COLOR=Blue][B]Rights[/B][/COLOR]
                If strRights = "Z" Then
                    DoCmd.OpenForm FrmPers
                    DoCmd.OpenForm FrmGlobalDeduct
                    DoCmd.OpenForm FrmVendorData
                    DoCmd.OpenForm FrmPayProcess
                    DoCmd.OpenForm FrmUser
                ElseIf strRights = "F" Then
                    DoCmd.OpenForm FrmPers
                    DoCmd.OpenForm FrmVendorData
                ElseIf strRights = "P" Then
                    DoCmd.OpenForm FrmGlobalDeduct
                    DoCmd.OpenForm FrmPayProcess
                    DoCmd.OpenForm FrmVendorData
                End If
            End If
        End With
    [COLOR=Red][B]End If[/B][/COLOR]
End Function
Also, What is Rights ?? I cannot see any variable in the code..
 
I changed the code, and the error I get is 31005.

"Rights" is a field within the "SltUser" query.
 
I am not sure providing just the error number would be any help.. What is the Error description? At which line is the error highlighted, when you click Debug?

If Rights is the Field in the Query you should access it by using..
Code: 
rsq!Rights
[COLOR=Green][B]' Or[/B][/COLOR]
rsq.Fields("Rights")
 
The function executes the 2 "Upd" queries, but then bombs.

I get a "Macro Single Step" box, and the only button available is
"Stop All Macros". It doesn't display a line of code causing error??

In "Arguments" box it displays:

UserPasswordCk([Forms]![FrmLogin]![LoginUser], etc.

Do I have right syntax for sending objects from form to the function?

The objects being sent are unbound text boxes.
 
I would suggest you move this code into the VBA Editor.. The VBA codes is not complicated at all..

attachment.php
attachment.php


Between the Click() and End Sub just use..
Code: 
Private yourButtonName_Click()
    Call UserPasswordCk(Me.[LoginUser], Me.[LoginPassword], Me.[MachName], Me.[UserName])
End Sub
When you use this, you will be able to Debug the error..
 

Attachments

  • attachment.jpg
    attachment.jpg
    70.6 KB · Views: 2,992
Paul,

I should have warned you, and Lagbolt that I'm a Beancounter that had not attempted a function in quite some time. That last change allowed me to correct a couple of syntax errors.

e.g.
"CCodeGrp = User" Changed to "CCodeGrp = 'User'"

Also, I was opening forms, and the form names were not enclosed in quotes.

MUCH Thanks to both of you guys!!! It works exactly as "planned".
 
Ha ha.. not sure how did we not see that.. :D

Glad to hear you have it sorted.. Good Luck..
 
You must log in or register to reply here.

Similar threads

N
Solved Receiving Emails in Access Database using CDO 2000 library
Replies
10
Views
560
sonic8
S
S
Solved Second SQl delete query raises runtime error 424
Replies
8
Views
364
Pat Hartman
P
N
How to ensure the open recordset include also any new record entered
Replies
9
Views
717
nector
N
N
VBA MS Access Error Handling
Replies
5
Views
808
Pat Hartman
P
N
Solved How to grab the last updated record in a table
Replies
10
Views
818
June7
June7

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom