Log in form to validate access to my Microsoft DB

[Bre035]

I wish to create a log in form to validate access to my Microsoft DB. I have created the TBL: Analyst with the following fields:
RACIF ID

I only want the user to enter their RACIF ID then press Enter to proceed. IF they exist in table then the main form will display if not then a system message will display.

[patjones]

Hi -

There are a few ways you could do this, but the simplest is probably to use the DLookup function to determine if the ID exists. If DLookup returns null, the ID doesn't exist in your table and you can display your error message. If DLookup returns not null, then you can open up your main form.

Pat

[Bre035]

I tried several versions of Dlookup without success.

[patjones]

If you can post the code that you tried so far then that would help me figure out where you're going wrong. DLookup has some pretty specific syntax. Thanks.

Pat

[Bre035]

Private Sub Command3_Click()


End Sub

Private Sub Detail_Click()

End Sub

Private Sub RACIF_ID_AfterUpdate()

RACIF_ID = DLookup(RACIF_ID, TBL: ANALYST, [RACIF_ID]= & RACIF_ID)

End Sub

[patjones]

Bre -

The arguments in DLookup are strings...

DLookup("RACIF_ID", "TBL: ANALYST", "[RACIF_ID] = " & Me.RACIF_ID.Value)

You want to have a conditional that does something like

If IsNull(DLookup("RACIF_ID", "TBL: ANALYST", "[RACIF_ID] = " & Me.RACIF_ID.Value)) Then
     MsgBox "No such user ID. Try again.", vbExclamation+vbOKOnly, "Log In Error"
     Exit Sub
Else
     'Close log-in form and open main form here
End If

I'm assuming that RACIF_ID is a number; but if it's a text value, the criteria needs to be modified as "[RACIF_ID] = '" & Me.RACIF_ID & "'". You might also want to modify the name of the text box on your log-in form since it makes the criteria argument in DLookup a little confusing.

Pat

[Bre035]

Thank you. I got it to work.

[Bre035]

Here is the code I used but I had to tweek some other properties on my text boxes:
Option Compare Database

Private Sub Enter_Click()
On Error GoTo Err_Enter_Click


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_login2"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_Enter_Click:
Exit Sub

Err_Enter_Click:
MsgBox Err.Description
Resume Exit_Enter_Click




End Sub


Private Sub asdfEnter_Click()
On Error GoTo Err_asdfEnter_Click

DoCmd.Minimize

Dim stDocName As String
Dim stLinkCriteria As String

Me.txtLoginCount = DLookup("racf_id", "tbl_racfid", "racf_id=Login_RacfID")

If IsNull(Me.txtLoginCount.Value) Then
MsgBox ("Not a valid Racf_ID")
Else
stDocName = "frm_login2"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_asdfEnter_Click:
Exit Sub

Err_asdfEnter_Click:
MsgBox Err.Description
Resume Exit_asdfEnter_Click

UCase (Me.Login_RacfID)

End Sub

Private Sub Form_Load()
'Me.txtLoginCount.Value = DLookup("LoginCount", "qry_getLogin_Count")
End Sub