Open SubForm based on Selected Check Box

  • Thread starter Thread starter Brian Belliveau
  • Start date Start date
B

Brian Belliveau

I'm relatively new to working with access ... more experience with Excel

What I would like to do is have a data entry form in which a second form
opens triggered by selection of a check box

Specifically, using an address book with a set of check boxes ... Contractor
/ Customer / Employee, for instance.

Checking on "Employee" would open a sub form for information such as birth
date & social insurance number & wage information, while checking "Customer"
would open a sub-form with details of their specific job.

Can this be done? ... How, please.

Thanks

Brian
 
Brian,

Since you indicate that you are new to using MS Access, I would suggest that
the usual way to open a second form from another form is to use command
buttons. In fact, if you use the Wizard and follow the prompts, Access will
write all the code you need to perform the action. If you open your main
form in design view and drag a command button to the form, the Wizard should
activate and will:

1. Ask you what operation you want to perform. You would select Form
Operations from the list box labeled "Categories".

2. When you select "Form Operations", the list box labeled "Actions" will
then show you available actions for forms. You would select "Open Form".

3. The next prompt will show you a list of forms from which to select.

4. After you select the desired form, the next prompt will ask you: "Do
you want the button to find specific information to display in the form?"
or "Open the form and show all records?"

5. For the kind of operation you describe in your post, you would probably
need to select the first-listed option: "Open the form and find specific
data to display."

6. The next prompt will show you fields from your Main form in the
left-hand list box and fields from the selected form in the right-hand list
box. All you have to do is identify the field from the Main form
(EmployeeID, possibly) which should match the field in the selected form
(EmployeeID), click on each and then click on the button ( <=> ) between
the list boxes.

7. Click "Next" and answer the prompts about naming your button - and you
are done. Access will have written all the code you need to open the
desired form with records that will match your Main form.

hth,
 
Thanks Cheryl ... that counts as a item learnt, but...

I guess the essence of what I would like to have happen is to select a name,
which could be a 'Contractor' or 'Customer' or 'Employee' or 'Freind' ...
and have the appropriate form open to display and modify the record. Is
there a way to do this?
 
Yes, you could select the person's name from a multi-column combo box, the
row source for which would be a query containing the name as well as the
relationship type (Customer, Contractor, etc.) and the unique identifier for
that name. Build your query first and save it. You can then use the
Combo Box wizard to build the combo box on your form.

In the After Update event of the combo box, you would then write some VBA
code to open the correct form (based on the relationship type) for the name
selected (based on the unique identifier). You would use the OpenForm
method, which is discussed in detail in VBA Help. Additional values found
in multi-column combo boxes are retrieved by referring to their column
number. For example, if you have 3 columns in your combo box (PersonName,
PersonID, PersonRelationship), you would refer to them in code as follows:

lngPersonID = Me!MyComboBox.Column(1)
strPersonRelationship = Me!MyComboBox.Column(2)
 
Back
Top