Tables Link

[meddling]

I have 3 tables that I will use as forms. I wanted to display the main table including 2 buttons that would open the other 2 forms.
On Table 1 I have a EmployeeID field, set to Autonumber, Indexed (no duplicates).
One Table 2 (which would correspond to the first button) I have more information I want filed UNDER the EmployeeID in question. And on Table 3, there is more information that I also wanted filed under the EmployeeID in question.
Both table 2 and table 3 have an EmployeeID field as well, set to Number, not indexed.
So, when you press the button it would open up the records information that the main form is on.

Someone from here (M8KWR) told me to add this line (below) to the code. It does open up the forms when I click the button, but it does not save the information

stLinkCriteria = "[EmployeeID]=" & Me![EmployeeID]

I have a One-to-One relationship set-up between the main Employee table and the other 2. Not sure how to set up the "Join Type" or to check the Referential Integrity box.

SOMEONE PLEASE HELP...

 

[willir]

meddling

Okay, 3 tables...
- EmployeeMaster, also control the primary key
- EmployeeDetail_1
- EmployeeDetail_2

And you want to create three forms. The EmployeeMaster form has two buttons that open either the Detail_1 or Detail_2.

Well there are two ways to accomplish this -- an easy way or a harder way.

With the "easy way", you embed the Detail_1 and Detail_2 as subforms on the main form. If real estate is tight on the form, you can use a tab control and have one detail form display on one tab (or page), and the other detail form display on another.

Why is this easier?? Well, you can use the property of the Parent or Master form link to the Child form. This way, you can save on coding. Then the command buttons would only hide / unhide the detail forms. Note: You can have two subforms on top of each other -- to the user, as long as one is visible and the other, they will not see the "clutter" you will see in design mode.

The other way, which is a little harder because it does involve coding, is to open the Detail forms as separate forms. It gets a little complicated in that when you open the Detail form, you have decide if this is a "new" or "existing" record, and take appropriate action. Instead of using a filter or where option when openning the form, I feel you need to make use of the OpenArgs in order to decide if the record is new or existing.

The easy way first.
Assumptions
- frmEmployeeMaster - employee master form
- sbfrmEmployeeDetail_1 - subform
- sbfrmEmployeeDetail_2 - subform
- cmdOpenDetail_1 and _2 - command buttons to open / unhide subforms.

Since we can not "see" your setup, it is hard to explain the best way to do this, but I am going to assume you will use either two subforms or a tab control with the subforms.

Make sure the "Properties" window is open (from menu "View" -> "Properties")

Design and size frmEmployeeMaster to accommodate the subforms. Create your subforms. Then click and drag each subform from the database window. Size and place the subforms as required. As stated, you can place them on top of each other although this makes it tough for further editing.

Add the command buttons but cancel out of the wizard. Click on the first command button, and then select the "Other" tab in the Properties window. Change the name to cmdOpenDetail_1.

Then click on the "Event" tab in the Properties window and select the OnClick field, select "[Event Procedure]" and then click on the "..." command button that appears to the right. This will take you to the VBA coding window.

Enter the folowing:

Me.sbfrmEmployeeDetail_1.Visible = True
Me.sbfrmEmployeeDetail_2.Visible = False

Go back to the form design. Select a field on the main form, and then select the first subform, sbfrmEmployeeDetail_1. Click on the "Data" tab in the Properties window. The LinkChildFields and LinkMasterFields should both reference the EmployeeID. This way, Access will know that if record exists the detail table, it will display it. If you create the detail record, it will use EmployeeID from the master form for the EmployeeID on the subform.

Repeat the above for cmdOpenDetail_2 except tweak the code so that Detail_2 is visilbe = true and Detail_1 visible = false.

If you decide to use the tab control form, the process is very much the same. The real trick with the tab control is to make absolutely sure that you place the subform on the tab page itself, and not on the tab control and not under the tab control. Otherwise, the subform will always be visible regardless of tab selected, or you never see the subform because it is hidden under the tab form. The trick here is to select the "tab" part of the form before draggnig and dropping the subform onto the main form. You can change the name of the tab by selecting the tab, and then look at the "Other" tab in the Properties window.

...Moving on
Okay, so instead, you want to open a separate form.

Same assumptions as above except
frmEmployeeDetail_1 and frmEmployeeDetail_2 are used instead of using sbfrm...

For the cmdOpenDetail_1, the OnClick event procedure runs

DoCmd.OpenForm frmEmployeeDetail_1, , , , , , Me.EmployeeID

Open the frmEmployeeDetail_1 in design mode. Select the "form" by clicking on the top left square where the verticle and horizontal rulers meet. Click on the "OnOpen" event and click on the "..." command button.

Add the following code...

Dim lngEmployeeID as Long

lngEmployeeID = Me.OpenArgs, strSQL as String

If DLookup("EmployeeID", "YourDetail_1_Table", "EmployeeID = " & lngEmployeeID) Then
   strSQL = "SELECT * From YourDetail_1_Table WHERE EmployeeID = " & lngEmployeeID
   Me.RecordSource = strSQL
   Me.Requery
Else
   DoCmd.GoToRecord , , acNewRec
   Me.EmployeeID = lngEmployeeID
End If

Here, if there is an employee record, display it as the ONLY record. If not found, then add a new record and use the OpenArgs to "pass" the EmployeeID.

Repeat the above for the second command button.

HINT: To ensure you will have a true one-to-one relationship, make sure that the EmployeeID on Detail1 and 2 tables is defined as the primary key, or at least make it "unique" with no duplciates. Otherwise, you may end up with multiple detail records for the same employee.

Richard