horatio_too
asked on
How to Duplicate MOST (but not all) of an existing Access Record in VBA
Hi
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.
Kind regards
Horatio_too
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.
Kind regards
Horatio_too
Why not just create an append query with only the fields that you want to move, and filter on the RecordNumber (for the record you want to copy). You could make the other unique field equal Max(unique field) + 1, or if you already know the new unique id, you could have it be part of the query.
this should get you started
http://support.microsoft.com/?kbid=208824
edit the codes to exclude the fields that you don't need
http://support.microsoft.com/?kbid=208824
edit the codes to exclude the fields that you don't need
ASKER
Hi dirknibleck & capricorn
Many thanks to both of you for your swift replies.
Both of your solutions would work, but have some inherent problems.
Firstly, with over 200 fields, I do not really want to explicitly have to select each field, whether it is in a query (dirknibleck) or in VBA (capricorn1).
The main reason for this is that if the table definition changes in due course (quite liklely), I would the solution to cope with change without having to add in the additional fields.
In pseudo code, my thinking is something like :-
Select all fields from quote table where quote number = 12345, except contact name field
Then append this back to the table under a new quote number
(That way, any additional fields do not need to have been defined)
Alternatively, I would like a way of loading all fields for a record into VBA, changing the value of some of the fields and writing it back as a new record.
Thus:-
select quote number = 12345 from tblQuotations as temporyrecordset
With the temporary recordset
set quotation number = 56789
set contact name = Null
set quotationdate = Date
Append temporaryrecordset as new record
Hope that that makes sense and helps to clarify things.
Thanks once again for your input.
Horatio_too
Many thanks to both of you for your swift replies.
Both of your solutions would work, but have some inherent problems.
Firstly, with over 200 fields, I do not really want to explicitly have to select each field, whether it is in a query (dirknibleck) or in VBA (capricorn1).
The main reason for this is that if the table definition changes in due course (quite liklely), I would the solution to cope with change without having to add in the additional fields.
In pseudo code, my thinking is something like :-
Select all fields from quote table where quote number = 12345, except contact name field
Then append this back to the table under a new quote number
(That way, any additional fields do not need to have been defined)
Alternatively, I would like a way of loading all fields for a record into VBA, changing the value of some of the fields and writing it back as a new record.
Thus:-
select quote number = 12345 from tblQuotations as temporyrecordset
With the temporary recordset
set quotation number = 56789
set contact name = Null
set quotationdate = Date
Append temporaryrecordset as new record
Hope that that makes sense and helps to clarify things.
Thanks once again for your input.
Horatio_too
how about using a function that will select all fields except f1,f2,f3,f4
You could loop through the field names of your recordset to develop a query string, inserting if statements to trap only the fields that you are interested in modifying/ignoring...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I just realized if you try to do a complete copy (which is what the function will attempt if you don't pass any fields to be excluded) you'll get a syntax error on the INSERT INTO statement. Rather than repost all the code, just put a space before the 'SELECT' in the first SQL Statement (amended lines follow):
'no fields excluded, attempt a complete copy'
CurrentDb.Execute "INSERT INTO " & TblQryName & _
" SELECT * FROM " & TblQryName & _
" WHERE " & Criteria, dbFailOnError
Of course, this functionality is only useful on a table with no primary key (so you should probably never have a need for it...), but it's there if you need it.
-mike
'no fields excluded, attempt a complete copy'
CurrentDb.Execute "INSERT INTO " & TblQryName & _
" SELECT * FROM " & TblQryName & _
" WHERE " & Criteria, dbFailOnError
Of course, this functionality is only useful on a table with no primary key (so you should probably never have a need for it...), but it's there if you need it.
-mike
ASKER
Hi mwolfe02
Many thanks - this looks exactly the sort of re-usable solution that I was looking for.
I will try it on site tomorrow (it's currently getting on for 9:30 pm here in the UK and my wife is telling me that work should be over for the day !
Kind regards
Horatio_too
Many thanks - this looks exactly the sort of re-usable solution that I was looking for.
I will try it on site tomorrow (it's currently getting on for 9:30 pm here in the UK and my wife is telling me that work should be over for the day !
Kind regards
Horatio_too
ASKER
Hi mwolfe02
Having now been able to apply this to the client's system, I have no hesitation in awarding you the points.
Your solution works superbly and has certainly been added to my Access toolkit. .
I had some initial trouble in getting the procedure call to work, so for the sake of anybody else picking up this thread, below is a snapshot of the call that I am making:-
Call PartialCopy("tblquotations
& Me.boxQuotationNumber & "'" & "))", _
"RecordNumber", "QuotationNumber")
This takes a copy of the record that matches the contents of boxQuotationNumber, APART from the RecordNumber and QuotationNumber.
Mike, one last question applicable to my current application. As already identified, I do not want to copy several of the exisiting fields (resolved by your solution), but ideally, at the same time, I would also like to to set a couple of other fields to a different value than that copied - any thoughts on how to achieve this ?
At present I am looping back through the table to pick up the new records into a Recordset and then making the changes, but a "one stop shop" would be even better !
Anyway thanks once again - great tool.
Kind regards
Horatio_too
Kind regards
Horatio_too
Having now been able to apply this to the client's system, I have no hesitation in awarding you the points.
Your solution works superbly and has certainly been added to my Access toolkit. .
I had some initial trouble in getting the procedure call to work, so for the sake of anybody else picking up this thread, below is a snapshot of the call that I am making:-
Call PartialCopy("tblquotations
& Me.boxQuotationNumber & "'" & "))", _
"RecordNumber", "QuotationNumber")
This takes a copy of the record that matches the contents of boxQuotationNumber, APART from the RecordNumber and QuotationNumber.
Mike, one last question applicable to my current application. As already identified, I do not want to copy several of the exisiting fields (resolved by your solution), but ideally, at the same time, I would also like to to set a couple of other fields to a different value than that copied - any thoughts on how to achieve this ?
At present I am looping back through the table to pick up the new records into a Recordset and then making the changes, but a "one stop shop" would be even better !
Anyway thanks once again - great tool.
Kind regards
Horatio_too
Kind regards
Horatio_too
Horatio_too,
See the attached code snippet. Because a function is limited to one parameter array, you have to pass the fields you want excluded and the fields you want to assign specific values to in the same array of strings. The downside is that 'FieldsExcluded' is no longer a great description of what values should get passed to the parameter array. Here is an example of how you might call this function:
CopyRecord "Batches", "BatchID=169238", "BatchID", "ApplyDate", "EntryDate=#" & date() & "#", "Posted=0"
In that example, the batch with BatchID 169238 will have all fields copied except for 'BatchID' and 'ApplyDate' (which will be assigned default values) and 'EntryDate' (which will be assigned today's date) and 'Posted' (which will be assigned 0).
I think this change makes this a far more flexible and useful solution.
-mike
See the attached code snippet. Because a function is limited to one parameter array, you have to pass the fields you want excluded and the fields you want to assign specific values to in the same array of strings. The downside is that 'FieldsExcluded' is no longer a great description of what values should get passed to the parameter array. Here is an example of how you might call this function:
CopyRecord "Batches", "BatchID=169238", "BatchID", "ApplyDate", "EntryDate=#" & date() & "#", "Posted=0"
In that example, the batch with BatchID 169238 will have all fields copied except for 'BatchID' and 'ApplyDate' (which will be assigned default values) and 'EntryDate' (which will be assigned today's date) and 'Posted' (which will be assigned 0).
I think this change makes this a far more flexible and useful solution.
-mike
''---------------------------------------------------------------------------------------
'' Procedure : CopyRecord
'' DateTime : 2/12/2008 11:13
'' Author : Mike
'' Purpose : Copy an existing record into the same table or updateable query.
'' Usage : FieldsToExclude is a parameter array that accepts an array of two unique
'' string formats:
'' 1. A field name by itself. This will cause the function to exclude that
'' field from the copy.
'' 2. A field name with an equal sign and a value assigned. This will cause
'' the function to copy the value on the right side of the equal sign
'' to the field on the left side of the equal sign as part of the copy.
''---------------------------------------------------------------------------------------
''
Public Function CopyRecord(TblQryName As String, _
Criteria As String, _
ParamArray FieldsToExclude())
On Error GoTo Err_CopyRecord
Dim db As Database, i As Integer, dummy As String, TQDef As Object, Fld As Field
Dim FieldsToCopy As String, SkipThisField As Boolean, ValueAssigned As Boolean
Dim ValuesToCopy As String, FieldValue As String
If UBound(FieldsToExclude) = -1 Then
'no fields excluded, attempt a complete copy'
CurrentDb.Execute "INSERT INTO " & TblQryName & _
" SELECT * FROM " & TblQryName & _
" WHERE " & Criteria, dbFailOnError
Else
Set db = CurrentDb
'Determine whether we were passed a table or query name'
On Error Resume Next
dummy = db.TableDefs(TblQryName).Name
If Err.Number = 0 Then
Set TQDef = db.TableDefs(TblQryName)
Else
Err.Clear
dummy = db.QueryDefs(TblQryName).Name
If Err.Number = 0 Then
Set TQDef = db.QueryDefs(TblQryName)
Else
Err.Clear
Err.Raise 513 + vbObjectError, , _
"Table/query '" & TblQryName & "' not found."
End If
End If
On Error GoTo Err_CopyRecord
'Build comma-delimited list of fields to copy'
FieldsToCopy = vbNullString
For Each Fld In TQDef.Fields
SkipThisField = False
ValueAssigned = False
For i = LBound(FieldsToExclude) To UBound(FieldsToExclude)
If Fld.Name = FieldsToExclude(i) Then
SkipThisField = True
ElseIf FieldsToExclude(i) Like Fld.Name & "*=*" Then
ValueAssigned = True
FieldValue = FieldsToExclude(i)
FieldValue = Trim(Right(FieldValue, _
Len(FieldValue) - InStr(FieldValue, "=")))
End If
Next i
If Not SkipThisField Then FieldsToCopy = FieldsToCopy & Fld.Name & ", "
If Not SkipThisField Then
If ValueAssigned Then
ValuesToCopy = ValuesToCopy & FieldValue & ", "
Else
ValuesToCopy = ValuesToCopy & Fld.Name & ", "
End If
End If
Next Fld
If Len(FieldsToCopy) = 0 Then
Err.Raise 514 + vbObjectError, , _
"All fields from the table/query have been excluded."
End If
FieldsToCopy = Left(FieldsToCopy, Len(FieldsToCopy) - Len(", "))
ValuesToCopy = Left(ValuesToCopy, Len(ValuesToCopy) - Len(", "))
'Copy partial record'
db.Execute "INSERT INTO " & TblQryName & " (" & FieldsToCopy & ") " & _
"SELECT " & ValuesToCopy & " " & _
"FROM " & TblQryName & " " & _
"WHERE " & Criteria, dbFailOnError
End If
Exit_CopyRecord:
Exit Function
Err_CopyRecord:
'LogError Err.Number, Err.Description, "CopyRecord", "Module1 Module"'
MsgBox Err.Description
Resume Exit_CopyRecord
End Function