Duplicate a record with some changes

[MikeA]

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

 

[Allen Browne]

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

 

[Maurice]

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

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

Reply to group, rather than allenbrowne at mvps dot org.

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

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

Reply to group, rather than allenbrowne at mvps dot org.

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

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

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

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Douglas J. Steele]

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

(no e-mails, please!)

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

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

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

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Allen Browne]

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

Reply to group, rather than allenbrowne at mvps dot org.

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

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

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

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

 

[MikeA]

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

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

Reply to group, rather than allenbrowne at mvps dot org.

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

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


:

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

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

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

Reply to group, rather than allenbrowne at mvps dot org.

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

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,

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


:

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


:

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

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

 

[MikeA]

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

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

Reply to group, rather than allenbrowne at mvps dot org.

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

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,

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


:

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


:

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

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

Let us know if removing the quotes solves the problem.
(The square brackets do the job.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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

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?

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


:

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,

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


:

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


:

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

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

Best to start a new thread for a new question.

You should be able to use SetFocus.
Without testing, it would be something like this:

Me.Parent.SetFocus
Me.Parent!BusinessUnitID.SetFocus

 

[MikeA]

Allen,

I started to do that, but then though that because the entire history of my
problem was here it would put the question in context if I added it here.

I added the code you suggested and I am getting an error message. "The
expression you entered has an invalid reference to the Parent property."

I have also added a message box with an if... then on a yes/No answer. I
plased the update code to run on a Yes answer. If works fine, but does it
effect the placement of the new code. If I included the code after the update
code in the If...then I get the error message. If I move it out of the
if...ten the code runs withut error but does not set focus.

Msg1 = MsgBox("Do you want to assign this issue to an additional Business
Unit?", vbYesNo, "New Issue Record")
If Msg1 = vbYes Then

thanks
--
MikeA

Best to start a new thread for a new question.

You should be able to use SetFocus.
Without testing, it would be something like this:

Me.Parent.SetFocus
Me.Parent!BusinessUnitID.SetFocus

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'm hoping you can help me again.

After the new record is created, I would like to have the focus on the
form
be set to the 1st of the 2 fields they need to add for the new record
[BusinessUnitID].

This field is on the form, not a subform. It is a # field that does a
lookup
in a table named Regulation.

 

[Allen Browne]

If the form you added this code to has no Parent, then it is not a subform.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I started to do that, but then though that because the entire history of
my
problem was here it would put the question in context if I added it here.

I added the code you suggested and I am getting an error message. "The
expression you entered has an invalid reference to the Parent property."

I have also added a message box with an if... then on a yes/No answer. I
plased the update code to run on a Yes answer. If works fine, but does it
effect the placement of the new code. If I included the code after the
update
code in the If...then I get the error message. If I move it out of the
if...ten the code runs withut error but does not set focus.

Msg1 = MsgBox("Do you want to assign this issue to an additional Business
Unit?", vbYesNo, "New Issue Record")
If Msg1 = vbYes Then

thanks
--
MikeA

Best to start a new thread for a new question.

You should be able to use SetFocus.
Without testing, it would be something like this:

Me.Parent.SetFocus
Me.Parent!BusinessUnitID.SetFocus

I'm hoping you can help me again.

After the new record is created, I would like to have the focus on the
form
be set to the 1st of the 2 fields they need to add for the new record
[BusinessUnitID].

This field is on the form, not a subform. It is a # field that does a
lookup
in a table named Regulation.

 

[MikeA]

I'm sorry I didn't make it clear on my posting below. I realize that the
field is on the form. I have been trying to use - Me.BusinessUnitID.Setfocus
but I keep getting an error saying it doesn't like .Setfocus. As I type it,
it wants me to use .value. I'm sure my syntax is wrong but done know what to
change. I've seen other posting where they appear to be using this format,
but I cannot get it to work.

thanks

Mike
--
MikeA

If the form you added this code to has no Parent, then it is not a subform.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I started to do that, but then though that because the entire history of
my
problem was here it would put the question in context if I added it here.

I added the code you suggested and I am getting an error message. "The
expression you entered has an invalid reference to the Parent property."

I have also added a message box with an if... then on a yes/No answer. I
plased the update code to run on a Yes answer. If works fine, but does it
effect the placement of the new code. If I included the code after the
update
code in the If...then I get the error message. If I move it out of the
if...ten the code runs withut error but does not set focus.

Msg1 = MsgBox("Do you want to assign this issue to an additional Business
Unit?", vbYesNo, "New Issue Record")
If Msg1 = vbYes Then

thanks
--
MikeA

Best to start a new thread for a new question.

You should be able to use SetFocus.
Without testing, it would be something like this:

Me.Parent.SetFocus
Me.Parent!BusinessUnitID.SetFocus

I'm hoping you can help me again.

After the new record is created, I would like to have the focus on the
form
be set to the 1st of the 2 fields they need to add for the new record
[BusinessUnitID].

This field is on the form, not a subform. It is a # field that does a
lookup
in a table named Regulation.

 

[MikeA]

Allen,

I figured out my focus problem. It was a Dah! I have to fields with similar
names. The one I was trying to use was the one NOT on the form. Works like a
charm when you use the correct field.

thanks for your help and patience.

Mike
--
MikeA

I'm sorry I didn't make it clear on my posting below. I realize that the
field is on the form. I have been trying to use - Me.BusinessUnitID.Setfocus
but I keep getting an error saying it doesn't like .Setfocus. As I type it,
it wants me to use .value. I'm sure my syntax is wrong but done know what to
change. I've seen other posting where they appear to be using this format,
but I cannot get it to work.

thanks

Mike
--
MikeA

If the form you added this code to has no Parent, then it is not a subform.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I started to do that, but then though that because the entire history of
my
problem was here it would put the question in context if I added it here.

I added the code you suggested and I am getting an error message. "The
expression you entered has an invalid reference to the Parent property."

I have also added a message box with an if... then on a yes/No answer. I
plased the update code to run on a Yes answer. If works fine, but does it
effect the placement of the new code. If I included the code after the
update
code in the If...then I get the error message. If I move it out of the
if...ten the code runs withut error but does not set focus.

Msg1 = MsgBox("Do you want to assign this issue to an additional Business
Unit?", vbYesNo, "New Issue Record")
If Msg1 = vbYes Then

thanks
--
MikeA


:

Best to start a new thread for a new question.

You should be able to use SetFocus.
Without testing, it would be something like this:

Me.Parent.SetFocus
Me.Parent!BusinessUnitID.SetFocus

I'm hoping you can help me again.

After the new record is created, I would like to have the focus on the
form
be set to the 1st of the 2 fields they need to add for the new record
[BusinessUnitID].

This field is on the form, not a subform. It is a # field that does a
lookup
in a table named Regulation.