Is it possible , in Access VBA, to duplicate an existing record EXCEPT FOR, say 3-4 fields ?
Currently developing a quotation system for a client, using an Access 2003 front-end and SQL Server (on SBS 2003) back end.
The problem that I am currently wrestling with is that for line-of-business reasons, it is often necessary to raise a virtually identical quote.
Unfortunately, the bit that is killing me at present is "virtually identical".
First thought was to insert a copy of the relevant record and then programmatically change the fields that weren't identical.
However, The table has both an 'autonumber' RecordNumber and also a unique, indexed Quote Number and the latter has tripped up my plan, as the copy process creates a temporary duplicate in this field (which therefore gets rejected) before I can change it to a new unique quote number.
The next thought was to create a temporary recordset within VBA and select just the required fields to duplicate. However, as this would require selecting approx 200 fields, this idea did not appeal too much (a) due to laziness (!) and (b) the scope for error/"finger trouble" is too great.
The best solution that I have come up with so far is to create a temporary table in Access holding the copied date, change the requisite fields (e.g. new quote number etc) and THEN append this data back to the original table, before deleting the temporary table.
To be frank, though, even this solution seems clunky and even downright dangerous, so does anybody have a beter and more elegant plan ?
As always, any help or guidance greatly appreciated.