How to copy/append entire record with modification to one field 
Author Message
 How to copy/append entire record with modification to one field

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



Sun, 25 Jan 2004 02:24:39 GMT  
 How to copy/append entire record with modification to one 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


Quote:
> 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



Sun, 25 Jan 2004 03:40:01 GMT  
 How to copy/append entire record with modification to one field
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.


Quote:
> 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



Mon, 26 Jan 2004 12:22:52 GMT  
 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



Tue, 27 Jan 2004 01:36:17 GMT  
 How to copy/append entire record with modification to one field

Quote:
> Uuuh - how do you expect to insert data into those fields if you don't
> include them in the field lists?

That was *my* question... <grin> I just wanted to do it programmatically vs.
manually.

The code you provided (Set tdfSrc = dbs.TableDefs(psSrcTbl)... For Each
fldSrc In tdfSrc.Fields) will work great.

Thanks!

Newt


Quote:
> 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



> > 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



Tue, 27 Jan 2004 04:56:11 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Copying entire records from one table to another.

2. Appending an entire record to a new table

3. Copying an entire table from one doc to another

4. Copying an entire Word document from one Word file to another

5. Copy entire table from one database to another

6. Copying an entire record

7. Copying an entire record?

8. Copying an entire record?

9. Copying an entire record?

10. copy from one doc append to another

11. Fast Way to update one field in one record

12. Copy Table Records and Paste Append

 

 
Powered by phpBB® Forum Software