Compare and Return Record

[atrain]

Hi, I understand that MS Access is not really meant for multi-users if robust security is the ultimate concern...but I'm trying to create a work-around where a user logs in and is prompted to enter two parameters to pull up their individual record. For example, they would put in an ID number and a Password...thes e two parameters are used to lookup in a table the exact record that matches both the ID number and password and returns information associated with that record.

Can someone help get me started on the VBA code that would do that?

So, the user would enter an ID Number and a Password in a form, then click a button to retrieve specific fields for 1 record in the table that matches.

Thanks!

[jaishu]

For creating a form, you can goto Design view in form and create your own text boxes(assuming you are new to Ms Access) and other controls , and on right click of each control you have properties events associated, you can chose on which event, you want to associate controls to, eg, ; on CLICK of a command button..please see below code, i did this for a login screen,
Where user enters, both username and password to goto next screen.(putall that info in a table)
I have just given an example - i have used Username selection in combobox rather than textbox,.hope this helps..

[code goes here]....

Private Sub Form_Load()
Dim a As Integer
DBconnect

a = Combobox.ListCo unt
Do Until a = 0
Combobox.Remove Item (a - 1)
a = a - 1
Loop
rs.Open "SELECT NAME,PASSWORD FROM LOGIN_TEST ", Cnt, adOpenKeyset, adLockOptimisti c
Do Until rs.EOF
Combobox.AddIte m (rs("NAME"))
rs.MoveNext
Loop
rs.Close

End Sub

Private Sub enter_click()
On Error GoTo ErrorHandler


'Check to see if data is entered into the UserName combo box
If IsNull(Combobox ) Or Me.Combobox = "" Then
MsgBox "You must select a User Name.", vbOKOnly, "Required Data"
Me.Combobox.Set Focus
Exit Sub
End If

'Check to see if data is entered into the Passcode box

'If IsNull(Text6.Va lue) Or Text6 = "" Then
' MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
' Me.Text6.SetFoc us
' Exit Sub
'End If

'Check value of Passcode in Table login_test1 to see if this
'matches value chosen in combo box

querylogin = "select * from login_test where name='" & Combobox & "' and password = ' "& Text6 &"'"
rs.Open querylogin, Cnt, adOpenStatic, adLockOptimisti c
If Not rs.EOF Then
'Close logon form and open Menu screen
User1 = Combobox
DoCmd.Close acForm, "loginscreen_te st1", acSaveNo
DoCmd.OpenForm "MAIN_MENU"

Else
'If User Enters incorrect Password more than 3 times Application will close
intLogonAttempt s = intLogonAttempt s + 1
If intLogonAttempt s >= 4 Then
MsgBox "You have exceeded the number of attempts,Applic ation will exit", _
'vbCritical, "Restricted Access!"

Application.Qui t

End
Else

MsgBox "Passcode Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.Text6.SetFoc us

End If
End If
rs.Close
ErrorHandlerExi t:
Exit Sub
ErrorHandler:
If Err.Number = 2001 Then
Resume Next
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExi t
End If
End Sub

[Code ends here]

[MindBender77]

So, the user would enter an ID Number and a Password in a form, then click a button to retrieve specific fields for 1 record in the table that matches.

Thanks!

How were you going to display their specific data?

This could be done using a subform were the record source is a query that selects user data where the criteria is their ID Number and Password that was enter on the Main Form.

Hope this Points you in the right directions,
Bender