Counting only 'checked' Yes/No fields on a

[pwdpwd]

I have a continuous form in 2007 that has a text field and a yes/no check box, bound to a query. The user can select as many of the check boxes as required; the functionality works fine, but I want to build in a check before they exit the form that they have selected at least one.



I have worked out a way to count the total of the Yes/No fields on the form, but how do I count only the fields that have been checked?

I have messed around with GetDatainField, but I have had no luck.

Any help would be gratefully appreciated

[jgelpi16]

What are you using to count the Yes/No fields? The value for the check-box when checked is -1. Could you only count the Yes/No field if it was a -1?

[ajetrumpet]

I have a continuous form in 2007 that has a text field and a yes/no check box, bound to a query. The user can select as many of the check boxes as required; the functionality works fine, but I want to build in a check before they exit the form that they have selected at least one.

my 2 cents:

a query is automatically saved by form updates when you change from record to record, right?? Well, why not, on exit of the form, simply use DCOUNT() and check the query itself for the number of -1's you have??

[jgelpi16]

That's why they pay you the big bucks Adam!

[ajetrumpet]

That's why they pay you the big bucks Adam!

That's right, bud! I'm the end all be all of human existence. NOT.

[pwdpwd]

Hi Guys - Thanks very much for the help so far, but the VB that I'm using gives erratic results (which I've no doubt is my fault!).

The following is the current state of the code for the <Exit> form button:

Private Sub Command12_Click()
Dim subavail As Integer
Let subavail = DCount("*", "qry_subjectsavailable")
If subavail = 0 Then
    Dialog.RichBox "You have not selected a Subject!", vbExclamation, "No Subject Selected!", , , ButtonDelay:=1
    Exit Sub
Else
    DoCmd.Close
    DoCmd.OpenForm "frmSubject"
End If
End Sub

I've also used a fuller version of DCount - =DCount("[subjectavailable]","tblsubjecttitles","[subjectavailable]=-'1'") with the same results.

What happens is that the first time that there are no subjects selected, the form exits back to the main form; the second time the message box pops up.

I've also used the code in a text box so that I can advise the users how many they have selected; the correct number shows, but it doesn't update in real time.

Any help would be greatly appreciated.

[jgelpi16]

I think I see one issue with your "full" version of DCount(). Your code states:

=DCount("[subjectavailable]","tblsubjecttitles","[subjectavailable]=-'1'")

I think it needs to read:

=DCount("subjectavailable","tblsubjecttitles","[subjectavailable]=-1")

OR

Dim blnTrue as Boolean
blnTrue = True
=DCount("subjectavailable","tblsubjecttitles","[subjectavailable]= " & blnTrue & "")

I am not positive, but I think since you surrounded your 1 with '1' that it was treating it as text? I typically use variables in my DCounts/DLookups so I'm not positive on that.

[ajetrumpet]

Dim blnTrue as Boolean
blnTrue = True
=DCount("subjectavailable","tblsubjecttitles","[subjectavailable]= " & blnTrue & "")

try:

Dim blnTrue as Boolean
blnTrue = True
=DCount("subjectavailable","tblsubjecttitles","[subjectavailable]= " & blnTrue)

I'm not even sure if booleans can be concated out like that.

[pwdpwd]

Hi Guys - Thanks very much for the suggestions - I used the correct version of the DCount statement as the control of a text box (that I can use as a warning message, and also to check what was happening) and also in the VB. Both worked, but they were not updating in 'real time' - both the text field and the vb functionality were both '1 step behind' as it were.

I then reasoned that I needed to do something with the checkbox; I did a bit of digging and used 'SaveRecord' as the after update event for the checkbox and the functionality of the exit button worked fine; not sure if its the right way of doing it, or if there's a better/simpler way, but it works.

The number in the text box is still out of synch, but I can live without that.

Thanks again for all your help - much appreciated.

[ajetrumpet]

ok, marked solved!