Counting Check boxes

[RichO]

ChrisO,

Did you get the PM I sent you?

I had explained the whole problem about why I was having trouble getting the forms and everything else into a file to send to you. I spent about an hour on it and gave up because there was always something else it was looking for. Also the tables are stored in SQL server and I was getting errors in trying to convert to Access 97.

Could this same task be accomplished by cycling through the recordset and summing the check box values?

I tried this but it looks like it only catches the first record in the recordset...

Dim db As Database
Dim rs As Recordset
Dim sql As String
Dim chex As Integer

Set db = CurrentDb

sql = "SELECT Check_Box FROM tb_Promo_Artist_Quotes WHERE Promo_ID = " & Me.Promo_ID
Set rs = db.OpenRecordset(sql)
If Not rs.EOF Then
chex = chex + rs!Check_Box
End If
rs.Close
Set rs = Nothing
Set db = Nothing

If chex <> 0 Then DoCmd.OpenReport "Promotion Schedules", acPreview, , "Promo_ID = " & Me.Promo_ID

Maybe you can tell me what is missing here...

Thanks again.

 

[ChrisO]

Yes I did get your PM and I replied on the 22 Feb. This was my reply.

----------
No problems RichO.

Believe it or not we all started there and some of us without the help of boards such as this.

There is a recordset way to do it and a NoData event in the report that could be used also. However, the method I have already given seems the simplest and whatever is causing it to fail may also cause the other methods to fail as well.

Don’t worry to much about the errors that you get when converting to A97.

The error message probably says something like “there have been errors during the conversion and will need to be fixed blah, blah blah.” I should be able to fix those.

If after zipping the db and it is still larger than 100K you could e-mail it to me directly at
(My email address was here)

If we can get this one sorted, I’ll write up the recordset method and the NoData method as well.

Regards,
Chris.
----------

Maybe your not setup to receive PM’s???

I’d prefer not to start on another method because we would simply be throwing more errors at the problem.

Anyway, can you simply post the Form/Sub-Form in A97 please and I’ll try with just that.

Regards,
Chris.

 

[ChrisO]

G’day RichO

Got the email OK.

Problem was that the sub-form was based on an SQL statement.

Please note the comment in my first reply: -
' It should work OK if the Record Source of the sub-form is a Table or Query but will
' not work if the Record Source of the sub-form is a SQL statement.

Try making sub-form tb_Promo_Artist_Quotes Record Source qryPromoArtistQuotes

Regards,
Chris.

 

[RichO]

Yes I read that the first time you posted it but I guess it really didn't register because all along I didn't realize that the subform was based on a SQL statement.

What exactly do you think was this programmer's reason for using SQL instead of using the referenced query directly?

Thanks again for all your help.

 

[ChrisO]

G’day RichO

That’s a very good question but I hope you don’t want a good answer.

“What evil lurks in the hearts of men.” (Assuming it was a man of course. )

SELECT [tb_Promo_Artist_Quotes].[Promo_ID] AS Expr1,
       [tb_Promo_Artist_Quotes].[Artist_Name] AS Expr2,
       [tb_Promo_Artist_Quotes].[Price_Quote] AS Expr3,
       [tb_Promo_Artist_Quotes].[Send_Schedule] AS Expr4,
       [tb_Promo_Artist_Quotes].[Discount_Description] AS Expr5,
       [tb_Promo_Artist_Quotes].[CDJ_Area] AS Expr6
FROM qryPromoArtistquotes;

Especially considering that the Controls were bound to the proper names of the fields and not Expr1, Expr2, Expr3, Expr4, Expr5 and Expr6.

This may have come about if qryPromoArtistquotes did not exist and the Form was opened in design mode.

By the way, one of the Reports “Promotion CDJ” is totally corrupted in the copy you sent me.

Would you still like the DAO and NoData methods?

The DAO method is straightforward enough.

The DoData method usually requires bouncing off the error handler in the procedure that attempts to open the Report but I tend to think it is usually best to prevent errors rather than compensate for them.

Regards,
Chris.

 

[RichO]

I tried using the On No Data event in the report but it still takes the time to query the data and then there is the message box that gets in the way.

I would like to see other possible methods if nothing else, for the learning purpose.

Thanks Chris.

 

[RichO]

Hi ChrisO,

Well, we're making progress here but I ran into a roadblock.

I changed the subform's record source to a query instead of the SQL and everything appears to run as normal. I no longer get the error from the Nz(Dsum... calculation but it still does not work, and here's why.

