Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    updating subform value based on form value (2003)

    I have a table called TblFacilitySelect that has 3 fields - identifier,name, and selection which is a yes/no field. I then put this table onto a form to allow users to select a name by checking the selction field yes or no. What I would like to do is to put a Selectname yes/no field in the main form above the subform which lists the names and checkboxes. Once the check box on the main form is checked, I would like all the selections on the subform to become selected and if unchecked make all the subform selections unchecked. I thought the best way would be in the after update property of Selectname put an if statement (if SelectName.value=true then ??). Would this work and what would be the rest of the code or other code that could be simplier.

    Thanks,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: updating subform value based on form value (2003)

    Is the subform linked to the main form by its Link Master Fields and Link Child Fields properties, and if so, on which field(s)?

  3. #3
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: updating subform value based on form value (2003)

    There is no linkage. I just dragged the table onto a blank form and then added the Selctname yes/no box to the main form.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: updating subform value based on form value (2003)

    Use code like this:

    Private Sub SelectName_AfterUpdate()
    Dim strSQL As String
    strSQL = "UPDATE TblFacilitySelect SET selection = " & Me.SelectName
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    Me.Subformname.Requery
    End Sub

    Subformname must be the name of the subform as a control on the main form. This is not necessarily the same as the name of the subform in the database window.

  5. #5
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: updating subform value based on form value (2003)

    Thanks. It works great.

  6. #6
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: updating subform value based on form value (2003)

    Users like the way it works but the problem is that this subform as about 2000 names and they would like to select by system(which is in subform). I decided to add a combo box on the main form named system and make the subform based on a query on the Tblfacilityselect. When I select the system, the subform gives results based on the system. How could I get the select button that you gave me code for select only those subform records instead of selecting or deselecting all of the records in the tblfacilityselect?

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: updating subform value based on form value (20

    Change this

    strSQL = "UPDATE TblFacilitySelect SET selection = " & Me.SelectName

    to

    strSQL = "UPDATE TblFacilitySelect SET selection = " & Me.SelectName & " were TblFacilitySelect.system = " & me.comboselect

    Assuming the field is called system, and that it is actually a number. If it is text you need

    strSQL = "UPDATE TblFacilitySelect SET selection = " & Me.SelectName & " were TblFacilitySelect.system = " & chr(34) & me.comboselect & chr(34)

    Also assumes the combo is called comboselect.

    Test this with test data first.
    Regards
    John



  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: updating subform value based on form value (20

    I assume you intended to write where instead of were

  9. #9
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: updating subform value based on form value (20

    Hans and John. Thanks for your help it worked.

  10. #10
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: updating subform value based on form value (20

    Related question... There exists the possibility of where I want to show all the records in the subform instead of selecting a system in the combo when the form opens. Would I need to somehow on the load event of the form put code with allows all values on the subform?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: updating subform value based on form value (20

    Does this do what you want?

    Private Sub Form_Load()
    Dim strSQL As String
    strSQL = "UPDATE TblFacilitySelect SET selection = True"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    Me.Subformname.Requery
    End Sub

  12. #12
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: updating subform value based on form value (20

    Not quite. When I open main form, the subform shows no records. When I select a system from combo, the subforms returns the table info with slection set to yes and whatever system I choose it already has the slection set to yes. What I would like to happen is when the form opens, it show all subform records with the selction set to no.

    Ben

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: updating subform value based on form value (20

    What is the record source of the subform?

  14. #14
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: updating subform value based on form value (20

    It is based on a query set to the choice of the system combo box on the main form.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: updating subform value based on form value (20

    I assume that the criteria for the system field in the query looks like

    [Forms]![NameOfMainForm]![NameOfComboBox]

    with the appropriate names substituted. Change it to

    [Forms]![NameOfMainForm]![NameOfComboBox] Or [Forms]![NameOfMainForm]![NameOfComboBox] Is Null

    If the combo box is blank, the query will return all records.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •