If Query result Is Null...MsgBox..Okl

[Bruce]

I have a form I use to enter workorder info into a table. On the form is a combo box that lists the types of workorders. If the user selects "Renewal" from the combo box, this executes a form/query to look for associated workorders. If the result is null, I need a MsgBox to pop up indicating so instead of an empty form showing up. So far here is the VBA code:

After Update:

If (WorkOrderType.Text = "Renewal") Then
If MsgBox("Search for Associated WorkOrders?", vbQuestion + vbYesNo, "WorkOrder_Association_Query") = vbYes Then


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "WorkOrder_Association_Query"
DoCmd.OpenForm stDocName

Should I add something to this script to look for an empty result or should I add it to the query/form that executes? A code sample would be very helpful to me. Thanks!

[pbaldy]

One way would be to use a DCount to test that query. Open the form if the count is greater than 0, message box if not.

[Bruce]

Been playing with this:

If (WorkOrderType.Text = "Renewal") Then

If MsgBox("Search for Associated WorkOrders?", vbQuestion + vbYesNo, "WorkOrder_Association_Query") = vbYes Then

If DCount ("SID", "WorkOrders", "L4ID=!Forms!ADD_WorkOrders!L4ID") = 0 Then
MsgBox "No WOrkOrders Found", vbOKOnly
Else

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "WorkOrder_Association_Query"
DoCmd.OpenForm stDocName

But get the error "You already cancelled that operation". I didn't spell anythign wrong or reference a non existent field so....wassup

[Bruce]

never mind the previous code example. it's not valid...however....the idea is:

If WorkOrder Type = Renewal
DCount "SID" from "table" where L4ID = CurrentForm.L4ID
If DCount value = 0 Then MsgBox "No Orders Found"
Else
DoCmd "WorkOrder_Association_Query
.
.
.
ElseIf
.
.
.

[pbaldy]

The logic is sound. The syntax on the DCount is off a little:

DLookup Usage Samples

[Bruce]

I think it's a problem with the If, Then, Else, Else If, End If sequencing.

[pbaldy]

I don't see a problem with that offhand, though you didn't post the whole code. I wouldn't use ".Text" though, as that requires focus.

[Bruce]

Ok, almost got it:

Dim CountJobs As Long

CountJobs = DCount("SID", "WorkOrders", "L4ID=""" & Me.L4ID.Value & """")
If (CountJobs = 0) Then
MsgBox "No Associated WorkOrders Found", vbOKOnly

End If

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "WorkOrderAssociation_Query"
DoCmd.OpenForm stDocName

End If

problem is after clicking OK to "No WorkOrders Found" the WorkOrderAssociation_Query still runs. Do I need an Else or Else If

[pbaldy]

There must be more, as you have 1 If and 2 End If's. That said, yes, you want an Else:

If (CountJobs = 0) Then
  MsgBox "No Associated WorkOrders Found", vbOKOnly      
Else
  'Open the form
End If

Personally I put all Dim statements at the top of the module. Makes them easier to find rather than being sprinkled throughout the code.

[Bruce]

Here's the full AfterUpdate event code:

Private Sub WorkOrderType_AfterUpdate()
If (WorkOrderType = "Activation") Or (WorkOrderType = "Renewal") Then

Me.OpenDate.Enabled = True
Me.CloseDate.Enabled = True
Me.TimeStamp.Enabled = True

Else:

Me.OpenDate.Enabled = False
Me.CloseDate.Enabled = False
Me.TimeStamp.Enabled = False

End If

If (WorkOrderType = "Renewal") Or (WorkOrderType = "Deactivation") Then
If MsgBox("Search for Associated Cases", vbOKOnly, "Case Search") Then

Dim CountJobs As Long

CountJobs = DCount("SID", "WorkOrders", "L4ID=""" & Me.L4ID.Value & """")
If (CountJobs = 0) Then
MsgBox "No Associated WorkOrders Found", vbOKOnly

End If

'Else

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "WorkOrderAssociation_Query"
DoCmd.OpenForm stDocName
End If

ElseIf (WorkOrderType = "Modification") Then
If MsgBox("Do you wish to modify WorkOrder Info?", vbQuestion + vbYesNo, "Edit_WorkOrders_Query_Form") = vbYes Then

stDocName = "Edit_WorkOrders_Query_Form"
DoCmd.OpenForm stDocName

ElseIf MsgBox("Do you wish to modify Customer Data?", vbQuestion + vbYesNo, "Edit_Customer_Query_Form") = vbYes Then
stDocName = "Edit_Custoemer_Query_Form"
DoCmd.OpenForm stDocName

ElseIf MsgBox("Do you wish to modify Billing Data?", vbQuestion + vbYesNo, "Edit_Billing_Query_Form") = vbYes Then
stDocName = "Edit_Billing_Query_Form"
DoCmd.OpenForm stDocName
End If
End If
End Sub

I have tried commenting out the End If and the Else and then both and then neither. The current code show above is as close as I can get...except the WorkOrderAssociation_Query still runs if No Associated Work Orders are Found. THANKS!

[pbaldy]

Does seeing the code indented help?

  If (WorkOrderType = "Activation") Or (WorkOrderType = "Renewal") Then

    Me.OpenDate.Enabled = True
    Me.CloseDate.Enabled = True
    Me.TimeStamp.Enabled = True

  Else:

    Me.OpenDate.Enabled = False
    Me.CloseDate.Enabled = False
    Me.TimeStamp.Enabled = False

  End If

  If (WorkOrderType = "Renewal") Or (WorkOrderType = "Deactivation") Then
    If MsgBox("Search for Associated Cases", vbOKOnly, "Case Search") Then

      Dim CountJobs As Long

      CountJobs = DCount("SID", "WorkOrders", "L4ID=""" & Me.L4ID.Value & """")
      If (CountJobs = 0) Then
        MsgBox "No Associated WorkOrders Found", vbOKOnly

      End If

      'Else

      Dim stDocName As String
      Dim stLinkCriteria As String

      stDocName = "WorkOrderAssociation_Query"
      DoCmd.OpenForm stDocName
    End If

  ElseIf (WorkOrderType = "Modification") Then
    If MsgBox("Do you wish to modify WorkOrder Info?", vbQuestion + vbYesNo, "Edit_WorkOrders_Query_Form") = vbYes Then

      stDocName = "Edit_WorkOrders_Query_Form"
      DoCmd.OpenForm stDocName

    ElseIf MsgBox("Do you wish to modify Customer Data?", vbQuestion + vbYesNo, "Edit_Customer_Query_Form") = vbYes Then
      stDocName = "Edit_Custoemer_Query_Form"
      DoCmd.OpenForm stDocName

    ElseIf MsgBox("Do you wish to modify Billing Data?", vbQuestion + vbYesNo, "Edit_Billing_Query_Form") = vbYes Then
      stDocName = "Edit_Billing_Query_Form"
      DoCmd.OpenForm stDocName
    End If
  End If

If not, the fix is attached, in my crude artistically-challenged way.

[Bruce]

Thanks, that did it!

[pbaldy]

No problemo, glad we got it sorted out.

[Bruce]

Now...I need to figure out how to add more criteria to the DCount function.

How would I structure the DCount to include "WorkOrderType=Activation" OR "WorkOrderType=Renewal" AND "CloseDate>Now()"

while keeping the L4ID field in tact.

I tried to add these but it stopped working.

Thanks froma Rookie!
bruce

[pbaldy]

At the bottom of that link there was an example of a multi-criteria DLookup. Does that help you solve it?