One parameter set for three delete queries?

[alley]

I have 3 tables-- jobs, empjobs, taskjobs.
Referential integrity is forced, so I must delete from the 'many' tables before I delete from the 'one' side (jobs).

My question is, how do I pass the parameter
(jobs dated before X date) to all three, so
I don't have to rely on the user to put the same exact date in each time? I read something about this once, but I can't remember where.

There is a front end select query to select job numbers dated < 'purge to' parameter.

Thanks. alley

 

[kathryn]

Good news is that if you have referential integrity set up properly, you don't have to worry about it. If you delete from the one side, the many records are deleted automatically. When I say &quot;set up properly&quot;, I mean that you have cascading deletions enabled. Look at your relationships to check.

Look at the help topic &quot;Delete records by using a query that includes only the &quot;one&quot; table in a one-to-many relationship&quot; It includes the following:

When you run a delete query on the &quot;one&quot; table in a one-to-many relationship, and you've enabled cascading deletes for this relationship, Microsoft Access also deletes related records from the &quot;many&quot; side.

HTH

 

[alley]

Kathryn,

I am aware of the cascading property of relationships, but I hesitate to set that up, because years (too many) of experience has taught me that the users will not read
the warning messages, and delete too much because they entered the wrong date,
crying all the way about recovery (they don't make backups often enough), or how
I could have written the program better. Warning messages work well for the new
user, but eventually immunize the daily, repetitive user.

I guess my real question is:

How do I pass the 'purge to date' parameter to all the individual delete action queries
so they are consistent with each other. Probably a Form requesting the date, and
command buttons for each delete action. That way they can delete whatever detail
they want, keeping one or the other for analysis reporting.

So the question resolves to:

How do you pass a text box control date to an action query?

It is amazing how writing down the problem clarifies muddy thinking.

Thanks for your response.

alley

 

[kathryn]

The query can use the Forms!YourFormName!YourTextBoxControl syntax as a criteria to a query. The form must be open, although not necessarily visible, so that the query can resolve the parameter.

Does that answer your question?

 

[cmmrfrds]

What I do, but object purists may not like is make a Public variable for the parameter and then return it in a function. I put it in the standard module for the application then it is available when the query is developed. Looks like.
Public ParmVar as &quot;whatever is appropriate&quot;
Function ReturnVar() as &quot;whatever is appropriate&quot;
ReturnVar = ParmVar
End Function

On your QBE form under criteria for the field type in
=ReturnVar()

Move the text field into the ParmVar and run your 3 queries

 

[alley]

Thanks Kathryn and cmmrfrds,

Two good answers! My lucky day.

Thanks.