Form and Table relations

[Abraham]

Help! I need to relate the content of two different forms in access 2007
using an element (id or control number) of a common table. How can I do
that? Thanks!

 

[Klatuu]

Does your table have a unique primary key? That would be the way for the two
forms to have a common indentifier, but if the forms are not a form/subform,
then one knows nothing about the other; however, you can make them sync up
using VBA. You would need code in the Form Current event of each form so
that when you change records in one, it will move the other form to the same
record.

It would be something like:

If not Me.NewRecord Then
With Forms!OtherForm.RecordsetClone
.FindFirst "[UniqueID] = " & Me.txtUniqueID
If Not .NoMatch Then
Forms!OtherForm.Bookmark = .Bookmark
End If
End With
End If

 

[Jackie L]

Wow,
That is a general question. The main thing you need to take care of before
setting up your forms is to get your table structure set up correctly. If
you are uncertain of your key fields, do not continue with the forms until
that is designed out correctly or you will find yourself going in circles.

After that point, you can relate the data through queries or code. Let us
know if you need help with the table structure. Can you give us an idea of
the information you are trying to work with?

Jackie

 

[Abraham]

Thank you for answer! I went back to the table and I think it is well design.
The problem is that the table contains too much information and it's
impossible to have all the information in one form so I designed two separate
forms, the problem is that now I don't know how to link their content into
the same table row. The table has an unique identifier. The purpose of the
forms is to facilitate the data entry process of a community survey with 31
questions (table).
Thank you for your help

 

[John W. Vinson]

Thank you for answer! I went back to the table and I think it is well design.
The problem is that the table contains too much information and it's
impossible to have all the information in one form so I designed two separate
forms, the problem is that now I don't know how to link their content into
the same table row. The table has an unique identifier. The purpose of the
forms is to facilitate the data entry process of a community survey with 31
questions (table).

If you have 31 *fields* in the table for the 31 questions... you've fallen
into the very common "survey spreadsheet" trap. This design is WRONG and will
get you into all sorts of problems (e.g. what do you do if you need to add
questions 32 and 33 - redesign your table, all your forms, all your queries,
all your reports!? OUCH!)

Consider instead using *three* tables: Questions (31 rows, until you decide to
add a new question or two); Surveys (one record for each person surveyed);
and Answers, related one to many to both these tables. For an excellent
worked-out example see Duane Hookum's "At Your Survey":
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

or

Roger Carlson's Training Registration database:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=TrainingRegistration.mdb

 

[Jackie L]

Abraham,

You could have one form with the data from your header table then the second
form should be for the detail (survey responses?). In order to link the
frmHeaderInfo to the frmSurveyDetail, you can create a query for the
frmSurveyDetail (from the table tblSurveryDetail) and in the criteria
section of the query, use the expression builder to point to the unique ID
field in frmHeaderInfo. Use that query as the record source for your
frmSurveyDetail. Then put a button on your frmHeaderInfo to open the
frmSurveyDetail. It will only show the information for the record you are
currently on on frmHeaderInfo.

It sounds like you have too much info to fit on a subform or tab control,
that is why my example is using two separate forms.

Hope this helps,
Jackie