Allen Browne - Help with duplicating Form & Subform!

[Eka1618]

This Message is for Allen Browne, However I need help ASAP, so if anyone
knows a solutions, it would be greatly appreciated if you could pass it on.

I have used your method for duplicating a form and all of it subforms. This
has worked well for each subform except for one (frmPattern).

The record source for this particular subform is tblPattern whose primary
key is a composite PK.

PAT_NO (pk)
PAT_SIZE (pk)
REQUEST_NO (pk/fk)
QUANTITY

I think I am close to a solution, but it will not duplicate this subform
record Access says 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.

Tom van Stiphout Has tried to help me with this, but no luck yet. The
following is what I have so far. Everything else is working except for when I
try to duplicate frmPattern.


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.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

If Me.subDupLock.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO tblLock( REQUEST_NO, L_PART_NO,
LOCK_LUG, LUG_TOOL, L_EWO_NO, L_SKID_NO, L_MAT_HEAT, L_MAT_TYPE, L_PLAT,
L_TOP_COAT, L_THREAD, L_HARD_VALUE ) " & _
"SELECT " & lngID & " As NewL_ID, L_PART_NO, LOCK_LUG,
LUG_TOOL, L_EWO_NO, L_SKID_NO, L_MAT_HEAT, L_MAT_TYPE, L_PLAT, L_TOP_COAT,
L_THREAD, L_HARD_VALUE " & _
"FROM tblLock WHERE REQUEST_NO = " & Me.REQUEST_NO & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
End If

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

Me.subDupGeneral.Form.Dirty = False
Me.subDupImpact.Form.Dirty = False
Me.subDupHandle.Form.Dirty = False
Me.subDupOffset.Form.Dirty = False
Me.subDupProof.Form.Dirty = False
Me.subDupStatic.Form.Dirty = False
Me.subDupSecurity.Form.Dirty = False
Me.subDupTorque.Form.Dirty = False

strSql = "INSERT INTO tblTest( REQUEST_NO, TEST_TYPE, UNITS,
A_M, SAMPLE_NO, PROVIDED, CYCLE_NO, CYCLE_TIME, TEST_TORQ, WRENCH_NO, RPM,
LOAD, MIN_LOAD, ACID, STRAIGHT_FAIL, INC_FAIL, START_PT, INCREMENT,
INSTALL_TRQ, MIN_TORQ, TRQ_SHUTOFF, TRQ_PT1, TRQ_PT2, TRQ_PT3, TRQ_PT4,
TRQ_PT5, TRQ_PT6, DWELL_ON, DWELL_OFF, WHEEL, WHEEL_NO, TEST_WASHER,
STUD_PER_TEST, STUD_PT_NO, CUST_SPEC, SHROUD, LPS, TAKE_TO_FAILURE,
BOTH_DIRECTIONS, DESCRIPTION, LOOKOUT ) " & _
"SELECT " & lngID & " As NewTEST_ID, TEST_TYPE, UNITS, A_M,
SAMPLE_NO, PROVIDED, CYCLE_NO, CYCLE_TIME, TEST_TORQ, WRENCH_NO, RPM, LOAD,
MIN_LOAD, ACID, STRAIGHT_FAIL, INC_FAIL, START_PT, INCREMENT, INSTALL_TRQ,
MIN_TORQ, TRQ_SHUTOFF, TRQ_PT1, TRQ_PT2, TRQ_PT3, TRQ_PT4, TRQ_PT5, TRQ_PT6,
DWELL_ON, DWELL_OFF, WHEEL, WHEEL_NO, TEST_WASHER, STUD_PER_TEST, STUD_PT_NO,
CUST_SPEC, SHROUD, LPS, TAKE_TO_FAILURE, BOTH_DIRECTIONS, DESCRIPTION,
LOOKOUT " & _
"FROM tblTest WHERE REQUEST_NO = " & Me.REQUEST_NO & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError


'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


I think I am having trouble because of the composite PK's. If you have any
suggestions please let me know, Thank You!

 

[Allen Browne]

Presumably we are talking about the code from this link:
http://allenbrowne.com/ser-57.html

The error message is:
PAT_NO cannot be NULL

So I assume the relevant code is this:

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

You are trying to pick up the Pat_No from the subform. It could be at a new
record, in which case the Pat_No would be null. That's not going to work.

