Switchboard

[MarieLine77]

tblIndividuals
I have a list of individuals in the same table (Employees & Residents)

I need to create a switchboard with 2 buttons that will give staff an option to OPEN the same FORM but with either all the RESIDENTS or EMPLOYEES

I have IndividualID and IndividualType (Res or Emp).

I tried with a query and it worked but I do not like the layout.

 

[Pat Hartman]

You've asked a similar question in another thread. I'm not sure what you are having trouble with. Do you not know how to make a form with two buttons or do you not know how to make the buttons open the same form but show different data? I'll assume it is the latter.

To open a form to a specific record or set of records, use the Where argument of the OpenForm method.
DoCmd.OpenForm "frmPeople", , , "PersonType = 'Emp'"

In the other button the statment will be:
DoCmd.OpenForm "frmPeople", , , "PersonType = 'Res'"

 

[Pat Hartman]

You rarely want to actually copy the data from one table to another since that would duplicate it. To simply show the data, use a query that joins the two tables as the RecordSource for the subform. As soon as you select a value from the combo, the related fields will "autofill". No coding is required. You do need to use a bit of care with this method though because you may not want the reference table to be updated using this subform. To prevent any changes to the "lookup" controls, set their Locked property to yes. You may also want to remove them from the tab order and change their Enabled property to No but you'll have to play around to determine how you want the form to act.

If you are getting a message about duplicate keys, you may not be properly populating the foreign key in the subform. Make sure that the master/child links for the subform control show the correct fields. If this is defined correctly, Access will automatically populate the foreign key when a subform record is added.

 

[MarieLine77]

You've asked a similar question in another thread. I'm not sure what you are having trouble with. Do you not know how to make a form with two buttons or do you not know how to make the buttons open the same form but show different data? I'll assume it is the latter.

To open a form to a specific record or set of records, use the Where argument of the OpenForm method.
DoCmd.OpenForm "frmPeople", , , "PersonType = 'Emp'"

In the other button the statment will be:
DoCmd.OpenForm "frmPeople", , , "PersonType = 'Res'"

Hello and thank you very much for your input.
My first Switchboard worked fine but was based on my original database (tblResidents and tblEmployees).

Once I followed your advice and merged all "individuals" in one table I deleted tblRsidents and tblEmployees and created one tblIndividuals which in turned rendered my Switchboard inoperable.

Can you please tell me where to type the above TWO "Where argument of the OpenForm method"?
I wish to use a blank Form and create a new Switchboard with two buttons (I cannot see "arguments" anywhere).

Thank you for your advice and patience.

 

[MarieLine77]

Is this what you are trying to do?

Yes, that is exactly it (I would like it bigger though if possible)
And I want these 3 buttons to open the same Form called "Individuals".

When I enter data in the database I have already set it so that I have a lookup of two options to choose from EMP or RES...

Thank you for your help.

 

[MarieLine77]

You are welcome,
This simple technique can be applied to your project. All forms and buttons were wizard generated. The record source for the forms can be looked at by clicking the "Three Dots" at the end of the record source, under the data tab in design view.

Will it be to much trouble to ask how you did the first button i.e. employee and I will do the rest... I cannot understand how you made of form of just employee

 

[Simon_MT]

Personally, I would an immediate From asking for Employees or Residents and create two Froms one for Employees and One for Resident or just filter out Employees or Residents depending on the selection.

Use the database tools rather than touching design mode.

Personally I would use a Continuous Form.

Simon

 

[MarieLine77]

Private Sub FormIndividuals_OnClick()
DoCmd.OpenForm "formIndividuals", , , , IndividualType = "Emp"
End Sub


Could someone help me. The above open method is not working.