Exporting new records only to csv

shabbaranks

Registered User.
Local time
Today, 12:15
Joined
Oct 17, 2011
Messages
300
Hi peeps!!

Im not entirely sure how to achieve this, I would like to setup an export which exports only newly added records from a table to a csv. Currently I have an export (based on a query) which exports everything. Would I need to create some sort of flag which indicates a record has already been exported? My current code is below

Code:
Private Sub ExportCSV_Click()
    Dim strPasswd
    strPasswd = InputBox("Enter Password", "Restricted Form")
    If strPasswd = "" Or strPasswd = Empty Then
        MsgBox "No Input Provided", vbInformation, "Required Data"
        Exit Sub
    End If
    If strPasswd = "export" Then
   DoCmd.OutputTo acOutputQuery, "ExportCSVQuery", "MicrosoftExcel(*.xlsx)", "N:\Timesheet Database\Output.csv", False, ""
    Else
        MsgBox "Sorry, you do not have access to this function", _
               vbOKOnly, "Important Information"
        Exit Sub
    End If
End Sub

I am unable to add a flag to the exported CSV as the columns it exports to have to be an exact match to the place they will be imported to so I am unable to add additionals.

Thanks
:)
 
Last edited:
You could add a YES/NO field to your table, say Exported, with a Default value of FALSE.

Add the field Exported to ExportCSVQuery making the criteria FALSE, so it show only the unexported records, now uncheck the Show box then it won't appear in the results.

After you've done the export you can run an update query to update Exported to TRUE where Exported is FALSE.

Since the record is now marked as TRUE it won't be output again, unless you deliberately change it that is.


I've also included another version (_version2) using a datestamp instead. By using a datestamp you could see when batches of records were exported.

I hope this helps.
 

Attachments

Last edited:
Great thanks, apologies though as I dont think I mentioned previously. The export is triggered from the Private Sub ExportCSV_Click() so wouldnt I need the click to append a tick to the Exported column so they wouldnt get exported again?

Thanks
 
Yes,

Just add an extra line to run the query that will update the Flag once the export has taken place.


Code:
If strPasswd = "export" Then
   DoCmd.OutputTo acOutputQuery, "ExportCSVQuery", "MicrosoftExcel(*.xlsx)", "N:\Timesheet Database\Output.csv", False, ""
   [B]DoCmd.OpenQuery "ExportFlagsUpdate"[/B] ' Or whatever the name is.
    Else
        MsgBox "Sorry, you do not have access to this function", _
               vbOKOnly, "Important Information"
        Exit Sub
    End If


If you get unwanted prompts about updating records you could use DoCmd.SetWarnings FALSE, to temporarily turn the warnings off, and DoCmd.SetWarnings TRUE to turn them back on.
 
I didnt get it to start with as I thought the same query would do both tasks but then I realised it needed the additional update query - she's working like a horse now.

Thanks!!
 
You're welcome.


Which one did you go for in the end?

The YES/NO field or the Datestamp?

As I mentioned the Datestamp is useful because it's easier to find a single batch of records if you have trouble.
 
Went for the yes no in the end as I already captured datestamps within the table so I could always query that if needs be. I seem to be having problems with my reports - but I better post that in the correct section :)

Thanks again
 

Users who are viewing this thread

Back
Top Bottom