Validation Rule by relating the data with table

[Falahuddin]

Dear All,

With very limitation knowledge I have about Access, I have tried several ways to validate my data with existing table I've developed.



Actually, I want the access to validate the data (Cellphone number) I've entered in the textbox by looking it in the existing table. So, if the data is there in the table, the message will pop up such as.. "Please continue". But if not, the message should be "this number was not registered yet.." and the textbox will be empty and ready for the new number...

I wish that you could help me on this matter.. Thank in advance..

Falah

[June7]

What table is the source of the cell phone numbers? What table is this textbox bound to?

Options:

1. Textbox BeforeUpdate event VBA code:
If IsNull(DLookup("Cell", "table", "Cell='" & Me.textbox & "'")) Then
MsgBox "This number not registered yet."
Else
MsgBox "Please continue"
End If

2. combobox

[Falahuddin]

Thank for your help. I will try this and let you know... actually the textbox is unbound...

[June7]

Why is it not bound? What is purpose of textbox? Have you considered using a combobox?

[Falahuddin]

The idea is to receive and record certain information through SMS from the cellphone number that already input into the database. So, I wanna do is to validate every message received from the cellphone with the existing number. I will not record the information from the cellphone which is not registered.
Using Combobox makes me more difficult, since I have search the number one by one.

I wrote the code you've suggested, and it doesn't work. I realize, some mistakes could be made, since I am a beginner in using VBA. This is what I have written:

Option Compare Database


Private Sub NOHP_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("no HP", "Tbl_Link1", "no HP=" & Me.NOHP & "")) Then MsgBox "The number is not registered yet..!"
Else
MsgBox "Please Continue.."

End If


End Sub


Please give me further advice to solve this problem.. Thanks

[Falahuddin]

Hi June..
After some tries, it works. But, when the number was input incorrect, after the message displayed, the cursor also moved to other cell. It suppose to stay in CELL called "NoHP".

Again, Please help..

[June7]

Try:

Private Sub NoHP_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("Cell", "table", "Cell='" & Me.textbox & "'")) Then
MsgBox "This number not registered yet."
Cancel = True
Me.NoHP = Null
Else
MsgBox "Please continue"
End If
End Sub

[Falahuddin]

Hi June7...

Appreciate your quick response to support me. I have tried writing the following code, it display yellow blocked

Option Compare Database


Private Sub No_HP_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("noHP", "Tbl_Link1", "noHP='" & Me.No_HP & "'")) Then
MsgBox "The number is not registered yet..!"
Cancel = True
Me.No_HP = Null ---->
Else
MsgBox "Please Continue.."
End If
End Sub

Need your final advice smoothen this process. .

Falah

[June7]

It's yellow but what is the exact error message?

[Falahuddin]

Here is the message..




I hope the image is visible ..

[June7]

Is there anything in the Validation Rule property of the textbox?

[Falahuddin]

Yes, I used phone number format... Does it disturb...?. Well, actually it's the INPUT MASK, not the VALIDATION RULE...

[June7]

Try instead:

Me.No_HP.Undo

[Falahuddin]

YES... It works now.. Thank you June.. I will find another challenge. Anyway, How to remark this thread as SOLVED...?.

[June7]

Thread Tools dropdown above first post - looks like you found it.