asked on

Make checkbox true if certain situation exists

I have a form that has a checkbox. I want the checkbox to be checked (true) if records exist in tblOutpurchase where the field EstN is equal to the field "txtRealEstN" on the form. Here is my code for teh oncurrent event of the form which is not working. Can someone spot the problem for me?

If Len([tblOutsidePurchase]![EstN] & Me.realEstN & "\*.*") = 0 Then
Me!chkbxHasOP = False
Else
Me!chkbxHasOP = True
End If

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

I tried both but EstN is definitely a text field. Neither one works.

SteveL13

ASKER

Whoops. I was wrong. Every form record I open now has the checkbox checked using...

Me.chkbxHasOP= dcount("*","tblOutsidePurchase","EstN='" & me.txtRealEstN & "'")> 0

SteveL13

ASKER

I only want the checkbox checked if a record exists in tblOutsidePurchase that has records in EstN that relate to the field "txtRealEstN" on the form "frmEstimateHeader" which is the form being opened that has the checkbox.

Rey Obrero (Capricorn1)

so it is working now?

Rey Obrero (Capricorn1)

what type of form do you have?

SteveL13

ASKER

No. No matter what record I open to on the form, the checkbox is checked even if no records exist in tblOutsidePurchase for the "txtRealEstN" on the form the checkbox is on.

SteveL13

ASKER

I'm sorry.. I don't know what you mean by what type of form do I have.

Rey Obrero (Capricorn1)

what is the RecordSource of your form?

Rey Obrero (Capricorn1)

is your form Single, continuous, datasheet?

Rey Obrero (Capricorn1)

does the value in textbox "txtRealEstN" changed for every record?

SteveL13

ASKER

Recordsource = tblMyEstimates
Is a single form
On the form, frmEstimateHeader, the textbox "txtRealEst" does change for every record in the table, "tblEstimateHeader"

Rey Obrero (Capricorn1)

ok.,
the Recordsource of the form frmEstimateHeader is tblMyEstimates ? is this correct?

how is the table "tblEstimateHeader" got involve here?

SteveL13

ASKER

the Recordsource of the form frmEstimateHeader is tblMyEstimates ? is this correct? YES

how is the table "tblEstimateHeader" got involve here? You asked my what the recordsource of the form with the checbok is.

Rey Obrero (Capricorn1)

so you have a form/subform setup?

SteveL13

ASKER

Yes... the main form is frmEstimateHeader. The subform is frmOutpurchase. If there are records in frmOutpurchase for the txtRealEstN the I want the checkbox on frmEstimateHeader to be checked.

Rey Obrero (Capricorn1)

ok. upload a copy of the db...

SteveL13

ASKER

Ok. See file.

Please note before you do anything. tblOutpurchase has no records for SCL-168 which IS a record in tblMyEstimates.

So, when you open frmEstimateHeader, for estimate SCL-168, the checkbox "chkbxHasOP" should NOT be checked because thare are NO records in tblOutpurchase for estimate SCL-168. But what is happening is that by openeing the form frmEstimateHeader to that record, it is checking the checkbox and should not be doing so since there are no records in tblOutpurchase for the estimate.

But if you go to Est SCL-162 in the form frmEstimateHeader the checkbox SHOULD be checked because there are records in tblOutpurchase for that estimate.
Example.accdb

SteveL13

ASKER

I think I found the problem. Here is what I have now based on your first suggestion: (note the =0 instead of >0). Plus I switched the True and False from my first question.

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