Copying Records in a Form

  • Thread starter Thread starter Samantha
  • Start date Start date
S

Samantha

The purpose of my database is to store information on our
projects. I have a form that is used to enter new projects
and their related information. It also has a combo box on
it that displays a list of existing projects. The user can
choose from this list and the info from that selected
project then displays on the form allowing the user to
make changes.
There is often duplicate information among the projects,
such as the client name, address, etc. I have added
a "duplicate" button to the form so we can copy projects
and change the necessary info but it isn't working
correctly. I chose "debug" when I was given the error and
found that it is due to the code that is in the
AfterUpdate event of the combo box. The code is as follows:

Private Sub Combo220_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Project Number] = '" & Me![Combo220]
& "'"
Me.Bookmark = rs.Bookmark
End Sub


It is the last line (Me.Bookmark = rs.Bookmar) that is
causing the problem. When I deleted this line I was able
to run the copy command, however, the combo button no
longer works right - it doesn't fill the form in according
to the selected project from the combo box.

I need both the combo box and the copy button to work
correctly. Any help would be appreciated.

Thanks, Samantha
 
Hi Samantha

The problem is the dot between Recordset and Clone. RecordsetClone (no dot)
is a property of the form. It's also a good idea to check that you have
actually found a matching record before setting the bookmark. You can make
good use of a With statement here:

With Me.RecordsetClone
.FindFirst "[Project Number] = '" _
& Me![Combo220] & "'"
If .NoMatch then
MsgBox "Record not found"
Else
Me.Bookmark = .Bookmark
End If
End With

However, I'm slightly confused - you say you have a problem with a
"duplicate" button which copies records, but this code selects the current
record from the list on a combo box.

Post a reply back if you still have any problems.
 
Graham,

Thanks for your assistance. I'm still having problems with
the form. First off, let me try to clarify my intent. The
form is used to enter new projects as well as to edit/view
existing projects (that is the purpose of the combo box
which has the code to select a specific record from a drop
down list). Because we have many projects that have the
same info in them, I have added the "duplicate" button so
a user may select a project from the combo box then
duplicate it and make the necessary changes to it.

I have entered the code as you suggested. This works for
the purpose of the combo box but still returns an error in
regards to the Bookmark line when the duplicate button is
clicked as did the original code.

I'm wondering if this problem would be fixed if there was
a way to be on a blank record then click duplicate then be
prompted to enter a project number to copy from - though
I'm not sure how to do that either.

Any suggestions you have are appreciated!

Samantha
-----Original Message-----
Hi Samantha

The problem is the dot between Recordset and Clone. RecordsetClone (no dot)
is a property of the form. It's also a good idea to check that you have
actually found a matching record before setting the bookmark. You can make
good use of a With statement here:

With Me.RecordsetClone
.FindFirst "[Project Number] = '" _
& Me![Combo220] & "'"
If .NoMatch then
MsgBox "Record not found"
Else
Me.Bookmark = .Bookmark
End If
End With

However, I'm slightly confused - you say you have a problem with a
"duplicate" button which copies records, but this code selects the current
record from the list on a combo box.

Post a reply back if you still have any problems.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

The purpose of my database is to store information on our
projects. I have a form that is used to enter new projects
and their related information. It also has a combo box on
it that displays a list of existing projects. The user can
choose from this list and the info from that selected
project then displays on the form allowing the user to
make changes.
There is often duplicate information among the projects,
such as the client name, address, etc. I have added
a "duplicate" button to the form so we can copy projects
and change the necessary info but it isn't working
correctly. I chose "debug" when I was given the error and
found that it is due to the code that is in the
AfterUpdate event of the combo box. The code is as follows:

Private Sub Combo220_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Project Number] = '" & Me![Combo220]
& "'"
Me.Bookmark = rs.Bookmark
End Sub


It is the last line (Me.Bookmark = rs.Bookmar) that is
causing the problem. When I deleted this line I was able
to run the copy command, however, the combo button no
longer works right - it doesn't fill the form in according
to the selected project from the combo box.

I need both the combo box and the copy button to work
correctly. Any help would be appreciated.

Thanks, Samantha
Click to expand...


.
Click to expand...
 
Hi Samantha

It seems that the problem must be in the Click event code for your Duplicate
button. Would you like to post that code?

