Form to table data

[Mike]

Firstly I'd like to thank all of those helping guys like me. Thanks a
million, you're helping more than you think. This is the scenario. I have
three tables which were automatically turned into forms.

Table 1 = ChanceryTable
Table 2 = ProtocolTable
Table 3 = CoupleTable

I start out at a form (Protocol Form) that works from the "Protocol Table".
When I go to the Couple's Form (working off the CoupleTable) from the
Protocol Form I'd like for the newly created "Protocol Number" to be
consistent with the new couple info that is going to be added to the
CoupleTable Form.

I have the "ProtocolIndexID" in both the Couples and Protocol table. But
the only time the information from the Protocol Table is updated is when I
manually change the "ProtocolIndexID" number within the Couples Table. Is
there a way I can have it connect to the couples information automatically
when I create the new Protocol Number?

I hope this makes sense and if I"m leaving something out please let me know
to be more specific. In a bind and hope to get some help soon. Thanks!

 

[jk]

Firstly I'd like to thank all of those helping guys like me. Thanks a
million, you're helping more than you think. This is the scenario. I have
three tables which were automatically turned into forms.

Table 1 = ChanceryTable
Table 2 = ProtocolTable
Table 3 = CoupleTable

I start out at a form (Protocol Form) that works from the "Protocol Table".
When I go to the Couple's Form (working off the CoupleTable) from the
Protocol Form I'd like for the newly created "Protocol Number" to be
consistent with the new couple info that is going to be added to the
CoupleTable Form.

I have the "ProtocolIndexID" in both the Couples and Protocol table. But
the only time the information from the Protocol Table is updated is when I
manually change the "ProtocolIndexID" number within the Couples Table. Is
there a way I can have it connect to the couples information automatically
when I create the new Protocol Number?

I hope this makes sense and if I"m leaving something out please let me know
to be more specific. In a bind and hope to get some help soon. Thanks!

Need a bit more info.

Do you have any relationships created for these tables. Is there only
ever one protocol record for each couple record? Is the
Protocollindexid an auto increment field? Do you just want to pass
the value of the protocolid from the protocol form to the
corresponding control on the couple form?

 

[Mike]

Yes there are relationships between all three tables.

Table 1 (ChanceryTable) is related to Table 2 (ProtocolTable) by the autonum
from Table 1 (ChanceryTable).

Table 2 (ProtocolTable) is related to Table 3 (CoupleTable) by the autonum
from Table 2 (ProtocolTable).

All the above relationships have the following for Join Properties:

Only include rows where the joined fields from both tables are equal.

Without "Enforce Referential Integrity" checked.

Please let me know if I'm still missing information. Thanks!

 

[jk]

Yes there are relationships between all three tables.

Table 1 (ChanceryTable) is related to Table 2 (ProtocolTable) by the autonum
from Table 1 (ChanceryTable).

Table 2 (ProtocolTable) is related to Table 3 (CoupleTable) by the autonum
from Table 2 (ProtocolTable).

All the above relationships have the following for Join Properties:

Only include rows where the joined fields from both tables are equal.

Without "Enforce Referential Integrity" checked.

Please let me know if I'm still missing information. Thanks!







- Show quoted text -

okay, that is one to one,

So you wan to be able to enter a protocol record, then open the
couples form and enter a related record. If the forms ar about to the
tables, that I would just pass the value of the needed ID's from one
form to another, you can do this with VBA or by setting the default
value fo the control to reference the needed value on another form:

VBA:
This assumes that these forms are not continuous, meaning they only
display one record at a time Also, the couple form can only be opened
from the protocol form or you will have to set the value through the
onclick of the control you are using to open the couple form.

this goes with the on open event of the form(couple for example)

Private Sub Form_Open(Cancel As Integer)
Me.ProtocolIndexID = Forms!ProtocolTable.ProtocolIndexID
'the me refers to the Couple Form
end sub


FORM CONTORL:
or you can select the ProtocolIndexID contorl on the couple form and
set the following for the default value
= Forms!ProtocolTable.ProtocolIndexID

you will need to adjust this to match the naming conventions of your
project.

You can make this control invisible so it all happens automatically
and can not be adjusted by the user. THen when you insert the record
from the couple form, it will have the appropriate ID from the
protocol form.

 

[jk]

okay, that is one to one,

So you wan to be able to enter a protocol record, then open the
couples form and enter a related record. If the forms ar about to the
tables, that I would just pass the value of the needed ID's from one
form to another, you can do this with VBA or by setting the default
value fo the control to reference the needed value on another form:

VBA:
This assumes that these forms are not continuous, meaning they only
display one record at a time Also, the couple form can only be opened
from the protocol form or you will have to set the value through the
onclick of the control you are using to open the couple form.

this goes with the on open event of the form(couple for example)

Private Sub Form_Open(Cancel As Integer)
Me.ProtocolIndexID = Forms!ProtocolTable.ProtocolIndexID
'the me refers to the Couple Form
end sub

FORM CONTORL:
or you can select the ProtocolIndexID contorl on the couple form and
set the following for the default value
= Forms!ProtocolTable.ProtocolIndexID

you will need to adjust this to match the naming conventions of your
project.

You can make this control invisible so it all happens automatically
and can not be adjusted by the user. THen when you insert the record
from the couple form, it will have the appropriate ID from the
protocol form.- Hide quoted text -

- Show quoted text -

Sorry, the following line should read
If the forms ar about to the tables

if the forms are bound to the tables

 

[Mike]

Worked like a charm!!! Thanks soo much for your assistance.