We help IT Professionals succeed at work.

Make checkbox true if certain situation exists

SteveL13
SteveL13 asked
on
244 Views
Last Modified: 2012-08-13
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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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

Author

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

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

Author

Commented:
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.
CERTIFIED EXPERT
Top Expert 2016

Commented:
so it is working now?
CERTIFIED EXPERT
Top Expert 2016

Commented:
what type of form do you have?

Author

Commented:
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.

Author

Commented:
I'm sorry.. I don't know what you mean by what type of form do I have.
CERTIFIED EXPERT
Top Expert 2016

Commented:
what is the RecordSource of your form?
CERTIFIED EXPERT
Top Expert 2016

Commented:
is your form Single, continuous, datasheet?
CERTIFIED EXPERT
Top Expert 2016

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

Author

Commented:
Recordsource = tblMyEstimates
Is a single form
On the form, frmEstimateHeader, the textbox "txtRealEst" does change for every record in the table, "tblEstimateHeader"
CERTIFIED EXPERT
Top Expert 2016

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

how is the table "tblEstimateHeader" got involve here?

Author

Commented:
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.
CERTIFIED EXPERT
Top Expert 2016

Commented:
so you have a form/subform setup?

Author

Commented:
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.
CERTIFIED EXPERT
Top Expert 2016

Commented:
ok. upload a copy of the db...

Author

Commented:
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

Author

Commented:
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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.