Data Validation from another table

[paulhenley]

I have two tables (Suppliers & Orders)
I have created a form to input the orders and I want the form to check that when the user adds a supplier id (to the order form) a lookup takes place and verifies if the supplier id exists in the supplier table. If it does input continues, if it does not exist, an error box appears saying so.

Please can anyone advise how I get the form to verify values in another table.

 

[David R]

Dlookup or Dcount will work.

DomainFunction("[FieldName]","table/queryName","WHERE CONDITION")

[This message has been edited by David R (edited 04-23-2002).]

 

[DJN]

Sounds like a job for the On Not In List event procedure. See if this code fits the bill. Set the Limit To List to Yes and add this code to the On Not In List Event Procedure.


Dim strMsg As String
Dim rst As Recordset
Dim db As Database

strMsg = "'" & NewData & "' is not in the list. "
strMsg = strMsg & "Would you like to add it?"

If MsgBox(strMsg, vbOKCancel, "Supplier") = vbOK Then

Set db = CurrentDb()
Set rst = db.OpenRecordset("TableName")
rst.AddNew
rst!Supplier = NewData

rst.Update
Response = acDataErrAdded


rst.Close

Else

Response = acDataErrContinue

End If

exit_cboSupplierID_NotInList:

Exit Sub

err_SupplierID_NotInList:

If Err = 2113 Then

Err = 0
Resume Next

Else

MsgBox str(Err)
MsgBox Err.Description
Resume exit_cboSupplierID_NotInList

End If

 

[paulhenley]

David,

I have tried to use Dlookup but can not get it to work - Please could you help with the code?

The details are
Table Field
Supplier suppid
Order ordsuppid

The order form is called CTL

I want the ordsuppid to check if value exists in Supplier.suppid and give message if not valid.

 

[David R]

Try this topic for what I use for a similar purpose: http://www.access-programmers.co.uk/ubb/Forum1/HTML/007152.html

 

[Pat Hartman]

Use a combobox to select the supplier rather than DLookup(). Then use the code posted by DavidR if you want the ability to enter a new supplier as you are entering an order.