P
Peter Johnson
I am attempting to create a Duplicate Record button on a form with a subform
and a sub-subform. I have copied KB article 132032. However, I cannot get
the new key field to populate the subform's table. The main table is
populated, and the sub table queries run without an error, but no records
are appended to the sub tables.
I have changed the append query to a select query to see what it happening.
When I run it manually, and enter the new number and number I'm copying
from, it finds the right record set and shows the new number. When I run it
from the form button, the new number is blank.
This is in Access 97. Here is the code I'm using on the button:
Private Sub cmdDuplicate_Click()
Dim dbs As Database, rst As Recordset, F As Form, NewItem As String
Set dbs = CurrentDb
Set rst = Me.RecordsetClone
On Error GoTo Err_cmdDuplicate_Click
' first do tbl Seals Process Sheet Header
'Prompt for new item number
NewItem = InputBox("Enter new Item Number, or press Cancel to exit", "Copy
Process Sheet")
MsgBox NewItem 'yup, it shows up fine
If Len(NewItem) < 1 Then
MsgBox "New Process Sheet not created", vbCritical + vbOKOnly, "No Item
Number"
Exit Sub
End If
Me.Tag = Me![ITEM NUMBER]
With rst
.AddNew
![ITEM NUMBER] = Trim(NewItem)
![LOT ID] = Me![LOT ID]
![ITEM DESCRIPTION] = Me![ITEM DESCRIPTION]
![DRAWING ID] = Me![DRAWING ID]
![Drawing Revision] = Me![Drawing Revision]
![Revision] = Me![Revision]
![Date] = Int(Now())
![User Name] = fOSUserName()
![Sample Board Number] = Me![Sample Board Number]
![Visual Standard Number] = Me![Visual Standard Number]
.Update
.Move 0, .LastModified
End With
Me.Bookmark = rst.Bookmark
' now do tbl Seals Process Sheet Operations
'DoCmd.SetWarnings False
DoCmd.OpenQuery "qry Duplicate Process Sheet Operations"
'DoCmd.SetWarnings True
' now do tbl Seals Process Sheet Operations Tooling
'DoCmd.SetWarnings False
DoCmd.OpenQuery "qry Duplicate Process Sheet Operations Tooling"
'DoCmd.SetWarnings True
Me![frm Operations Subform with tabs].Requery
Me![frm Operations Tooling subform].Requery
Exit_cmdDuplicate_Click:
Exit Sub
Err_cmdDuplicate_Click:
MsgBox Err.Description
Resume Exit_cmdDuplicate_Click
End Sub
The query "qry Duplicate Process Sheet Operations" has these fields:
This is the new item number prompted in the code above:
Expr1: [forms]![zNEW]![NewItem]
This is the item number that is being copied:
Field: ITEM NUMBER
Criteria: [forms]![zNEW].[tag]
The query "qry Duplicate Process Sheet Operations Tooling" is set up
similarly.
Neither query generates an error when run from the button (note I've
commented out the SetWarnings statements), but in both the ITEM NUMBER field
is blank. Both work if they are run manually and the ITEM NUMBER and TAG
are input from the keyboard.
Thanks for your assistance. I'm obviously missing something elementary, but
I can't figure out what it is.
Peter
and a sub-subform. I have copied KB article 132032. However, I cannot get
the new key field to populate the subform's table. The main table is
populated, and the sub table queries run without an error, but no records
are appended to the sub tables.
I have changed the append query to a select query to see what it happening.
When I run it manually, and enter the new number and number I'm copying
from, it finds the right record set and shows the new number. When I run it
from the form button, the new number is blank.
This is in Access 97. Here is the code I'm using on the button:
Private Sub cmdDuplicate_Click()
Dim dbs As Database, rst As Recordset, F As Form, NewItem As String
Set dbs = CurrentDb
Set rst = Me.RecordsetClone
On Error GoTo Err_cmdDuplicate_Click
' first do tbl Seals Process Sheet Header
'Prompt for new item number
NewItem = InputBox("Enter new Item Number, or press Cancel to exit", "Copy
Process Sheet")
MsgBox NewItem 'yup, it shows up fine
If Len(NewItem) < 1 Then
MsgBox "New Process Sheet not created", vbCritical + vbOKOnly, "No Item
Number"
Exit Sub
End If
Me.Tag = Me![ITEM NUMBER]
With rst
.AddNew
![ITEM NUMBER] = Trim(NewItem)
![LOT ID] = Me![LOT ID]
![ITEM DESCRIPTION] = Me![ITEM DESCRIPTION]
![DRAWING ID] = Me![DRAWING ID]
![Drawing Revision] = Me![Drawing Revision]
![Revision] = Me![Revision]
![Date] = Int(Now())
![User Name] = fOSUserName()
![Sample Board Number] = Me![Sample Board Number]
![Visual Standard Number] = Me![Visual Standard Number]
.Update
.Move 0, .LastModified
End With
Me.Bookmark = rst.Bookmark
' now do tbl Seals Process Sheet Operations
'DoCmd.SetWarnings False
DoCmd.OpenQuery "qry Duplicate Process Sheet Operations"
'DoCmd.SetWarnings True
' now do tbl Seals Process Sheet Operations Tooling
'DoCmd.SetWarnings False
DoCmd.OpenQuery "qry Duplicate Process Sheet Operations Tooling"
'DoCmd.SetWarnings True
Me![frm Operations Subform with tabs].Requery
Me![frm Operations Tooling subform].Requery
Exit_cmdDuplicate_Click:
Exit Sub
Err_cmdDuplicate_Click:
MsgBox Err.Description
Resume Exit_cmdDuplicate_Click
End Sub
The query "qry Duplicate Process Sheet Operations" has these fields:
This is the new item number prompted in the code above:
Expr1: [forms]![zNEW]![NewItem]
This is the item number that is being copied:
Field: ITEM NUMBER
Criteria: [forms]![zNEW].[tag]
The query "qry Duplicate Process Sheet Operations Tooling" is set up
similarly.
Neither query generates an error when run from the button (note I've
commented out the SetWarnings statements), but in both the ITEM NUMBER field
is blank. Both work if they are run manually and the ITEM NUMBER and TAG
are input from the keyboard.
Thanks for your assistance. I'm obviously missing something elementary, but
I can't figure out what it is.
Peter