BASIC HELP PLEASE

[christopher007]

Ok I have 2 related tables, Table1 & Table2, Table1 is related to Table2 by ID fields. I make a form on table1 & a form on Table2. I then put a button with the wizard on the Table1-form that opens related data in the Table2-form. This works fine when there is related data in Table2. However when coming to add a new record by opening table1-form and clicking the button to table2-form, the ID number goes to 0?

Is this right or am I missing something.

 

[KenReay]

Hi

Sounds like you have a one-one relationship between the two tables

If this is the case, reconsider your design why not combine table1 and table2 into one table

If it is not a one-one relationship, then use a subform a main form, with master child link fields set to Id, access will then take care of populating teh id column of the 'child'

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[christopher007]

Hi thanks for the reply

I have a one-many relation ship and yes it works fine with subform parent form, but my delegate wants the database to work in different forms, why can you not open one form like so:-

stLinkCriteria = "[ID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

As I said this works fine with existing data, but when you add data or there is no data in second table the foreign key field goes to 0.

I really want to know why this is and if there is a work around.

 

[KenReay]

Hi

As you have discovered, you can do it that way, provided, a match record exists in table2.

There are two situations where you will encounter a problem

1) A new row has been inserted in table1 (by definition there are no rows in table2)

2) A row existed in table1, but not in table2

I have not done this before but I think the route I would follow would be to use OpenArgs to pass teh id from Table1 (ie the foriegn key) to form2, in the on open event of form2, check if the record count of the recordsource of form2 is zero, if it is, add a new record and populate the foriegn key using the value from openargs

Unless you can think of a better idea, look up OpenArgs in Help

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[christopher007]

Thanks Ken
Chris