How to return the value of a record in a column within List Box for validation purposes.

Question

Hey guys, I'm trying to add a condition to a button to check for the status of current orders and not allow a new entry if there is a status in my listbox marked as "ACTIVE." 

My listbox has a customized row source querying the results from a table. It has some formatting to display my data in a certain way for example this particular data pulls "Orders" from a table and it classified orders WITHOUT a "Delivered Date" as "ACTIVE" and orders WITH a "Delivered Date" as "COMPLETED." I want my button to prevent new orders from being created UNLESS there are no "ACTIVE" orders currently.

Answer 1

Cut out the middleman and go straight to the data, e.g.

If IsNull(DLookup("OrderID","Orders", "[Delivered Date] IS NULL")) Then
    ' code to allow new entry
Else
    ' code to disallow new entry
End If

---

Ken Sheridan, Stafford, England

Answer 2

Thanks for that. I took it a step further and here's what I made, hope it helps someone else out:

Dim stSTATUS As String

stSTATUS = IIf(Nz(DLookup("DischargeDate", "tblVADAdmissionHx", "skVadPatientID=" & Me.skVADPatientID & " and AdmitDate = #" & DMax("AdmitDate", "tblVADAdmissionHx", "skVadPatientID=" & Me.skVADPatientID & "") & "#"), 0) = 0, "ACTIVE", "DISCHARGED")

If stSTATUS = "ACTIVE" Then
MsgBox "This patient already has an ACTIVE admission. Please discharge that FIN # prior to creating a new one.", vbCritical, "ACTIVE ADMISSION"
Exit Sub
Else
End If