Split form problems on selection change

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sprads - 16 Jul 2007 23:44 GMT

Vista Ultimate / Office 2007 Pro

Great to have the split form funtionality in Access...but...I've have two
issues I hope someone can give advice on....please...

Firstly,
I use a split form; datasheet to show all records, and the main form to show
full details of the record selected in the datasheet. The form has a sub-form
showing associated records from another table. I have a button to delete the
current record, which also runs a query to delete the sub-forms associated
records from the other table. However, if the selected record in the split
form datasheet is deleted by pressing Del it will delete the current record,
but of course does not run the query to delete associated records. Setting
the forms 'allow deletions' property to No simply prevents me from deleting
the record at all, even from clicking my delete button on the main form. How
can I prevent deletion via the datasheet, thus forcing to use the delete
button on my form (thus deleting all relevant records via my delete query)??

Secondly, and it's also to do with the split form...
Keeping with my example above of a split form; datasheet and main form to
show the selected record in the datasheet... A label caption (and other
controls) on the main form changes according to the 'status' (field) of a
record. However, changing selection in the datasheet does not repaint the
main form to change the label to the records 'status'. It simply changes the
current record displayed. How can I fire an event to repaint (or requery?)
the main form to correctly show the label caption according to the records
'status'?? The 'on selection change' property doesn't seem to do anything...

I'd really appreciate some help on the above.
Cheers,
John

Reply to this Message

tina - 17 Jul 2007 04:07 GMT

if those "associated records in the subform" are actually child records
related to the parent record in the main form (single view or datasheet),
then the tables should be properly related in the Relationships window, with
CascadeDelete enabled in the link dialog. when your tables are correctly set
up as described, then deleting a parent record will cause the related child
records to be deleted automatically - without Delete queries, or VBA code,
or anything else needed.

hth

> Vista Ultimate / Office 2007 Pro
>
[quoted text clipped - 19 lines]
> controls) on the main form changes according to the 'status' (field) of a
> record. However, changing selection in the datasheet does not repaint the

> main form to change the label to the records 'status'. It simply changes the
> current record displayed. How can I fire an event to repaint (or requery?)
[quoted text clipped - 4 lines]
> Cheers,
> John

Reply to this Message

Sprads - 17 Jul 2007 10:30 GMT

Hey Tina,

Thx for your advice. Yep, I'd considered letting Access do the job using
CascadeDelete but was a bit concerned about it's reliability in multiuser
environment. I'll give it a shot though.

I'm pushing it but...any ideas on my second point...how to update a form
label on selection change in the split form datagrid?

Many thanks once again!
John.

> if those "associated records in the subform" are actually child records
> related to the parent record in the main form (single view or datasheet),
[quoted text clipped - 47 lines]
> > Cheers,
> > John

Reply to this Message

Sprads - 17 Jul 2007 21:18 GMT

Update:
Thanks for your advice Tina. I've made use of the CascadeDelete and seems to
work nicely. I've also found that the OnCurrent event does trigger upon
selection change in the datasheet, however label captions will not refresh.
I'm using an unbound text box to show what's needed...which is nice!

Cheers.
Sprads

> Hey Tina,
>
[quoted text clipped - 59 lines]
> > > Cheers,
> > > John

Reply to this Message