New Login questiion

[John Petty]

I have created a login form in which the user has to type in their company ID
and password to allow access to database manipulation (other than readonly).
This part works great. But I am having an issue with a certain
textbox_afterupdate. After the user types in their ID, I want to have a
"Name" textbox verify that it is indeed the intended user (or if not found,
note it in a msgbox and allow to create new as needed).

The problem is that I don't quite know how to pull the data from a table
record.

Any help would be appreciated.

Thanks in advance,

John Petty

 

[Bob Quintal]

Sorry, Here's the code.

Private Sub tbBadge_AfterUpdate()

If IsNull(Me.tbBadge) Or Me.tbBadge = "" Then
MsgBox "You must enter a Badge Number", vbOKOnly, "Required
Data" Me.tbBadge.SetFocus
Exit Sub
Else
SELECT DISTINCT FirstName, LastName
FROM tblEmp
WHERE .BadgeNumber = Me.tbBadge.Value

Me.tbName.Value = tblEmp.FirstName & " " & tblEmp.LastName


Me.tbPassword.SetFocus
End Sub

Also, I get a "Case" error on the SELECT Statement.. ???

Visual Basic is not SQL, they are two different languages.
Modify your code as follows.

Private Sub tbBadge_AfterUpdate()
Dim strSQL as string
dim RS as recordset

If IsNull(Me.tbBadge) Or Me.tbBadge = "" Then
MsgBox "You must enter a Badge Number", vbOKOnly, "Required
Data"
Me.tbBadge.SetFocus
Exit Sub
Else
'Put the SQL into a VB variable
strSQL = "SELECT DISTINCT FirstName, LastName " _
& "FROM tblEmp " _
& "WHERE .BadgeNumber = " & Me.tbBadge.Value

'now open the recordset
set RS = currentdb.openrecordset(strSQL)
'you need to test that the recordset returns a value.
if RS.EOF
'do whatever if there is no name.
else
Me.tbName.Value = RS!FirstName & " " & RS!LastName
end if

RS.Close

Me.tbPassword.SetFocus

End Sub

 

[John Petty]

I have created a login form in which the user has to type in their company ID
and password to allow access to database manipulation (other than readonly).
This part works great. But I am having an issue with a certain
textbox_afterupdate. After the user types in their ID, I want to have a
"Name" textbox verify that it is indeed the intended user (or if not found,
note it in a msgbox and allow to create new as needed).

The problem is that I don't quite know how to pull the data from a table
record.

Any help would be appreciated.

Thanks in advance,

John Petty

Sorry, Here's the code.

Private Sub tbBadge_AfterUpdate()

If IsNull(Me.tbBadge) Or Me.tbBadge = "" Then
MsgBox "You must enter a Badge Number", vbOKOnly, "Required Data"
Me.tbBadge.SetFocus
Exit Sub
Else
SELECT DISTINCT FirstName, LastName
FROM tblEmp
WHERE .BadgeNumber = Me.tbBadge.Value

Me.tbName.Value = tblEmp.FirstName & " " & tblEmp.LastName


Me.tbPassword.SetFocus
End Sub

Also, I get a "Case" error on the SELECT Statement.. ???

 

[John Petty]

Thanks Bob,

I do seem to be getting a type mismatch error around to "Set RS" code. No
quite sure where. The only thing I can think of would be in the strSQL
statement. The string is fine but the badgenumber is set to string and the
tbBadge.value is an Integer, but the "Str" function isn't helping. Any ideas?

 

[John Petty]

I was wrong. The tbBadge is a string. Duh :>)

Still can't find the issue though.

 

[Douglas J. Steele]

To eliminate the type mismatch, change

dim RS as recordset

to

Dim RS as DAO.Recordset

If that raises a different error about user type not found, then you must
not have a reference set to DAO (by default, neither Access 2000 nor Access
2002 have the reference set). While in the VB Editor, select Tools |
References from the menu bar, scroll through the list of available
references until you find the reference for Microsoft DAO 3.6 Object
Library, select it, and back out of the dialog.

Note that you can eliminate the need for a recordset by using DLookup: it's
actually capable of returning more than one value.

Private Sub tbBadge_AfterUpdate()

If Len(Me.tbBadge & vbNullString) = 0 Then
MsgBox "You must enter a Badge Number", _
vbOKOnly, "Required Data"
Me.tbBadge.SetFocus
Exit Sub
Else
Me.tbName.Value = DLookup( _
"[FirstName] + ' ' + [LastName]", _
"[tblEmp]", _
"[BadgeNumber] = " & Me.tbBadge.Value)
End If

Me.tbPassword.SetFocus

End Sub

 

[Bob Quintal]

I was wrong. The tbBadge is a string. Duh :>)

Still can't find the issue though.

since badgenumber is a string, you need to add quotes into the sql
statement, around the value you are trying to pass.

using single quotes:
& "WHERE .BadgeNumber = '" & Me.tbBadge.Value & "'"
or using double quotes
& "WHERE .BadgeNumber = """ & Me.tbBadge.Value & """"

You dont need the Str() function as the conversion is implicit,
because the concatenation operator (&) does this automatically.

Hope that helps.


Q