append query as part of history table

[jgoodman00]

I have written a history routine, whereby if a record is changed (me.dirty) an append query runs, & appends the old data to a history table. I have used the field RefNo as the unique identifier. i have then referenced the field on the form as the criteria for running the query. However, the form can exist as itself, or as a subform within a master form. This raises a problem, in as much as the criteria parameter can come from two different locations. I put the first parameter in the first criteria field, & then as an OR criteria i put in the second possible location of RefNo. This works, but irrespective of which form is open, it asks for the other one. I could get around this by having two queries, but it will get very messy as we have over 30 forms!

So does anyone have any other suggestions as to how I can take the RefNo from either field, but without asking for the other one??? James Goodman
j.goodman00@btinternet.com

 

[BlackKnight]

Hi,

If I understand you correctly you have a query that you want to use a RefNo parameter to select a record that will then be appended to a table.

All you need to do is add a parameter in your query called [RefNo]. Don't hardcode a reference to a form. In the Dirty event you can then create a recordset based upon a parameter query object where you can supply the RefNo value as a parameter.
Place the code needed to do it in a module and call it in all of yur form's dirty events.

Have a good one!
BK