Check checkbox on main form if any record has been entered in the subform

I have a checkbox on a main form that I would like to be checked (true) if any single record has been entered in the forms subform.

How can I accomplish this?
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
If this is a bound control, you would set the value of it in the AfterUpdate event of the subform.  In order to account for deletes, you should actually count the rows each time using DCount() and if the count is > 0, then update the field in the main form.
If DCount("*", "yoursubformqueryortable", "ForeignKey = " & Me.ForeignKey) > 0 Then
    Me.Parent.chkSubformRecords = True
Else
    Me.Parent.chkSubformRecords = False
End If

Open in new window

Gustav BrockCIOCommented:
It would be a lot simpler to use the RecordsetClone:

    Me.Parent!chkSubformRecords = CBool(Me.RecordsetClone.RecordCount)

/gustav
SteveL13Author Commented:
I tried this but it doesn't work.

Private Sub Form_AfterUpdate()

    If DCount("*", "tblLineItemsAddlComponents", "ID = " & Me.txtID) > 0 Then
        Me.Parent.chkbxMasterItem = True
    Else
        Me.Parent.chkbxMasterItem = False
    End If

End Sub

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jeffrey CoachmanMIS LiasonCommented:
Clearly define "doesn't work"?
SteveL13Author Commented:
If I enter a record in the subform the checkbox does check (although I have to close the form and re-open it to see it checked).  

But if I remove the record in the subform the checkbox stays checked even after closing the form and re-opening it.
PatHartmanCommented:
You may be able to get the parent form to show the update by using  repaint.
Me.Parent.Repaint
If that doesn't work, then you will need to force the parent record to save.  You can do that using Refresh.
Me.Parent.Refresh

Is MasterItem defined as Boolean in the table?
Gustav BrockCIOCommented:
You would have to call this both in the AfterInsert and AfterDelete events:

    Me.Parent!chkSubformRecords.Value = CBool(Me.RecordsetClone.RecordCount)

The AfterUpdate event is not needed.

 /gustav
SteveL13Author Commented:
First of all the checkbox field is a Yes/No field in the table.  I guess I don't know how to define it as Boolean in the table.

So then I tried:

Me.Parent!chkSubformRecords.Value = CBool(Me.RecordsetClone.RecordCount)

in the AfterInsert and OnDelete events and the checkbox stayed checked even though I had removed the record in the sub-report.
Gustav BrockCIOCommented:
You may need to run similar code in the OnCurrent event of the parent form:

Me!chkSubformRecords.Value = CBool(Me!NameOfYourSubformControl.Form.RecordsetClone.RecordCount)

Open in new window


/gustav
SteveL13Author Commented:
I have added this to the oncurrent event of the parent form:

Me!chkbxMasterItem.Value = CBool(Me!subfrmLineItemsAddlComponents.Form.RecordsetClone.RecordCount)

Open in new window


But the checkbox on the parent form still does not check if I add a record to the subform or uncheck if all records have been deleted from the subform unless I close the form and reopen.
Gustav BrockCIOCommented:
It will update when a first record has been inserted
But you are right, it will not update when the last has been deleted. except if you move the code from the Delete event to:

     Private Sub Form_AfterDelConfirm(Status As Integer)

In any case, you con't have to close the main form, just move to another record.

This is tested. If your forms don't behave, something else is going on.

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.