?
Solved

Setting control value on a form

Posted on 2013-05-31
17
Medium Priority
?
379 Views
Last Modified: 2013-06-01
I have a subform, each record has a check box "resolved". I would like to set a value on the main form IF all records on the subform are "resolved." Thanks for the help.
0
Comment
Question by:mijwil
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 5
17 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39211031
Add this to the AfterUpdate event of the resolved checkbox on your subform, so that this check is made anytime the user updates this value in a record:

Dim rs as dao.recordset
set rs = Me.RecordsetClone
rs.FindFirst "Resolved = False"
if rs.NoMatch = true then
        Me.Parent.txtResolved = "RESOLVED"
End If
set rs = nothing

Open in new window


Also add this to the current event of your mainform, to run this check as the user opens the form or navigates through records:

Dim rs as dao.recordset
set rs = Me.SubformControlName.Form.RecordsetClone
rs.FindFirst "Resolved = False"
if rs.NoMatch = true then
        Me.txtResolved = "RESOLVED"
End If
set rs = nothing

Open in new window


You'll need to change field names, etc as appropriate for your database.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39211093
you can simply use Dcount() to do that

if dcount("*",me.subformControlName.recordsource) =  dcount("*",me.subformControlName.recordsource,"resolved=-1") then
       me.textboxName="ALL RESOLVED"

      else
          me.textboxName="NOT ALL RESOLVED"


end if
0
 

Author Comment

by:mijwil
ID: 39211214
CAP1 - Using Dcount
Please understand that I know barely enough to cause problems. I don't understand when to use brackets or ! as separators. Below is what I tried and I believe I probably have the control name syntax wrong. Also, I am placing this code "On Open". Is that correct?

if dcount("*",me.[subfrm].Form![Resolved].recordsource) =  dcount("*",me.[subfrm].Form![Resolved].recordsource,"resolved=-1") then
       me.textboxName="ALL RESOLVED"

      else
          me.textboxName="NOT ALL RESOLVED"
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39211234
what is the name of the textbox in the Main form ?
    - change "textboxName" with the actual name

what is the name of the control that holds the subform?

- you have to change "subformControlName" with that name..

if dcount("*",me.subformControlName.recordsource) =  dcount("*",me.subformControlName.recordsource,"resolved=-1") then
       me.textboxName="ALL RESOLVED"

      else
          me.textboxName="NOT ALL RESOLVED"


end if
   

<Also, I am placing this code "On Open". Is that correct?>
 that is fine, as long as it serve your purpose, you can also use the LOAD event of the Main form.
0
 

Author Comment

by:mijwil
ID: 39211362
The names were changed. I was just using an example.
The Main form is "13frmWODetail" The option value I am trying to set on the main form is "ShortageStatus.

The subfrm name is "13subfrmShortage" The check box on the subform is "resolved"

I was using (incorrectly I believe) the "On Open" of the main form. ; me.resolved wouldn't point to anything on the main form.

I used the "on open" of the subform which I think is where you were directing me and here is the exact code I tried:

If DCount("*", Me.Resolved.RecordSource) = DCount("*", Me.Resolved.RecordSource, "resolved=-1") Then
        [Forms]![13frmWODetail]![ShortageStatus] = 2
   
    Else
        [Forms]![13frmWODetail]![ShortageStatus] = 1

I'm getting an error "method or data member not found" with reference to "recordSource" - Is "RecordSource" supposed to be a control name?

Thanks for your help.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39211365
mijwil,

Did you try what I suggested in the first post?

Fwiw, the DCount method will only work if your recordsource is very simple (ie: the name of a table or query).

If the recordsource property contains a SELECT statement, the DCount method based on the recordsource will return an error.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39211378
<I used the "on open" of the subform which I think is where you were directing me and here is the exact code I tried:>

This is incorrect, use the OPEN OR LOAD event of the MAIN form


use this

If DCount("*", Me.[13subfrmShortage].RecordSource) = DCount("*", Me.[13subfrmShortage].RecordSource, "resolved=-1") Then
       
        Me.[ShortageStatus] = 2
    Else
       
         Me.[ShortageStatus] = 1
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39211380
Using your names:

Add this to the afterUpdate event of the checkbox on the subform (this will update the main form as the user checks/unchecks boxes in the subform):

Dim rs as dao.recordset
set rs = Me.RecordsetClone
rs.FindFirst "Resolved = False"
if rs.NoMatch = true then
        Me.Parent.ShortageStatus = 2
Else
        Me.Parent.ShortageStatus = 1
End If
set rs = nothing

Open in new window


Also add this to the current event of your mainform, to run this check as the user opens the form or navigates through records:

Dim rs as dao.recordset
set rs = Me.[13subfrmShortage].Form.RecordsetClone
rs.FindFirst "Resolved = False"
if rs.NoMatch = true then
        Me.ShortageStatus = 2
Else
        Me.ShortageStatus = 1
End If
set rs = nothing

Open in new window

0
 

Author Comment

by:mijwil
ID: 39211649
Cap1 - I get an error on "recordsource"

mbizup - The code for the subform works great. The code for main form I get an error on the "Else" statement.  Just FYI - I tried the DCount first because it looked simpler.

I tried:
Dim rs As dao.Recordset
Set rs = Me.[13subfrmShortage].Form.RecordsetClone
rs.FindFirst "ShortageResolved = False"
If rs.NoMatch = True Then
        [Forms]![13frmWODetail]![ShortageStatus] = 2

Else
        [Forms]![13frmWODetail]![ShortageStatus] = 1

End If
Set rs = Nothing

And I tried:
The same as above with : Me.ShortageStatus = #
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39211662
<Cap1 - I get an error on "recordsource">

post the codes that you used..
0
 

Author Comment

by:mijwil
ID: 39211994
Private Sub Form_Open(Cancel As Integer)

If DCount("*", Me.[13subfrmShortage].RecordSource) = DCount("*", Me.[13subfrmShortage].RecordSource, "Shortageresolved=-1") Then
       
        Me.[ShortageStatus] = 2
    Else
       
        Me.[ShortageStatus] = 1
         
End Sub

The error highlights "RecordSource"
0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 39212023
For the main form... if the ShortageStatus is on the main form, the code should be as follows (you had inadvertently included the subform reference before shortageStatus... probably causing the error):

' Main form code
Dim rs As dao.Recordset
Set rs = Me.[13subfrmShortage].Form.RecordsetClone
rs.FindFirst "ShortageResolved = False"
If rs.NoMatch = True Then
        Me.[ShortageStatus] = 2

Else
        Me.[ShortageStatus] = 1

End If
Set rs = Nothing

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 39212037
<< The same as above with : Me.ShortageStatus = #>>

What was the exact error message?  Do you have the correct control names?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39212213
@mijwil

upload a copy of your db..






.
0
 

Author Comment

by:mijwil
ID: 39212730
mbizup - both codes work fine. For whatever reason, it won't work "On Open" on the main form but works "Before Update."

It's all good. Thanks for the help.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39212765
Hi..  I'm glad that worked, but take another look at my comments. My recommendation was to put that code in the on CURRENT event of the main form.  Did you try that?

 The idea behind the current event is to run this check as the form opens and as the user navigates through main form records.
0
 

Author Comment

by:mijwil
ID: 39212779
I missed that. I'll do that.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

764 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