Solved

Setting control value on a form

Posted on 2013-05-31
17
377 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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 500 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

717 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