Message box if record is not found

S

Sara

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!!!????
 
D

Daniel

wouldn't rs.NoMatch work in this case

....
If rs.NoMatch then
MsgBox ("The User ID you entered is not in the records")
Else
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 If
....
 
O

Ofer Cohen

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
 
O

Ofer Cohen

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
Else
MsgBox "Invalid Employee ID"
End If
 
S

Steve

Basically you have a search form. Why not use an unbound combobox on your
form where it displays the employee name and has the value of EmployeeID.
You can then enter code in the AfterUpdate event to find the record which
has the EmployeeID of the employee you selected in the combobox.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
 
S

Sara

Daniel,


Thanks for your response, what you suggest makes sense to me, when I type
"rs." the reserved word NoMatcht appears which I guess is a good indicator
but when I enter aN Employee ID in my form that is not in the records I get a
visual basic error that takes me to debug the code, I don't know what I am
doing wrong.

Daniel said:
wouldn't rs.NoMatch work in this case

...
If rs.NoMatch then
MsgBox ("The User ID you entered is not in the records")
Else
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 If
...
--
Hope this helps,

Daniel P


Sara said:
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!!!????
Click to expand...
Click to expand...
 
S

Sara

Well, I though about that but the employee table has 3000 records and it will
keep growing, so I didnt want to hav a combobox with that many options, it
would be a very long dropdown box, whereas if they type the user ID that is
unique and only has 3 digits I think it's easier.

Steve said:
Basically you have a search form. Why not use an unbound combobox on your
form where it displays the employee name and has the value of EmployeeID.
You can then enter code in the AfterUpdate event to find the record which
has the EmployeeID of the employee you selected in the combobox.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)




Sara said:
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!!!????
Click to expand...
Click to expand...
 
S

Sara

Ofer Cohen,

Thanks a lot, it works nicely!!!!



Ofer Cohen said:
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
Else
MsgBox "Invalid Employee ID"
End If

--
Good Luck
BS"D


Sara said:
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!!!????
Click to expand...
Click to expand...
 
S

Steve

You can turn on the autoexpand property of the combobox. You then start
entering the name of the employee and the list automatically scrolls to the
first name beginning with the letters you entered. As soon as you see the
name you are looking for (probably 3 -4 letters), you can click on the name
which then executes your code.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)





Sara said:
Well, I though about that but the employee table has 3000 records and it
will
keep growing, so I didnt want to hav a combobox with that many options, it
would be a very long dropdown box, whereas if they type the user ID that
is
unique and only has 3 digits I think it's easier.

Steve said:
Basically you have a search form. Why not use an unbound combobox on your
form where it displays the employee name and has the value of EmployeeID.
You can then enter code in the AfterUpdate event to find the record which
has the EmployeeID of the employee you selected in the combobox.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)




Sara said:
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!!!????
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Double clik item on list box to auto populate other controls 3
Seeking Suggestions 3
Automatically create record in related table 3
cannot update field error box 6
combo box only loads one record into form 3
How to prevent field from duplicated data if check box is true? 1
Need Help With Make Table Query 2
combo box to update record via form 3

Top