If value entered is not equal to table value

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

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?
 
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
 
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"

Klatuu said:
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


Matt said:
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?
Click to expand...
Click to expand...
 
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 & "'"))
Click to expand...

Matt said:
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"

Klatuu said:
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


Matt said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Okay, Now I'm getting an error where it says Me.txtBTN saying "Method or Data
member not found"

Access is really fun.

Klatuu said:
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 & "'"))
Click to expand...

Matt said:
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"

Klatuu said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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.

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

Access is really fun.

Klatuu said:
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 & "'"))
Click to expand...

Matt said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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
 
Back
Top