using a pin

[echo0001]

Hello,

I have a textbox "Text2" with a 4 digi pin typed into it, when i click on a command botton i want it to check table "Admins" for that pin in field "Pin_".

If it is the correct pin, it will then fill in another table "Access History" with the Date, time and Name of admin in the fields "Date_", "Time_", "Name_".

The Table "Admins" has the following fields "Name_" and "Pin_"

I have spent over 7 hours trying to do this and got now where, i think im missing something very simple.

Cheers

 

[vbaInet]

Here's some aircode:

Dim rs as dao.recordset

set rs = currentdb.openrecordset("Select * From [Admins] Where [Pin_] = " & Me![Pin_] & ";", dbopensnapshot)

with rs
     If .recordcount <> 0 then
          Msgbox "Pin was found"
     Else
          Msgbox "Pin was not found"
     end if
end with

set rs = nothing

But the case of duplicating data is not good database design.

 

[echo0001]

Just a Note, Im not duplicating any data.

Also im getting an invalid argument error, on the Set rs = CurrentDb.Op..... line. i have spent a good 2 hours trying to get it to work with no luck.

 

[vbaInet]

So what your'e doing is like a log?

Try this one:

set rs = currentdb.openrecordset("Select * From [Admins] Where [Pin_] = " & Me![Pin_], dbOpenDynaset)

 

[DCrake]

What is the actual code you are using?
Can we sit it?

 

[echo0001]

This will basically be access control in to the database using pins to ID the admin and let them in also at the same time create a history of the logins, Im just working on the History log right now.

Well i have been deleting it over and over and starting again each time. I have just got your code right now.


Private Sub Command20_Click()

Dim rs As dao.Recordset

Set rs = CurrentDb.OpenRecordset("Select Admins.Pin_, Admins.Name_ From [Admins] Where [Pin_] = " & Me![Text2], dbOpenDynaset)

With rs
If .RecordCount <> 0 Then
'still need to write the next code just wanted to get the bit above working first
Else

End If
End With

Set rs = Nothing

End Sub

 

[vbaInet]

OK, you didn't mention the current state?

 

[echo0001]

I will upload a copy and then i think things will become clear.

 

[echo0001]

Put in 1217 as a Pin on the access control form then click enter

 

[vbaInet]

Just an explanation of what is happening now will do. This is a simple matter that doesn't require seeing your db.

 

[echo0001]

I have got this code now,

It is saying to few parameters expected = 1



Private Sub Command20_Click()

Dim rs As dao.Recordset

Set rs = CurrentDb.OpenRecordset("Select [Admins].[Pin_], [Admins].[Name_] From [Admins] Where [Pin_] = (Me.[Text2]);", dbOpenDynaset)

With rs
If .RecordCount <> 0 Then
'still need to write the next code just wanted to get the bit above working first
Else

End If
End With

Set rs = Nothing

End Sub

 

[vbaInet]

What is the datatype of Pin_?

 

[echo0001]

What is the datatype of Pin_?

Text
Field Size = 4
Input mask = Password
Default Value = "0000"
Validation Rule = Between 0 And 9999

Didnt know if the extra stuff above would help

 

[vbaInet]

In that case you need to wrap the input in quotes (as highlighted):

set rs = currentdb.openrecordset("Select * From [Admins] Where [Pin_] = [B][COLOR=Red]'[/COLOR][/B]" & Me![Pin_] & "[B][COLOR=Red]'[/COLOR][/B];", dbOpenSnapshot)

The extra stuff wasn't needed

 

[echo0001]

Ok its working, thank you mate

 

[vbaInet]

So now you can get the Name_ field from the Admin table using rs![Name_] or ![Name_]

You could have also used a DCount() to check and then a DLookup() to get the value.

 

[DCrake]

Humour me and explain why your fields end with an underscore?

 

[echo0001]

Humour me and explain why your fields end with an underscore?

word such as Date, Time, Name are special words and are used in some way by access to save any trouble with this i just put an underscore after each word, once i did it with date on another table i just kept it the same.