Multi-table form problem (1 Viewer)

[kevygee]

I'm creating a database that contains some information spread out over multiple tables. In order to enter information into the database there are a couple forms that the user can access to enter all the information, and these data will be placed in the appropriate tables. I am also creating a form that will be used for data-viewing and simple editting purposes. This form will contain data from many different tables and viewed all together in the same form.

I created this second, more complicated form (more complicated because it contains more tables all viewed on the same form) and editting and adding new data doesn't work. It won't let me type in the fields. I've narrowed down the problem to the combination of three tables and I will explain the nature of the relationships of these tables.

First of all, I have a table the contains general person information. These needed to be broken down in to sub-groups, employee and contract. Each of these two sub-groups of people has their own specific fields that need to be entered, so I created three tables. The first table, person, has general person fields. The other two tables, employee and contract, contain fields that are specific to their person type and a foriegn key referencing the associated person information.

My problem is that, on the more complicated form, I wanted to put all the fields related to that entry. So a contract person would have all the fields filled in for the general person, the fields for contract person would be filled in, and then the fields for employee would be blank. Now, I'm sure there is a better way to do this (for example gray out the employee fields when a contract person is viewed, or hide fields for employee etc.) but I'm new at VB and that seemed more complicated.

If anyone has any ideas on what the problem might, or how I could resolve this, that would be amazing. Thanks in advance

 

[Pat Hartman]

You have two separate 1-1 relationships. The easiest solution is to create two subforms. One for employees and one for contracts. In the Current event of the main form, you would change the subform's SourceObject to one or the other depending on a value in the primary table that tells you which type the person is. Each of the three forms should be based on a query that selects data from only the necessary table. The main form shows data from the people table, sub1 shows data from the employee table, and sub2 shows data from the contracts table. The main form has only ONE subform control. You just change its contents as necessary - one line of code is all it takes.

 

[DavePem]

You have two separate 1-1 relationships. The easiest solution is to create two subforms. One for employees and one for contracts. In the Current event of the main form, you would change the subform's SourceObject to one or the other depending on a value in the primary table that tells you which type the person is. Each of the three forms should be based on a query that selects data from only the necessary table. The main form shows data from the people table, sub1 shows data from the employee table, and sub2 shows data from the contracts table. The main form has only ONE subform control. You just change its contents as necessary - one line of code is all it takes.

Thanks Pat for this comments.
Im also a newie to this forum.....
I am amazed at the support on offer....
Pat....
I would like to know what is the code you are suggesting.
I have a similar problem where I am trying to establish a d/base where
I use the autonumber generator as a key number for records...
but it links to either of two forms one for truck information & the
other for tractor information. I assume I would need to set this up as a
main form and two sub forms... which would be stroed in 3 separate tables?
Can you have 2 tables linking to the same field in the main table (eg a field called PlantID)?

Thanks in advance...

A new learner...

Regards

Dave P

 

[Pat Hartman]

You currently have three tables. That wouldn't change. The code to swap SourceObjects is simple:

If Me.SomeField = "some value" Then
    Me.SubformControlName.SourceObject = "frmNameOfFormA"
Else
    Me.SubformControlName.SourceObject = "frmNameOfFormB"
EndIF

Put the code in the AfterUpdate event of which ever field determines which subform should show. You will also need to put the code in the FORM's Current event.