Duplicate a record with some changes

I have a database (Access 2000) that allows me to add compliance issues. Up
until now it has been 1 issue to 1 Business Unit. I now need to be able to
create a new record with most of the data the same, but in the new record I
need to select a different Business Unit and a different Business Unit
Contact for the new issue. The Business Unit and Business Unit Contacts are
each in their own tables named accordingly. The issues are in a table named
tbl_Issues.

I would like the users to not have to reenter all of the data, so I am
looking for a solution.

I was thinking of using the Control Wizard to create a "Copy" button, but I
need to be able to modify it to blank out the two fields and require the user
to select new values from the tables. Does this sound doable or do I need to
do this with all VB code moving data field by field? Could someone suggest
the best approach and the code to make it work. I am very new to VB.

This is the code supplied by the Wizard.

Private Sub Add_additional_Business_Unit_Click()
On Error GoTo Err_Add_additional_Business_Unit_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_Add_additional_Business_Unit_Click:
Exit Sub

Err_Add_additional_Business_Unit_Click:
MsgBox Err.Description
Resume Exit_Add_additional_Business_Unit_Click

Thanks
--
MikeA

A better solution would be to create the new record in a different way.

Set the Bookmark of the form's RecordsetClone to the current record.
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark

Then move to a new record:
RunCommand acCmdRecordsGotoNew

Then copy the fields you want from the clone set into the new record:
Me.[SomeField] = rs![SomeField]
Me.[AnotherField] = rs![AnotherField]
'etc

