By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,183 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Using DLookup to Reference Multiple Tables

P: 45
I am trying to get an onclick event to open a form based on the logged in username from Windows.

The idea is to open either form A or B after I have used DLookup to check a table to see if the user can be found in the table?

If the user is not found in the table then open form B

DoCmd.Minimize
Text19 = DLookup("[username]", "Service", "[windowsname] = text17") Then
DoCmd.OpenForm "Assign ", acNormal, , , , , Me.Number
Else
DoCmd.OpenForm "Manager", acNormal, , , , , Me.Number
End If


The other thing is; I am passing a value from the previous form in which the button was clicked (That value is holding the record number for the record being used)

Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
Me.Number.Value = Me.OpenArgs
Me.Requery
End If
End Sub

The main purpose is to allow only a select group of people found in the table to submit a request to a particular individual and for those not found in the table submit it to another individual.

Thanks any help will be appreciated
Feb 23 '10 #1

✓ answered by TheSmileyCoder

Expand|Select|Wrap|Line Numbers
  1. if nz(Dlookup("KEY_User","[myTable]","tx_UserName='" & Environ("UserName") & "'"),"")<>"" then
  2.   'OpenForm
  3. else
  4.   'OpenOtherForm
  5.  
  6. End If
Environ("UserName") will fetch the windows username for you.
Dlookup will try to find the KEY_User by comparing windows username to username stored in table field tx_UserName (in my example) and return the key, or null, if it can't find it. Thats why I added the nz() function, in case null is returned.

Share this Question
Share on Google+
2 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
Expand|Select|Wrap|Line Numbers
  1. if nz(Dlookup("KEY_User","[myTable]","tx_UserName='" & Environ("UserName") & "'"),"")<>"" then
  2.   'OpenForm
  3. else
  4.   'OpenOtherForm
  5.  
  6. End If
Environ("UserName") will fetch the windows username for you.
Dlookup will try to find the KEY_User by comparing windows username to username stored in table field tx_UserName (in my example) and return the key, or null, if it can't find it. Thats why I added the nz() function, in case null is returned.
Feb 23 '10 #2

P: 45
Worked like a charm; thank you so much.............
Feb 23 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.