VBA Relationship Management

[Fornatian]

Does anyone out there know how I can change a relationship type between tables programatically.

i.e. What I want to do is:

A. Provide a lookup list box with the option to delete items

B. When an item is selected, go away and lookup whether there are any related 'children'

C. If so, give the user the option to delete the item and all associated children.
(This is where I am having trouble because I beleieve I would have to change the relationship to one of cascading deletions/updates) on the fly, carry out the operation, then change it back again.

I suppose, one way to do it is with delete queries but I think the above method might be quicker? Any advice always welcomed.

 

[Robert Dunstan]

Have a look at the help for VBA under Relationships. You could use the Relations collection to achieve what you are trying to do. Unfortunately my knowledge of programming relationships is basically non-existent as I always maintain that once your structure is correct and working then don't mess with it.

One question I do have is that have you enforced referential integrity in your relationships?

The best advice I can offer is to consider using the delete query instead

 

[Fornatian]

yes, my relationships are enforced to trap user foul ups because the majority of the tables I reference are one column affairs used to support lookups in combo boxes.

Control vs Flexibility all over again.