ID reference

[Guest]

I have a database under construction and have a number of forms and tables.
The thing is as each table has its auto number I have a column called
ContactID which is my main contact table autonumber. When entering data onto
another form I thought it would autmatically add my contact ID to the other
form table as I have a ContactID column, and it does seem to bring up those
related details each time I scroll through my contacts on my DB. I dont
really undertsand the relationships between tables as some are relted and
some arent yet they still bring up the details for the relevant contacts.
Does anybody understand my problem??

Paul

 

[John Vinson]

I have a database under construction and have a number of forms and tables.
The thing is as each table has its auto number I have a column called
ContactID which is my main contact table autonumber. When entering data onto
another form I thought it would autmatically add my contact ID to the other
form table as I have a ContactID column, and it does seem to bring up those
related details each time I scroll through my contacts on my DB. I dont
really undertsand the relationships between tables as some are relted and
some arent yet they still bring up the details for the relevant contacts.
Does anybody understand my problem??

Paul

If you're trying to link an autonumber field in one table to an
autonumber field in another table - DON'T. It won't work.

And just having a field named ContactID in a "child" table does
absolutely nothing to fill in any particular value in that field.

Normally, one would use a Form based on the Contacts table, with
Subforms based on the related tables; the Master and Child Link Fields
of the subform control would be the ContactID, so that the subforms
will display the information for the currently selected contact. New
subform records will automatically get the ContactID filled in.

If you're using separate, standalone forms, then you need to fill in
the proper ContactID yourself, either manually or using VBA code. I am
not sure how you're entering data into the second table, or what
you're using as your second form - care to explicate?

John W. Vinson[MVP]

 

[Guest]

I have a main form which is Contact Details, autonumber ContactID. I have a
number of independant forms which I complete which until a short while ago I
had a contactID field on each form. I specified in properties for that field
source =forms!formsContactdetails!ContactID. This did bring up the relevant
infor for each record BUT this obviousley didnt store the contcatID on the
source table just refernced the Contact table I think. Now Ive changed the
properties as such, have changed the control source to the ContactID field in
the relevant table(text) field of the form I am completing and changed the
default value to =forms!formsContcatdeatils!contactID. Now this does place
the contactID number ref in the relevant tables as it didnt b4 but now when I
open each of the Contacts info on these tables and forms it doesnt just
select the info for that specific contact I have open. I shows all records
in that table.

Any ideas??

Paul

 

[John Vinson]

I have a main form which is Contact Details, autonumber ContactID.

No. You don't.

You have a main TABLE whic is Contract Details, primary key autonumber
ContactID.

It sounds like you're making the very common mistake of designing your
database, forms first, and using the table structure as an
afterthought. That's like building a house, trim and windows first,
and then figuring out where to pour the foundation!

Let's get the TABLES set up correctly first, and *then* worry about
the forms.

I have a
number of independant forms which I complete which until a short while ago I
had a contactID field on each form. I specified in properties for that field
source =forms!formsContactdetails!ContactID. This did bring up the relevant
infor for each record BUT this obviousley didnt store the contcatID on the
source table just refernced the Contact table I think.

Exactly. That simply displays the value from the currently selected
record on the other form.

Now Ive changed the
properties as such, have changed the control source to the ContactID field in
the relevant table(text) field of the form I am completing and changed the
default value to =forms!formsContcatdeatils!contactID. Now this does place
the contactID number ref in the relevant tables as it didnt b4 but now when I
open each of the Contacts info on these tables and forms it doesnt just
select the info for that specific contact I have open. I shows all records
in that table.

Any ideas??

Other than what I suggested previously, which you haven't apparently
tried or replied to, no. Use a SUBFORM. That's what they're for. The
DefaultValue will affect *ONLY* newly added records, and unreliably at
that. It's simply not the right way to accomplish what you're trying
to do!

John W. Vinson[MVP]

 

[Guest]

Sorry John thanks for your advice, your right I was missing valuable tables
to help make this info possible. I have now gone back and managed to sort out
this problem using your advice. Wonder if you get chance you could have a
look at my subform dissappearing question in the forms forum???

Many thanks again!!

P