Duplicate a record in a form that has a VBA code attached to it

  • Thread starter elliottpt via AccessMonster.com
  • Start date
E

elliottpt via AccessMonster.com

Hi

I'm struglling to figure this one out and some help would be much appreciated.


I have a data capturing form that has some VBA code attached to it that
creates a unique key based on information entered. I need to have the option
of duplicating a specific record which can be edited after duplication. I
currently have a control button(setup using the wizard) for the duplication.
When it is clicked upon the following error is displayed:

Run-time error '3020'

Update or CancelUpdate without Addnew or Edit


Below is my code with the problem seeming to be at the section with the stars
(***):

Private Sub TotalPallets_AfterUpdate()
'Create variables to hold VesselNo,Supplier code,POD and POL
' and Key
Dim vVesselNo As String
Dim sSuppCode As String
Dim pPOD As String
Dim pPOL As String
Dim kKey As String
Dim cComm As String
Dim pPlt As Integer

'Assign the text in the Supplier code text box to
' the sSuppCode variable.
Forms!Vesselcapturing!TotalPallets.SetFocus
pPlt = Forms!Vesselcapturing!TotalPallets.Text

'You must set the focus to a text box before
' you can read its contents.
Forms!Vesselcapturing!VesselNo.SetFocus
vVesselNo = Forms!Vesselcapturing!VesselNo.Text

Forms!Vesselcapturing!POL.SetFocus
pPOL = Forms!Vesselcapturing!POL.Text

Forms!Vesselcapturing!POD.SetFocus
pPOD = Forms!Vesselcapturing!POD.Text

Forms!Vesselcapturing!Comm.SetFocus
cComm = Forms!Vesselcapturing!Comm.Text

Forms!Vesselcapturing!Clientcodes.SetFocus
sSuppCode = Forms!Vesselcapturing!Clientcodes.Text

'Combine portions of the last and first names
' to create the Key.
kKey = vVesselNo & Left(sSuppCode, 5) & Left(cComm, 2) & pPlt & Left(pPOL,
3) & Left(pPOD, 3)

'Don't store the Key unless it is less than 22 characters long.
' (This would indicate all fields not filled it.)
If Len(kKey) <= 22 Then
Forms!Vesselcapturing!KEY.SetFocus
*** Forms!Vesselcapturing!KEY = kKey
End If

'Set the focus where it would have gone naturally.
Forms!Vesselcapturing!SailDate.SetFocus
End Sub

Help would be much appreciated!!!!!!
Regards
Philip
 
M

Marshall Barton

elliottpt said:
I have a data capturing form that has some VBA code attached to it that
creates a unique key based on information entered. I need to have the option
of duplicating a specific record which can be edited after duplication. I
currently have a control button(setup using the wizard) for the duplication.
When it is clicked upon the following error is displayed:

Run-time error '3020'
Update or CancelUpdate without Addnew or Edit

Below is my code with the problem seeming to be at the section with the stars
(***):

Private Sub TotalPallets_AfterUpdate()
'Create variables to hold VesselNo,Supplier code,POD and POL
' and Key
Dim vVesselNo As String
Dim sSuppCode As String
Dim pPOD As String
Dim pPOL As String
Dim kKey As String
Dim cComm As String
Dim pPlt As Integer

'Assign the text in the Supplier code text box to
' the sSuppCode variable.
Forms!Vesselcapturing!TotalPallets.SetFocus
pPlt = Forms!Vesselcapturing!TotalPallets.Text

'You must set the focus to a text box before
' you can read its contents.
Forms!Vesselcapturing!VesselNo.SetFocus
vVesselNo = Forms!Vesselcapturing!VesselNo.Text

Forms!Vesselcapturing!POL.SetFocus
pPOL = Forms!Vesselcapturing!POL.Text

Forms!Vesselcapturing!POD.SetFocus
pPOD = Forms!Vesselcapturing!POD.Text

Forms!Vesselcapturing!Comm.SetFocus
cComm = Forms!Vesselcapturing!Comm.Text

Forms!Vesselcapturing!Clientcodes.SetFocus
sSuppCode = Forms!Vesselcapturing!Clientcodes.Text

'Combine portions of the last and first names
' to create the Key.
kKey = vVesselNo & Left(sSuppCode, 5) & Left(cComm, 2) & pPlt & Left(pPOL,
3) & Left(pPOD, 3)

