Solved

Setting control value on a form

Posted on 2013-05-31
17
329 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 119

Expert Comment

by:Rey Obrero
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
 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
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 functions 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 Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now