linking two forms (2 cont)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Essentially what I have is two main forms based on two main tables both
having a unique "Case ID" field, linked on a one-to-one relationship. I split
the table because when I created the form it returned an Access message
saying something like "your data will be truncated...". So... having two
tables/forms I now would like to click on a command button and open a new
record within the second form with the Id copied into it's linked 'Case ID'
field from having just created a new unique 'Case ID' and record in the first
form.

I've tried to build a macro, but without success.

Look forward to your/any reply.
Thanks.
 
Andrey,
If the fields from both tables all relate to the ONE table in a One to
Many relationship, then they should all be in the same table.
"Your data will be truncated" is usually caused when you change a field
length (in table design) from say 100 to 60... even if your data may have
never exceeded 60 to begin with. So... you may have split the table for the
wrong reason.
I strongly suggest you reconsider the split...

But, if you must, then you'll have to create a Relationship via CaseID
from tbl1 to tbl2 using Tools/Relationship. Specify Referential Integrity,
and Cascading, and Join them.
That way, whenever you create a new record in tbl1, a corresponding
record (blank) is forced/created in tbl2.
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
 
Thanks for replying and the cascading suggestion, I never used it before (bad
of me not to have taken the time to find out what those two options in the
ref. intg. were - but now I have).

However, despite this, when I input a new record in the first form the id
does not copy accross to the second form so that I can continue the record.
I'm still left having to remember the id I created for that record and enter
it manually into the second form. Any ideas on how to have it automatically
copied into the second form.

I suppose I could create a single field subform based on the 'Case ID' from
the second table, embed it in the first form to see if it will be populated
when I create a new record. But this doesn't seem right even it did work, or
does it? Nothing ventured and all that...

Cheers
 
The CaseID field in both tables should be Indexed No Dupes, with CaseID in
table 1 the Key field (usually an autonumber), and CaseID in table 2 should
a Long number.
The Relationship you set up should indicate a 1 to 1 relationship with
Referential Integrity.

If that still doesn't do the trick, then cut and paste (or append) then tbl2
fields into tbl1 and deep-six tbl2, . A table can handle up to 255 fields.
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
 
Back
Top