'Don't store the Key unless it is less than 22 characters long.
' (This would indicate all fields not filled it.)
If Len(kKey) <= 22 Then
Forms!Vesselcapturing!KEY.SetFocus
*** Forms!Vesselcapturing!KEY = kKey
End If

'Set the focus where it would have gone naturally.
Forms!Vesselcapturing!SailDate.SetFocus
End Sub
Click to expand...


I'm afraid that I do not understand how that code is
supposed to duplicate a record and trying to set the
(primary?) Key field may not be permitted in some
situations. It appears that your code is reassigning the
Key field to a dfferent value and even if it didn't cause an
error, may mess up the current record.

Note 1: The Access object model is different than VB's
object model. The most important difference to your
question is that Access only uses the Text property for
fairly rare and unusual situations. Instead, you should use
the Value property, which does not need the focus. Also,
since Value is the default property, you do not need to
specify it.

Note 2: If the referenced control and/or field is in the
same form as the code, you should use the Me object instead
of the full Forms!... reference

Note 3: A bound form has a recordset that you can
manipulate as any other recordset. There is also the form's
RecordsetClone that can be very useful in many situations.

Finally, The wizard generated code to duplicate a record has
several flaws and should not be used in many/most
situations. You can duplicate a record by using code like
this untested air code:

If Me.Dirty Then Me.Dirty = False 'save any changes

