If statement with Dlookup, plus open form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to write a function that when someone enters a phone number in a
text box, it will look this phone number up in a table, then let me know if
the phone number is already in the table or if they have to add it. The
below formula isn't working the way I need it too. It tells me that every
phone number I type into the text box is not in the table. I know this is
wrong because I tried typing a phone number in the text box that I knew was
in the table, and it still came back as it wasn't. What is wrong with my
formula? Also, If the person clicks "yes" when it pops up saying "do you
want to add a new customer", I would like it to open up another form. How
can I do this?


If IsNull(DLookup("[BTN]", "Master Table", "[BTN]" = "" & Forms![Sales
Tracker1]![BTN])) Then MsgBox ("The phone number does not exist in your
Module")
If MsgBox("Do You Want to Add A New Customer?", vbQuestion + _
vbYesNo, "Phone Number Not Found") = vbYes Then
'Do whatever if you want to add a btn
Else
'Do whatever if you don't want to add it
End If

End Sub
 
When the DLookUp crtieria is text you need to surround it with single quotes
DLookup("[BTN]", "Master Table", "[BTN] = '" & Forms![Sales Tracker1]![BTN]
& "'")
 
I copied this into my formula, but it still comes back as saying the phone
number is not in the table, even though I know it is.

Dennis said:
When the DLookUp crtieria is text you need to surround it with single quotes
DLookup("[BTN]", "Master Table", "[BTN] = '" & Forms![Sales Tracker1]![BTN]
& "'")

Matt said:
I am trying to write a function that when someone enters a phone number in a
text box, it will look this phone number up in a table, then let me know if
the phone number is already in the table or if they have to add it. The
below formula isn't working the way I need it too. It tells me that every
phone number I type into the text box is not in the table. I know this is
wrong because I tried typing a phone number in the text box that I knew was
in the table, and it still came back as it wasn't. What is wrong with my
formula? Also, If the person clicks "yes" when it pops up saying "do you
want to add a new customer", I would like it to open up another form. How
can I do this?


If IsNull(DLookup("[BTN]", "Master Table", "[BTN]" = "" & Forms![Sales
Tracker1]![BTN])) Then MsgBox ("The phone number does not exist in your
Module")
If MsgBox("Do You Want to Add A New Customer?", vbQuestion + _
vbYesNo, "Phone Number Not Found") = vbYes Then
'Do whatever if you want to add a btn
Else
'Do whatever if you don't want to add it
End If

End Sub
Click to expand...
Click to expand...
 
The syntax is correct, I use it all over the place. BTN is a text field on
your form & a text field in the master table ? Sales Tracker1 is the correct
name for the form ?

Matt said:
I copied this into my formula, but it still comes back as saying the phone
number is not in the table, even though I know it is.

Dennis said:
When the DLookUp crtieria is text you need to surround it with single quotes
DLookup("[BTN]", "Master Table", "[BTN] = '" & Forms![Sales Tracker1]![BTN]
& "'")

Matt said:
I am trying to write a function that when someone enters a phone number in a
text box, it will look this phone number up in a table, then let me know if
the phone number is already in the table or if they have to add it. The
below formula isn't working the way I need it too. It tells me that every
phone number I type into the text box is not in the table. I know this is
wrong because I tried typing a phone number in the text box that I knew was
in the table, and it still came back as it wasn't. What is wrong with my
formula? Also, If the person clicks "yes" when it pops up saying "do you
want to add a new customer", I would like it to open up another form. How
can I do this?


If IsNull(DLookup("[BTN]", "Master Table", "[BTN]" = "" & Forms![Sales
Tracker1]![BTN])) Then MsgBox ("The phone number does not exist in your
Module")
If MsgBox("Do You Want to Add A New Customer?", vbQuestion + _
vbYesNo, "Phone Number Not Found") = vbYes Then
'Do whatever if you want to add a btn
Else
'Do whatever if you don't want to add it
End If

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Shoot. I didn't have the correct number of digits in my btn field, that's
why it wasn't working. I've been trying to figure this out for about a week
now. Thank you for your help!

By the way, The lookup is working. It's popping up the message "That phone
number does not exist in your module." when the phone number is not there.
But, if the phone number is included in the master table, It still prompts,
"Do you want to add a new customer." If the phone number is already in the
table, I don't want it to prompt at all. Also, When the person clicks "yes"
on the popup box asking if they want to add a new customer, I would like it
to open up another form I've built. I'm guessing I have to create a macro
and then place the function somewhere after the then statement. Any advice
The syntax is correct, I use it all over the place. BTN is a text field on
your form & a text field in the master table ? Sales Tracker1 is the correct
name for the form ?

Matt said:
I copied this into my formula, but it still comes back as saying the phone
number is not in the table, even though I know it is.

Dennis said:
When the DLookUp crtieria is text you need to surround it with single quotes
DLookup("[BTN]", "Master Table", "[BTN] = '" & Forms![Sales Tracker1]![BTN]
& "'")

:


I am trying to write a function that when someone enters a phone number in a
text box, it will look this phone number up in a table, then let me know if
the phone number is already in the table or if they have to add it. The
below formula isn't working the way I need it too. It tells me that every
phone number I type into the text box is not in the table. I know this is
wrong because I tried typing a phone number in the text box that I knew was
in the table, and it still came back as it wasn't. What is wrong with my
formula? Also, If the person clicks "yes" when it pops up saying "do you
want to add a new customer", I would like it to open up another form. How
can I do this?


If IsNull(DLookup("[BTN]", "Master Table", "[BTN]" = "" & Forms![Sales
Tracker1]![BTN])) Then MsgBox ("The phone number does not exist in your
Module")
If MsgBox("Do You Want to Add A New Customer?", vbQuestion + _
vbYesNo, "Phone Number Not Found") = vbYes Then
'Do whatever if you want to add a btn
Else
'Do whatever if you don't want to add it
End If

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Try this

If IsNull(DLookup("[BTN]", "Master Table", "[BTN]" = "" & Forms![Sales
Tracker1]![BTN])) Then
MsgBox ("The phone number does not exist in your Module")
If MsgBox("Do You Want to Add A New Customer?", vbQuestion + _
vbYesNo, "Phone Number Not Found") = vbYes Then
DoCmd.OpenForm "Form to Add"
Else
'Do whatever if you don't want to add it
End If
Else
MsgBox "Phone Number Already Exists"
End if
End Sub

Matt said:
Shoot. I didn't have the correct number of digits in my btn field, that's
why it wasn't working. I've been trying to figure this out for about a week
now. Thank you for your help!

By the way, The lookup is working. It's popping up the message "That phone
number does not exist in your module." when the phone number is not there.
But, if the phone number is included in the master table, It still prompts,
"Do you want to add a new customer." If the phone number is already in the
table, I don't want it to prompt at all. Also, When the person clicks "yes"
on the popup box asking if they want to add a new customer, I would like it
to open up another form I've built. I'm guessing I have to create a macro
and then place the function somewhere after the then statement. Any advice
The syntax is correct, I use it all over the place. BTN is a text field on
your form & a text field in the master table ? Sales Tracker1 is the correct
name for the form ?

Matt said:
I copied this into my formula, but it still comes back as saying the phone
number is not in the table, even though I know it is.

:

When the DLookUp crtieria is text you need to surround it with single quotes
DLookup("[BTN]", "Master Table", "[BTN] = '" & Forms![Sales Tracker1]![BTN]
& "'")

:


I am trying to write a function that when someone enters a phone number in a
text box, it will look this phone number up in a table, then let me know if
the phone number is already in the table or if they have to add it. The
below formula isn't working the way I need it too. It tells me that every
phone number I type into the text box is not in the table. I know this is
wrong because I tried typing a phone number in the text box that I knew was
in the table, and it still came back as it wasn't. What is wrong with my
formula? Also, If the person clicks "yes" when it pops up saying "do you
want to add a new customer", I would like it to open up another form. How
can I do this?


If IsNull(DLookup("[BTN]", "Master Table", "[BTN]" = "" & Forms![Sales
Tracker1]![BTN])) Then MsgBox ("The phone number does not exist in your
Module")
If MsgBox("Do You Want to Add A New Customer?", vbQuestion + _
vbYesNo, "Phone Number Not Found") = vbYes Then
'Do whatever if you want to add a btn
Else
'Do whatever if you don't want to add it
End If

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Dennis, Thank you for all your help on this problem I'm having!!

I think I'm just going to let it go the way it is. I tried entering the
Else statement that you suggested, but it's saying I need an IF statement.
I'm guessing because the Else is after the End IF. I tried putting it in the
else statement before the End If, but it didn't work either.

Dennis said:
Try this

If IsNull(DLookup("[BTN]", "Master Table", "[BTN]" = "" & Forms![Sales
Tracker1]![BTN])) Then
MsgBox ("The phone number does not exist in your Module")
If MsgBox("Do You Want to Add A New Customer?", vbQuestion + _
vbYesNo, "Phone Number Not Found") = vbYes Then
DoCmd.OpenForm "Form to Add"
Else
'Do whatever if you don't want to add it
End If
Else
MsgBox "Phone Number Already Exists"
End if
End Sub

Matt said:
Shoot. I didn't have the correct number of digits in my btn field, that's
why it wasn't working. I've been trying to figure this out for about a week
now. Thank you for your help!

By the way, The lookup is working. It's popping up the message "That phone
number does not exist in your module." when the phone number is not there.
But, if the phone number is included in the master table, It still prompts,
"Do you want to add a new customer." If the phone number is already in the
table, I don't want it to prompt at all. Also, When the person clicks "yes"
on the popup box asking if they want to add a new customer, I would like it
to open up another form I've built. I'm guessing I have to create a macro
and then place the function somewhere after the then statement. Any advice
The syntax is correct, I use it all over the place. BTN is a text field on
your form & a text field in the master table ? Sales Tracker1 is the correct
name for the form ?

:

I copied this into my formula, but it still comes back as saying the phone
number is not in the table, even though I know it is.

:

When the DLookUp crtieria is text you need to surround it with single quotes
DLookup("[BTN]", "Master Table", "[BTN] = '" & Forms![Sales Tracker1]![BTN]
& "'")

:


I am trying to write a function that when someone enters a phone number in a
text box, it will look this phone number up in a table, then let me know if
the phone number is already in the table or if they have to add it. The
below formula isn't working the way I need it too. It tells me that every
phone number I type into the text box is not in the table. I know this is
wrong because I tried typing a phone number in the text box that I knew was
in the table, and it still came back as it wasn't. What is wrong with my
formula? Also, If the person clicks "yes" when it pops up saying "do you
want to add a new customer", I would like it to open up another form. How
can I do this?


If IsNull(DLookup("[BTN]", "Master Table", "[BTN]" = "" & Forms![Sales
Tracker1]![BTN])) Then MsgBox ("The phone number does not exist in your
Module")
If MsgBox("Do You Want to Add A New Customer?", vbQuestion + _
vbYesNo, "Phone Number Not Found") = vbYes Then
'Do whatever if you want to add a btn
Else
'Do whatever if you don't want to add it
End If

End Sub
Click to expand...
Click to expand...
Click to expand...
 
The if is the first one If IsNull .... which means you have to put the MsgBox
"The phone number does not exist" on a new line

Matt said:
Dennis, Thank you for all your help on this problem I'm having!!

I think I'm just going to let it go the way it is. I tried entering the
Else statement that you suggested, but it's saying I need an IF statement.
I'm guessing because the Else is after the End IF. I tried putting it in the
else statement before the End If, but it didn't work either.

Dennis said:
Try this

If IsNull(DLookup("[BTN]", "Master Table", "[BTN]" = "" & Forms![Sales
Tracker1]![BTN])) Then
MsgBox ("The phone number does not exist in your Module")
If MsgBox("Do You Want to Add A New Customer?", vbQuestion + _
vbYesNo, "Phone Number Not Found") = vbYes Then
DoCmd.OpenForm "Form to Add"
Else
'Do whatever if you don't want to add it
End If
Else
MsgBox "Phone Number Already Exists"
End if
End Sub

Matt said:
Shoot. I didn't have the correct number of digits in my btn field, that's
why it wasn't working. I've been trying to figure this out for about a week
now. Thank you for your help!

By the way, The lookup is working. It's popping up the message "That phone
number does not exist in your module." when the phone number is not there.
But, if the phone number is included in the master table, It still prompts,
"Do you want to add a new customer." If the phone number is already in the
table, I don't want it to prompt at all. Also, When the person clicks "yes"
on the popup box asking if they want to add a new customer, I would like it
to open up another form I've built. I'm guessing I have to create a macro
and then place the function somewhere after the then statement. Any advice
on how I can do this?

:

The syntax is correct, I use it all over the place. BTN is a text field on
your form & a text field in the master table ? Sales Tracker1 is the correct
name for the form ?

:

I copied this into my formula, but it still comes back as saying the phone
number is not in the table, even though I know it is.

:

When the DLookUp crtieria is text you need to surround it with single quotes
DLookup("[BTN]", "Master Table", "[BTN] = '" & Forms![Sales Tracker1]![BTN]
& "'")

:


I am trying to write a function that when someone enters a phone number in a
text box, it will look this phone number up in a table, then let me know if
the phone number is already in the table or if they have to add it. The
below formula isn't working the way I need it too. It tells me that every
phone number I type into the text box is not in the table. I know this is
wrong because I tried typing a phone number in the text box that I knew was
in the table, and it still came back as it wasn't. What is wrong with my
formula? Also, If the person clicks "yes" when it pops up saying "do you
want to add a new customer", I would like it to open up another form. How
can I do this?


If IsNull(DLookup("[BTN]", "Master Table", "[BTN]" = "" & Forms![Sales
Tracker1]![BTN])) Then MsgBox ("The phone number does not exist in your
Module")
If MsgBox("Do You Want to Add A New Customer?", vbQuestion + _
vbYesNo, "Phone Number Not Found") = vbYes Then
'Do whatever if you want to add a btn
Else
'Do whatever if you don't want to add it
End If

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top