If value entered is not equal to table value

[Guest]

I'm not sure if this is possible, but what I would like is if someone enters
a value on a form, I want it to look up the value in a table, and if the
value they entered is not already in the table then an error message comes up.

I have a telephone number field on a form where the person will be entering
a sale to a customer. There is a table I have that includes a list of
telephone numbers for each customer. The person should not be able to enter
a new sale if the telephone number is not already included in the table.
This will make them enter the customer's information first in the table, then
they will be allow to enter the sale.

Can this be done?

 

[Guest]

Matt,

Is the Before Update event of the text box that accepts the phone number:

If IsNull(DLookup("[ThePhoneNumber]", "MyTable", "[ThePhoneNumber] = " _
& Me.txtPhoneNumber) Then "The phone number does not exist
If msgbox("Do You Want to Add A New Customer?",vbQuestion + _
vbYesNo,"Phone Number Not Found") = vbYes Then
'Do whatever to add a new customer
Else
'Do whatever if you don't want to add it
Endif
Else
'Take The Order
End If

 

[Guest]

This is what I wrote:

If IsNull(DLookup("[BTN]", "Master Table", "[BTN] = " _
& Me.txtBTN)) Then "The phone number does not exist"
If MsgBox("Do You Want to Add A New Customer?", vbQuestion + _
vbYesNo, "Phone Number Not Found") = vbYes Then
'Do whatever to add a new customer
Else
'Do whatever if you don't want to add it
End If
Else
'Take The Order
End If

It's giving me an error message for the first if statement.
"Expected: line number or label or statement or end of statement"

Matt,

Is the Before Update event of the text box that accepts the phone number:

If IsNull(DLookup("[ThePhoneNumber]", "MyTable", "[ThePhoneNumber] = " _
& Me.txtPhoneNumber) Then "The phone number does not exist
If msgbox("Do You Want to Add A New Customer?",vbQuestion + _
vbYesNo,"Phone Number Not Found") = vbYes Then
'Do whatever to add a new customer
Else
'Do whatever if you don't want to add it
Endif
Else
'Take The Order
End If

I'm not sure if this is possible, but what I would like is if someone enters
a value on a form, I want it to look up the value in a table, and if the
value they entered is not already in the table then an error message comes up.

I have a telephone number field on a form where the person will be entering
a sale to a customer. There is a table I have that includes a list of
telephone numbers for each customer. The person should not be able to enter
a new sale if the telephone number is not already included in the table.
This will make them enter the customer's information first in the table, then
they will be allow to enter the sale.

Can this be done?

 

[Guest]

Matt,
Try this variation. Notice the single quotes. I think I was testing on a
numeric value. Because it is text, it may need the quotes. Check the Help
file, too. Getting quotes right in Where statements (that's what this is
actually) always gives me headaches.

If IsNull(DLookup("[BTN]", "Master Table", "[BTN] = '" _

& Me.txtBTN & "'"))

This is what I wrote:

If IsNull(DLookup("[BTN]", "Master Table", "[BTN] = " _
& Me.txtBTN)) Then "The phone number does not exist"
If MsgBox("Do You Want to Add A New Customer?", vbQuestion + _
vbYesNo, "Phone Number Not Found") = vbYes Then
'Do whatever to add a new customer
Else
'Do whatever if you don't want to add it
End If
Else
'Take The Order
End If

It's giving me an error message for the first if statement.
"Expected: line number or label or statement or end of statement"

Matt,

Is the Before Update event of the text box that accepts the phone number:

If IsNull(DLookup("[ThePhoneNumber]", "MyTable", "[ThePhoneNumber] = " _
& Me.txtPhoneNumber) Then "The phone number does not exist
If msgbox("Do You Want to Add A New Customer?",vbQuestion + _
vbYesNo,"Phone Number Not Found") = vbYes Then
'Do whatever to add a new customer
Else
'Do whatever if you don't want to add it
Endif
Else
'Take The Order
End If

I'm not sure if this is possible, but what I would like is if someone enters
a value on a form, I want it to look up the value in a table, and if the
value they entered is not already in the table then an error message comes up.

I have a telephone number field on a form where the person will be entering
a sale to a customer. There is a table I have that includes a list of
telephone numbers for each customer. The person should not be able to enter
a new sale if the telephone number is not already included in the table.
This will make them enter the customer's information first in the table, then
they will be allow to enter the sale.

Can this be done?

 

[Guest]

Okay, Now I'm getting an error where it says Me.txtBTN saying "Method or Data
member not found"

Access is really fun.

Matt,
Try this variation. Notice the single quotes. I think I was testing on a
numeric value. Because it is text, it may need the quotes. Check the Help
file, too. Getting quotes right in Where statements (that's what this is
actually) always gives me headaches.

If IsNull(DLookup("[BTN]", "Master Table", "[BTN] = '" _

& Me.txtBTN & "'"))

This is what I wrote:

If IsNull(DLookup("[BTN]", "Master Table", "[BTN] = " _
& Me.txtBTN)) Then "The phone number does not exist"
If MsgBox("Do You Want to Add A New Customer?", vbQuestion + _
vbYesNo, "Phone Number Not Found") = vbYes Then
'Do whatever to add a new customer
Else
'Do whatever if you don't want to add it
End If
Else
'Take The Order
End If

It's giving me an error message for the first if statement.
"Expected: line number or label or statement or end of statement"

Matt,

Is the Before Update event of the text box that accepts the phone number:

If IsNull(DLookup("[ThePhoneNumber]", "MyTable", "[ThePhoneNumber] = " _
& Me.txtPhoneNumber) Then "The phone number does not exist
If msgbox("Do You Want to Add A New Customer?",vbQuestion + _
vbYesNo,"Phone Number Not Found") = vbYes Then
'Do whatever to add a new customer
Else
'Do whatever if you don't want to add it
Endif
Else
'Take The Order
End If


:

I'm not sure if this is possible, but what I would like is if someone enters
a value on a form, I want it to look up the value in a table, and if the
value they entered is not already in the table then an error message comes up.

I have a telephone number field on a form where the person will be entering
a sale to a customer. There is a table I have that includes a list of
telephone numbers for each customer. The person should not be able to enter
a new sale if the telephone number is not already included in the table.
This will make them enter the customer's information first in the table, then
they will be allow to enter the sale.

Can this be done?

 

[Guest]

Have you tried [forms]![frmMyForm]!txtBTN ? I am only guessing now. I
always have to try several variations to get it to work. Access really is
fun, you get to try to figure out which of a bunch of syntax models applies
at any given time. As long as I have been doing this, I still have to take
several guesses to get it right.

Okay, Now I'm getting an error where it says Me.txtBTN saying "Method or Data
member not found"

Access is really fun.

Matt,
Try this variation. Notice the single quotes. I think I was testing on a
numeric value. Because it is text, it may need the quotes. Check the Help
file, too. Getting quotes right in Where statements (that's what this is
actually) always gives me headaches.

If IsNull(DLookup("[BTN]", "Master Table", "[BTN] = '" _

& Me.txtBTN & "'"))

This is what I wrote:

If IsNull(DLookup("[BTN]", "Master Table", "[BTN] = " _
& Me.txtBTN)) Then "The phone number does not exist"
If MsgBox("Do You Want to Add A New Customer?", vbQuestion + _
vbYesNo, "Phone Number Not Found") = vbYes Then
'Do whatever to add a new customer
Else
'Do whatever if you don't want to add it
End If
Else
'Take The Order
End If

It's giving me an error message for the first if statement.
"Expected: line number or label or statement or end of statement"

:

Matt,

Is the Before Update event of the text box that accepts the phone number:

If IsNull(DLookup("[ThePhoneNumber]", "MyTable", "[ThePhoneNumber] = " _
& Me.txtPhoneNumber) Then "The phone number does not exist
If msgbox("Do You Want to Add A New Customer?",vbQuestion + _
vbYesNo,"Phone Number Not Found") = vbYes Then
'Do whatever to add a new customer
Else
'Do whatever if you don't want to add it
Endif
Else
'Take The Order
End If


:

I'm not sure if this is possible, but what I would like is if someone enters
a value on a form, I want it to look up the value in a table, and if the
value they entered is not already in the table then an error message comes up.

I have a telephone number field on a form where the person will be entering
a sale to a customer. There is a table I have that includes a list of
telephone numbers for each customer. The person should not be able to enter
a new sale if the telephone number is not already included in the table.
This will make them enter the customer's information first in the table, then
they will be allow to enter the sale.

Can this be done?

 

[Van T. Dinh]

Do you have a TextBox / Control named "txtBTN" on your Form?

If you do, try:

If DCount("[BTN]", "Master Table", "[BTN] = '" & Me.txtBTN & "'") = 0 Then
'Telephone number doesn't exist