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

  • 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.


    • Edited by InnVis Thursday, September 5, 2019 5:24 PM
    Thursday, September 5, 2019 5:05 PM

Answers

  • 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

    • Marked as answer by InnVis Thursday, September 5, 2019 11:40 PM
    Thursday, September 5, 2019 10:30 PM

All replies

  • 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

    • Marked as answer by InnVis Thursday, September 5, 2019 11:40 PM
    Thursday, September 5, 2019 10:30 PM
  • 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

    Thursday, September 5, 2019 11:40 PM