By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,259 Members | 1,680 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,259 IT Pros & Developers. It's quick & easy.

Compare and Return Record

P: 1
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...these 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!
Feb 13 '08 #1
Share this Question
Share on Google+
2 Replies


P: 22
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.ListCount
Do Until a = 0
Combobox.RemoveItem (a - 1)
a = a - 1
Loop
rs.Open "SELECT NAME,PASSWORD FROM LOGIN_TEST ", Cnt, adOpenKeyset, adLockOptimistic
Do Until rs.EOF
Combobox.AddItem (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.SetFocus
Exit Sub
End If

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

'If IsNull(Text6.Value) Or Text6 = "" Then
' MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
' Me.Text6.SetFocus
' 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, adLockOptimistic
If Not rs.EOF Then
'Close logon form and open Menu screen
User1 = Combobox
DoCmd.Close acForm, "loginscreen_test1", acSaveNo
DoCmd.OpenForm "MAIN_MENU"

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

Application.Quit

End
Else

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

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

[Code ends here]
Feb 13 '08 #2

MindBender77
100+
P: 234
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
Feb 13 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.