Processing Once a Year Data- Not wanting incremental records

[KnightWatch]

Hi all,

I've got to maintain once a year data for an annual registration process. Basically, each October, I wish to download all my active clients into a small new Access database so that I can send them out letters (which are all set up on different reports) for the annual registration process.

Once I do the uploading of data into my new access database, I want to lock the data for that year. In other words, if any new clients arrive in say November, they still will not qualify for the registration process as they missed the cut off date. (Basically, I don't want any more incremental data once the data is uploaded once a year)

So far I have set up the import process where active client data is imported into a temporary table (called ttmpReRegistrationEndofYearData). I then set up an Append Query to copy the data into the processing table (called tblReRegistrationEndofYearData) Note- the two tables are identical..

Within the dataset, I have a field "Registration Year" where I have this year (2010) listed against each record. My thoughts were to filter the append query to only copy records where Registration Year <> this year. The idea being that any new incremental records for 2010 (should someone try to run the process again to pick up any new clients) wouldn't come into the processing table for 2010. That works great. Obviously though, with that criteria, I wouldn't be able to get in the 2010 data in the first place because of "Registration Year <> 2010". So I think code might be required?

I have one button at the moment that brings in the data and runs two queries (one to update the Registration Year with this year in the table and the other being the append query mentioned above) but I think more might need to be added here.I also have in the code to delete the data in the temporary table once the process is completed and also remove any previous years data should it be in processing table (this to accommodate for next October when it has to remove all 2010 data)

I have no idea where to start with this to get it to:

1. Once loaded in October, not allow any further records to be uploaded in the same year for processing
2. Delete any previous years work to only process this years.

Could anyone give some specific instructions and insights on this please?

Here's what I have so far in the code... (Thanks a lot!)

Private Sub Upload_Data_Click()

On Error GoTo Err_Upload_Data_Click

Dim stDocName As String
Dim stDocName1 As String

stDocName = "UpdateCurrentYear"
stDocName1 = "AppendDataToReRegistrationTable"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ttmpReRegistrationEndOfYearData", "C:\Users\Matt\Documents\Business\\Clients\Clients Data Data\Input\DataDownloadForRPM.xls", True, "Application Advanced Find View!"

'[Import files into temporary table (Test)]


Application.SetOption "Confirm Action Queries", False

DoCmd.OpenQuery stDocName, acViewNormal, acEdit
If MsgBox("Would you like to upload?", vbYesNo + vbExclamation, "STAR Upload File") = vbNo Then
Cancel = True
Else

DoCmd.OpenQuery stDocName1, acViewNormal, acEdit

DoCmd.RunSQL "DELETE * FROM tblReRegistrationEndOfYearData WHERE [tblReRegistrationEndOfYearData].[RegistrationYear] < Year(Now())"
'DoCmd.RunSQL "DELETE * FROM ttmpReRegistrationEndOfYearData"

Application.SetOption "Confirm Action Queries", True

MsgBox "Data successfully imported into RPM to commence this years re-registration procedures.", vbOKOnly + vbInformation, "Result...."
Exit_Upload_Data_Click:
End If
Exit Sub

Err_Upload_Data_Click:

' MsgBox Err.Description
MsgBox "No Data was Uploaded. " & Err.Description, vbCritical + vbOKOnly, "Result...."
Application.SetOption "Confirm Action Queries", False

Application.SetOption "Confirm Action Queries", True

Resume Exit_Upload_Data_Click

End Sub

 

[Uncle Gizmo]

I would just like to point out that really long questions like this seldom get an answer.
You may wish to consider your problem and break it down into smaller problems and ask them all as separate questions. It’s a much more successful approach to getting help.