With Me.RecordsetClone
.FindFirst "Key = """ & Me.Key & """"
If Not .NoMatch Then
MsgBox "Key " & Me.Key & " does not exist."
Beep
Exit Sub
End If

.AddNew
!SuppCode = Me.SuppCode
!TotalPallets = Me.TotalPallets
!VesselNo = Me.VesselNo
!POL = Me.POL
!POD = Me.POD
!Comm = Me.Comm
!Clientcodes = Me.Clientcodes

kKey = vVesselNo & Left(Me.SuppCode, 5) _
& Left(Me.Comm, 2) _
& pPlt & Left(Me.POL,3) _
& Left(Me.POD, 3)
'Don't store the Key unless it is less than
'22 characters long.
' (This would indicate all fields not filled it.)
If Len(kKey) <= 22 Then
!KEY = kKey ' this is questionable?? <<<<<<<<
End If
.Update

Me.BookMark = .LastModified 'move to new record
 
E

elliottpt via AccessMonster.com

Hi Marshall

Thanks.

The code I used is to create a key; it works well. The problem arises when I
attempt to duplicate the record(using a control button from the wizard) as
access seems unable to copy/duplicate the VB code to the new record. It
should work as on a normal record, ie it should lie dormant until triggered
by the afterupdate on total pallets.

Would I attach your "duplication" code to a new control button a) and b)
would it be set to on click event?

what would the following two scenarios look like?(sorry im still a novice wtr
VB)
Note 2: If the referenced control and/or field is in the
same form as the code, you should use the Me object instead
of the full Forms!... reference

Note 3: A bound form has a recordset that you can
manipulate as any other recordset. There is also the form's
RecordsetClone that can be very useful in many situations.
Click to expand...

thanks a stack.
Philip

Marshall said:
I have a data capturing form that has some VBA code attached to it that
creates a unique key based on information entered. I need to have the option
Click to expand...
[quoted text clipped - 56 lines]
Forms!Vesselcapturing!SailDate.SetFocus
End Sub
Click to expand...

I'm afraid that I do not understand how that code is
supposed to duplicate a record and trying to set the
(primary?) Key field may not be permitted in some
situations. It appears that your code is reassigning the
Key field to a dfferent value and even if it didn't cause an
error, may mess up the current record.

Note 1: The Access object model is different than VB's
object model. The most important difference to your
question is that Access only uses the Text property for
fairly rare and unusual situations. Instead, you should use
the Value property, which does not need the focus. Also,
since Value is the default property, you do not need to
specify it.

Note 2: If the referenced control and/or field is in the
same form as the code, you should use the Me object instead
of the full Forms!... reference

Note 3: A bound form has a recordset that you can
manipulate as any other recordset. There is also the form's
RecordsetClone that can be very useful in many situations.

Finally, The wizard generated code to duplicate a record has
several flaws and should not be used in many/most
situations. You can duplicate a record by using code like
this untested air code:

If Me.Dirty Then Me.Dirty = False 'save any changes

With Me.RecordsetClone
.FindFirst "Key = """ & Me.Key & """"
If Not .NoMatch Then
MsgBox "Key " & Me.Key & " does not exist."
Beep
Exit Sub
End If

.AddNew
!SuppCode = Me.SuppCode
!TotalPallets = Me.TotalPallets
!VesselNo = Me.VesselNo
!POL = Me.POL
!POD = Me.POD
!Comm = Me.Comm
!Clientcodes = Me.Clientcodes

kKey = vVesselNo & Left(Me.SuppCode, 5) _
& Left(Me.Comm, 2) _
& pPlt & Left(Me.POL,3) _
& Left(Me.POD, 3)
'Don't store the Key unless it is less than
'22 characters long.
' (This would indicate all fields not filled it.)
If Len(kKey) <= 22 Then
!KEY = kKey ' this is questionable?? <<<<<<<<
End If
.Update

Me.BookMark = .LastModified 'move to new record
Click to expand...
 
M

Marshall Barton

I do not follow your scenario. If your code is only setting
the Key field for a new (duplicated?) record, how did you
get to the new record and when does the user enter the value
for TotalPallets on this new record?

What value is in the Key field (and how did it get there)
before you calculate a new value for Key? What are the
characteristics of the Key field (indexed?, unique?,
primary?)
--
Marsh
MVP [MS Access]

The code I used is to create a key; it works well. The problem arises when I
attempt to duplicate the record(using a control button from the wizard) as
access seems unable to copy/duplicate the VB code to the new record. It
should work as on a normal record, ie it should lie dormant until triggered
by the afterupdate on total pallets.

Would I attach your "duplication" code to a new control button a) and b)
would it be set to on click event?

what would the following two scenarios look like?(sorry im still a novice wtr
VB)
Note 2: If the referenced control and/or field is in the
same form as the code, you should use the Me object instead
of the full Forms!... reference

Note 3: A bound form has a recordset that you can
manipulate as any other recordset. There is also the form's
RecordsetClone that can be very useful in many situations.

Marshall said:
I have a data capturing form that has some VBA code attached to it that
creates a unique key based on information entered. I need to have the option
Click to expand...
[quoted text clipped - 56 lines]
Forms!Vesselcapturing!SailDate.SetFocus
End Sub
Click to expand...

I'm afraid that I do not understand how that code is
supposed to duplicate a record and trying to set the
(primary?) Key field may not be permitted in some
situations. It appears that your code is reassigning the
Key field to a dfferent value and even if it didn't cause an
error, may mess up the current record.

Note 1: The Access object model is different than VB's
object model. The most important difference to your
question is that Access only uses the Text property for
fairly rare and unusual situations. Instead, you should use
the Value property, which does not need the focus. Also,
since Value is the default property, you do not need to
specify it.

Note 2: If the referenced control and/or field is in the
same form as the code, you should use the Me object instead
of the full Forms!... reference

Note 3: A bound form has a recordset that you can
manipulate as any other recordset. There is also the form's
RecordsetClone that can be very useful in many situations.

Finally, The wizard generated code to duplicate a record has
several flaws and should not be used in many/most
situations. You can duplicate a record by using code like
this untested air code:

If Me.Dirty Then Me.Dirty = False 'save any changes

With Me.RecordsetClone
.FindFirst "Key = """ & Me.Key & """"
If Not .NoMatch Then
MsgBox "Key " & Me.Key & " does not exist."
Beep
Exit Sub
End If

.AddNew
!SuppCode = Me.SuppCode
!TotalPallets = Me.TotalPallets
!VesselNo = Me.VesselNo
!POL = Me.POL
!POD = Me.POD
!Comm = Me.Comm
!Clientcodes = Me.Clientcodes

kKey = vVesselNo & Left(Me.SuppCode, 5) _
& Left(Me.Comm, 2) _
& pPlt & Left(Me.POL,3) _
& Left(Me.POD, 3)
'Don't store the Key unless it is less than
'22 characters long.
' (This would indicate all fields not filled it.)
If Len(kKey) <= 22 Then
!KEY = kKey ' this is questionable?? <<<<<<<<
End If
.Update

Me.BookMark = .LastModified 'move to new record
Click to expand...
Click to expand...
 
E

elliottpt via AccessMonster.com

Scenario as follows:

I imported a recordset to a table which has been set as a default for the
form.

The key field is blank from outset, it is a text field.It is just another
field. The end user inputs data on the form; some of which is the default as
described above while other info will be captured by means of drop down
boxes/input.(Total pallets default 0)

Total pallets is the one of the last values to be input and also the trigger
for the the creation of the key(afterupdate). The code concatenates 4 fields
and stores it in the key field. When I move to a new record and the
information is saved to the table and then the above is repeated for a new
record; a new key is created for the specific record.

This all works fine.

In some cases the default recordset's information is not sufficient since
information on a single record needs to me duplicated sothat it can be
ammended.Therby having 2 records with many similar values for the fields but
not identical as they will be ammended.

As I need the option of duplicating a specific records default information
(the whole line), I used the command button wizard to create a the
duplication action in the form; ie the new record

When this is button is clicked upon the following error appears:

Run-time error '3020'

Update or CancelUpdate without Addnew or Edit

The duplicated value is now stored in the newly created paste errors table.

With the error being with my VBA code. I want and need the VB code(not the
key value but the "formula") to be duplicated too sothat it will trigger when
data is captured in the total pallets field on the newly duplicated record.

Hope this helps to clarify your questions, I really do appreiate your help

Marshall said:
I do not follow your scenario. If your code is only setting
the Key field for a new (duplicated?) record, how did you
get to the new record and when does the user enter the value
for TotalPallets on this new record?

What value is in the Key field (and how did it get there)
before you calculate a new value for Key? What are the
characteristics of the Key field (indexed?, unique?,
primary?)
The code I used is to create a key; it works well. The problem arises when I
attempt to duplicate the record(using a control button from the wizard) as
Click to expand...
[quoted text clipped - 81 lines]
Click to expand...
 
M

Marshall Barton

elliottpt said:
Scenario as follows:

I imported a recordset to a table which has been set as a default for the
form.

The key field is blank from outset, it is a text field.It is just another
field. The end user inputs data on the form; some of which is the default as
described above while other info will be captured by means of drop down
boxes/input.(Total pallets default 0)

Total pallets is the one of the last values to be input and also the trigger
for the the creation of the key(afterupdate). The code concatenates 4 fields
and stores it in the key field. When I move to a new record and the
information is saved to the table and then the above is repeated for a new
record; a new key is created for the specific record.

This all works fine.

In some cases the default recordset's information is not sufficient since
information on a single record needs to me duplicated sothat it can be
ammended.Therby having 2 records with many similar values for the fields but
not identical as they will be ammended.

As I need the option of duplicating a specific records default information
(the whole line), I used the command button wizard to create a the
duplication action in the form; ie the new record

When this is button is clicked upon the following error appears:

Run-time error '3020'

Update or CancelUpdate without Addnew or Edit

The duplicated value is now stored in the newly created paste errors table.

With the error being with my VBA code. I want and need the VB code(not the
key value but the "formula") to be duplicated too sothat it will trigger when
data is captured in the total pallets field on the newly duplicated record.

Hope this helps to clarify your questions, I really do appreiate your help

Marshall said:
I do not follow your scenario. If your code is only setting
the Key field for a new (duplicated?) record, how did you
get to the new record and when does the user enter the value
for TotalPallets on this new record?

What value is in the Key field (and how did it get there)
before you calculate a new value for Key? What are the
characteristics of the Key field (indexed?, unique?,
primary?)
The code I used is to create a key; it works well. The problem arises when I
attempt to duplicate the record(using a control button from the wizard) as
Click to expand...
[quoted text clipped - 81 lines]
Click to expand...
Click to expand...


Sorry, but I still don't understand. If the key generating
code works, then there must be more going on than I can
determine. Aside from all the SetFocus and .Text stuff, it
looks ok to me and I have no idea how that code by itself
can cause that error.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA to see if a Section exists in Word Document, and if it does, delete it 2
record COUNT() query not working in VBA code 3
Update a date field in sub-form from dates fields on main form 1
Inserting information to a table on form close 1
Duplicate record in Form and Subform 0
Macro VBA coding, Copying content from one workbook to a new one 0
Set dynamic reference to a control in a public function 1
Problems with SQL in vba code 2

Top