Adding "child" records to be linked to a parameterized "parent" record

[jnp834]

I am fairly new to vb coding fo Access so forgive me if this seems
obvious.

How do I create a form to allow the user to enter new data thereby
creating a new record, that needs to be inserted into the table along
with IDs from its two "parent" tables?

To be more specific...
I have 3 relevant tables in my system:
Land Table - Land ID is PK
Resources Table - Resource ID is PK (Land ID is FK)
Resource Value Table - Value ID is PK (Land ID and Resource ID are
FKs)

I need to allow the user to enter multiple records into Resource Value
table, for any given Resource for each Land Parcel. So I need to let
the user input 1) Land Id/Name and 2) the Resource ID this new
Resource Value record is related to (since the Resource Value record
needs to store the affiliated Land ID and Resource ID).

Can someone please give me guidance on this...

Thanks

 

[strive4peace]

Make a form based on the Land Table

make another form based on the Resource Value Table. On this form, make
a combobox to collect the Resource ID.

ControlSource --> [Resource ID]
RowSource --> SELECT [Resource ID], description_fieldname FROM
[Resources Table] ORDER by description_fieldname
ColumnCount --> 2
ColumnWidths --> 0;2
ListWidth --> 2

on the Land Table form, create a subform control

SourceObject --> form for the Resource Value Table

LinkMasterFields --> [Land ID]
LinkChildFields --> [Land ID]

and here is some generic information you may find useful:

each form or subform should be based on just one table. to create a
subform, put a subform control on you mainform using the toolbox (Cancel
the wizard if it pops up and fill properties manually)

Then, from the design view of the main form

1. turn on the properties window
(r-click anywhere and choose Properties from the shortcut menu)

2. click ONE time on the subform control

3. click on the DATA tab of the Properties window

LinkMasterFields --> MainID
LinkChildFields --> MainID

If you have multiple fields, delimit the list with semi-colon

LinkMasterFields --> MainID;maincontrolname
LinkChildFields --> MainID;childcontrolname

where MainID is an autonumber field (usually) in the parent table and a
Long Integer field in the child table

the link field properties actually use controls, not fields -- so the
controls you reference must be ON each of the respective forms and the
NAME property is what you need to reference -- if a control is bound, I
usually make the Name of the control the same as the ControlSource (what
is in it)

It is common to set the Visible property to No for the control(s) used
in LinkChildFields

*** Difference between Subform Control and Subform ***

The first click on a subform control puts handles* around the subform
object.
*black squares in the corners and the middle of each size -- resizing
handles

The subform object has properties such as

Name
SourceObject
LinkMasterFields
LinkChildFields
Visible
Locked
Left
Top
Width
Height

the subform control is just a container for the subform.

the subform itself is an independent form -- you can open it directly
from the database window and it has the same properties of the main
form. It is only called a subform because of the way it is being used.

To summarize, when you are in the design view of the main form, the
first click on the subform is the subform control -- you will see the
handles around the edges -- and the second click gets you INTO it -- you
will see a black square where the rulers intersect in the upper left of
the "form" you are "in" (and this is the same as if you went to the
design directly)

me.subform.controlname --> the subform control
me.subform.controlname.form --> the form inside the subform control

Personally, I edit subforms directly, not from inside the main form -- I
have had trouble with Access putting property changes in the wrong place
for RowSources and RecordSource. Since it happens there occassionally,
for major changes, I go to the design view of the "sub"form directly
from the databse window when the main form is closed.



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*