Copy Previou Record

[Marleen]

I have a database that manages my webstore. Each store item record has 26
fields. I would like to have a way when adding new items to be able to just copy
the previous item when most everything is the same for the current item being
added. Could someone offer a suggestion on how to accomplish this.

Thank you very much!

Marleen

[Bob Darlington]

Marleen,
There may well be a more elegant way, but I use the 'FieldNames' function
below to create a list of fields in the form's underlying query, where
'exclFld' is field to be ignored (normally Autonumber primary index field).
Then I use the resultant list as follows:

db.Execute "INSERT INTO tIncomeAllocAdd SELECT " _
& FieldNames("tIn comeAlloc", "Counter") _
& " FROM tIncomeAlloc " _
& "WHERE RecNo = " & vReceiptNumber, dbFailOnError

Public Function FieldNames(tblN ame As String, exclFld As String) As String
On Error GoTo ErrorFieldNames
Dim fldNames As String, td As TableDef, qd As QueryDef, db As Database, fld
As Field

Set db = CurrentDb
Set qd = db.QueryDefs(tb lName)

For Each fld In qd.Fields
If fld.Name <> exclFld Then
If fldNames <> "" Then
fldNames = fldNames & ", [" & fld.Name & "]"
Else
fldNames = "[" & fld.Name & "]"
End If
End If
Next

FieldNames = fldNames
Exit Function

ErrorFieldNames :
MsgBox Error$
Exit Function
End Function

Hope this helps.
--
Bob Darlington
Brisbane
"Marleen" <mm*****@earthl ink.net> wrote in message
news:vQ******** *********@newsr ead3.news.atl.e arthlink.net...

I have a database that manages my webstore. Each store item record has 26
fields. I would like to have a way when adding new items to be able to just copy the previous item when most everything is the same for the current item being added. Could someone offer a suggestion on how to accomplish this.

Thank you very much!

Marleen

[Marleen]

Bob,

Thank you for your response!

Could you give me a thumbnail sketch of how this works.

Thanks!

Marleen
"Bob Darlington" <bo*@dpcmanAX.c om.au> wrote in message
news:40******** *************** @news.optusnet. com.au...

Marleen,
There may well be a more elegant way, but I use the 'FieldNames' function
below to create a list of fields in the form's underlying query, where
'exclFld' is field to be ignored (normally Autonumber primary index field).
Then I use the resultant list as follows:

db.Execute "INSERT INTO tIncomeAllocAdd SELECT " _
& FieldNames("tIn comeAlloc", "Counter") _
& " FROM tIncomeAlloc " _
& "WHERE RecNo = " & vReceiptNumber, dbFailOnError

Public Function FieldNames(tblN ame As String, exclFld As String) As String
On Error GoTo ErrorFieldNames
Dim fldNames As String, td As TableDef, qd As QueryDef, db As Database, fld
As Field

Set db = CurrentDb
Set qd = db.QueryDefs(tb lName)

For Each fld In qd.Fields
If fld.Name <> exclFld Then
If fldNames <> "" Then
fldNames = fldNames & ", [" & fld.Name & "]"
Else
fldNames = "[" & fld.Name & "]"
End If
End If
Next

FieldNames = fldNames
Exit Function

ErrorFieldNames :
MsgBox Error$
Exit Function
End Function

Hope this helps.
--
Bob Darlington
Brisbane
"Marleen" <mm*****@earthl ink.net> wrote in message
news:vQ******** *********@newsr ead3.news.atl.e arthlink.net...

I have a database that manages my webstore. Each store item record has 26
fields. I would like to have a way when adding new items to be able to

just copy

the previous item when most everything is the same for the current item

being

added. Could someone offer a suggestion on how to accomplish this.

Thank you very much!

Marleen

[Bob Darlington]

Marleen,
FieldNames() just generates a string of field names to insert into the SQL,
instead of typing them individually.
One field in the table will be a primary key field which cannot be
duplicated, therefore it is excluded from the string (exclFld). For this
solution to work, this key must be an autonumber, otherwise an error will
occur.
The following statement might be clearer than my origianl post:

db.Execute "INSERT INTO TableName SELECT " _
& FieldNames("Tab leName", "PrimaryKeyFiel d") _
& " FROM TableName " _
& "WHERE PrimaryKeyField = " & vPrimaryKeyFiel d,
dbFailOnError

vPrimaryKeyFiel d identifies the record you wish to copy.

--
Bob Darlington
Brisbane
"Marleen" <mm*****@earthl ink.net> wrote in message
news:AX******** *********@newsr ead3.news.atl.e arthlink.net...

Bob,

Thank you for your response!

Could you give me a thumbnail sketch of how this works.

Thanks!

Marleen
"Bob Darlington" <bo*@dpcmanAX.c om.au> wrote in message
news:40******** *************** @news.optusnet. com.au...

Marleen,
There may well be a more elegant way, but I use the 'FieldNames' function below to create a list of fields in the form's underlying query, where
'exclFld' is field to be ignored (normally Autonumber primary index field). Then I use the resultant list as follows:

db.Execute "INSERT INTO tIncomeAllocAdd SELECT " _
& FieldNames("tIn comeAlloc", "Counter") _
& " FROM tIncomeAlloc " _
& "WHERE RecNo = " & vReceiptNumber, dbFailOnError

Public Function FieldNames(tblN ame As String, exclFld As String) As String On Error GoTo ErrorFieldNames
Dim fldNames As String, td As TableDef, qd As QueryDef, db As Database, fld As Field

Set db = CurrentDb
Set qd = db.QueryDefs(tb lName)

For Each fld In qd.Fields
If fld.Name <> exclFld Then
If fldNames <> "" Then
fldNames = fldNames & ", [" & fld.Name & "]"
Else
fldNames = "[" & fld.Name & "]"
End If
End If
Next

FieldNames = fldNames
Exit Function

ErrorFieldNames :
MsgBox Error$
Exit Function
End Function

Hope this helps.
--
Bob Darlington
Brisbane
"Marleen" <mm*****@earthl ink.net> wrote in message
news:vQ******** *********@newsr ead3.news.atl.e arthlink.net...

I have a database that manages my webstore. Each store item record has 26 fields. I would like to have a way when adding new items to be able to

just copy

the previous item when most everything is the same for the current

item being

added. Could someone offer a suggestion on how to accomplish this.

Thank you very much!

Marleen

[Lyle Fairfield]

"Marleen" <mm*****@earthl ink.net> wrote in
news:vQ******** *********@newsr ead3.news.atl.e arthlink.net:

I have a database that manages my webstore. Each store item record has
26 fields. I would like to have a way when adding new items to be able
to just copy the previous item when most everything is the same for the
current item being added. Could someone offer a suggestion on how to
accomplish this.

Thank you very much!

Marleen

I run this code in the onClick event of a command button or the function
call of a pop up menu item, the caption of each being, "New Duplicate
Record".

With DoCmd
.RunCommand acCmdSelectReco rd
.RunCommand acCmdCopy
.RunCommand acCmdPasteAppen d
End With

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)