?
Solved

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

Posted on 2011-05-10
16
Medium Priority
?
231 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:SteveL13
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 49

Expert Comment

by:Dale Fye
ID: 35732601
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35732640
just use this one line

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

Author Comment

by:SteveL13
ID: 35732808
Neither of these suggestions worked.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35732824
are you getting an error?
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 35732875
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)
0
 
LVL 75
ID: 35732909
Why are you trying to set a Check box with the value of what is apparently a numeric field ... and it's count ?  

mx
0
 

Author Comment

by:SteveL13
ID: 35733004
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
0
 

Author Comment

by:SteveL13
ID: 35733008
I am not getting an error.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35733036
this should do it, just one line


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


check the codes you are using
0
 

Author Comment

by:SteveL13
ID: 35733207
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35733231
place this in the close event of 2nd form

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

Author Comment

by:SteveL13
ID: 35733264
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.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35733279


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

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 35733362
Instead of placing the codes in the load event of form1, place them in the activate event of form1.
In the close event of form2, seT the focus to form1
0
 

Author Comment

by:SteveL13
ID: 35736985
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.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35737073
upload a copy of the db. (.mdb version)
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question