ADO error. . . simple recordset . . . Error -2147217904

[sportsguy]

I am not sure which required field is missing, can anyone make an observation which I cannot seem to see. The point of the code is to verify the computer logon name against a predetermined list of logon names before continuing. The approved logon names are in a table, which is selected in a drop down box by sales district number, there will be only one record returned.

thanks in advance

Private Sub Continue_Click()

Dim strUserName As String
Dim anyLogon As String
Dim Validated As Boolean

On Error GoTo Err_Continue

strUserName = ap_GetUserName()

Dim cnnLocal As New ADODB.Connection
Set cnnLocal = CurrentProject.Connection
Dim rstCurr As New ADODB.Recordset

rstCurr.Open "SELECT tblDISTRICTS.logon FROM tblDISTRICTS WHERE (((tblDISTRICTS.strDistrict)=Forms!setup.cboxDistrict));", cnnLocal, adOpenForwardOnly, adLockReadOnly, adCmdText

anyLogon = rstCurr.GetString(, , , , "Null")

Debug.Print "Logon name = " & anyLogon

If anyLogon = strUserName Then
    Validated = True
Else
    Validated = False
End If

rstCurr.Close
cnnLocal.Close
Set cnnLocal = Nothing
Set rstCurr = Nothing

        
' Validation Sequence
If Validated = True Or strUserName = "adminuser" Then
    GoTo OKtoLoad
Else
    MsgBox "Please Contact your NSSM to Continue", vbCritical, "VALIDATION ERROR!"
End If
        
GoTo Exit_Continue
        
        
OKtoLoad:

Debug.Print "OktoLoad"

Exit_Continue:
 Exit sub

Err_Continue:
error coding

End Sub

 

[sportsguy]

Evidently, a form control value can not be used in the where clause of a query executed by ADO. . . i find that strange, but that is the error, or I have written the sql code wrong. . .

so I have to either execute an existing access query, or scroll through the record set to find the record. Scrolling through 150 records is simple enough.

Private Sub Continue_Click()

Dim Validated As Boolean
Dim strUserName As String
Dim cn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim rsField As ADODB.Field

On Error GoTo Err_Continue

strUserName = ap_GetUserName()
Validated = False

Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset

rs.Open "SELECT tblDISTRICTS.logon FROM tblDISTRICTS;", cn, adOpenForwardOnly, adLockReadOnly

While Validated = False Or Not rs.EOF
    For Each rsField In rs.Fields
        If rsField.Value = strUserName Then
            Debug.Print rsField.Value & " - " & strUserName
            Validated = True
            rs.MoveLast
        End If
    Next
    rs.MoveNext
Wend

rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
Set rsField = Nothing
        
' Validation Sequence
If strUserName = "admin" Then
    GoTo OKtoLoad
ElseIf Validated = False Then
    MsgBox "Please Contact your NSSM to Continue", vbCritical, "VALIDATION ERROR!"
    GoTo Exit_Continue
End If

OKtoLoad:

took me all day, but "finance Wiz, Access HACK!"

sportsguy

 

[boblarson]

Well, actually for one you need to separate the control from the string to return the value:

Instead of this:

"SELECT tblDISTRICTS.logon FROM tblDISTRICTS WHERE (((tblDISTRICTS.strDistrict)=Forms!setup.cboxDistrict));", cnnLocal, adOpenForwardOnly, adLockReadOnly, adCmdText

You need this:

"SELECT tblDISTRICTS.logon FROM tblDISTRICTS WHERE (((tblDISTRICTS.strDistrict)=" & Forms!setup.cboxDistrict & "));", cnnLocal, adOpenForwardOnly, adLockReadOnly, adCmdText

and if District is a string then you need

"SELECT tblDISTRICTS.logon FROM tblDISTRICTS WHERE (((tblDISTRICTS.strDistrict)=" & Chr(34) & Forms!setup.cboxDistrict & Chr(34) & "));", cnnLocal, adOpenForwardOnly, adLockReadOnly, adCmdText

 

[boblarson]

But you might have to assign all of that to a string variable first and then use it in the Open command.

 

[LPurvis]

Bob's given you the answer to work with your SQL statement.
You shouldn't have any need of a dediated string for the SQL statement unless you want one for readability (a fine reason in itself of course!)

To explicitly address your concern of
>> "Evidently, a form control value can not be used in the where clause of a query executed by ADO"
It's discussed in this thread.

Cheers.

 

[sportsguy]

"SELECT tblDISTRICTS.logon FROM tblDISTRICTS WHERE (((tblDISTRICTS.strDistrict)=" & Forms!setup.cboxDistrict & "));", cnnLocal, adOpenForwardOnly, adLockReadOnly, adCmdText

DUH! gawd am i slow. . . i haven't done any in depth coding for about 3 years. . i forget so quickly!

thank you Bob larson!

sportsguy