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

Using DLookup to Lookup another table if value is blank

P: 45
Help I am stumped;

I am trying to get DLookup to use the email value from another table if a user does not exist in the first table.
Expand|Select|Wrap|Line Numbers
  1. i = DLookup("email", "Member", "windowsname = text114")
  2. If i = "" Then
  3. emailaddress = DLookup("[email]", "Manager", "[username] = text114")
  4. Else
  5. emailaddress = i
Mar 29 '10 #1
Share this Question
Share on Google+
8 Replies

Expert 2.5K+
P: 3,532
Per Access Help
If no record satisfies criteria or if domain contains no records, the DLookup function returns a Null.
So the correct test would be

If IsNull(i) Then

Linq ;0)>
Mar 29 '10 #2

P: 45
I guess I didn't explain myself well in my post; here goes.

The emailaddress value which is being captured using the DLookup function is then going to be stored in another table called "Input". My problem is when I use the code below
Expand|Select|Wrap|Line Numbers
  1. i = DLookup("email", "Member", "windowsname = text114")
  2. If isnull(i) Then
  3. emailaddress = DLookup("[email]", "Manager", "[username] = text114")
  4. Else
  5. emailaddress = i
the email value is not being captured (I have a field in the input table called emailaddress to store this captured value and it's blank) and stored in the input table and neither is the second part of the if statement being fulfilled.

The whole event is trigged through a button click.

Thank you
Mar 29 '10 #3

Expert Mod 15k+
P: 31,770
When posting any code on here please :
  1. Ensure you have Option Explicit set (See Require Variable Declaration).
  2. Try to compile it. If it doesn't compile for any reason please explain that clearly - including the error message and which line of your code it appears on. Compilation is done from the Visual Basic Editor menu - Debug \ Compile Project (Where Project is the actual name of your project).
  3. Copy your code (using the Clipboard - Cut / Copy / Paste) from your project directly into your post. Typing in code is not appreciated as it is likely to introduce typos which cause members to waste their time unnecessarily.
  4. Ensure that the code in your post is enveloped within CODE tags (For more on this see BB Code List). The hash (#) button in the posting page helps with this. Simply select your code and click on the hash button to have it enveloped automatically.
If all these points are covered then all members will be better able to understand, and therefore attempt to answer, your question.
Mar 29 '10 #4

Expert Mod 15k+
P: 31,770
Frankly there's so much off in your code it's hard to know where to start. A more carefully phrased question might have avoided some of the confusion.

The most glaring issue is that your use of what is probably a TextBox control on your form (text114) is not valid. DLookup, being SQL based as it is, would have no direct link to your form or its controls (It wouldn't recognise the name). If you were to use a more standard approach and add the value of the control to the string parameter as a literal, then quotes (') (See Quotes (') and Double-Quotes (") - Where and When to use them) would be needed. A bit like :
Expand|Select|Wrap|Line Numbers
  1. i = DLookup("email", "Member", "[windowsname] = '" & Me.text114 & "'")
Mar 29 '10 #5

P: 45
Find my code below

The emailaddress is being captured when the user exists in the manager table; however the emailaddress is not captured when the user only exists in the member table. I need this emailaddress to be captured because in another procedure this emailaddress is used to send an email using this email address (emailaddress value is stored in another table called purchasetable)
Expand|Select|Wrap|Line Numbers
  1. Private Sub purchase_Click()
  2. Dim mess_body As String
  3. Dim ref As String
  4. Dim appOutLook As Outlook.Application
  5. Dim MailOutLook As Outlook.MailItem
  6. Set appOutLook = CreateObject("Outlook.Application")
  7. Set MailOutLook = appOutLook.CreateItem(olMailItem)
  8. Set appOutLook = CreateObject("Outlook.Application")
  9. Set MailOutLook = appOutLook.CreateItem(olMailItem)
  10. Text114 = Environ("username")
  11. MemServ = DLookup("[Forename]&' '&[Surname]", "Member", "[windowsname] = text114")
  12. Date_Completed = Now()
  13. i = DLookup("email", "Member", "[windowsname] = '" & Me.Text114 & "'")
  14. If IsNull(i) Then
  15. emailaddress = DLookup("email", "Manager", "[username] = '" & Me.Text114 & "'")
  16. Else
  17. i = emailaddress
  18. If IsNull(DLookup("[windowsname]", "Member", "windowsname = '" & Me.Text114 & "'")) And IsNull(DLookup("[username]", "Manager", "username = '" & Me.Text114 & "'")) Then
  19. MsgBox "You Are Not Authorized To Use This Form"
  20. Else
  21. If Val(Me.Wire_Amount) > DLookup("Limit", "Member", "windowsname = '" & Me.Text114 & "'") Then
  22.  MsgBox "Amount Over approval limit, Please Assign to Your Manager"
  23. Else
  24. With MailOutLook
  25.     .BodyFormat = olFormatRichText
  26.     .To = emailaddress
  27.     .Subject = "PURCHASE TRANSFER"
  28.     .Send
  29. End With
  30. MsgBox "A Notification Email Has Been Sent"
  31. 'MsgBox MailOutLook.Body
  32. Exit Sub
  33. email_error:
  34. MsgBox "An error was encountered." & vbCrLf & "The error message is: " & Err.Description
  35. Resume Error_out
  36. Error_out:
  37. End If
  38. End If
  39. End If
  40. End Sub
Mar 30 '10 #6

Expert Mod 15k+
P: 31,770
To be honest I have no desire to continue on a thread where you cannot maintain a dialogue. Your post in no way includes any reference to any of my previous attempts to assist, and actually breaks the site rules again, in a way you've already been explicitly instructed about. Quite apart from the peremptory and demanding tone of the post.

Good luck.
Mar 30 '10 #7

P: 45
I am sorry if my request came across as demanding. Did not in anyway mean that.

Sorry if I upset you

Thank you
Mar 31 '10 #8

Expert Mod 15k+
P: 31,770
I'm thoroughly mollified (I'm happy with your response), but I still feel it will be difficult to work on this even though your manners are not an issue.

To work together there needs to be better communication. Without that it is simply a sequence of restarting from scratch. Progress is very difficult. I still wish you luck, and I'm not offended, I simply won't continue in this thread.
Apr 8 '10 #9

Post your reply

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