What it should do is:
1) generate or prompt for a new ProjectNumber
2) add a new record to the form's RecordsetClone (.AddNew)
3) fill in all the fields you want to duplicate from the current record on
the form
4) fill in the new ProjectNumber and anything else that should be different
from the source record
5) save the new record (.Update)
6) if required, duplicate records in any related table(s) - for example,
ProjectTasks
7) requery the combo box to include the new record
8) set the combo box value to the new ProjectNumber and call its AfterUpdate
procedure to simulate selecting the new project

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Samantha said:
Graham,

Thanks for your assistance. I'm still having problems with
the form. First off, let me try to clarify my intent. The
form is used to enter new projects as well as to edit/view
existing projects (that is the purpose of the combo box
which has the code to select a specific record from a drop
down list). Because we have many projects that have the
same info in them, I have added the "duplicate" button so
a user may select a project from the combo box then
duplicate it and make the necessary changes to it.

I have entered the code as you suggested. This works for
the purpose of the combo box but still returns an error in
regards to the Bookmark line when the duplicate button is
clicked as did the original code.

I'm wondering if this problem would be fixed if there was
a way to be on a blank record then click duplicate then be
prompted to enter a project number to copy from - though
I'm not sure how to do that either.

Any suggestions you have are appreciated!

Samantha
-----Original Message-----
Hi Samantha

The problem is the dot between Recordset and Clone. RecordsetClone (no dot)
is a property of the form. It's also a good idea to check that you have
actually found a matching record before setting the bookmark. You can make
good use of a With statement here:

With Me.RecordsetClone
.FindFirst "[Project Number] = '" _
& Me![Combo220] & "'"
If .NoMatch then
MsgBox "Record not found"
Else
Me.Bookmark = .Bookmark
End If
End With

However, I'm slightly confused - you say you have a problem with a
"duplicate" button which copies records, but this code selects the current
record from the list on a combo box.

Post a reply back if you still have any problems.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

The purpose of my database is to store information on our
projects. I have a form that is used to enter new projects
and their related information. It also has a combo box on
it that displays a list of existing projects. The user can
choose from this list and the info from that selected
project then displays on the form allowing the user to
make changes.
There is often duplicate information among the projects,
such as the client name, address, etc. I have added
a "duplicate" button to the form so we can copy projects
and change the necessary info but it isn't working
correctly. I chose "debug" when I was given the error and
found that it is due to the code that is in the
AfterUpdate event of the combo box. The code is as follows:

Private Sub Combo220_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Project Number] = '" & Me![Combo220]
& "'"
Me.Bookmark = rs.Bookmark
End Sub


It is the last line (Me.Bookmark = rs.Bookmar) that is
causing the problem. When I deleted this line I was able
to run the copy command, however, the combo button no
longer works right - it doesn't fill the form in according
to the selected project from the combo box.

I need both the combo box and the copy button to work
correctly. Any help would be appreciated.

Thanks, Samantha
Click to expand...


.
Click to expand...
Click to expand...
 
Graham,
Following is the code for the On Click event of the
duplicate button. The code generated automatically when I
created the command button using the toolbox. Except I did
add the last line that enters a project # of 00000.00
which the user would have to change. I don't know much
about writing code. Many of the things I've learned has
been by searching this newsgroup for similar problems.

I went in a deleted the Me.Bookmark = .Bookmark line in
the combo box code - that allowed the copy button to work
as desired but the combo box no longer worked, I added it
back in.

Basically the duplicate button should copy everything
(from the ProjectInfo table only) except the project #
which the user will enter as well as make changes to the
copied fields.

Any suggestions?
Thanks, Samantha

Private Sub Copy_Click()
On Error GoTo Err_Copy_Click

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

Me.Project_Number = "00000.00"

Exit_Copy_Click:
Exit Sub

Err_Copy_Click:
MsgBox Err.Description
Resume Exit_Copy_Click

End Sub
-----Original Message-----
Hi Samantha

It seems that the problem must be in the Click event code for your Duplicate
button. Would you like to post that code?

