Duplicate the record in form and subform

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

Hi Allen Browne,

I try to use your code to duplicate the record in form and subform but I got
a error message "Error#3265". BTW, my primary key value is not autonumber,
how to change the code to fit my database?
Thanks for your expert comments in advance!
 
Presumably we are talking about this article:
http://allenbrowne.com/ser-57.html

The "Item not found" error was caused by which line?
Whichever line it is, you have some name Access can't identify, e.g. you
have not changed the field names to match what's in your form.
 
Hi Allen,

Sorry for the typo error on the field name! Have corrected but another error
message is "error#3058, primary key value can not be null" caused by below
line :
..update
Have mentioned that my primary key value is not a autonumber, please advise
how to change the code to fit my database.
Thanks again!
 
Whatever method you're using to create your PK is going to have to be done
programmatically in your code i.e.

With Me.RecordsetClone
.AddNew
![PK field] = some value
!CustomerID = Me.CustomerID
!EmployeeID = Me.EmployeeID
!OrderDate = Date
'etc for other fields.
.Update
 
That's right, Andy

If the primary key is not an Autonumber, you must assign something to it.

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

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

Beetle said:
Whatever method you're using to create your PK is going to have to be done
programmatically in your code i.e.

With Me.RecordsetClone
.AddNew
![PK field] = some value
!CustomerID = Me.CustomerID
!EmployeeID = Me.EmployeeID
!OrderDate = Date
'etc for other fields.
.Update

--
_________

Sean Bailey


Andy said:
Hi Allen,

Sorry for the typo error on the field name! Have corrected but another
error
message is "error#3058, primary key value can not be null" caused by
below
line :
.update
Have mentioned that my primary key value is not a autonumber, please
advise
how to change the code to fit my database.
Thanks again!
Click to expand...
Click to expand...
 
Thanks, Beetle & Allen,

Have changed the code as below but got an error message "Error#3061,
parameter not much enough, expected is 1" caused by below line :

DBEngine(0)(0).Execute strSql, dbFailOnError

Appreciate your expert commnts! Thanks in advance!

