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?
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

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

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

/gustav
0
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:
Clearly define "doesn't work"?
0
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.
0
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?
0
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
0
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.
0
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
0
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.
0
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
0

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.