A Simple Task?

[gfbush]

I'm trying to do a simple task which is to transfer all data from 1 table to an archive table, based on a specific data field in the table. Is there any easy way of doing this, I have created 3 queries, that selects the data, transfer's it to the new table and then deletes it from the original table.

Now this works, but as the Database is not managed by me on a day to day basis I wanted to make it easy to perform. I tried to create a form that is used to select the date and then get the query to use this as the criteria. I've seen the [Forms]![FormName]![FormField], but can't get this to work and i've also used Parameter's which work, but you have to use the Parameter 3 times and remember to use the same date?

I hope that explains the problem and look forward to your help.

 

[Galaxiom]

Usually transferring to archive tables is unnecessary unless the table is very, very large. I expect at something like ten million records it might be worth contemplating.

As an experiment I have let one of my tables continue to grow and it has almost reached six million records and still does not cause any problems with no special provisions at all.

Moreover the move to the archive table should be done occasional as a managed process. Certainly not something required daily.

 

[JamesMcS]

Well, really, you'd only need two - an append query with the correct criteria, and a delete query to get rid of what you've copied over (a compact/repair and a check of the copied-over data should be done before you delete the old stuff).

But as Galaxiom says it's definitely not something that should be done by a user, and definitely not every day - shifting that much data around, you're asking for problems (corruption being a biggie). I used to get rid of the older data once the DB reaches about 1.75 gigs, in my case that's about 15 million records, 7 fields per record.

Now, I generally find it easier to have a nightly backup of the data using a batch file, then just delete old info out as it fills up - then the backups become the archive.

 

[gfbush]

Thanks Guys, Size isn't the problem the table holds payment data which is used in all manner of reports. Every year, the club club will write off payments over 2 years old, so the easiest thing is to move this data to a different table.

I've added a couple of screen shots, the first 1 is the query 1 have using a Parameter and the 2nd 1 is the way I'd like to do it using forms, but it doesn't work.

 

[JamesMcS]

OK I think it's

<=forms![archive date].form![end date]

Assuming your form is called "Archive Date" and the field you want to take the date from is "end date".

The form must be open when the query is run otherwise it won't work.

 

[Galaxiom]

The above postings show a widespread chronic misue of terminology. Developers should be aware that a textbox on a form is NOT a "Field". It is a "Control". Fields are in queries and tables. The difference is important to understand especially when the control must be referred to dynamically using a variable in vba.

Forms(formname).Controls(controlname) emphasises the fact that the textbox is a control not a field.

Controls on a main form can be referred to without the .Form because Form is the default property of the Form Object.

So you will often see the expression James used above written as:
Forms![archive date]![end date]

If a control by that name is not found Access will look for a field in the Record Source table or query with that name. This can sometimes lead to unexpected outcomes.

In that case it is actually referring to:
Forms![archive date].Form.Recordset![end date]

However a subformcontrol (ie a control used to display a subform) does not have a default property so the property must be included:

Forms![archive date]![subformcontrolname].Form![end date]

The use of the dot or bang operator often confuses too. The dot precedes Properties while the bang separates objects or collections. However Access automatically substitutes the correct operator internally if you use the dot everywhere. The Intellisense in VBA only works with dots everywhere.

 

[JamesMcS]

Widespread and chronic eh? Posting(s)? I only replied once so far with any code whatsoever... My humblest and most heartfelt apologies for referring to a control as a field. Could be to do with not having had any tea yet this morning.... to be remedied now.

So you're saying the code bit in my last post is wrong? In my experience referring to an CONTROL with forms!formname!Controlname makes Access think you're talking about a form called Controlname, but forms!forname.form!controlname works fine.

And I didn't realise we were talking about subforms were we?

Thanks for the lesson in terminology

 

[Galaxiom]

I meant no offence James.

The misuse is widespread (many postings at this site) and chronic (over a long period of time). The difference is important because it leads to confusion.

I will acknowledge that the word "field" has been used just once in this thread. I guess I should have taken more notice and used the correct number for the noun.

I did not say your code was wrong. I was just explaining something that many find confusing (when the .Form is used and when it isn't). Since this is the OP's first post I thought thy might find it helpful to have it explained.

In my experience most developers omit the Form property when referring to a control on a main form. It is the default and that is why it can be omitted. Just like Value is the default propery of a control and is usually omitted.

However you are incorrect about Forms!formname!controlname making Access think you are referring to a form. A form object cannot appear in that context. Access always looks first for a control.

If you are referring to a subform you must refer to the subformcontrolname, not the formname used as the Source Object of the subformcontrol. This misunderstanding also causes a lot of problems for new developers.

The Wizard in Access names both the subformcontrol and the form it displays by the same name. This is why you think Access would expect you are referring to a form in that context without the Form property.

My inclusion of the subform context was simply to complete the explanation of the use the Form property.

Sorry for any distress I may have caused.

 

[gfbush]

Thanks Guys, that's got it all sorted out.