Here is the code :
Private Sub Command36_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 String '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
![QUOTATION NO] = InputBox("Please enter the quotation
number :", "Hello Kitty")
![COMPANY NO] = Me.[COMPANY NO]
![CUSTOMER NO] = Me.[CUSTOMER NO]
![LOCATION NO] = Me.[LOCATION NO]
![QUOT DATE] = DATE
![EFFECTIVE DATE] = Me.[EFFECTIVE DATE]
![PAYMENT TERMS] = Me.[PAYMENT TERMS]
![DELIVERY TERMS] = Me.[DELIVERY TERMS]
![ITEM HEADER] = Me.[ITEM HEADER]
'etc for other fields.
.Update

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

lngID = ![QUOTATION NO]

'Duplicate the related records: append query.
If Me.[QUOTATION TABLE DETAILS
subform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [QUOTATION TABLE DETAILS] ( [QUOTATION
NO], [ITEM NO], [ITEM CONTENT], [QTY], [UNIT], [UNIT PRICE] ) " & _
"SELECT " & lngID & " As NewID, [ITEM NO], [ITEM
CONTENT], [QTY], [UNIT], [UNIT PRICE] " & _
"FROM [QUOTATION TABLE DETAILS] WHERE [QUOTATION NO] = "
& Me.[QUOTATION NO] & ";"
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



Allen Browne said:
That's right, Andy

If the primary key is not an Autonumber, you must assign something to it.

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

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

Beetle said:
Whatever method you're using to create your PK is going to have to be done
programmatically in your code i.e.

With Me.RecordsetClone
.AddNew
![PK field] = some value
!CustomerID = Me.CustomerID
!EmployeeID = Me.EmployeeID
!OrderDate = Date
'etc for other fields.
.Update

--
_________

Sean Bailey


Andy said:
Hi Allen,

Sorry for the typo error on the field name! Have corrected but another
error
message is "error#3058, primary key value can not be null" caused by
below
line :
.update
Have mentioned that my primary key value is not a autonumber, please
advise
how to change the code to fit my database.
Thanks again!

:

Presumably we are talking about this article:
http://allenbrowne.com/ser-57.html

The "Item not found" error was caused by which line?
Whichever line it is, you have some name Access can't identify, e.g.
you
have not changed the field names to match what's in your form.

Hi Allen Browne,

I try to use your code to duplicate the record in form and subform
but I
got
a error message "Error#3265". BTW, my primary key value is not
autonumber,
how to change the code to fit my database?
Thanks for your expert comments in advance!
Click to expand...
Click to expand...
Click to expand...
 
The request for a parameter means you have a name Access doesn't recognise.
So some name in the SQL statement is no correct.
 
Allen,

Have checked the names in the SQL statement are correct, what's the problem?
BTW, any change in the SQL statement if the PK value is string?

Here is my code, please check, thanks sooooooooooo much!
Private Sub Command36_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 String '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
![QUOTATION NO] = InputBox("Please enter the quotation
number :", "Hello Kitty")
![COMPANY NO] = Me.[COMPANY NO]
![CUSTOMER NO] = Me.[CUSTOMER NO]
![LOCATION NO] = Me.[LOCATION NO]
![QUOT DATE] = DATE
![EFFECTIVE DATE] = Me.[EFFECTIVE DATE]
![PAYMENT TERMS] = Me.[PAYMENT TERMS]
![DELIVERY TERMS] = Me.[DELIVERY TERMS]
![ITEM HEADER] = Me.[ITEM HEADER]
'etc for other fields.
.Update

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

lngID = ![QUOTATION NO]

'Duplicate the related records: append query.
If Me.[QUOTATION TABLE DETAILS
subform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [QUOTATION TABLE DETAILS] ( [QUOTATION
NO], [ITEM NO], [ITEM CONTENT], [QTY], [UNIT], [UNIT PRICE] ) " & _
"SELECT " & lngID & " As NewID, [ITEM NO], [ITEM
CONTENT], [QTY], [UNIT], [UNIT PRICE] " & _
"FROM [QUOTATION TABLE DETAILS] WHERE [QUOTATION NO] = "
& Me.[QUOTATION NO] & ";"
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
 
If you foreign key is a string, it must be enclosed in quotes. For an
explanation of how to do that, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

Further up the routine, the primary key value is saved as a Long. That may
not be right either if this is Text.

To help you with the debugging, insert the line:
Debug.Print strSql
just above the Execute. When it fails, you can then open the Immediate
Window (Ctrl+G), copy the statement to clipboard, and paste into SQL View in
a query. You can then sort it out, or compare it to an example query that
does work.

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

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

Andy said:
Allen,

Have checked the names in the SQL statement are correct, what's the
problem?
BTW, any change in the SQL statement if the PK value is string?

Here is my code, please check, thanks sooooooooooo much!
Private Sub Command36_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 String '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
![QUOTATION NO] = InputBox("Please enter the quotation
number :", "Hello Kitty")
![COMPANY NO] = Me.[COMPANY NO]
![CUSTOMER NO] = Me.[CUSTOMER NO]
![LOCATION NO] = Me.[LOCATION NO]
![QUOT DATE] = DATE
![EFFECTIVE DATE] = Me.[EFFECTIVE DATE]
![PAYMENT TERMS] = Me.[PAYMENT TERMS]
![DELIVERY TERMS] = Me.[DELIVERY TERMS]
![ITEM HEADER] = Me.[ITEM HEADER]
'etc for other fields.
.Update

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

lngID = ![QUOTATION NO]

'Duplicate the related records: append query.
If Me.[QUOTATION TABLE DETAILS
subform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [QUOTATION TABLE DETAILS] (
[QUOTATION
NO], [ITEM NO], [ITEM CONTENT], [QTY], [UNIT], [UNIT PRICE] ) " & _
"SELECT " & lngID & " As NewID, [ITEM NO], [ITEM
CONTENT], [QTY], [UNIT], [UNIT PRICE] " & _
"FROM [QUOTATION TABLE DETAILS] WHERE [QUOTATION NO] =
"
& Me.[QUOTATION NO] & ";"
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

Allen Browne said:
The request for a parameter means you have a name Access doesn't
recognise.
So some name in the SQL statement is no correct.
Click to expand...
Click to expand...
 
Dear Allen,

It works great now. Thanks very much for your expert comments!!!

Allen Browne said:
If you foreign key is a string, it must be enclosed in quotes. For an
explanation of how to do that, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

Further up the routine, the primary key value is saved as a Long. That may
not be right either if this is Text.

To help you with the debugging, insert the line:
Debug.Print strSql
just above the Execute. When it fails, you can then open the Immediate
Window (Ctrl+G), copy the statement to clipboard, and paste into SQL View in
a query. You can then sort it out, or compare it to an example query that
does work.

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

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

Andy said:
Allen,

Have checked the names in the SQL statement are correct, what's the
problem?
BTW, any change in the SQL statement if the PK value is string?

Here is my code, please check, thanks sooooooooooo much!
Private Sub Command36_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 String '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
![QUOTATION NO] = InputBox("Please enter the quotation
number :", "Hello Kitty")
![COMPANY NO] = Me.[COMPANY NO]
![CUSTOMER NO] = Me.[CUSTOMER NO]
![LOCATION NO] = Me.[LOCATION NO]
![QUOT DATE] = DATE
![EFFECTIVE DATE] = Me.[EFFECTIVE DATE]
![PAYMENT TERMS] = Me.[PAYMENT TERMS]
![DELIVERY TERMS] = Me.[DELIVERY TERMS]
![ITEM HEADER] = Me.[ITEM HEADER]
'etc for other fields.
.Update

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

lngID = ![QUOTATION NO]

'Duplicate the related records: append query.
If Me.[QUOTATION TABLE DETAILS
subform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [QUOTATION TABLE DETAILS] (
[QUOTATION
NO], [ITEM NO], [ITEM CONTENT], [QTY], [UNIT], [UNIT PRICE] ) " & _
"SELECT " & lngID & " As NewID, [ITEM NO], [ITEM
CONTENT], [QTY], [UNIT], [UNIT PRICE] " & _
"FROM [QUOTATION TABLE DETAILS] WHERE [QUOTATION NO] =
"
& Me.[QUOTATION NO] & ";"
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

Allen Browne said:
The request for a parameter means you have a name Access doesn't
recognise.
So some name in the SQL statement is no correct.

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

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

Thanks, Beetle & Allen,

Have changed the code as below but got an error message "Error#3061,
parameter not much enough, expected is 1" caused by below line :

DBEngine(0)(0).Execute strSql, dbFailOnError

Appreciate your expert commnts! Thanks in advance!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top