How To Validate Textbox Data With Another Table Field

[vaibhav2015]

Hi,

I have created form and added text box and command buttons.

I want to cross check the data entered in textbox is valid or not from another table.

If entered data (request number) is there in table then command button is enabled otherwise it should give message "Request number is invalid.".

Please advice how I can write VBA code in form. I have try below VBA code but I got error.

Private Sub Text0_AfterUpdate()
If Me.Text0 = [ACT_Main_Table]![MDM request no] Then
Me.command1.SetFocus
Me.command2.SetFocus

Else
MsgBox " Please enter valid MDM request Number", vbCritical

End If
End Sub

 

[leanpilar]

I will try to put code on exit of the filed and try to check recordset

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("your table", dbOpenDynaset)
rst.findfirst "[yourfield with request number]=" & me.field to check
if rst.nomatch then
MsgBox " Please enter valid MDM request Number", vbCritical
me.field with number.setfocus
exit sub
end if
rst.close
set rst = nothing
db.close
set db = nothing

 

[arnelgp]

Private Sub Text0_AfterUpdate()
Me.command1.Enabled=False
Me.command2.Enabled=False

If NZ(DCount("*","[ACT_Main_Table]","[MDM request no] = '" & Me.Text0 & "'"), 0) > 0 Then
Me.command1.Enabled=True
Me.command2.Enabled=True

Else
MsgBox " Please enter valid MDM request Number", vbCritical

End If
End Sub

 

[vaibhav2015]

Hi,
I have tried above code. I am getting error for line .
If Nz(DCount("*", "[ACT_Main_Table]", "[MDM request no] = '" & Me.Text0 & "'"), 0) > 0 Then

Please advice.

 

[vaibhav2015]

error message- Run time error 3464 - Data Type mismatch in criteria expression.

 

[arnelgp]

if your field [MDM request no] is numeric type:

If Nz(DCount("*", "[ACT_Main_Table]", "[MDM request no] = " & nz(Me.Text0,0)), 0) > 0

 

[vaibhav2015]

Thank you so much, its working fine.

 

[vaibhav2015]

Hi,
I have two tables and I want to validate the material column in two tables.
Material field type is "Short text"

table1 [ACT_Import_Table]![Material]
table2 [ACT_Ext_Table]![Material].

I want to compare above two tables, I will enter materials in "ACT_Ext_Table" If that Material exist in "ACT_Import_Table", I want to run query1 and If materials is not exist in "ACT_Import_Table" I will run another query2.
Can you please advice how I can use VBA codes to run above macro.