Dlookup for Table record different than Table source on Form?

[Heatshiver]

I have two tables, "tblUsers" and "tblDailyReport". tblUsers only records Text for the field "UserNum". tblDailyReport has many fields, but has a direct relationship from UserNum to its own "UserID".



People have the ability to add to UserNum to create their own unique user. I have a form that uses the field values from tblDailyReport. When the person fills in their User ID, there is meant to be a validation to ensure that the user was created in tblUsers before.

Initially I used the UserID field, but I kept getting Name & Type errors. I then deleted that and created an unbound text box called "User". I tried switched to using VBA instead of an expression (which I prefer). Here is what I have as of right now:

If DLookup("UserNum", "tblUsers", "User = '" & [Forms]![frmGenSum]![User] & "'") Then
'Do Nothing
Else
MsgBox "Please input a valid User ID."
[Forms]![frmGenSum]![User] = ""
Exit Sub
End If


Nothing happens, it allows whatever characters are inserted to remain even if wrong. Am I calling the other table correctly? Is there something I have written wrong? Any help would be much appreciated. Thanks!

[Robeen]

Are 'UserID' and 'UserNum' - both Text fields in your tables?

[Robeen]

I did a quick Mock-Up of what I think your scenario is [Text data type for the UserNum & UserID fields].

I've got this in the After Update Event for the txtUserID TextBox on my form [DailyReport]. It works the way I think you want it to.

Private Sub txtUserID_AfterUpdate()
If IsNull(DLookup("UserNum", "tblUsers", "UserNum = '" & [Forms]![DailyReport]![txtUserID] & "'")) Then
  MsgBox "UserID Not Found!"
Else
    MsgBox "UserID Found! "End If
End Sub

Let me know if this helps!

[Heatshiver]

Thank you so much Robeen! It worked!

BTW, I have seen your posts before and they have helped me out, so thanks for those too!

[Robeen]

I'm always happy when I can help here as I have received help from some of our experts & know what a blessing it has been.
All the best!