Duplicate Records That Contain Composite PK

E

Eka1618

Hello,

I used Allen Browne's script for duplicating records from a form and it's
subforms.

The one subform I use has a composite PK. I am having dificulty writing the
SQL statement in my code. The following script does not seem to grab the
value for PAT_NO.

Here is what I have so far:


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
!TITLE = Me.TITLE
!EMP_ID = Me.lboRequestor.Value
!REQUESTEE = Me.lboRequestee.Value
!DUE_DATE = Me.DUE_DATE
!CUSTOMER = Me.CUSTOMER
!NOTES = Me.NOTES
!R_TYPE = Me.R_TYPE
.Update

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

'Duplicate the related records: append query.

If Me.frmPattern.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO tblPattern( QUANTITY ) " & _
"SELECT QUANTITY FROM tblPattern WHERE REQUEST_NO = " &
Me.REQUEST_NO & _
" AND PAT_NO = " & Me.frmPattern.Form.PAT_NO & " AND
PAT_SIZE = '" & Me.frmPattern.Form.PAT_SIZE & "';"
DBEngine(0)(0).Execute strSql, dbFailOnError
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


The message I get is that The value for PAT_NO cannot be NULL. There is a
value in all of the fields I am trying to copy at the time I click this
button.

Here are the specs for tblPattern:
PAT_NO (PK)
PAT_SIZE (PK)
REQUEST_NO (PK/FK)
QUANTITY

If anyone knows how I can modify this SQL statement, Please let me know,
Thank You!
 
T

Tom van Stiphout

On Wed, 17 Sep 2008 06:23:00 -0700, Eka1618

Take a close look at the Insert sql statement. It only inserts into a
field named QUANTITY. Obviously it should insert into several fields,
including PAT_NO and PAT_SIZE.
Perhaps it helps you to first create an Insert query in the query
designer; then look at the sql statement that was generated.

-Tom.
Microsoft Access MVP
 
E

Eka1618

Hi Tom,

I have tested the SQL differently a few times. This way was just the closest
I came to getting it to work.

For instance if I used this:

If Me.frmPattern.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO tblPattern( PAT_NO, PAT_SIZE, QUANTITY ) " & _
"SELECT PAT_NO, PAT_SIZE, QUANTITY " & _
"FROM tblPattern WHERE REQUEST_NO = " & Me.REQUEST_NO & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
End If

It would tell me that I did not have the correct number of values to insert.

I am pretty sure I have to set this statement up a little differently than
Allen's example because I am using a composite PK, but I am just having a
hard time trying to figure it out.

Here is another example of one that I have working (This is another subform
that's duplicated when this button is clicked):

If Me.subDupKey.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO tblKey( REQUEST_NO, K_PART_NO, K_EWO_NO, K_SKID_NO,
K_MAT_HEAT, K_MAT_TYPE, K_PLAT, K_TOP_COAT, K_HEX, K_HARD_VALUE ) " & _
"SELECT " & lngID & " As NewK_ID, K_PART_NO, K_EWO_NO, K_SKID_NO,
K_MAT_HEAT, K_MAT_TYPE, K_PLAT, K_TOP_COAT, K_HEX, K_HARD_VALUE " & _
"FROM tblKey WHERE REQUEST_NO = " & Me.REQUEST_NO & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
End If

I my select statement I am leaving REQUEST_NO out because I want to get the
new request number from the parent form. I want the same PAT_NO, PAT_SIZE and
Quantity, but it will not allow me to set up a statement similar to the one
above because it just says there are not enough values... I do not understand
why it works for one subform and not the other, that is why I believe my
problem is with the composite PK's.

If you have any other suggestions please let me know, Thank you!
 
T

Tom van Stiphout

On Wed, 17 Sep 2008 08:34:01 -0700, Eka1618

If you can zip your db, email it to me and add some explanation on
what I should be looking for, I will take a look at it. The insert
statement with 3 fields that you list below must be pretty close to
the final solution.

I'm sure you can figure out how to remove the .no.spam filter from my
email address.

-Tom.
Microsoft Access MVP
 
E

Eka1618

Hi Tom,

I am an intern and I do not think I am authorized to zip this database to
you (or anyone else for that matter). All I know is that I need to have a
solution quick, I am not sure what else to do. I have still be searching for
a way to solve this problem the last couple days, but no luck yet.

If you or anyone else reading this thread has any solutions, please let me
know.

Thanks again!
 

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

Top