Access Locks up on this code...

[MkIIISupra]

I created the code below for a function that I am building up to but I can't get to the next segment because the silly thing Locks up Access! What I have is a table with 3 fields and then I have a form that is supported by the table. On the form are 2 Unbound Text boxes used to recieve input.

txtPassWord
txtMbrName

The table – tblPassFail has three fields –
passWord
mbrName
accessLvl

As you can see in the code below I am doing a simple If statement to compare the Unbound Text Fields with the Table Fields. It works on the first record but I can’t seem to get the silly thing to loop through and end the way I want it too. I got some of the code ideas from the BOF, EOF Properties Example (Microsoft Access) help file. I am using MS Access 97 on a WinNT 4.0 Svce pack 6+?.

Private Sub btnSubmit_Click()
On Error GoTo Err_btnSubmit_Click
' Database and Record variables
Dim dbs As Database
Dim rst As Recordset
' Set the Database and Recordset variables active to current tblPassFail
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblPassFail&quot

' Find Info in the system done with a loop
Do Until rst.EOF
If (Me.txtPassWord) = Me.passWord Then
If (Me.txtMbrName) = Me.mbrName Then
lblMessage.Caption = Me.passWord & " " & Me.mbrName
Else
rst.MoveNext
End If
lblMessage.Caption = "You are not authorized access to this application."
End If
Loop

Exit_btnSubmit_Click:
Exit Sub

Err_btnSubmit_Click:
MsgBox Err.Description
Resume Exit_btnSubmit_Click

End Sub

Again many Thanks!
GSM1(SW)J. T. McDonald USN One by one the penguins steal my sanity!

 

[FancyPrairie]

I see a few thing that look unusal to me. First, I don't see where you are comparing the values on the form (Me) with the values in the recordset (rst). Also, not sure what you are trying to do with your If...Then statement. But this is what I think you want.

Do Until rst.EOF
If (Me.txtPassWord) = rst!Password Then
If (Me.txtMbrName) = rst!mbrName Then
lblMessage.Caption = rst!Password & " " & rst!mbrName
End If
Else
lblMessage.Caption = "You are not authorized access to this application."
End If
rst.MoveNext
Loop

 

[TrojanRabbit]

It will only move through the recordset when password and mbrname don't match. If they do match, you have an infinite loop going. Rather than looping through the table looking for a match, you ought to use FindFirst.

rst.FindFirst "passWord = '" & Me.txtPassWord & "' AND mbrName = '" & _ Me.txtMbrName & "'"
If rst.NoMatch Then
lblMessage.Caption = "You are not authorized access to this application."
Else
lblMessage.Caption = rst!passWord & " " & rst!mbrName
End If

If txtPassWord or txtMbrName includes ', then you would need to use "" instead of ' in the code.

 

[MkIIISupra]

FancyPrairie (Programmer) - Yours seems to work, now I am able to continue! Thank you!!! One question, could you explain the difference between the ! and . in VB / VBA?
One by one the penguins steal my sanity!

 

[TrojanRabbit]

! and . can be tought to explain. Generally, ! is used for members of a collection (e.g., Forms!frmName; tblname!fieldname). . is used for properties/procedures in an object (e.g., tbl.properties). Another way to put it is, ! for collection members, . for everything else. Best explanation I can give.