How to allow new record entry but stop pagedown (1 Viewer)

[CrystalSurfer]

I have a number of sub forms managed by a tab control which is on my main Client form.
I can enter a new client and complete any of the subforms as required.
Most of these subforms are displaying 'child' tables to the main Client table. One of these has a 1-1 relationship with Client. (ie: an extension table, yes there is a good reason for this)
All good so far.
However, after completing the 'extension' subform if the Pagedown key is pressed (say accidentally) a new entry record is displayed on the subform. This is not good as I only want to allow 1 record for the Client on this particular subform.
Access 2003 does not stop me from entering data on this "new" record. It only tells me theres a problem when it attempts to save the record. Thats because I have set the foreign key on the extension table to be unique (no duplicates).

Is there anyway of trapping this or stopping a new record from being displayed on this subform?
I don't want to set 'Allow Additions' to No because I DO want to be able to add new clients through these forms.

Is it possible to check if displaying a new record and if so, then enable 'Allow Additions' on the subforms, having them set to No initially?
Thanks.

 

[Tim L]

Rather than have the main form and subform with seperate Record Sources use a single query for the form Record Source which joins the main table and extension table, so that all of the fields are available to the form (you can still use the tabs).

No records will be created in the extension table until data is entered into the fields specifically relating to it and it is not possible to navigate to another record in the extension table, without also going to another record in the main table.

On the, there's probably more than one way to skin a cat idea... With regards to the Allow Additions, would it not be possible to use the On Current event to determine if there was one record and if so, to then set Allow Additions = No, otherwise have it set as Yes? You could possibly also use the form After Update to set the Allow Additions to No as well, so that once a record has been added it then prevents further records?

Although my first suggestion should work and is better all round.

Tim

 

[CrystalSurfer]

Tim, that is a most excellent suggestion.
Thanks for your help.
CS

 

[Tim L]

You're welcome.

Because I'd been working on this and have now got it done, I'm going to post it anyway; someone might find it useful, and someone else may find that they could advise me on better implementation.

Anyway, attached should be an example DB illustrating why extension tables could be a good thing and one way of implementing them.

Regards,

Tim