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!

 

[VilaRestal]

DLookup looks up the value and returns it. So doing 'If Dlookup Then' is not right unless perhaps you're looking up a boolean field (and even then Null (no records or null value in the record's field) will produce an error - null won't convert to boolean automatically).

What you want is either:

If Nz(DLookup("UserNum", "tblUsers", "User = '" & [Forms]![frmGenSum]![User] & "'"),"") <> "" Then

or

If DCount("*", "tblUsers", "User = '" & [Forms]![frmGenSum]![User] & "'") = 1 Then

Perhaps you should switch back to UserID too.

And also, if this code is in the frmGenSum form then you can replace [Forms]![frmGenSum] with Me

 

[Heatshiver]

Thanks for the help VilaRestal.

I ended getting it to work with this:

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

 

[KeythStone]

I have a similar issue.

I would like a button become visible depending on the application user name. So, I'm checking: Environ("USERNAME") and setting as string.

Dim userstring as String
userstring = Environ("USERNAME")

Me.Open_Aging_btn.Visible = False
If Environ("username") = Nz(Application.DLookup("user_name", "username_assignment_t", "[user_name] = userstring"), "") Then
Me.Open_Aging_btn.Visible = True


When my name is the only name in the "username_assignment_t" it works, but when I add other users to the table it doesn't work. No errors are generated, just it doesn't show button.

Any help would be great
KeythStone

 

[VilaRestal]

I'm not sure what the criteria for whether the button should show would be. It appears that it's just if the Windows username is in the username_assignment_t table.

If so, then I think all you need is to check whether any records with that name exist:

If DCount("*", "username_assignment_t", "[user_name] = """ & Replace(Environ("username"),"""","""""") & """") > 0 Then Me.Open_Aging_btn.Visible = True

or better still:

Me.Open_Aging_btn.Visible = DCount("*", "username_assignment_t", "[user_name] = """ & Replace(Environ("username"),"""","""""") & """")

would set it visible if present and invisible if not - all in one line