Enable a button on Main form based on records in the subform

[Hello1]

I have a button on the Main form which is disabled, a subform with a combobox which has 3 columns, 1. ID, 2.Name and 3.Vaild (is a check box).
Now that button on the main form should change to enabled if the combobox colum(3) = True, and this works fine but the problem is if the subform contains more than one record. Every record will have that combobox.
The VBA code which I use

Private Sub Form_Current()
If Me.sbfName.Form!cboComboBox.Column(2) = -1 Then
        Me.cmdButton.Enabled = True
    Else
        Me.cmdButton.Enabled = False
    End If
End Sub

I suppose this code only checks the first record of the subform?
However, I need the button to be enabled if one or more records of the subform have the combobox.column(2) = True
Thanks

 

[theDBguy]

You could try using DCount() and count the True values in the source table.

 

[Hello1]

Thanks for the reply, I have been researching the DCount() but cant figure it out how to. The subform source doesnt contain the table which has the True value, the table is only in the combobox source through the query builder.

I tried by adding a txtValue field in the subform Detail section and then I put the Control Source to =[cboComboBox].[Column](3), then I added a txtCount in the header of the subform and tried this DCount([txtValue];[cboComboBox];-1) but of course I get an Error in that filed, just #Error.

 

[theDBguy]

Hi. To properly help you, you may have to post a sample copy of your db with test data.

 

[Hello1]

Its in another language and quite messed up
If I dont manage I will make an example database and upload.
So far no luck, I tried a Count expression in the control source of txtCount, to count all the true values

=Count(IIf(Nz([txtValue];0)=-1;1;Null))

tried also

=Abs(Sum([txtValue]))

In the txtValue filed I get the right values, eithe -1 or 0 but in the txtCount with the expressions it just doesnt want to work, gives me #Error

 

[theDBguy]

Hi. Just as an idea, create a query where you can combine the source table of the subform and the source table of the combobox, so you can have the True column you can count using DCount().

 

[Hello1]

Yup that was it. I added the table and the filed to the source of the subForm and then I added the field (Value) to the subform detail. After than I used the =Abs(Sum([Value])) in control source of an unbound txt control (txtCount) and it works now.
Now should I leave it like this or is it better to go for the DCount? Right now I dont see any problems with leaving it as it is, for the button to enable I will just go

If Me.txtCount <> 0 Then
  Me.Button.Enable = True 
Else
  Me.Button.Enable = False
End If

Thanks again!

 

[theDBguy]

Hi. I think you can leave it as is. Using DCount() will create an extra trip to the table. Cheers!

 

[Hello1]

Hmm still the button doesnt enable but in the code it does when I go through debugging.
I even tried this function which I found and modified it. In the debugging everything works but on the form the button still stays disabled.

Function CountChecked() as Integer
Dim intTotalChecked As Integer 
Dim ctl As Control 
 
intTotalChecked = 0 
For Each ctl In Me 
   If ctl.ControlType = acCheckBox Then intTotalChecked = intTotalChecked - ctl.Value 
Next 
 
CountChecked = intTotalChecked 
End Function

Strange is that it does work when I use it as in the first post I wrote but when I want to move or count that value it does work in code but not on the form.

Edit: Made a new database to test and it works fine there, must be some other code interfering or something then, I dont know

 

[Hello1]

I found a "solution".
So now it orks like this...
I have a Forms which has the disabled button "cmdButton", in that form is a subform which has a check box field "Value". Also in the subform there is a text box "txtCount" which isnt bound to anything and its control source is = Abs(Sum([Value])), it shows the number of check boxes which have the True value.

Now the VBA code which should enable the "cmdButton" if the "txtCount" is not equal to 0

Private Sub Form_Current()
    If Me.sbfForm.Form!txtCount <> 0 Then
       Me.cmdButton.Enabled = True
    Else
       Me.cmdButton.Enabled = False
    End If
End Sub

As I said this doesnt want to work on the Form while in the debugging it works.
I added another button to the main form "cmdButton1" and copied the VBA code to the buttons on click event.

Private Sub cmdButton1_Click()
    If Me.sbfForm.Form!txtCount <> 0 Then
       Me.cmdButton.Enabled = True
    Else
       Me.cmdButton.Enabled = False
    End If
End Sub

This way the code works on the form and the "cmdButton" enables if "txtCount" <>0 but when this code is in the On Current event it doesnt want to work.
What could be the reason?
Could it be that the code executes too fast and the txtCount Expression is too slow to calculate on time, or something similar?
Thanks

 

[MajP]

It could be a timing issue like you say, where you are getting the value before it is actually calculated. I would try

