Relationships, automatically update another table 1

[oggstar1]

My [main] from, is linked by a one to many relationship to a form called [sheet].
[Sheet] is effectively a sub form of [main],
Relationship- [Main] = one; [sheet]= many; Join properties = 1

[Accounts] is a sub form of [sheet].
Relationship - One to one; Join properties = 2, all fields from sheets 1 and only those fields from accounts where join fields are equal.

All the above forms (Main, sheet and accounts) are visible to the user in the one screen.

So if the user enters details in the Main form and then enter details in the accounts from. The user cannot update the record, because a linked table is required to be updated in sheet 1.

I would like sheet1 to be automatically updated with default text in certain fields the first time text is entered into the main form.

Any ideas appreciated; hope it makes sense

 

[dhookom]

Tables are generally related to each other one to many or whatever. Forms can have subforms that related by setting the Link Master/Child properties. Normally you would have a primary key field as the Master and the related foreign key as the Child.

There should be no reason to enter a record into a "3rd" generation table without a matching record in the "2nd" generation table.

It would help if you described your link master child properties of your forms/subforms and told us why you would add a record to the Accounts subform without adding at least one field value (to create a record) in the Sheet table.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[oggstar1]

Thanks for the reply; you have summed up the situation very well.

It is unlikey that the user will enter details in to the
3rd" generation table without a matching record in the "2nd" generation table.

However it is posible because my second and third generation tables are displayed in a form that has approximalty 10 tabs.

I thought it may be possible to hide my third generation form tabs until a number is entered in the primary key field in the 2nd generataion table.

Such as using the on current event procedure

Select Case Me!ID
Case "Is Null" or Case ""
[Accountstab].Visible = False
Case Else
[Accountstab].Visible = True
End Select

However this did not work






enter a record into a "3rd" generation table without a matching record in the "2nd" generation table.

 

[dhookom]

To check to see if a value is null you should use
IsNull(Me!ID)

Try:
Me.AccountsTab.Visible = Not IsNull(Me!ID)

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[oggstar1]

Thanks works well in the current event procedure, I have also tried to change the ID (primary key autonumber)feild event afterupdate so it changes the first time the feild is updated.

Rather than changing the record before the Accountstab appears. Any ideas

As the following does not work in the after update event.
Me.AccountsTab.Visible = Not IsNull(Me!ID).

 

[dhookom]

You could try place the code in the After Insert event of the subform.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[oggstar1]

I tryed both the after update and after insert in the primarty form. Me.Accountstab.Visible = True

On form currnet i have
Me.Accountstab.Visible = Not IsNull(Me!ID)

This does work but only after i click out of the form or channge records and come back to the form, before the accounts forms appears. I would prefer this to change striaght away if possible.

I have also tryed the afterupdate in the subform and had no luck.

Any ideas appreciated.

 

[dhookom]

You would need to use the same code in the after insert of the Sheet subform.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[oggstar1]

Got it, works in the before insert.
thanks