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

[Bruce]

Thanks Paul but....I can't find the link you mention.

[pbaldy]

I posted a link in post 5 to a good resource on syntax for DLookup's.

[Bruce]

Thanks Paul but no, it didn't help. I tried all different configurations and can not come up with the right sturcture for:

DCount (SID from WorkOrders where WorkOrderType=Activation
OR WorkOrderType=Renewal
AND CloseDate = >Now() AND L4ID = Me.L4ID.Value

You seem to be very good at this. Can you build it? After I get this working, I'm done....and will leave you alone.

Much Thanks!

[pbaldy]

"WorkOrderType='Activation' OR WorkOrderType='Renewal' AND CloseDate = > #" & Now() & "# AND L4ID = " & Me.L4ID.Value

You will likely need to add parentheses to clarify the And's and Or's.

[Bruce]

Thanks Paul, That string made it past the VBA debug (with a single)) at the end, but when I run it, the rest of the code doesn't seem to work. ex. If the count = 0, no MsgBox indicating so is shown. If count = 1, the DoCmd open form doesn't run. Hummm.

[pbaldy]

What is the exact code now?

[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 WorkOrders", vbOKOnly, "WorkOrder Search") Then
Dim CountJobs As Long
CountJobs = DCount("SID", "WorkOrderType='Activation' OR WorkOrderType='Renewal' AND CloseDate = > #" & Now() & "# AND L4ID = " & Me.L4ID.Value)
If (CountJobs = 0) Then
MsgBox "No Associated WorkOrders Found", vbOKOnly

Else
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "WorkOrderAssociation_Query"
DoCmd.OpenForm stDocName
End If
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_Customer_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

[pbaldy]

The part I gave you was just the criteria, or 3rd argument. From the look of it, you've left out the second argument, the table name.

[Bruce]

Sorry, copy & paste error:

CountJobs = DCount("SID", "WorkOrders", "WorkOrderType='Activation' OR WorkOrderType='Renewal' AND CloseDate = > #" & Now() & "# AND L4ID = " & Me.L4ID.Value)

I inserted the table name (WorkOrders). Still have the same problem. mentioned in the previous post.

Thanks for your continued support!

Bruce

[pbaldy]

When you say doesn't work, what is happening? Without the parentheses in the criteria I mentioned earlier, you might be getting an unexpected count. Do you know how to set a breakpoint and check the values during runtime? If so, what is the count returned and is it correct? If not:

http://www.baldyweb.com/Debugging.htm

[Bruce]

Thanks, when I say it doesn't work. 1. If DCount returns a value of "0" the MsgBox should appear indicating no associated records found. it doesn't. If DCount returns a value of "1", the WorkOrderAssociation_Query should launch and retrieve the record. it doesn't. I will try to set up a variable that displays the results of DCount but I'm so new to this stuff (VBA) it may take me a while.

p.s. I'm a Network Engineer by trade. I just started working with Access about 2-3 months ago. I've created a nice little database and this is the last "issue" before I can start using it in a production env. Wish me luck

Bruce

p.p.s The WorkOrderAssociation_Query uses the same criteria as the DCount formula we've been working on. I can run it by itself and see the results.

[pbaldy]

You're not in Northern Nevada are you? I know a Bruce who's a network engineer. If you're the Bruce working on my Symantec quote, I'll just come over there!

Yes, first order of business is determining what the DCount is returning, but frankly based on the code it should do one or the other. Try setting a breakpoint and stepping through the code one line at a time. You'll see the exact path the code takes, and perhaps figure out why it's not doing what you expect. Can you post the db?

[Bruce]

I wish....I'm in Northern Virginia (Telecom Capitol) waiting for 32" of snow to melt. I figured out the problem last night. Here's the final:

CountJobs = DCount("SID", "WorkOrders", "(WorkOrderType='Activation' OR WorkOrderType='Renewal') AND CloseDate > #" & Now() _
& "# AND L4ID=""" & Me.L4ID.Value & """")

Notice how the OR statement had to be enclosed and the "=" had to be removed from the CloseDate. I broke it down one at a time and had a little help from my wife who is a DB Design specialist Oracle 10G Master.
I usually don't like to ask her for help since VB is not her gig.

Appreciate your help!
Bruce

[pbaldy]

No problem Bruce, glad you got it sorted out.