• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 399
  • Last Modified:

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




0
horatio_too
Asked:
horatio_too
  • 3
  • 3
  • 2
  • +1
1 Solution
 
dirknibleckCommented:
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.
0
 
Rey Obrero (Capricorn1)Commented:
this should get you started
http://support.microsoft.com/?kbid=208824

edit the codes to exclude the fields that you don't need
0
 
horatio_tooAuthor Commented:
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








0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Rey Obrero (Capricorn1)Commented:
how about using a function that will select all fields except f1,f2,f3,f4
0
 
dirknibleckCommented:
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...
0
 
mwolfe02Commented:
Horatio_too,

I do this often enough myself, that I decided to go ahead and write a generic procedure that would handle the chore.  See the attached code.  I've declared it as a function so that you could call it directly from the OnClick property of a command button control if you were so inclined.

Notice the last parameter of the function is a parameter array, which will accept an arbitrary number of fields you want excluded.  Also, you can pass the function either a table name or the name of an updateable query.

Hopefully this will work well for you.  If not, I've at least added to my own repertoire.  Thanks for the inspiration.

-mike

PS  You'll need a reference to DAO if you don't already have one.
Public Function PartialCopy(TblQryName As String, _
                            Criteria As String, _
                            ParamArray FieldsToExclude())
    On Error GoTo Err_PartialCopy
 
    Dim db As Database, i As Integer, dummy As String, TQDef As Object, Fld As Field
    Dim FieldsToCopy As String, SkipThisField As Boolean
 
    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_PartialCopy
 
        'Build comma-delimited list of fields to copy'
        FieldsToCopy = vbNullString
        For Each Fld In TQDef.Fields
            SkipThisField = False
            For i = LBound(FieldsToExclude) To UBound(FieldsToExclude)
                If Fld.Name = FieldsToExclude(i) Then SkipThisField = True
            Next i
            If Not SkipThisField Then FieldsToCopy = FieldsToCopy & Fld.Name & ", "
        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(", "))
        
        'Copy partial record'
        db.Execute "INSERT INTO " & TblQryName & " (" & FieldsToCopy & ") " & _
                          "SELECT " & FieldsToCopy & " " & _
                          "FROM " & TblQryName & " " & _
                          "WHERE " & Criteria, dbFailOnError
                          
    End If
    
Exit_PartialCopy:
    Exit Function
Err_PartialCopy:
    'LogError Err.Number, Err.Description, "PartialCopy", "Module1 Module"'
    MsgBox Err.Description
    Resume Exit_PartialCopy
End Function

Open in new window

0
 
mwolfe02Commented:
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
0
 
horatio_tooAuthor Commented:
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
0
 
horatio_tooAuthor Commented:
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("tblquotationsummary", "(((tblquotationsummary.QuotationNumber)='" _
                                & 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
0
 
mwolfe02Commented:
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
''---------------------------------------------------------------------------------------
'' 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

Open in new window

0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now