The table tb_Promo_Artist_Quotes used by the subform contains thousands of entries linked to tb_Promotions by the primary key Promo_ID, in a one to many relationship. The code you supplied is correctly summing the check boxes in the table, but it is summing ALL of the check boxes, not just the ones that are linked to the current record in the form.

The reports that open are based only on the current record in the form, so I need to sum only those check boxes.

Any ideas how to fix this?

 

[ChrisO]

G'day RichO

Try adding this criteria to the DSum.

If Nz(DSum("Send_Schedule", Me.tb_Promo_Artist_quotes.Form.RecordsetClone.Name, "Promo_ID = " & [Promo_ID]), False) = False Then
    MsgBox "No data for Report."
Else
    MsgBox "Report can open."
End If

I'll knock up a DAO method when I get a chance.

Regards,
Chris.

 

[RichO]

BINGO!

Thanks, that helped alot.


I'll keep an eye open for the DAO code.

 

[ChrisO]

G’day RichO.

You may wish to try the code in the On Current event as well…

Private Sub Form_Current()

    Me.cmdPrintPromotion.Enabled = Not Me.NewRecord
    
End Sub


Private Sub cmdPrintPromotion_Click()
    Dim blnCheckBoxFound As Boolean
    Dim rstClone         As DAO.Recordset
    
    On Error GoTo ErrorHandler
    
    Me.tb_Promo_Artist_quotes.Form.Dirty = False
    
    [color=green]'  Method 1[/color]
    If Nz(DSum("Send_Schedule", Me.tb_Promo_Artist_quotes.Form.RecordsetClone.Name, "Promo_ID = " & [Promo_ID]), False) = False Then
        MsgBox "No data for Report."
    Else
        MsgBox "Report can open."
    End If
    [color=green]'  End Method 1
    
    
    '  Method 2[/color]
    Set rstClone = Me.tb_Promo_Artist_quotes.Form.RecordsetClone
    rstClone.MoveFirst
    
    blnCheckBoxFound = False
    
    Do Until rstClone.EOF
        If (rstClone![Send_Schedule] = True) Then
            blnCheckBoxFound = True
            Exit Do
        End If
        rstClone.MoveNext
    Loop
    
    If (blnCheckBoxFound = False) Then
        MsgBox "No data for Report."
    Else
        MsgBox "Report can open."
    End If
    [color=green]'  End Method 2[/color]


ExitProcedure:
    On Error Resume Next
    Set rstClone = Nothing
    Exit Sub

ErrorHandler:
    MsgBox Err.Description
    Resume ExitProcedure
    
End Sub

Hope that makes some sense.

Regards,
Chris.

 

[RichO]

OK most of this code makes sense to me...

I don't follow what this code is for:

Me.cmdPrintPromotion.Enabled = Not Me.NewRecord

...and why is rstclone dimmed as DAO.recordset instead of just recordset?

Most of the existing code in this database uses recordset as opposed to recordsetclone, so why one and not the other?

Thanks again.

 

[ChrisO]

G’day RichO

Why? Me.cmdPrintPromotion.Enabled = Not Me.NewRecord

Simplest way to explain it is to go to a new record on the Main form and press the Print Promotion button. An error occurs because the criteria will not be able to find [Promo_ID], [Promo_ID] does not exist until some data is entered into the Main form.

It therefore seems better to me to disable the button under this circumstance, if the user can’t push the button…no error.

Why is rstClone dimmed as DAO.Recordset instead of just Recordset?

It looks like the db was first written under A97 and there was no need to specify DAO because that’s all there was. By A2K Micro$oft had different ideas and wanted people to use the new ADO. In versions greater than A97 there is, by default, no reference to DAO and the disambiguated reference to a Recordset will cause an error in later versions.

Therefore, in versions greater than A97, a reference needs to be made to DAO AND that reference needs to be set as a higher priority than ADO IF it is to work simply as Recordset. If DAO.Recordset is specified then you will still need to create a reference to DAO under Tools/References but the priority doesn’t matter.

I would have as a guess this is the most commonly asked question about Access and that is the reason most sites have a FAQ which answers the question, Mile-O did on this site. (Worth a read.)

Another question which sometime arises is what is the difference between DAO and ADO?
Answer…dyslexia; commonly refereed to as DNA, “National Dyslexia Association”.

However, more often than that the question arises, which should I use?
Answer… a can of worms.
Speed also seems to play a part in large record sets. DAO seems to usually win over ADO, but of course, they must be both doing the same job and it must be timed.

Hope that helps.

Regards,
Chris.