Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

Setting control value on a form

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
mijwil
Asked:
mijwil
  • 6
  • 6
  • 5
1 Solution
 
mbizupCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
mijwilAuthor Commented:
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
Independent Software Vendors: 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!

 
Rey Obrero (Capricorn1)Commented:
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
 
mijwilAuthor Commented:
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
 
mbizupCommented:
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
 
Rey Obrero (Capricorn1)Commented:
<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
 
mbizupCommented:
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
 
mijwilAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
<Cap1 - I get an error on "recordsource">

post the codes that you used..
0
 
mijwilAuthor Commented:
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
 
mbizupCommented:
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
 
mbizupCommented:
<< The same as above with : Me.ShortageStatus = #>>

What was the exact error message?  Do you have the correct control names?
0
 
Rey Obrero (Capricorn1)Commented:
@mijwil

upload a copy of your db..






.
0
 
mijwilAuthor Commented:
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
 
mbizupCommented:
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
 
mijwilAuthor Commented:
I missed that. I'll do that.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 6
  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now