I have 2 tables. Employee Main and Accruals Used. When they select an
employee on the Accruals used form (uses Accruals used table), I want to see if
that employee is eligible to take time (some aren't). I was trying to use an
IF statement, but I can't get it to work since the info that I need to refer to
is in the Employee Main table. I thought it was like table![employee
main]...etc, but that doesn't work either. TIA! Here's the code I tried:

Private Sub EmployeeID_LostFocus()

Dim Msg1, Msg2, Style, Title, Response
Msg1 = "Employee is part time. Not eligible."
Msg2 = "Employee is a casual. Not eligible."
Style = vbOK + vbExclamation
Title = "Error"

If [employee main].Status = "Part time" Then
Response = MsgBox(Msg1, Style, Title)
Else
If [employee main].Status = "casual" Then
Response = MsgBox(Msg2, Style, Title)
End If
End If

End Sub

If you don't have the field available in the form's recordsource then you can
use the DLookup function.

Select Case DLookup("Status","[Employee Main]", _
"EmployeeID=" & Chr(34) & Me.EmployeeID & Chr(34))
Case "Part Time"
MsgBox ...
Case "Casual"
MsgBox ...
End Select

If your EmployeeID (or whatever your primary key is for Employee Main) is not
text but numeric then remove the & Chr(34) from the DLookup.


AngiW wrote:
>
> I have 2 tables. Employee Main and Accruals Used. When they select an
> employee on the Accruals used form (uses Accruals used table), I want to see if
> that employee is eligible to take time (some aren't). I was trying to use an
> IF statement, but I can't get it to work since the info that I need to refer to
> is in the Employee Main table. I thought it was like table![employee
> main]...etc, but that doesn't work either. TIA! Here's the code I tried:
>
> Private Sub EmployeeID_LostFocus()
>
> Dim Msg1, Msg2, Style, Title, Response
> Msg1 = "Employee is part time. Not eligible."
> Msg2 = "Employee is a casual. Not eligible."
> Style = vbOK + vbExclamation
> Title = "Error"
>
> If [employee main].Status = "Part time" Then
> Response = MsgBox(Msg1, Style, Title)
> Else
> If [employee main].Status = "casual" Then
> Response = MsgBox(Msg2, Style, Title)
> End If
> End If
>
> End Sub

John,
Thank you for that...I forgot how to do cases. But it's not working. It lets
me add the record even if they're part time and a message box never comes up.
EmployeeID is a number field. Primary key for Employee table is Employee ID.
Foreign key is EmployeeID. Does the fact that I'm using a lookup on the
foreign key to display their name (last name + first name) instead of their
number change anything? I looked and it's storing their name instead of their
number in the table, which is what I didn't want, but that's what it's doing.
I have the bound column set to the number, not the name. Here's what I now
have. What am I doing wrong?? So much for me thinking this was easy!

Select Case DLookup("[Status]", "Employee Main", _
"[Employee ID] = Me.EmployeeID")
Case "Part Time"
Response = MsgBox(Msg1, Style, Title)
Case "Casual"
Response = MsgBox(Msg2, Style, Title)
End Select

You need to remove Me.EmployeeID from the quotes, so that you have a value being
put into the criteria string.

Select Case DLookup("[Status]", "Employee Main", _
"[Employee ID] =" & Me.EmployeeID)
Case "Part Time"
Response = MsgBox(Msg1, Style, Title)
Case "Casual"
Response = MsgBox(Msg2, Style, Title)
End Select


AngiW wrote:
>
> John,
> Thank you for that...I forgot how to do cases. But it's not working. It lets
> me add the record even if they're part time and a message box never comes up.
> EmployeeID is a number field. Primary key for Employee table is Employee ID.
> Foreign key is EmployeeID. Does the fact that I'm using a lookup on the
> foreign key to display their name (last name + first name) instead of their
> number change anything? I looked and it's storing their name instead of their
> number in the table, which is what I didn't want, but that's what it's doing.
> I have the bound column set to the number, not the name. Here's what I now
> have. What am I doing wrong?? So much for me thinking this was easy!
>
> Select Case DLookup("[Status]", "Employee Main", _
> "[Employee ID] = Me.EmployeeID")
> Case "Part Time"
> Response = MsgBox(Msg1, Style, Title)
> Case "Casual"
> Response = MsgBox(Msg2, Style, Title)
> End Select

Thank you...that was it!