asked on

Make checkbox checked on 1nd form when 2nd form is closed

I have the following code in a close event of form 2. It is supposed to be checking a checkbox on form 1 (frmEstimateHeader") as true (or false) if the criteria is met. But it is not working. What am I doing wrong?

If Forms!frmEstimateHeader.chkbxHasOP = DCount("*", "tblOutPurchase", "EstN='" & Me.txtRealEstN & "'") = 0 Then
Forms!frmEstimateHeader.chkbxHasOP.Value = False
End If

If Forms!frmEstimateHeader.chkbxHasOP = DCount("*", "tblOutPurchase", "EstN='" & Me.txtRealEstN & "'") > 0 Then
Forms!frmEstimateHeader.chkbxHasOP.Value = True
End If

Dale Fye

How about just this single line:

Private Sub Form_Close

Forms!frmEstimateHeader.chkbxHasOP = DCOUNT("*", "tblOutPurchase", "EstN='" & me.txtRealEstN & "'")

End Sub

In Access, any non-zero number will evaluate to True

Rey Obrero (Capricorn1)

just use this one line

Forms!frmEstimateHeader.chkbxHasOP = DCount("*", "tblOutPurchase", "EstN='" & Me.txtRealEstN & "'") > 0

SteveL13

ASKER

Neither of these suggestions worked.

Rey Obrero (Capricorn1)

are you getting an error?

Dale Fye

I don't normally use wildcards in my domain functions. Does tblOutPurchase have an autonumber field? If so, try somethin like:

DCount("ID", "tblOutPurchase", "EstN='" & Me.txtRealEstN & "'")

Also, is txtRealEstN a numeric value, or a string. If it is numeric, try:

DCount("ID", "tblOutPurchase", "EstN=" & Me.txtRealEstN)

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Why are you trying to set a Check box with the value of what is apparently a numeric field ... and it's count ?

mx

SteveL13

ASKER

txtRealEstN is a text field.

All I want to do is check the checkbox if there are any records in tblOutpurchase that relate to the key field, EstN where the form field is txtRealEstN

SteveL13

ASKER

I am not getting an error.

Rey Obrero (Capricorn1)

this should do it, just one line

Forms!frmEstimateHeader.chkbxHasOP = DCount("*", "tblOutPurchase", "EstN='" & Me.txtRealEstN & "'") > 0

check the codes you are using

SteveL13

ASKER

Didn't work. Here is what I am using in the onload event of frmEstimateHeader which is working perfectly. If a record exists intblOutpurchase related to the key field, the checkbox is checked. If no records exist then the checkbox is not checked. AllI want to have happen is this same logic when the 2nd form is closed.

If Me.chkbxHasOP = DCount("*", "tblOutPurchase", "EstN='" & Me.txtRealEstN & "'") = 0 Then
Me!chkbxHasOP = False
End If

If Me.chkbxHasOP = DCount("*", "tblOutPurchase", "EstN='" & Me.txtRealEstN & "'") > 0 Then
Me!chkbxHasOP = True
End If

Rey Obrero (Capricorn1)

place this in the close event of 2nd form

Forms!frmEstimateHeader.setfocus
Forms!frmEstimateHeader.chkbxHasOP = DCount("*", "tblOutPurchase", "EstN='" & Me.txtRealEstN & "'") > 0

SteveL13

ASKER

Did not work. If I delete the record(s) in tblOutpurchase related to the key field and then close form 2, the checkbox on form 1 is still checked and should not be checked.

Rey Obrero (Capricorn1)

Forms!frmEstimateHeader.setfocus
Forms!frmEstimateHeader.chkbxHasOP = DCount("*", "tblOutPurchase", "EstN='" & Me.txtRealEstN & "'") > 0
Forms!frmEstimateHeader.requery

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SteveL13

ASKER

This is driving me crazy. I put this in the activate event of code 1:
If Me.chkbxHasOP = DCount("*", "tblOutPurchase", "EstN='" & Me.txtRealEstN & "'") = 0 Then
Me!chkbxHasOP = False
End If

If Me.chkbxHasOP = DCount("*", "tblOutPurchase", "EstN='" & Me.txtRealEstN & "'") > 0 Then
Me!chkbxHasOP = True
End If

And I put this in the onclode event of form 2:
Forms!frmEstimateHeader.SetFocus
Forms!frmEstimateHeader.chkbxHasOP = DCount("*", "tblOutPurchase", "EstN='" & Me.txtRealEstN & "'") > 0
Forms!frmEstimateHeader.Requery

And it is still not working correctly when the records are deleted from tblOutsidePurchases.

Rey Obrero (Capricorn1)

upload a copy of the db. (.mdb version)