What it should do is:
1) generate or prompt for a new ProjectNumber
2) add a new record to the form's RecordsetClone (.AddNew)
3) fill in all the fields you want to duplicate from the current record on
the form
4) fill in the new ProjectNumber and anything else that should be different
from the source record
5) save the new record (.Update)
6) if required, duplicate records in any related table (s) - for example,
ProjectTasks
7) requery the combo box to include the new record
8) set the combo box value to the new ProjectNumber and call its AfterUpdate
procedure to simulate selecting the new project

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

Thanks for your assistance. I'm still having problems with
the form. First off, let me try to clarify my intent. The
form is used to enter new projects as well as to edit/view
existing projects (that is the purpose of the combo box
which has the code to select a specific record from a drop
down list). Because we have many projects that have the
same info in them, I have added the "duplicate" button so
a user may select a project from the combo box then
duplicate it and make the necessary changes to it.

I have entered the code as you suggested. This works for
the purpose of the combo box but still returns an error in
regards to the Bookmark line when the duplicate button is
clicked as did the original code.

I'm wondering if this problem would be fixed if there was
a way to be on a blank record then click duplicate then be
prompted to enter a project number to copy from - though
I'm not sure how to do that either.

Any suggestions you have are appreciated!

Samantha
-----Original Message-----
Hi Samantha

The problem is the dot between Recordset and Clone. RecordsetClone (no dot)
is a property of the form. It's also a good idea to check that you have
actually found a matching record before setting the bookmark. You can make
good use of a With statement here:

With Me.RecordsetClone
.FindFirst "[Project Number] = '" _
& Me![Combo220] & "'"
If .NoMatch then
MsgBox "Record not found"
Else
Me.Bookmark = .Bookmark
End If
End With

However, I'm slightly confused - you say you have a problem with a
"duplicate" button which copies records, but this code selects the current
record from the list on a combo box.

Post a reply back if you still have any problems.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Samantha" <[email protected]> wrote
Click to expand...
in
message
The purpose of my database is to store information on our
projects. I have a form that is used to enter new projects
and their related information. It also has a combo
Click to expand...
box
on
it that displays a list of existing projects. The
Click to expand...
user
can
choose from this list and the info from that selected
project then displays on the form allowing the user to
make changes.
There is often duplicate information among the projects,
such as the client name, address, etc. I have added
a "duplicate" button to the form so we can copy projects
and change the necessary info but it isn't working
correctly. I chose "debug" when I was given the error and
found that it is due to the code that is in the
AfterUpdate event of the combo box. The code is as follows:

Private Sub Combo220_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Project Number] = '" & Me! [Combo220]
& "'"
Me.Bookmark = rs.Bookmark
End Sub


It is the last line (Me.Bookmark = rs.Bookmar) that is
causing the problem. When I deleted this line I was able
to run the copy command, however, the combo button no
longer works right - it doesn't fill the form in according
to the selected project from the combo box.

I need both the combo box and the copy button to work
correctly. Any help would be appreciated.

Thanks, Samantha


.
Click to expand...
Click to expand...


.
Click to expand...
 
Hi Samantha

You cannot delete the Me.Bookmark = .Bookmark line, because then your combo
box will do absolutely nothing.

I see you are doing a straight copy and paste, although you may not be able
to see that because the wizard code is so archaic. You may not realize, but
the "acMenuVer70" in the wizard code actually means "use Access 95 menus" -
that's how out of date the wizards are!

I suggest instead of:

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

you should use:

RunCommand acCmdSelectRecord
RunCommand acCmdCopy
RunCommand acCmdPasteAppend

At least then the code will be up-to-date and easier to understand.

Now, the curious question is: why does the paste operation fire the
AfterUpdate event of your unbound combobox?

I'm afraid I don't have an answer to that, but I can suggest a workaround.
In the declarations section, right at the top of your form module, declare a
boolean variable:
Dim fCopying As Boolean

Now, modify your Copy_Click code as follows:

On Error GoTo Err_Copy_Click
fCopying = True

RunCommand acCmdSelectRecord
RunCommand acCmdCopy
RunCommand acCmdPasteAppend

Me.Project_Number = "00000.00"
Exit_Copy_Click:
fCopying = False
Exit Sub
Err_Copy_Click:
MsgBox Err.Description
Resume Exit_Copy_Click

