Issues with code...

[Villarreal68]

Hello everyone,

I'll start by saying that this is my first DB..
I'm having a bit of a problem trying to get this to work maybe someone can give me some direction.

What I'm trying to do:
I want my main form to show a query result of work orders that are active. This is the code that I'm using under the "After Update" of a Builder's Names combobox AND "On Current" event of the main page., so when the user selects a Builder the result will show on a text box called txtWOCont. And refresh when the page changes to the next record.

The code works fine when there is something to report but as soon as the result is 0 it gives me an ugly error message.

If anyone can tell me what I'm doing wrong or what I can add to fix the issue or tell me if I'm going on totally the wrong way about doing it. Any and all help is welcome and appreciated.

Here's the code (keep in mind that this is a total amature trying to do something with Access):

If NewRecord Then
        Me.txtWOCount = " "
        
Else
        'Opening the report (minimized) that runs the query
        Dim stDocName As String
        stDocName = "rptBuilderActiveWO"
        DoCmd.OpenReport stDocName, acPreview
        
        'This is what I tried to do to fix the "#Error" event that happens_
        'when there is a zero response from the query 
            If IsError(Reports!rptBuilderActiveWO!txtCount) Then
            Me.txtWOCount = " "
            Else
            Me.txtWOCount = Reports!rptBuilderActiveWO!txtCount
            End If

        'Once the reslut is populated to txtWOCount I close the report.
        Dim stDocName1 As String
        stDocName1 = "CloseReport.CloseActiveWO"
        DoCmd.RunMacro stDocName1
    End If

Everything works fine until there is a zero value on the query. An ugly "Runtime Error Response comes up -2147352567 (80020009).

I ran a search in TechNet for this error but found nothing!

Thanks in advance for your time and efforts.

René

 

[pbaldy]

Try this, getting rid of all that stuff:

Me.txtWOCount = DCount("*", "NameOfReportQuery")

Where "NameOfReportQuery" is the name of the query that is the source for the report.

 

[Villarreal68]

Thank you!, Thank you!, Thank you!

Wow! Its definitively better when you know your stuff! I want to be like you when I grow old!

Simple to the point without going in circles for a response.

Thank you Sir! Learning lots! I have a long ways to go, but with this kind of help I'll get ther faster!

 

[pbaldy]

HEY! Who you calling old?!?

Okay, I'm old, but it's mean to point it out!

 

[Villarreal68]

You got it wrong! LOL!

I was talking about me!!! I'm 40 y.o. and I 'm barely in the process of learning MS Access. I've been in Networking for the past 17 yrs, I'm comfortable creating DOS batch files and scripts, but never thought I needed MS Access or any programming knowledge. And about 3 months ago I got a job as a Database/Network Admin (go figure!) and now I realize how dumb I was to never jump in the programming wagon!

Thanks for your help! And the oldie is me not you! LOL!

René

 

[pbaldy]

Ha! You're just a kid. I've got 10 years on you.