How to copy/append entire record with modification to one field
Uuuh - how do you expect to insert data into those fields if you don't
include them in the field lists?
With dynamic SQL, you can generate the list of fields automatically by
looping through the tabledef's Fields collection. I used to have a function
to do that ... where is it ... ah! here:
Public Function buildInsQryFldList(ByVal psSrcTbl As String, _
ByVal psDestTbl As String) As String
'******************************
'This function builds a comma-delimited string list of fieldnames common to
the two passed
'table names which is passed back to the calling procedure to be used in
'an insert query. It surrounds each fieldname with [] to avoid naming
errors.
'This avoids the "Unknown Field error.
'********************************
Dim tdfSrc As TableDef, tdfDest As TableDef
Dim fldSrc As Field, fldDest As Field
Dim sSrcName As String, sDestName As String
Dim DestTblFlds_a() As String
Dim dbs As DATABASE
Dim sFldLst$, i As Integer
sFldLst = ""
Set dbs = CurrentDb
Set tdfSrc = dbs.TableDefs(psSrcTbl)
Set tdfDest = dbs.TableDefs(psDestTbl)
For Each fldSrc In tdfSrc.Fields
sSrcName = fldSrc.Name
For Each fldDest In tdfDest.Fields
sDestName = fldDest.Name
If sDestName = sSrcName Then sFldLst = IIf(sFldLst = "", "[" & sDestName
& "]", sFldLst & ",[" & sDestName & "]")
Next fldDest
Next fldSrc
buildInsQryFldList = sFldLst
End Function
You can modify this function to exclude the intAuto and intBoxNumber field
names. Then create your dynamic SQL statement like this:
Sub DupRecord(plOldIntAuto As Long,plNewBoxNumber As Long)
Dim sSQL As String,sFldList As String
sFldList = buildInsQryFldList("tablename", "tablename")
sSQL = "Insert Into tablename (intBoxNumber," & sFldList & ")" & _
" Select " & plNewBoxNumber & "," & sFldList & " FROM Tablename " & _
" Where intAuto = " & plOldIntAuto
Currentdb.execute sSQL
End Sub
Alternatively, you could use a couple recordsets.
HTH,
Bob Barrows
Quote:
> Unfortunately, it is the "<rest of fields" that I'm trying to avoid.... I
> don't want to have to modify the code every time I add or remove a field.
> > Create a query with the following SQL:
> > INSERT INTO tablename (intBoxNumber,<rest of fields excluding intAuto)
> > Select 700,<rest of fields excluding intAuto AND intBoxNumber in same
> order
> > as above fieldlist>
> > From table
> > Where intAuto = 4
> > HTH,
> > Bob Barrows
> > > Using Access97 (yuck):
> > > I have a record with 20-some fields.
> > > intAuto is an autonumber field
> > > intBoxNumber is *not* an autonumber field
> > > I want to duplicate (copy/paste append) a specific record (ex:
> intAuto=4)
> > > but assign it a specific intBoxNumber (ex: 700).
> > > How do I do this (using INSERT INTO?) without specifically itemizing
> *all*
> > > fields/values?
> > > The database is in development and I don't want to go back into my
code
> > > every time we add/remove a field from the table.
> > > Thanks.
> > > Newt
