none
How to use radio buttons to load subform RRS feed

  • Question

  • I have a database with data on sales persons. Each person has three addresses, a W-9, company, and personal address. I need to have three buttons the user selects. The buttons are 'W-9', 'Company', and 'Personal'. Each button will select the corresponding address for the person. I want that address to be displayed on the main form.

    I was planning to have a sub-form on the main form to display the address. I haven't worked with Access in a couple of years and need a refresher on how to make the buttons pull the data. The image below shows the main form with the proposed buttons and a box where the mailing address will appear.

    My guess is to tie each button to a query that pulls the data based on the button selected.

    Wednesday, December 13, 2017 5:03 PM

All replies

  • Firstly you need to get the underlying model right.  What you have is a many-to-many relationship type between customers, addresses and address types, as the same address might be of more than one category for the same customer. So a CustomerAddressTypes table to model the relationship type would be like this:

    CustomerAddressTypes
    ….CustomerAddressID
    ….AddressID
    ….AddressTypeID

    If two or more customers cannot share the same address you'd need a further table to model this constraint:

    CustomerAddresses
    ….CustomerAddressID
    ….AddressID

    Both tables are 'all key', i.e. their primary keys are in each case composite ones of all columns in the table.  CustomerAddressTypes  would reference CustomerAddresses on CustomerAddressID and AddressID in an enforced relationship.

    The value of an option group is an integer number representing the selected option, so you can set the OptionValue property of each option button to a corresponding AddrressTypeID value.  The subform would be based on a query which joins the CustomerAddressTypes and Addresses tables.  The query would reference the option group as a parameter on the AddressTypeID column, using the syntax:

        [Forms]![NameOfParentForm]![NameOfOptionGroup]

    The subform would be linked to the parent form on CustomerID by means of the LinkMasterFields and LinkChildFields properties of the subform control.

    In the AfterUpdate event procedure of the option group requery the subform with:

        Me.NameOfSubformControl.Requery

    Note that the ' subform control' is the control in the parent form's Controls collection, which houses the subform.  The control's name might or might not be the same as its source form object.

    An alternative set-up would be to have three separate subforms, each restricted to one address type in its RecordSource query, and placed on separate pages of a tab control.  Each would be linked to the parent form on CustomerID, but there would then be no need for a parameter in the subforms' queries, and no need for an option group in the parent form.  The user would simply select the tab for the desired address type.  You'll find an example of categorized subforms, though not in a tab control, in FindRecord.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.



    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Wednesday, December 13, 2017 6:10 PM Hyperlink added.
    Wednesday, December 13, 2017 6:09 PM
  • Hello TWIKLE,

    Where are these addresses fields? If they are the fields of the main form, you could try to use Option Button Click event to change the mail address box's control source.

    Private Sub Company_GotFocus()
    Me.AddressBox.ControlSource = "Company"
    End Sub
    
    Private Sub Personal_GotFocus()
    Me.AddressBox.ControlSource = "Personal"
    End Sub
    
    Private Sub W_9_GotFocus()
    Me.AddressBox.ControlSource = "W-9"
    End Sub
    

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 14, 2017 10:12 AM
  • Where are these addresses fields? If they are the fields of the main form, you could try to use Option Button Click event to change the mail address box's control source.
    That would assume that the table is badly designed, encoding data as column headings.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

    If the OP's table definition is flawed in this way, they would be better advised to correct the fundamental design error rather than merely to move the deckchairs on the Titanic.


    Ken Sheridan, Stafford, England

    Thursday, December 14, 2017 12:08 PM