RE: Message box if record is not found

Try using EOF (End Of File)

Set rs = CurrentDb.OpenRecordset(strSQL)
If Not rs.Eof Then
With rs
Me.FirstName = .Fields("FirstName")
Me.LastName = .Fields("LastName")
Me.Email = .Fields("Email")
Me.Company = .Fields("Company")
Me.City = .Fields("City")
Me.State = .Fields("State")
End With
End If

--
Good Luck
BS"D


"Sara" wrote:

Hi

I have a form that when I enter an Employee ID in one textbox populates
other textboxes with more information about that particular employee.
However, If I type an invalid Employee ID I want to display a Message box
"Invalid Employee ID" instead of the error I get.
This is the code I am using:


strSQL = "SELECT [accessNS Intermodal Users (3)].FirstName, [accessNS
Intermodal Users (3)].LastName,[accessNS Intermodal Users (3)].State,
[accessNS Intermodal Users (3)].Company, [accessNS Intermodal Users
(3)].City, [accessNS Intermodal Users (3)].Email " & _
"FROM [accessNS Intermodal Users (3)] " & _
"WHERE ((([accessNS Intermodal Users (3)].RacfID)= '" &
[Forms]![DataEntry]![Text17] & "'));"

Set rs = CurrentDb.OpenRecordset(strSQL)

With rs
Me.FirstName = .Fields("FirstName")
Me.LastName = .Fields("LastName")
Me.Email = .Fields("Email")
Me.Company = .Fields("Company")
Me.City = .Fields("City")
Me.State = .Fields("State")
End With

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End Sub


Where and what code do I need to add to let the user now the entry is
invalid, by this I mean that that particular employee ID doesn't exist on the
Employees table?
I tried something like this after the query but it didn't work.

'If rs.RecordCount = 0 Then
'MsgBox ("The User ID you entered is not in the records")
'End If

Could somebody help me please!!!????


.

Relevant Pages

  • RE: Message box if record is not found
    ... MsgBox "Invalid Employee ID" ... I have a form that when I enter an Employee ID in one textbox populates ... If I type an invalid Employee ID I want to display a Message box ... Intermodal Users ].LastName,.State, ...
    (microsoft.public.access.formscoding)
  • RE: Message box if record is not found
    ... MsgBox "Invalid Employee ID" ... I have a form that when I enter an Employee ID in one textbox populates ... If I type an invalid Employee ID I want to display a Message box ... Intermodal Users ].LastName,.State, ...
    (microsoft.public.access.formscoding)
  • Re: Message box if record is not found
    ... You can turn on the autoexpand property of the combobox. ... form where it displays the employee name and has the value of EmployeeID. ... If I type an invalid Employee ID I want to display a Message ... Intermodal Users ].LastName,.State, ...
    (microsoft.public.access.formscoding)
  • RE: Message box if record is not found
    ... I have a form that when I enter an Employee ID in one textbox populates ... "Invalid Employee ID" instead of the error I get. ... Intermodal Users ].LastName,.State, ...
    (microsoft.public.access.formscoding)
  • Re: Message box if record is not found
    ... I though about that but the employee table has 3000 records and it will ... has the EmployeeID of the employee you selected in the combobox. ... "Invalid Employee ID" instead of the error I get. ... Intermodal Users ].LastName,.State, ...
    (microsoft.public.access.formscoding)