Export with Formatting

[CoffeeGuru]

Hi and apologies if this is not in the correct area.

I have the following built in access macro

[B]ExportWithFormatting[/B]
   Object Type      Form
   Object Name      My List
   Output Format    Excel Workbook (*.xlsx)
   Output File
   Auto Start       Yes
   Template File
   Encoding
   Output Quality   Print

this works fine but I need to add a column into the exported file that does not exist in Access - basically a concatenation of my Customer ID and the current Date Stamp (in Microsoft format)

eg customer number = 12345
current date time = 05/04/2016 = 42465
to create a Job reference number of 1234542465

Any ideas how I can do this so that the last column in my excel sheet contains this as JobRefNo

 

[cyanidem]

I would create a query containing that JobRefNo field and export query instead of form.

 

[CoffeeGuru]

Hi cyanidem

Trouble is JobRefNo is created at runtime so I can't create a query to include it then.

My form - frmtelesales is interactive and used to select the records I want to see and is based on a single unbound box that runs through all my fields to get a list.
i.e. I could type in almost anything - a part of a postcode for example and it will run through and filter on postcodes to display only those records whose postcodes match this same box could just as easily have a town in it and the filter will work just the same showing all records with a contact in that town.

Once I have this list, I currently use ExportWithFormatting to export it to excel and send this to out telesales people who then work on it, fill in some blanks then this list get appended back into the database as a job - into tblJobs.

So I had this great idea to update or append but there isn't a way for the database to know if the sheet has been uploaded already, so If I export a concatenation of the ContactID and the current date then this will be the JobRefNo.

JobRefNo = CustomerID&Date()

That way the same CustomerID can be used over and over.
If the JobRefNo does not exist in tblJobs then the sheet will append
but
If the JobRefNo already exists in tblJobs it will update instead