Solved

Setting control value on a form

Posted on 2013-05-31
17
350 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
  • 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
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.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

832 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