Save As for Multiple Record Save

L

Lordlentz

The code below has been adapted from an example by Allen Browne to duplicate
records in a form and related records in a subform. It works great. However,
instead of "!PlanName = Me.PlanName"; I would like to have a popup form come
up and allow the user to input the name they would like to save the record
as. Any Help would be appreciated.

Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!PlanName = Me.PlanName
!NeighborhoodID = NeighborhoodID
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !PlanID

'Duplicate the related records: append query.
If Me.ItemList.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblItemList] ( PlanID, ItemID,
Quantity) " & _
"SELECT " & lngID & " As NewID, ItemID, Quantity " & _
"FROM [tblItemList] WHERE PlanID = " & Me.PlanID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler
End Sub
 
S

SteveS

This is untested (but it should work).

Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.
Dim Message As String, Title As String '<<
Dim strNewName As String '<<

' Set prompt for input box
Message = "Enter new plan name or accept the current name " '<<
' Set title for input box
Title = "Get new Record Name" '<<

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'get new plan name - defaults to current plan name
strNewName = InputBox(Message, Title, Me.PlanName) '<<

'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!PlanName = strNewName '<<
!NeighborhoodID = NeighborhoodID
'etc for other fields.
.Update

'===== SNIP =============


Lines I added/modified indicated by '<<

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Lordlentz said:
The code below has been adapted from an example by Allen Browne to duplicate
records in a form and related records in a subform. It works great. However,
instead of "!PlanName = Me.PlanName"; I would like to have a popup form come
up and allow the user to input the name they would like to save the record
as. Any Help would be appreciated.

Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!PlanName = Me.PlanName
!NeighborhoodID = NeighborhoodID
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !PlanID

'Duplicate the related records: append query.
If Me.ItemList.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblItemList] ( PlanID, ItemID,
Quantity) " & _
"SELECT " & lngID & " As NewID, ItemID, Quantity " & _
"FROM [tblItemList] WHERE PlanID = " & Me.PlanID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler
End Sub
Click to expand...
 
L

Lordlentz

Thank you SteveS. Works great. However, when cancel is hit it still copies
the record, only without a plan name. How would I get it to abort the copy
if cancel is hit? I apologize for my lack of vb knowledge.

SteveS said:
This is untested (but it should work).

Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.
Dim Message As String, Title As String '<<
Dim strNewName As String '<<

' Set prompt for input box
Message = "Enter new plan name or accept the current name " '<<
' Set title for input box
Title = "Get new Record Name" '<<

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'get new plan name - defaults to current plan name
strNewName = InputBox(Message, Title, Me.PlanName) '<<

'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!PlanName = strNewName '<<
!NeighborhoodID = NeighborhoodID
'etc for other fields.
.Update

'===== SNIP =============


Lines I added/modified indicated by '<<

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Lordlentz said:
The code below has been adapted from an example by Allen Browne to duplicate
records in a form and related records in a subform. It works great. However,
instead of "!PlanName = Me.PlanName"; I would like to have a popup form come
up and allow the user to input the name they would like to save the record
as. Any Help would be appreciated.

Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!PlanName = Me.PlanName
!NeighborhoodID = NeighborhoodID
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !PlanID

'Duplicate the related records: append query.
If Me.ItemList.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblItemList] ( PlanID, ItemID,
Quantity) " & _
"SELECT " & lngID & " As NewID, ItemID, Quantity " & _
"FROM [tblItemList] WHERE PlanID = " & Me.PlanID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler
End Sub
Click to expand...
Click to expand...
 
S

SteveS

Try adding the lines between the '============

(air code!!!)

'----snip-----

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'get new plan name - defaults to current plan name
strNewName = InputBox(Message, Title, Me.PlanName)

'
'=====================

' exit sub if Cancel button selected
If Len(strNewName) = 0 Then
Exit Sub
End If

'=====================

'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
'----snip-----

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Lordlentz said:
Thank you SteveS. Works great. However, when cancel is hit it still copies
the record, only without a plan name. How would I get it to abort the copy
if cancel is hit? I apologize for my lack of vb knowledge.

SteveS said:
This is untested (but it should work).

Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.
Dim Message As String, Title As String '<<
Dim strNewName As String '<<

' Set prompt for input box
Message = "Enter new plan name or accept the current name " '<<
' Set title for input box
Title = "Get new Record Name" '<<

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'get new plan name - defaults to current plan name
strNewName = InputBox(Message, Title, Me.PlanName) '<<

'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!PlanName = strNewName '<<
!NeighborhoodID = NeighborhoodID
'etc for other fields.
.Update

'===== SNIP =============