Finally, add the following line to the start of your combobox's AfterUpdate
procedure:
If fCopying Then Exit Sub

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Samantha said:
Graham,
Following is the code for the On Click event of the
duplicate button. The code generated automatically when I
created the command button using the toolbox. Except I did
add the last line that enters a project # of 00000.00
which the user would have to change. I don't know much
about writing code. Many of the things I've learned has
been by searching this newsgroup for similar problems.

I went in a deleted the Me.Bookmark = .Bookmark line in
the combo box code - that allowed the copy button to work
as desired but the combo box no longer worked, I added it
back in.

Basically the duplicate button should copy everything
(from the ProjectInfo table only) except the project #
which the user will enter as well as make changes to the
copied fields.

Any suggestions?
Thanks, Samantha

Private Sub Copy_Click()
On Error GoTo Err_Copy_Click

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

Me.Project_Number = "00000.00"

Exit_Copy_Click:
Exit Sub

Err_Copy_Click:
MsgBox Err.Description
Resume Exit_Copy_Click

End Sub
-----Original Message-----
Hi Samantha

It seems that the problem must be in the Click event code for your Duplicate
button. Would you like to post that code?

What it should do is:
1) generate or prompt for a new ProjectNumber
2) add a new record to the form's RecordsetClone (.AddNew)
3) fill in all the fields you want to duplicate from the current record on
the form
4) fill in the new ProjectNumber and anything else that should be different
from the source record
5) save the new record (.Update)
6) if required, duplicate records in any related table (s) - for example,
ProjectTasks
7) requery the combo box to include the new record
8) set the combo box value to the new ProjectNumber and call its AfterUpdate
procedure to simulate selecting the new project

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

Thanks for your assistance. I'm still having problems with
the form. First off, let me try to clarify my intent. The
form is used to enter new projects as well as to edit/view
existing projects (that is the purpose of the combo box
which has the code to select a specific record from a drop
down list). Because we have many projects that have the
same info in them, I have added the "duplicate" button so
a user may select a project from the combo box then
duplicate it and make the necessary changes to it.

I have entered the code as you suggested. This works for
the purpose of the combo box but still returns an error in
regards to the Bookmark line when the duplicate button is
clicked as did the original code.

I'm wondering if this problem would be fixed if there was
a way to be on a blank record then click duplicate then be
prompted to enter a project number to copy from - though
I'm not sure how to do that either.

Any suggestions you have are appreciated!

Samantha

-----Original Message-----
Hi Samantha

The problem is the dot between Recordset and Clone.
RecordsetClone (no dot)
is a property of the form. It's also a good idea to
check that you have
actually found a matching record before setting the
bookmark. You can make
good use of a With statement here:

With Me.RecordsetClone
.FindFirst "[Project Number] = '" _
& Me![Combo220] & "'"
If .NoMatch then
MsgBox "Record not found"
Else
Me.Bookmark = .Bookmark
End If
End With

However, I'm slightly confused - you say you have a
problem with a
"duplicate" button which copies records, but this code
selects the current
record from the list on a combo box.

Post a reply back if you still have any problems.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

message
The purpose of my database is to store information on
our
projects. I have a form that is used to enter new
projects
and their related information. It also has a combo box
on
it that displays a list of existing projects. The user
can
choose from this list and the info from that selected
project then displays on the form allowing the user to
make changes.
There is often duplicate information among the projects,
such as the client name, address, etc. I have added
a "duplicate" button to the form so we can copy projects
and change the necessary info but it isn't working
correctly. I chose "debug" when I was given the error
and
found that it is due to the code that is in the
AfterUpdate event of the combo box. The code is as
follows:

Private Sub Combo220_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Project Number] = '" & Me! [Combo220]
& "'"
Me.Bookmark = rs.Bookmark
End Sub


It is the last line (Me.Bookmark = rs.Bookmar) that is
causing the problem. When I deleted this line I was able
to run the copy command, however, the combo button no
longer works right - it doesn't fill the form in
according
to the selected project from the combo box.

I need both the combo box and the copy button to work
correctly. Any help would be appreciated.

Thanks, Samantha


.
Click to expand...


.
Click to expand...
Click to expand...
 
Graham,

Thank you so very much for your help! Your solution in
your last post worked perfectly and this database is
finally done!! I truly appreciate your time and effort.

Samantha
 
Back
Top