Private Sub Form_Current()
   Doevents
   me.recalc  'I think that recalcs the sub as well
   'for testing see if the value is what you expect
   Msgbox Me.sbfForm.Form!txtCount 
   If Me.sbfForm.Form!txtCount <> 0 Then
       Me.cmdButton.Enabled = True
    Else
       Me.cmdButton.Enabled = False
    End If
End Sub

 

[MajP]

Not a big deal, but you can toggle things easier with code like this instead of if then.

Me.cmdButton.Enabled = (Me.sbfForm.Form!txtCount <> 0)

The right side returns true or false.

 

[Hello1]

It could be a timing issue like you say, where you are getting the value before it is actually calculated. I would try

Private Sub Form_Current()
   Doevents
   me.recalc  'I think that recalcs the sub as well
   'for testing see if the value is what you expect
   Msgbox Me.sbfForm.Form!txtCount 
   If Me.sbfForm.Form!txtCount <> 0 Then
       Me.cmdButton.Enabled = True
    Else
       Me.cmdButton.Enabled = False
    End If
End Sub

No luck. When I go through debugging the msg box returns the right value but when I do it without debugging, so normal form use, it returns always 0. And I can see that the "txtCount" in the subform is empty when the msg box is open.
I tried adding Me.sbfForm.Form.Recalc but didnt help.
I will have to continue tomorrow, quite late now.
Thanks for the help!

 

[MajP]

It sounds like a timing issue. How about going the other direction and have the subform set the parent form. In the subforms on current

me.parent.cmdbuttonEnabled = (Me.txtCount <> 0)

You may need some error trapping because when the parent is loading this will likely error.

 

[Hello1]

It didnt work either, I found a delay function which was working sometimes but was too slow and didnt work always

Public Function Pause(NumberOfSeconds As Variant)
    On Error GoTo Error_GoTo

    Dim PauseTime As Variant
    Dim Start As Variant
    Dim Elapsed As Variant

    PauseTime = NumberOfSeconds
    Start = Timer
    Elapsed = 0
    Do While Timer < Start + PauseTime
        Elapsed = Elapsed + 1
        If Timer = 0 Then
            ' Crossing midnight
            PauseTime = PauseTime - Elapsed
            Start = 0
            Elapsed = 0
        End If
        DoEvents
    Loop

Exit_GoTo:
    On Error GoTo 0
    Exit Function
Error_GoTo:
    Debug.Print Err.Number, Err.Description, Erl
    GoTo Exit_GoTo
End Function

Because it wasnt optimal I decided not to use it.
The solution was to create a query and copy the SQL to VBA:
The query was of 4 tables. First one the source table of the main form, the second one the source table of the subform, third was a table related to the second table, I need it because the table containing the Value (which is the fourth table) is related to it. The fields I took were the ID of the first table which had the criteria to match the ID on the main form, then I added the Value field and a custom field named Count: =Abs(Sum([4thTable].[Value])). After that I went to the SQL view and copied the SQL and pasted it in the VBA.

 Set MyDb = CurrentDb
    Set MyRs = MyDb.OpenRecordset("The SQL of the query I made")
	Me.cmdButton.Enabled = (MyRs!Count <> 0)

Now it works perfectly, the button enables immediately and disables too, depending on the Value ofc.
However, I have a problem now when I want to add a new record because the new record doesnt have an ID yet nor the Value so I get an error when I want to add a new record.
Any idea how could I work around that?
Thanks!

 

[Hello1]

Is there a way to say if its a new record then dont do this code sth like

If Not acNewRec Then
    Set MyDb = CurrentDb
    Set MyRs = MyDb.OpenRecordset("The SQL of the query I made")
    Me.cmdButton.Enabled = (MyRs!Count <> 0)
End If

Of course I tried this hehe but the value of acNewRecord is always the same and it doesnt skip the code if I go to new record.

 

[Gasman]

Me.NewRecord ?

 

[Hello1]

lol sorry I quite often tend to miss simple things, still new to all this
Thanks!

 

[MajP]

Maybe do this simpler. Make a group by query where you group on the ID in the main form and count the checkbox field where it is not null. So you should have a query that returns

ID  CountOfChecked
1     1
2     0
3     4

Now you can do a simple dlookup
Me.cmdButton.enabled = (dlookup("countOfChecked", "qryCounts", "ID = " & Me.id) > 0)

 

[Hello1]

I tried it but the query gives all the checkboxes back 0 and -1 no matter if I put in criteria True or Is Not Null.
Anyway, instead the the CountOfChecked I made a custome field CountChecked besides the ID and added the function Abs(Sum([4thTable].[CountOfChecked])) and the query returns the right results. Then I just followed your DLookup code and works same as my solution with less code
Now which one to keep I will see probably your solution but I will comment mine just to know there is another way to do it

Thanks again for the help guys, I guess now everything works as I wanted it to