RecordSet Validation

benkingery

Registered User.
Local time
Today, 13:08
Joined
Jul 15, 2008
Messages
153
I am trying to implement a password protected timecard database and I am having a hard time validating the user and PIN Number on the clock in form. I have a table (Employee) containing Social Sec. Number (SSN, PK) and Pin number (PersonalPIN). Within my form, on the afterupdate portion of the pin number, I want to trigger a validation to query the employee table for a match between the SSN and PIN (both are unbound fields) they put in on the form. From is called TimeCard

here is what I have so far:

Code:
Private Sub PIN_AfterUpdate()

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection

Dim myRecordSet As ADODB.Recordset
myRecordSet.ActiveConnection = cnn1

Dim SQL As String
SQL = "SELECT Employee.SSN, Employee.PersonalPIN FROM Employee WHERE (((Employee.SSN)=" & SSN.text) AND ((Employee.PersonalPIN)=" & PIN.text))"

myRecordSet.Open (SQL)

'recordcount
If RecSet.RecordCount > 0 Then
    'user does exist in database
    MsgBox(I WILL CODE DESIRED FUNCTIONALITY HERE")
Else
    'user does not exist
    MsgBox("I WILL CODE DESIRED FUNCTIONALITY HERE")
End If


myRecordSet.Close
cnn1.Close
Set cnn1 = Nothing
Set myRecordSet = Nothing

End Sub

I am getting a sytax error on the SQL statement. Pretty sure I have the wrong syntax on the part where I'm trying to insert the values from the unbound text fields.

Thanks in advance for help.
 
Try using the WSL:

Code:
SQL = "SELECT Employee.SSN, Employee.PersonalPIN FROM Employee WHERE (((Employee.SSN)=" & Me.SSN & ") AND ((Employee.PersonalPIN)=" & Me.PIN & "))"

You code is assuming the the SSN and PIN are numberic data types.

If there are test then use:

Code:
SQL = "SELECT Employee.SSN, Employee.PersonalPIN FROM Employee WHERE (((Employee.SSN)=""" & Me.SSN & """) AND ((Employee.PersonalPIN)=""" & Me.PIN & """))"
 
Thanks for the suggestion. I tried option 1 and it has gotten rid of my syntax error.

Now I'm getting:

Run-time error '91':

"Object variable or With block variable not set"

When I Debug, it highlights the section of code that reads:

myRecordSet.ActiveConnection = cnn1

Any suggestions. With your updates, here is what I now have:

Code:
Private Sub PIN_AfterUpdate()

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection

Dim myRecordSet As ADODB.Recordset
myRecordSet.ActiveConnection = cnn1

Dim SQL As String
SQL = "SELECT Employee.SSN, Employee.PersonalPIN FROM Employee WHERE (((Employee.SSN)=" & Me.SSN & ") AND ((Employee.PersonalPIN)=" & Me.PIN & "))"

myRecordSet.Open (SQL)

'recordcount
If RecSet.RecordCount > 0 Then
    'user does exist in database
    MsgBox ("Please choose on of the folloiwng Time Card options")
    Me.Arrive.Visible = True
    Me.LunchOut.Visible = True
    Me.LunchIn.Visible = True
    Me.Depart.Visible = True
Else
    'user does not exist
    MsgBox ("Please check your Social Sec. Number and Password and try again")
    Me.Arrive.Visible = False
    Me.LunchOut.Visible = False
    Me.LunchIn.Visible = False
    Me.Depart.Visible = False
End If


myRecordSet.Close
cnn1.Close
Set cnn1 = Nothing
Set myRecordSet = Nothing

End Sub
 
Dim myRecordSet As New ADODB.Recordset
or
Dim myRecordSet As ADODB.Recordset
Set myRecordSet = New ADODB.Recordset
 
Thanks for the update. I appreciate it. I can't say I understand why, but it looks like I resolved that error at hand.

Now I'm getting another error:

Run-time error '-2147217904 (80040e10)':

No value given for one or more required parameters.

Any idea what that might be about?
 
You've resolved that first error, OK.
(What exactly did you do other than the suggested, and required, implementation?)

So you're still opening
SQL = "SELECT Employee.SSN, Employee.PersonalPIN FROM Employee WHERE (((Employee.SSN)=" & Me.SSN & ") AND ((Employee.PersonalPIN)=" & Me.PIN & "))"

Have you implemented Boyd's suggestion from earlier in this thread?

SQL = "SELECT Employee.SSN, Employee.PersonalPIN FROM Employee WHERE (((Employee.SSN)=""" & Me.SSN & """) AND ((Employee.PersonalPIN)=""" & Me.PIN & """))"

Or you could just have
SQL = "SELECT SSN, PersonalPIN FROM Employee WHERE SSN='" & Me.SSN & "' AND PersonalPIN='" & Me.PIN & "'"

That assumes that both SSN and PersonalPIN are text types.

Cheers.
 
Thank you Leigh for both of your suggestions. I added the 'New' to the syntax of ADODB connection and it resolved that particular error message.

I am still having problems with the syntax on the SQL portion of the recordset. I did try boyd's suggestion earlier I used both in his post for the SQL statement and both result in errors. I tried your SQL suggestion and now it looks like it is properly creating the Recordset, but it is not returning the correct results. I'm putting in a match for SSN and PIN from the data I've entered on Employee table, but the recordset count is coming back as ZERO, so its defaulting to my "Invalid SSN/PIN" message.

Here's what I have:

Code:
Private Sub PIN_AfterUpdate()
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection

Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn1

Dim SQL As String
SQL = "SELECT SSN, PersonalPIN FROM Employee WHERE SSN='" & Me.SSN & "' AND PersonalPIN='" & Me.PIN & "'"

myRecordSet.Open (SQL)

'recordcount
If myRecordSet.RecordCount > 0 Then
    'user does exist in database
    MsgBox ("Please choose on of the following Time Card options")
    Me.Arrive.Visible = True
    Me.LunchOut.Visible = True
    Me.LunchIn.Visible = True
    Me.Depart.Visible = True
Else
    'user does not exist
    MsgBox ("Please check your Social Sec. Number and Password and try again")
    Me.Arrive.Visible = False
    Me.LunchOut.Visible = False
    Me.LunchIn.Visible = False
    Me.Depart.Visible = False
End If


myRecordSet.Close
cnn1.Close
Set cnn1 = Nothing
Set myRecordSet = Nothing

End Sub

If my SSN field is Text, and all that is contained is 9 numbers, will that matter?
 
That the text contains only numeric characters won't matter at all.
Before your line
myRecordSet.Open SQL

(you shouldn't have the brackets in it - they're unrequired)
put the command
Debug.Print SQL

And then look to the Immediate Window (Ctrl-G) and copy that SQL statement and place it into the SQL view of a new query.
Execute that and see what the results are.

Cheers.
 
Thanks for update again. I did as you mentioned and copied the SQL from the Immediate window and pasted into SQL View of a new query.

The results are exactly as I expected them to be....... 1 record and 1 record only.....

Yet, the recordset test logic is giving me the result as if the record count of the record set is 0, not 1.....

Frustrated!
 
Code:
SELECT SSN, PersonalPIN FROM Employee WHERE SSN='123456789' AND PersonalPIN='1234'


Obviously just test data, but there is a user on the employee table with SSN 123456789 and PIN 1234.

When I put that SQL into the SQL view of a new Query, I get 1 record, exactly as i would have expected.
 
From VBA Help:

The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source.

Syntax for the Open recordset:
recordset.Open Source, ActiveConnection, CursorType, LockType, Options


When opening a ado recordset, the default for cursor is a Forward-Only. So you will need to specify the Cursor Type when you open up the recordset.

Search VBA help using the keyword recordset. There is a topic on Ado recordsets that give you details on the different CursorTypes
 
You've only been using the recordcount to determine if there are any records?
Not actually testing to see if there's data present?

Scooterbug has since jumped in with an explanation for you that explains why that's a bad idea.
 
You've nailed it Scooterbug. Thank you. I changed the cursor type to Keyset. I obviously am out of my realm of knowledge here, so please let me know if you think I've done something that you think is NOT a good idea.

To answer your question Purvis, I am ONLY using this recordset to find an exact match with what the user enters. For example if user enters their Social Sec. Number and a password that does not match, then there won't be a record to match that in the Recordset because of the parameter values I've included. There will always only be 1 match for a social security number and password, so I figured this was a good authentication.

Its still probably not the best, but we're a small company (less than 100 employees) and there isn't any sensitive data behind this authentication, just the ability to clock in or out.

Thanks all for your help.
 
I just mean you were putting all your eggs in one basket (the RecordCount basket).
I hadn't appreciated that was your only determination that there were no records.

Actually testing for data (handling any raised error) or checking BOF and EOF properties will work regardless of the cursor type.
e.g.
If Not (myRecordSet.BOF AND myRecordSet.EOF) Then

That would allow you to keep the slightly more efficient ForwardOnly cursor, were you so inclined.
 
The other option is to pull the SSN and PIN number with a recordset based on the UserID. You can then compare them to each other and if one is wrong you can alert the user to what it is.
 

Users who are viewing this thread

Back
Top Bottom