CODEWITHSUNDEEP
validationms-access
Chris
Chris

Reputation: 308

Access 2013: Validation Rule Based on Query Results

I have a query that contains two fields; SID & Status.

I have set my criteria to only include the blank status'.

I now would like to set a validation rule to my form so when the user attempts to input the SID it checks to see if it exists in the query results. If not, return a message box informing the user that the SID has already been completed.

Upvotes: 0

Views: 1125

Answers (2)

HarveyFrench
HarveyFrench

Reputation: 4568

So on the form you want the user to enter a SID into a textbox and check that the SID is one of the SIDs in the list that has a blank status.

This sounds like you need to change the text box to be a combo box that is based on the query and that the combo box "limit to list" property is set to true. This will only allow the user to choose (or manually type in) SIDS that appear on the list.

Sorted!

Sound good?

Upvotes: 0

Johnny Bones
Johnny Bones

Reputation: 8402

In the AfterUpdate event of the textbox, do something like this:

Dim db as Database
Dim rec as Recordset

Set db = CurrentDB
Set rec = db.OpenRecordset("SELECT Status FROM MyTable WHERE SID = '" & txtSID.Value & "'")

If rec.EOF Then
  'All is well, the SID hasn't been found
Else
  MsgBox "This SID has already been completed"
End If

Upvotes: 1

Related Questions