Using DLookup to Lookup another table if value is blank

[toadmaster]

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.

i = DLookup("email", "Member", "windowsname = text114")
If i = "" Then
emailaddress = DLookup("[email]", "Manager", "[username] = text114")
Else
emailaddress = i

[missinglinq]

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)>

[toadmaster]

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

i = DLookup("email", "Member", "windowsname = text114")
If isnull(i) Then
emailaddress = DLookup("[email]", "Manager", "[username] = text114")
Else
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

[NeoPa]

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.

[NeoPa]

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 :

i = DLookup("email", "Member", "[windowsname] = '" & Me.text114 & "'")

[toadmaster]

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)

Private Sub purchase_Click()
Dim mess_body As String
Dim ref As String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
Text114 = Environ("username")
MemServ = DLookup("[Forename]&' '&[Surname]", "Member", "[windowsname] = text114")
Date_Completed = Now()
i = DLookup("email", "Member", "[windowsname] = '" & Me.Text114 & "'")
If IsNull(i) Then
emailaddress = DLookup("email", "Manager", "[username] = '" & Me.Text114 & "'")
Else
i = emailaddress
If IsNull(DLookup("[windowsname]", "Member", "windowsname = '" & Me.Text114 & "'")) And IsNull(DLookup("[username]", "Manager", "username = '" & Me.Text114 & "'")) Then
MsgBox "You Are Not Authorized To Use This Form"
Else
If Val(Me.Wire_Amount) > DLookup("Limit", "Member", "windowsname = '" & Me.Text114 & "'") Then
 MsgBox "Amount Over approval limit, Please Assign to Your Manager"
Else
With MailOutLook
    .BodyFormat = olFormatRichText
    .To = emailaddress
    .Subject = "PURCHASE TRANSFER"
    .Send
End With
MsgBox "A Notification Email Has Been Sent"
'MsgBox MailOutLook.Body
Exit Sub
email_error:
MsgBox "An error was encountered." & vbCrLf & "The error message is: " & Err.Description
Resume Error_out
Error_out:
End If
End If
End If
End Sub

[NeoPa]

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.

[toadmaster]

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

Sorry if I upset you

Thank you

[NeoPa]

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.