Lines I added/modified indicated by '<<

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Lordlentz said:
The code below has been adapted from an example by Allen Browne to duplicate
records in a form and related records in a subform. It works great. However,
instead of "!PlanName = Me.PlanName"; I would like to have a popup form come
up and allow the user to input the name they would like to save the record
as. Any Help would be appreciated.

Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!PlanName = Me.PlanName
!NeighborhoodID = NeighborhoodID
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !PlanID

'Duplicate the related records: append query.
If Me.ItemList.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblItemList] ( PlanID, ItemID,
Quantity) " & _
"SELECT " & lngID & " As NewID, ItemID, Quantity " & _
"FROM [tblItemList] WHERE PlanID = " & Me.PlanID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler
End Sub
Click to expand...
Click to expand...
Click to expand...
 
L

Lordlentz

Works perfectly. Thanks a million.

SteveS said:
Try adding the lines between the '============

(air code!!!)

'----snip-----

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'get new plan name - defaults to current plan name
strNewName = InputBox(Message, Title, Me.PlanName)

'
'=====================

' exit sub if Cancel button selected
If Len(strNewName) = 0 Then
Exit Sub
End If

'=====================

'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
'----snip-----

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Lordlentz said:
Thank you SteveS. Works great. However, when cancel is hit it still copies
the record, only without a plan name. How would I get it to abort the copy
if cancel is hit? I apologize for my lack of vb knowledge.

SteveS said:
This is untested (but it should work).

Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.
Dim Message As String, Title As String '<<
Dim strNewName As String '<<

' Set prompt for input box
Message = "Enter new plan name or accept the current name " '<<
' Set title for input box
Title = "Get new Record Name" '<<

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'get new plan name - defaults to current plan name
strNewName = InputBox(Message, Title, Me.PlanName) '<<

'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!PlanName = strNewName '<<
!NeighborhoodID = NeighborhoodID
'etc for other fields.
.Update

'===== SNIP =============


Lines I added/modified indicated by '<<

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

The code below has been adapted from an example by Allen Browne to duplicate
records in a form and related records in a subform. It works great. However,
instead of "!PlanName = Me.PlanName"; I would like to have a popup form come
up and allow the user to input the name they would like to save the record
as. Any Help would be appreciated.

Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!PlanName = Me.PlanName
!NeighborhoodID = NeighborhoodID
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !PlanID

'Duplicate the related records: append query.
If Me.ItemList.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblItemList] ( PlanID, ItemID,
Quantity) " & _
"SELECT " & lngID & " As NewID, ItemID, Quantity " & _
"FROM [tblItemList] WHERE PlanID = " & Me.PlanID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler
End Sub
Click to expand...
Click to expand...
Click to expand...
 
S

SteveS

:)

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Lordlentz said:
Works perfectly. Thanks a million.

SteveS said:
Try adding the lines between the '============

(air code!!!)

'----snip-----

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'get new plan name - defaults to current plan name
strNewName = InputBox(Message, Title, Me.PlanName)

'
'=====================

' exit sub if Cancel button selected
If Len(strNewName) = 0 Then
Exit Sub
End If

'=====================

'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
'----snip-----

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Lordlentz said:
Thank you SteveS. Works great. However, when cancel is hit it still copies
the record, only without a plan name. How would I get it to abort the copy
if cancel is hit? I apologize for my lack of vb knowledge.

:

This is untested (but it should work).

Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.
Dim Message As String, Title As String '<<
Dim strNewName As String '<<

' Set prompt for input box
Message = "Enter new plan name or accept the current name " '<<
' Set title for input box
Title = "Get new Record Name" '<<

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'get new plan name - defaults to current plan name
strNewName = InputBox(Message, Title, Me.PlanName) '<<

'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!PlanName = strNewName '<<
!NeighborhoodID = NeighborhoodID
'etc for other fields.
.Update

'===== SNIP =============


Lines I added/modified indicated by '<<

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

The code below has been adapted from an example by Allen Browne to duplicate
records in a form and related records in a subform. It works great. However,
instead of "!PlanName = Me.PlanName"; I would like to have a popup form come
up and allow the user to input the name they would like to save the record
as. Any Help would be appreciated.

Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!PlanName = Me.PlanName
!NeighborhoodID = NeighborhoodID
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !PlanID

'Duplicate the related records: append query.
If Me.ItemList.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblItemList] ( PlanID, ItemID,
Quantity) " & _
"SELECT " & lngID & " As NewID, ItemID, Quantity " & _
"FROM [tblItemList] WHERE PlanID = " & Me.PlanID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler
End Sub
Click to expand...
Click to expand...
Click to expand...
 

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

Duplicate record in Form and Subform 0
Allen Browne's Duplicate Record Code 7
Copy Master and Sub record to an exisiting record with specified P 15
Duplicate Form & Subform for record 0
allenbrowne's code for duplicating records 7
INSERT INTO Not working correctly 1
Allen Browne - Help with duplicating Form & Subform! 10
Duplicate record code almost works 3

Top