lookup in form but not change the record on the table.

[Chekotah]

I have a form that has employee ID, employee name and Password. I am trying to get it to look at the table and enter the employee name based off the employee id table without changing the record or adding a new record to it. this is being used for the employee to have access the program. I am using this for the first level for the security and user interface to the database.

[John_G]

Hi -

What I suggest is that your login form not be bound to a table or query, and that you use VBA to query the employee table to verify or validate what is entered on the form. The code can also populate other text boxes on the form.

HTH

John

[Chekotah]

Hi John,

The form isn't bound to a table. Can you assist me in writing the code. I am by no means a code writer. however, throughout this project I am learning as I go.

Thanks
Chekotah

[John_G]

Here are a couple of ideas.

First, put a command button on your form (Labeling it "Login" would be ideal). The checking you need to do could go in the "On Click" event of the button.

Code might be something linke this:

Dim UserID as String   ' could also be numeric
'
'  Check that user ID and password are both filled in
'
If isnull(me!username) or isnull(me!PW) then
  msgbox "Both user ID and password must be specified"
  exit sub
endif
'
' Is user ID valid (does it exist)?
'
if dcount("*","Employee","ID = '" & me!username & "'") = 0 then
  msgbox "User ID '" & UserID & "' was not found"
  exit sub
endif
'
' User ID exists - is the supplied password correct?
'
if Dlookup("CurrentPW","Employee","ID = '" & me!username & "'") <> me!PW then
  msgbox "Incorrect password supplied for User ID '" & me!username & "'"
  exit sub
endif
'
' Credentials are ok  - retrieve employee name
'
me!Employeename = Dlookup("EmployeeName","Employee","ID = '" & me!username & "'")
'
'  Then would follow whatever else you want to to as part of the login procedure
'
Exit Sub

That is a very basic procedure, but it should help you get started. One thing you might do is close the login form and open the application main form, if all the validation checks are passed.

HTH

John