Re: Duplicate the record in form and subform

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" wrote:

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Beetle" <Beetle@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0C90F455-E349-40F2-83A9-1E5EAD5AF577@xxxxxxxxxxxxxxxx
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" wrote:

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!

"Allen Browne" wrote:

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.

"Andy" <Andy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F433142D-F96C-41EC-AD9F-8408A6D669A4@xxxxxxxxxxxxxxxx
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!


.

Relevant Pages

  • Re: Allen Brownes function: "Duplicate record (and related child record)" -- my modified ver
    ... Duplicate the main form record and related records in the subform. ... Dim strSQL As String ... Dim lngOldID As Long '*** Primary key value of the orginial record. ... - "TrackingNumber" is primary key ...
    (microsoft.public.access.formscoding)
  • Re: MFC/ODBC/ACCESS Creating a New DataRecord in a table with primary key
    ... The error message is quite informative - it is telling you that you have ... > I have a table with an auto number field as primary key. ... > eate duplicate values in the index, primary key, or relationship. ... > Is there any possibility to get the new key from the database instead of ...
    (microsoft.public.vc.mfc)
  • Re: duplicate row, related rows, and their related rows
    ... Dim db as DAO.Database ... Dim strSql As String ... Dim lngMiddleID As Long 'Middle table's primary key ... I would like to duplicate a row and all child related rows. ...
    (microsoft.public.access.formscoding)
  • Duplicating a record
    ... and I need the ability for the user to duplicate a record and then edit ... The records are PartNo, Width, and Length. ... Index or primary key cannot contain a Null value ... but I can't figure out why the error message keeps ...
    (microsoft.public.access.forms)
  • Re: Duplicating reocords from main form and subform
    ... Do you mean you get an error message? ... Duplicate the main form record and related records in the ... Dim lngID As Long 'Primary key value of the new record. ... 'Make sure there is a record to duplicate. ...
    (microsoft.public.access.forms)