You will need to rework this so you can pick up the Pat_No from somewhere
else: either the main form, or from a table, or with AddNew on a Recordset
(i.e. using the logic we used to add the record to the main form rather than
merely an INSERT query.)

Obviously we can't write your code for you, but that's the approach to take.

 

[Eka1618]

Thanks for the suggestion,

I didn't mean to sound like I wanted someone to write my code, that is not
what I want at all. I just wanted to know where I was going wrong.

I am going to try getting this value another way. Hope fully this will work
well for me.

Thanks again.

 

[Eka1618]

Hi Allen,

Since you last response I have not been able to work on this issue again
until today.

One important thing I forgot to mention the other day is that the records
that are being duplicated are exsisting records from an earlier time.

When the parent for opens, I prompt the user to find the request they want
to duplicate. If there are patterns associated with the request, then the
records that appear in frmPattern were already saved, so it does not seem
possible for frmPattern to be at a new record (with the example I have been
using). I have frmPattern Data Entry Property set to No.

However, it is possible for it to be at a new record, but in the example
that I have been trying to test this with, the pattern information has
already been saved. This is why I am confused as to why it says it is NULL.

I am going to try using form_current event on frmPattern to set PAT_NO and
PAT_SIZE to variables accessible in the parent form. I just wanted to mention
the fact the the records that I am trying in my example are exsisting records
at the time the duplicate button is clicked.

If you have any other suggestions now that I told you that, please let me
know.

Thanks Again!

 

[Allen Browne]

Not sure I followed.

Appending to the RecordsetClone of the form does not move it to a new
record.

The subform could be at a new record if there are no related records there,
or if it happens to be at the new record.

 

[Eka1618]

Hi Allen,

You Said : Appending to the RecordsetClone of the form does not move it to a
new record.

So getting the value by using the current event will not work then (if that
is what you mean). This is what I originally thought and so I do not
understand what you mean by getting the values from somewhere else. Do you
mean by creating a query and using that recordset, or using the table?

I have done another test and I think that perhaps I am wrong about what is
going on durring the code. I used the following:

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 told me that a PK cannot be null. As a test I ran this as a seperate
query and used the request number of 133 and I do have 1 record that matches.

I am wondering if it is not the PAT_NO and PAT_SIZE at all and if it is the
REQUEST_NO. It would make more sense to me to say insert pattern number, size
and quantity where the request number = the request that I am duplicating.
However, I think I need the code to give me that new Request Number value so
that I can append that as well.

I think the code should read more like this:

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

This sort of makes more sense, but I do not think it could work at the same
time. tblPattern is looking for REQUEST_NO, PAT_NO and PAT_SIZE since they
are a composite PK and I do not think it is getting that new REQUEST_NO value.

I think this better explains whats going on, please let me know what you
think when you get a chance, Thank You!

 

[Allen Browne]

Immediately before the Execute line, temporarily add this line:
Debug.Print strSql

When it fails, open the Immediate Window.
Copy what you see there, and paste it into a new query.
Try running the query.
See if you can pin down what's wrong.

 

[Eka1618]

Hi Allen,

This is what printed in the Immediate Window:

INSERT INTO tblPattern( PAT_NO, PAT_SIZE, QUANTITY ) SELECT PAT_NO,
PAT_SIZE, QUANTITY FROM tblPattern WHERE REQUEST_NO = 133;

I ran it seperatly in a query and It said cannot append 1 record into table
due to key violations. I am certain that it is because of the request number,
it is not inserting anything into it.

When I created table pattern I wanted to create a unique index on the entire
row for a table, but the only way I know how to do this in Access is to
create the composite PK which includes the PAT_NO, PAT_SIZE and REQUEST_NO.
This way I can tell the DB that there needs to be a unique pattern record for
each request, not for each row in tblPattern.

Please let me know if you have any other thoughts... Thanks again for all
your help!

 

[Eka1618]

Allen,

I just wanted to clarify that there needs to be a unique pattern within each
request, but there can be many patterns, so I used a 1:M relation between
tblRequest and tblPattern.

 

[Allen Browne]

Hi Erica

I'm going to have to let you do your debugging here. You will need to
examine the query, figure out what you need to have, and how to get the code
to generate that statement.

It's worth persuing.

 

[Eka1618]

Well I figured out what I needed to do to make this work.

Here is the code if anyone is interested.

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

I seemed to have been confused with what lngID was doing in the other
subforms I was duplicating. so I did a few tests and the above code
eventually work

Thanks again Allen!