By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,474 Members | 3,214 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,474 IT Pros & Developers. It's quick & easy.

Copy Previou Record

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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("tIncomeAlloc", "Counter") _
& " FROM tIncomeAlloc " _
& "WHERE RecNo = " & vReceiptNumber, dbFailOnError

Public Function FieldNames(tblName 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(tblName)

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*****@earthlink.net> wrote in message
news:vQ*****************@newsread3.news.atl.earthl ink.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

Nov 12 '05 #2

P: n/a
Bob,

Thank you for your response!

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

Thanks!

Marleen
"Bob Darlington" <bo*@dpcmanAX.com.au> wrote in message
news:40***********************@news.optusnet.com.a u...
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("tIncomeAlloc", "Counter") _
& " FROM tIncomeAlloc " _
& "WHERE RecNo = " & vReceiptNumber, dbFailOnError

Public Function FieldNames(tblName 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(tblName)

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*****@earthlink.net> wrote in message
news:vQ*****************@newsread3.news.atl.earthl ink.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


Nov 12 '05 #3

P: n/a
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("TableName", "PrimaryKeyField") _
& " FROM TableName " _
& "WHERE PrimaryKeyField = " & vPrimaryKeyField,
dbFailOnError

vPrimaryKeyField identifies the record you wish to copy.

--
Bob Darlington
Brisbane
"Marleen" <mm*****@earthlink.net> wrote in message
news:AX*****************@newsread3.news.atl.earthl ink.net...
Bob,

Thank you for your response!

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

Thanks!

Marleen
"Bob Darlington" <bo*@dpcmanAX.com.au> wrote in message
news:40***********************@news.optusnet.com.a u...
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("tIncomeAlloc", "Counter") _
& " FROM tIncomeAlloc " _
& "WHERE RecNo = " & vReceiptNumber, dbFailOnError

Public Function FieldNames(tblName 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(tblName)

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*****@earthlink.net> wrote in message
news:vQ*****************@newsread3.news.atl.earthl ink.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



Nov 12 '05 #4

P: n/a
"Marleen" <mm*****@earthlink.net> wrote in
news:vQ*****************@newsread3.news.atl.earthl ink.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 acCmdSelectRecord
.RunCommand acCmdCopy
.RunCommand acCmdPasteAppend
End With

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.