The new record is not saved until the user completes the extra fields.
(Use Form_BeforeUpdate if you need to check that programmatically.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MikeA" wrote in message
...
I have a database (Access 2000) that allows me to add compliance issues. Up
until now it has been 1 issue to 1 Business Unit. I now need to be able to
create a new record with most of the data the same, but in the new record
I
need to select a different Business Unit and a different Business Unit
Contact for the new issue. The Business Unit and Business Unit Contacts
are
each in their own tables named accordingly. The issues are in a table
named
tbl_Issues.

I would like the users to not have to reenter all of the data, so I am
looking for a solution.

I was thinking of using the Control Wizard to create a "Copy" button, but
I
need to be able to modify it to blank out the two fields and require the
user
to select new values from the tables. Does this sound doable or do I need
to
do this with all VB code moving data field by field? Could someone suggest
the best approach and the code to make it work. I am very new to VB.

This is the code supplied by the Wizard.

Private Sub Add_additional_Business_Unit_Click()
On Error GoTo Err_Add_additional_Business_Unit_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_Add_additional_Business_Unit_Click:
Exit Sub

Err_Add_additional_Business_Unit_Click:
MsgBox Err.Description
Resume Exit_Add_additional_Business_Unit_Click

Thanks
--
MikeA

Another solution could be to write the values to the Tag of the designated
fields and then when you go to a new record read the Tags from the controls
with a loop and fill in the designated fields. That way you can decide which
fields should be copied...

Maurice

"Allen Browne" wrote:

A better solution would be to create the new record in a different way.

Set the Bookmark of the form's RecordsetClone to the current record.
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark

Then move to a new record:
RunCommand acCmdRecordsGotoNew

Then copy the fields you want from the clone set into the new record:
Me.[SomeField] = rs![SomeField]
Me.[AnotherField] = rs![AnotherField]
'etc

The new record is not saved until the user completes the extra fields.
(Use Form_BeforeUpdate if you need to check that programmatically.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MikeA" wrote in message
...
I have a database (Access 2000) that allows me to add compliance issues. Up
until now it has been 1 issue to 1 Business Unit. I now need to be able to
create a new record with most of the data the same, but in the new record
I
need to select a different Business Unit and a different Business Unit
Contact for the new issue. The Business Unit and Business Unit Contacts
are
each in their own tables named accordingly. The issues are in a table
named
tbl_Issues.

I would like the users to not have to reenter all of the data, so I am
looking for a solution.

I was thinking of using the Control Wizard to create a "Copy" button, but
I
need to be able to modify it to blank out the two fields and require the
user
to select new values from the tables. Does this sound doable or do I need
to
do this with all VB code moving data field by field? Could someone suggest
the best approach and the code to make it work. I am very new to VB.

This is the code supplied by the Wizard.

Private Sub Add_additional_Business_Unit_Click()
On Error GoTo Err_Add_additional_Business_Unit_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_Add_additional_Business_Unit_Click:
Exit Sub

Err_Add_additional_Business_Unit_Click:
MsgBox Err.Description
Resume Exit_Add_additional_Business_Unit_Click

Thanks
--
MikeA

Thanks Allen

Where do I put this code? As a procedure in the button I was playing with?
How do i trigger the action?

thanks
--
MikeA


"Allen Browne" wrote:

A better solution would be to create the new record in a different way.

Set the Bookmark of the form's RecordsetClone to the current record.
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark

Then move to a new record:
RunCommand acCmdRecordsGotoNew

Then copy the fields you want from the clone set into the new record:
Me.[SomeField] = rs![SomeField]
Me.[AnotherField] = rs![AnotherField]
'etc

The new record is not saved until the user completes the extra fields.
(Use Form_BeforeUpdate if you need to check that programmatically.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MikeA" wrote in message
...
I have a database (Access 2000) that allows me to add compliance issues. Up
until now it has been 1 issue to 1 Business Unit. I now need to be able to
create a new record with most of the data the same, but in the new record
I
need to select a different Business Unit and a different Business Unit
Contact for the new issue. The Business Unit and Business Unit Contacts
are
each in their own tables named accordingly. The issues are in a table
named
tbl_Issues.

I would like the users to not have to reenter all of the data, so I am
looking for a solution.

I was thinking of using the Control Wizard to create a "Copy" button, but
I
need to be able to modify it to blank out the two fields and require the
user
to select new values from the tables. Does this sound doable or do I need
to
do this with all VB code moving data field by field? Could someone suggest
the best approach and the code to make it work. I am very new to VB.

This is the code supplied by the Wizard.

Private Sub Add_additional_Business_Unit_Click()
On Error GoTo Err_Add_additional_Business_Unit_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_Add_additional_Business_Unit_Click:
Exit Sub

Err_Add_additional_Business_Unit_Click:
MsgBox Err.Description
Resume Exit_Add_additional_Business_Unit_Click

Thanks
--
MikeA

Allen,

I tried using the code in the button and either i am doing it wrong or this
is not the place to do it. I am getting a compile error that reads
"User-defined type not defined. The Help is talking about Type...End Type
statements. Is there something else I need to add here? I am a real new to VB
so I am at a loss as to where to go with this.

thanks

mike
--
MikeA


"MikeA" wrote:

Thanks Allen

Where do I put this code? As a procedure in the button I was playing with?
How do i trigger the action?

thanks
--
MikeA


"Allen Browne" wrote:

A better solution would be to create the new record in a different way.

Set the Bookmark of the form's RecordsetClone to the current record.
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark

Then move to a new record:
RunCommand acCmdRecordsGotoNew

Then copy the fields you want from the clone set into the new record:
Me.[SomeField] = rs![SomeField]
Me.[AnotherField] = rs![AnotherField]
'etc

The new record is not saved until the user completes the extra fields.
(Use Form_BeforeUpdate if you need to check that programmatically.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MikeA" wrote in message
...
I have a database (Access 2000) that allows me to add compliance issues. Up
until now it has been 1 issue to 1 Business Unit. I now need to be able to
create a new record with most of the data the same, but in the new record
I
need to select a different Business Unit and a different Business Unit
Contact for the new issue. The Business Unit and Business Unit Contacts
are
each in their own tables named accordingly. The issues are in a table
named
tbl_Issues.

I would like the users to not have to reenter all of the data, so I am
looking for a solution.

I was thinking of using the Control Wizard to create a "Copy" button, but
I
need to be able to modify it to blank out the two fields and require the
user
to select new values from the tables. Does this sound doable or do I need
to
do this with all VB code moving data field by field? Could someone suggest
the best approach and the code to make it work. I am very new to VB.

This is the code supplied by the Wizard.

Private Sub Add_additional_Business_Unit_Click()
On Error GoTo Err_Add_additional_Business_Unit_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_Add_additional_Business_Unit_Click:
Exit Sub

Err_Add_additional_Business_Unit_Click:
MsgBox Err.Description
Resume Exit_Add_additional_Business_Unit_Click

Thanks
--
MikeA

Is it complaining about the line of code

Dim rs As DAO.Recordset

If so, while in the VB Editor, select Tools | References from the menu bar,
scroll through the list of available references until you find the entry for
Microsoft DAO 3.6 Object Library, select it (by checking the box to the left
of it), then click on OK to close the dialog.

The problem you're encountering is despite the fact that DAO is the
preferred method to communicate with Jet databases (i.e. MDB or MDE files),
Microsoft decided to remove the reference as a default in Access 2000 and
2002. (They corrected this oversight in Access 2003)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"MikeA" wrote in message
...
Allen,

I tried using the code in the button and either i am doing it wrong or
this
is not the place to do it. I am getting a compile error that reads
"User-defined type not defined. The Help is talking about Type...End Type
statements. Is there something else I need to add here? I am a real new to
VB
so I am at a loss as to where to go with this.

thanks

mike
--
MikeA


"MikeA" wrote:

Thanks Allen

Where do I put this code? As a procedure in the button I was playing
with?
How do i trigger the action?

thanks
--
MikeA


"Allen Browne" wrote:

A better solution would be to create the new record in a different way.

Set the Bookmark of the form's RecordsetClone to the current record.
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark

Then move to a new record:
RunCommand acCmdRecordsGotoNew

Then copy the fields you want from the clone set into the new record:
Me.[SomeField] = rs![SomeField]
Me.[AnotherField] = rs![AnotherField]
'etc

The new record is not saved until the user completes the extra fields.
(Use Form_BeforeUpdate if you need to check that programmatically.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MikeA" wrote in message
...
I have a database (Access 2000) that allows me to add compliance
issues. Up
until now it has been 1 issue to 1 Business Unit. I now need to be
able to
create a new record with most of the data the same, but in the new
record
I
need to select a different Business Unit and a different Business
Unit
Contact for the new issue. The Business Unit and Business Unit
Contacts
are
each in their own tables named accordingly. The issues are in a table
named
tbl_Issues.

I would like the users to not have to reenter all of the data, so I
am
looking for a solution.

I was thinking of using the Control Wizard to create a "Copy" button,
but
I
need to be able to modify it to blank out the two fields and require
the
user
to select new values from the tables. Does this sound doable or do I
need
to
do this with all VB code moving data field by field? Could someone
suggest
the best approach and the code to make it work. I am very new to VB.

This is the code supplied by the Wizard.

Private Sub Add_additional_Business_Unit_Click()
On Error GoTo Err_Add_additional_Business_Unit_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste
Append

Exit_Add_additional_Business_Unit_Click:
Exit Sub

Err_Add_additional_Business_Unit_Click:
MsgBox Err.Description
Resume Exit_Add_additional_Business_Unit_Click

Thanks
--
MikeA

Yes. Set the button's On Click property to:
[Event Procedure]

Then click the Build button (...) beside the property.
Access opens the code window, showing the code you originally posted.

Replace these 3 lines with your new code:

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70

Then check that Access understands your code by choosing Compile in the
Debug menu.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MikeA" wrote in message
...
Allen,

I tried using the code in the button and either i am doing it wrong or
this
is not the place to do it. I am getting a compile error that reads
"User-defined type not defined. The Help is talking about Type...End Type
statements. Is there something else I need to add here? I am a real new to
VB
so I am at a loss as to where to go with this.

thanks

mike
--
MikeA


"MikeA" wrote:

Thanks Allen

Where do I put this code? As a procedure in the button I was playing
with?
How do i trigger the action?

thanks
--
MikeA


"Allen Browne" wrote:

A better solution would be to create the new record in a different way.

Set the Bookmark of the form's RecordsetClone to the current record.
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark

Then move to a new record:
RunCommand acCmdRecordsGotoNew

Then copy the fields you want from the clone set into the new record:
Me.[SomeField] = rs![SomeField]
Me.[AnotherField] = rs![AnotherField]
'etc

The new record is not saved until the user completes the extra fields.
(Use Form_BeforeUpdate if you need to check that programmatically.)

"MikeA" wrote in message
...
I have a database (Access 2000) that allows me to add compliance
issues. Up
until now it has been 1 issue to 1 Business Unit. I now need to be
able to
create a new record with most of the data the same, but in the new
record
I
need to select a different Business Unit and a different Business
Unit
Contact for the new issue. The Business Unit and Business Unit
Contacts
are
each in their own tables named accordingly. The issues are in a table
named
tbl_Issues.

I would like the users to not have to reenter all of the data, so I
am
looking for a solution.

I was thinking of using the Control Wizard to create a "Copy" button,
but
I
need to be able to modify it to blank out the two fields and require
the
user
to select new values from the tables. Does this sound doable or do I
need
to
do this with all VB code moving data field by field? Could someone
suggest
the best approach and the code to make it work. I am very new to VB.

This is the code supplied by the Wizard.

Private Sub Add_additional_Business_Unit_Click()
On Error GoTo Err_Add_additional_Business_Unit_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste
Append

Exit_Add_additional_Business_Unit_Click:
Exit Sub

Err_Add_additional_Business_Unit_Click:
MsgBox Err.Description
Resume Exit_Add_additional_Business_Unit_Click

Thank you both. I'm a big step closer, but now I'm getting an error message
that I have not been able to figure out. Maybe you can point me in the right
direction.

The error message is 'Item not found in this collection", but it does not
say what item. Below are the fields I am moving. I get a new record and the
top 4 fields are populated in the new record, but not the last 4. I have
change the order of the last 4 records and run the code with the same
results. I have commented them out one at a time to see if i could identify
the problem but that didn't make a difference either.

Me.[ReviewName] = rs![ReviewName] -- Text field
Me.[IssueName] = rs![IssueName] -- Test field
Me.[RegulationID] = rs![RegulationID] -- This is a # field that does a
lookup to a table named "Regulation".
Me.["Opened Date"] = rs!["Opened Date"] -- Date field
'Me.[Comment] = rs![Comment] -- Memo field
'Me.["Repeat Issue"] = rs!["Repeat Issue"] -- Yes/No box
'Me.["Opened By"] = rs!["Opened By"] -- This is a # field that does a
lookup to a table names "Contacts".
'Me.["Assigned To"] = rs!["Assigned To"] -- This goes to the same table
in the same way as the last field.

This is the code for the lookup.

SELECT [Contacts].[ID], [Contacts].[First Name] & " " & [Contacts].[Last
Name] AS Expr1 FROM Contacts ORDER BY [Contacts].[Last Name];

Any ideas?

thanks

Mike

--
MikeA


"Allen Browne" wrote:

Yes. Set the button's On Click property to:
[Event Procedure]

Then click the Build button (...) beside the property.
Access opens the code window, showing the code you originally posted.

Replace these 3 lines with your new code:

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70

Then check that Access understands your code by choosing Compile in the
Debug menu.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MikeA" wrote in message
...
Allen,

I tried using the code in the button and either i am doing it wrong or
this
is not the place to do it. I am getting a compile error that reads
"User-defined type not defined. The Help is talking about Type...End Type
statements. Is there something else I need to add here? I am a real new to
VB
so I am at a loss as to where to go with this.

thanks

mike
--
MikeA


"MikeA" wrote:

Thanks Allen

Where do I put this code? As a procedure in the button I was playing
with?
How do i trigger the action?

thanks
--
MikeA


"Allen Browne" wrote:

A better solution would be to create the new record in a different way.

Set the Bookmark of the form's RecordsetClone to the current record.
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark

Then move to a new record:
RunCommand acCmdRecordsGotoNew

Then copy the fields you want from the clone set into the new record:
Me.[SomeField] = rs![SomeField]
Me.[AnotherField] = rs![AnotherField]
'etc

The new record is not saved until the user completes the extra fields.
(Use Form_BeforeUpdate if you need to check that programmatically.)

"MikeA" wrote in message
...
I have a database (Access 2000) that allows me to add compliance
issues. Up
until now it has been 1 issue to 1 Business Unit. I now need to be
able to
create a new record with most of the data the same, but in the new
record
I
need to select a different Business Unit and a different Business
Unit
Contact for the new issue. The Business Unit and Business Unit
Contacts
are
each in their own tables named accordingly. The issues are in a table
named
tbl_Issues.

I would like the users to not have to reenter all of the data, so I
am
looking for a solution.

I was thinking of using the Control Wizard to create a "Copy" button,
but
I
need to be able to modify it to blank out the two fields and require
the
user
to select new values from the tables. Does this sound doable or do I
need
to
do this with all VB code moving data field by field? Could someone
suggest
the best approach and the code to make it work. I am very new to VB.

This is the code supplied by the Wizard.

Private Sub Add_additional_Business_Unit_Click()
On Error GoTo Err_Add_additional_Business_Unit_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste
Append

Exit_Add_additional_Business_Unit_Click:
Exit Sub

Err_Add_additional_Business_Unit_Click:
MsgBox Err.Description
Resume Exit_Add_additional_Business_Unit_Click

1. What's with the quotes, e.g.:
Me.["Opened Date"]
That doesn't look right.

2. Does the code compile? (Compile in Debug menu.)

3. If it runs, which line gives the error?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MikeA" wrote in message
...
Thank you both. I'm a big step closer, but now I'm getting an error
message
that I have not been able to figure out. Maybe you can point me in the
right
direction.

The error message is 'Item not found in this collection", but it does not
say what item. Below are the fields I am moving. I get a new record and
the
top 4 fields are populated in the new record, but not the last 4. I have
change the order of the last 4 records and run the code with the same
results. I have commented them out one at a time to see if i could
identify
the problem but that didn't make a difference either.

Me.[ReviewName] = rs![ReviewName] -- Text field
Me.[IssueName] = rs![IssueName] -- Test field
Me.[RegulationID] = rs![RegulationID] -- This is a # field that does a
lookup to a table named "Regulation".
Me.["Opened Date"] = rs!["Opened Date"] -- Date field
'Me.[Comment] = rs![Comment] -- Memo field
'Me.["Repeat Issue"] = rs!["Repeat Issue"] -- Yes/No box
'Me.["Opened By"] = rs!["Opened By"] -- This is a # field that does a
lookup to a table names "Contacts".
'Me.["Assigned To"] = rs!["Assigned To"] -- This goes to the same table
in the same way as the last field.

This is the code for the lookup.

SELECT [Contacts].[ID], [Contacts].[First Name] & " " & [Contacts].[Last
Name] AS Expr1 FROM Contacts ORDER BY [Contacts].[Last Name];

Any ideas?

thanks

Mike

--
MikeA


"Allen Browne" wrote:

Yes. Set the button's On Click property to:
[Event Procedure]

Then click the Build button (...) beside the property.
Access opens the code window, showing the code you originally posted.

Replace these 3 lines with your new code:

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70

Then check that Access understands your code by choosing Compile in the
Debug menu.

"MikeA" wrote in message
...
Allen,

I tried using the code in the button and either i am doing it wrong or
this
is not the place to do it. I am getting a compile error that reads
"User-defined type not defined. The Help is talking about Type...End
Type
statements. Is there something else I need to add here? I am a real new
to
VB
so I am at a loss as to where to go with this.

thanks

mike
--
MikeA


"MikeA" wrote:

Thanks Allen

Where do I put this code? As a procedure in the button I was playing
with?
How do i trigger the action?

thanks
--
MikeA


"Allen Browne" wrote:

A better solution would be to create the new record in a different
way.

Set the Bookmark of the form's RecordsetClone to the current record.
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark

Then move to a new record:
RunCommand acCmdRecordsGotoNew

Then copy the fields you want from the clone set into the new
record:
Me.[SomeField] = rs![SomeField]
Me.[AnotherField] = rs![AnotherField]
'etc

The new record is not saved until the user completes the extra
fields.
(Use Form_BeforeUpdate if you need to check that programmatically.)

"MikeA" wrote in message
...
I have a database (Access 2000) that allows me to add compliance
issues. Up
until now it has been 1 issue to 1 Business Unit. I now need to be
able to
create a new record with most of the data the same, but in the new
record
I
need to select a different Business Unit and a different Business
Unit
Contact for the new issue. The Business Unit and Business Unit
Contacts
are
each in their own tables named accordingly. The issues are in a
table
named
tbl_Issues.

I would like the users to not have to reenter all of the data, so
I
am
looking for a solution.

I was thinking of using the Control Wizard to create a "Copy"
button,
but
I
need to be able to modify it to blank out the two fields and
require
the
user
to select new values from the tables. Does this sound doable or do
I
need
to
do this with all VB code moving data field by field? Could someone
suggest
the best approach and the code to make it work. I am very new to
VB.

This is the code supplied by the Wizard.

Private Sub Add_additional_Business_Unit_Click()
On Error GoTo Err_Add_additional_Business_Unit_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste
Append

Exit_Add_additional_Business_Unit_Click:
Exit Sub

Err_Add_additional_Business_Unit_Click:
MsgBox Err.Description
Resume Exit_Add_additional_Business_Unit_Click

Allen,

It was my understanding (probably incorrectly) that if the field name
contained a blank [Opened Date] that you had to group it by using quotes ("")
or brackes (). I take it that this is not needed. I do not get a compile
error, just a small window that pops up in front of the form with that
message. If I click OK, I can manually enter the rest of the data.

Mike

--
MikeA


"Allen Browne" wrote:

1. What's with the quotes, e.g.:
Me.["Opened Date"]
That doesn't look right.

2. Does the code compile? (Compile in Debug menu.)

3. If it runs, which line gives the error?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MikeA" wrote in message
...
Thank you both. I'm a big step closer, but now I'm getting an error
message
that I have not been able to figure out. Maybe you can point me in the
right
direction.

The error message is 'Item not found in this collection", but it does not
say what item. Below are the fields I am moving. I get a new record and
the
top 4 fields are populated in the new record, but not the last 4. I have
change the order of the last 4 records and run the code with the same
results. I have commented them out one at a time to see if i could
identify
the problem but that didn't make a difference either.

Me.[ReviewName] = rs![ReviewName] -- Text field
Me.[IssueName] = rs![IssueName] -- Test field
Me.[RegulationID] = rs![RegulationID] -- This is a # field that does a
lookup to a table named "Regulation".
Me.["Opened Date"] = rs!["Opened Date"] -- Date field
'Me.[Comment] = rs![Comment] -- Memo field
'Me.["Repeat Issue"] = rs!["Repeat Issue"] -- Yes/No box
'Me.["Opened By"] = rs!["Opened By"] -- This is a # field that does a
lookup to a table names "Contacts".
'Me.["Assigned To"] = rs!["Assigned To"] -- This goes to the same table
in the same way as the last field.

This is the code for the lookup.

SELECT [Contacts].[ID], [Contacts].[First Name] & " " & [Contacts].[Last
Name] AS Expr1 FROM Contacts ORDER BY [Contacts].[Last Name];

Any ideas?

thanks

Mike

--
MikeA


"Allen Browne" wrote:

Yes. Set the button's On Click property to:
[Event Procedure]

Then click the Build button (...) beside the property.
Access opens the code window, showing the code you originally posted.

Replace these 3 lines with your new code:

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70

Then check that Access understands your code by choosing Compile in the
Debug menu.

"MikeA" wrote in message
...
Allen,

I tried using the code in the button and either i am doing it wrong or
this
is not the place to do it. I am getting a compile error that reads
"User-defined type not defined. The Help is talking about Type...End
Type
statements. Is there something else I need to add here? I am a real new
to
VB
so I am at a loss as to where to go with this.

thanks

mike
--
MikeA


"MikeA" wrote:

Thanks Allen

Where do I put this code? As a procedure in the button I was playing
with?
How do i trigger the action?

thanks
--
MikeA


"Allen Browne" wrote:

A better solution would be to create the new record in a different
way.

Set the Bookmark of the form's RecordsetClone to the current record.
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark

Then move to a new record:
RunCommand acCmdRecordsGotoNew

Then copy the fields you want from the clone set into the new
record:
Me.[SomeField] = rs![SomeField]
Me.[AnotherField] = rs![AnotherField]
'etc

The new record is not saved until the user completes the extra
fields.
(Use Form_BeforeUpdate if you need to check that programmatically.)

"MikeA" wrote in message
...
I have a database (Access 2000) that allows me to add compliance
issues. Up
until now it has been 1 issue to 1 Business Unit. I now need to be
able to
create a new record with most of the data the same, but in the new
record
I
need to select a different Business Unit and a different Business
Unit
Contact for the new issue. The Business Unit and Business Unit
Contacts
are
each in their own tables named accordingly. The issues are in a
table
named
tbl_Issues.

I would like the users to not have to reenter all of the data, so
I
am
looking for a solution.

I was thinking of using the Control Wizard to create a "Copy"
button,
but
I
need to be able to modify it to blank out the two fields and
require
the
user
to select new values from the tables. Does this sound doable or do
I
need
to
do this with all VB code moving data field by field? Could someone
suggest
the best approach and the code to make it work. I am very new to
VB.

This is the code supplied by the Wizard.

Private Sub Add_additional_Business_Unit_Click()
On Error GoTo Err_Add_additional_Business_Unit_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste
Append

Exit_Add_additional_Business_Unit_Click:
Exit Sub

Err_Add_additional_Business_Unit_Click:
MsgBox Err.Description
Resume Exit_Add_additional_Business_Unit_Click