Basic form/subform problem

[George Bowyer]

This is probably really basic stuff to most of you, but here goes.

(In the past, I have always had tables with zillions of fields, rather than having linked tables, so I have never had to mess with this stuff before...)

I have a db with 2 tables. A Main table with, say, contact names and addresses and a Lesser table with specialised info that I only want for some of those contacts.

The Primary Key in the Main table is fldContNum (autonumber), the primary key in the Lesser tables is fldNum. The two tables are linked 0ne-to-one on those fields.

There is not a record in the Lesser table for every record in the Main table.

I have a form showing records from the main table and a sub-form showing records from the Lesser. They are linked Child fld Num / Master fldContNum.

At the moment, I can go to a Contact from the main table and I can enter info for him in the sub-form, thus creating a record in the lesser table. That works ok.

However, if I move the main form to another contact and then go back, that info that I just entered does not show in the subform. In other words the subform is in "add new record" mode, whilst I want it to go automatically to the existing "Lesser" record with that number.

I can make it do this with VBA and searching recordsets and suchlike easily enough, but I feel that there must be a much more basic way of linking the two form and tables.

Also I want to make sure that if there is already a "Lesser" record with that number, I want to make it so that the sub form cannot even try to make a new record (because then I obviously get primary key violations).

I hope that I have made myself relatively clear here...

Thanks

George

 

[wazz]

I'd like to give your main problem a try. Just make a copy of your database before trying anything. If it works, keep the one you're working on.

i would start by changing the PK of your "lesser" table to match the PK of the main table. it is, after all, the same "thing" (ID) you are working with, no? that means, in the related (lesser) table, the PK and FK then become one and the same (that's what you want). secondly you have to insure that the PK/FK in the related (lesser) table is indexed to accept unique values only. that way there will be no duplicates, and no referential integrity errors.

i'll leave you with that for now.
hth
w

btw: consider giving your PKs the suffix 'ID' (fldPrimaryKeyID).

 

[George Bowyer]

Hmm, well I seem to have solved the problem - so far - by using one table based on a join query rather than a table and sub table each with it's own query.

As I say, that's probably obvious to other people, but it's new to me (and whilst I can do lots of pretty things wih VBA, SQL is completely a foreign country.)