RE: New Login questiion

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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?



"Bob Quintal" wrote:

=?Utf-8?B?Sm9obiBQZXR0eQ==?= <JohnPetty@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote in news:440A0A02-7204-456B-A781-A36C0FCAAE69@xxxxxxxxxxxxx:



"John Petty" wrote:

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.. ???


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


--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com


.

Relevant Pages

  • RE: New Login questiion
    ... "John Petty" wrote: ... Private Sub tbBadge_AfterUpdate ... MsgBox "You must enter a Badge Number", vbOKOnly, "Required ... 'now open the recordset ...
    (microsoft.public.access.formscoding)
  • Error 429 when opening a disconnected recordset
    ... The following code creates and opens a disconnected ADODB.Recordset. ... Private Sub pTest1() ... Dim rs As Recordset ... MsgBox Err.Description & vbCrLf & Err.Number, ...
    (microsoft.public.data.ado)
  • Error 429 when opening a disconnected recordset
    ... The following code creates and opens a disconnected recordset. ... This has happend on Win ME and on Win XP. ... Private Sub pTest1() ... MsgBox Err.Description & vbCrLf & Err.Number, ...
    (microsoft.public.vb.database.ado)
  • Re: Closing a form automatically if no records
    ... : Private Sub Form_Load ... why isn't Recordset itself good enough here? ... : MsgBox "No records.." ... Arno R ...
    (comp.databases.ms-access)
  • Re: access 2003
    ... Dim ctl As Control ... Dim rs As Recordset ... This sets the query definitions for choosing data to create an invoice using ... Event on combo box: Private Sub ChooseCust_AfterUpdate ...
    (microsoft.public.access.conversion)