Determine if any checkbox is true in datasheet (1 Viewer)

Jupie23

Registered User.
Local time
Today, 01:13
Joined
Nov 9, 2017
Messages
84
I have a form with a datasheet subform, which contains a combobox of documents to select from. They can add as many documents as they want. 3 of those documents require a notary, 6 do not. If the user chooses a document that requires a notary, Notary Required is automatically checked on the subform and table. If they select, for example, 5 documents, if any one of them requires a notary, I need rptCollLetterNotary to open. If all 5 documents do not need a notary, then rptCollLetter needs to open.

Is it better to check if the checkbox is checked on the form, or to check the table? I'm not very familiar with SQL or Dlookups if that is what I need to do, will need some guidance on that. Thank you!
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:13
Joined
Oct 29, 2018
Messages
21,358
Hi. I'm not sure if it matters where you check it. I think either form or table should work. However, it's really hard to tell without seeing your database.
 

Jupie23

Registered User.
Local time
Today, 01:13
Joined
Nov 9, 2017
Messages
84
I have a main table (tblExceptions) that contains all of the other information that will appear on the reports, like customer name, address, etc, and then a related table (tblLetterJunction) just for documents needed that only contains:

ID
ExceptionID (from main table)
Document Needed (#)
Notary (yes/no).
 

Jupie23

Registered User.
Local time
Today, 01:13
Joined
Nov 9, 2017
Messages
84
Also, I tried doing code that if the checkbox was checked for notary, to open the notary letter, but it didn't open accurately if there were some boxes checked and some not checked in the datasheet.

Code:
If [Forms]![frmTobeProcessed]![frmDocSub].[Form]![chkNotary] = -1 Then
    DoCmd.OpenReport "rptCollLetterNotary", acViewPreview, , "[ID]=Forms!frmTobeProcessed!ID"
ElseIf [Forms]![frmTobeProcessed]![frmDocSub].[Form]![chkNotary] = 0 Then
    DoCmd.OpenReport "rptCollLetter", acViewPreview, , "[ID]=Forms!frmTobeProcessed!ID"
ElseIf [Forms]![frmTobeProcessed]![frmDocSub].[Form]![chkNotary] = 0 And [Forms]![frmTobeProcessed]![frmDocSub].[Form]![chkNotary] = -1 Then
    DoCmd.OpenReport "rptCollLetterNotary", acViewPreview, , "[ID]=Forms!frmTobeProcessed!ID"
End If
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:13
Joined
Oct 29, 2018
Messages
21,358
I have a main table (tblExceptions) that contains all of the other information that will appear on the reports, like customer name, address, etc, and then a related table (tblLetterJunction) just for documents needed that only contains:

ID
ExceptionID (from main table)
Document Needed (#)
Notary (yes/no).
Hi. Would you consider posting a demo copy of your database showing the particular area you need help with?
 

Jupie23

Registered User.
Local time
Today, 01:13
Joined
Nov 9, 2017
Messages
84
Here you go. I took out a lot of info so it looks weird, but this should cover what I need. If the datasheet has any checks=true, then I need the notary letter. If none checked, then the other letter. Thanks for looking!
 

Attachments

  • Database1.accdb
    1.1 MB · Views: 95

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:13
Joined
Oct 29, 2018
Messages
21,358
Here you go. I took out a lot of info so it looks weird, but this should cover what I need. If the datasheet has any checks=true, then I need the notary letter. If none checked, then the other letter. Thanks for looking!
Okay, I hope this is what you meant...
 

Attachments

  • Database1(1).accdb
    560 KB · Views: 84

Users who are viewing this thread

Top Bottom