B
bdehning
I am having trouble following Microsofts KBA 132032 Example. I get it to
work as they say for Northwinds example but I need the ability the enter the
Primary Key and not have it automatically determined. I get index or primary
key can't contain null value.
My main form is 'Account Information' which has Primary Key [Policy Number].
The Subform is 'Location' and has Primary Key [Location ID] and includes
[Policy Number] field.
Here is the code I am using for the Duplicate Button:
Private Sub btnduplicate_Click()
Dim dbs As Database, Rst As Recordset
Dim F As Form
'Return Database variable pointing to current database.
Set dbs = CurrentDb
Set Rst = Me.RecordsetClone
On Error GoTo Err_btnDuplicate_Click
'Tag property to be used later by the append query.
Me.Tag = Me![Policy Number]
'Add new record to end of Recordset object.
With Rst
.AddNew
![Account Name] = Me![Account Name]
!EAP = Me!EAP
![X-Mod] = Me![X-Mod]
![Class Code] = Me![Class Code]
![Nature of Operations] = Me![Nature of Operations]
![Inception Date] = Me![Inception Date]
![Expiration Date] = Me![Expiration Date]
![Account Contact] = Me![Account Contact]
![Account Contact Phone] = Me![Account Contact Phone]
![Account Email] = Me![Account Email]
![Producing Division] = Me![Producing Division]
!Underwriter = Me!Underwriter
![Agency Name] = Me![Agency Name]
![Agency Address] = Me![Agency Address]
![Agency City] = Me![Agency City]
![Agency State] = Me![Agency State]
![Agency Zip Code] = Me![Agency Zip Code]
![Agency Contact] = Me![Agency Contact]
![Agency Email] = Me![Agency Email]
![Agency Phone Number] = Me![Agency Phone Number]
![Controlling Consultant] = Me![Controlling Consultant]
![Service Frequency] = Me![Service Frequency]
.Update 'Save Changes.
.Move 0, .LastModified
End With
Me.Bookmark = Rst.Bookmark
'Run the Duplicate Location append query which selects all
'detail records that have the Policy Number stored in the form's
'Tag property and appends them back to the location table with the
'Policy Number of the duplicated main record form.
DoCmd.SetWarnings False
DoCmd.OpenQuery "Duplicate Location"
DoCmd.SetWarnings True
'Requery the subform to display the newly appended records.
Me![Location Subform].Requery
Exit_btnDuplicate_Click:
Exit Sub
Err_btnDuplicate_Click:
MsgBox Error$
Resume Exit_btnDuplicate_Click:
End Sub
One of my issues is determing where to use [Policy Number] and [Location ID]
since in Northwinds they use [OrderID] for both.
Can you take a look at the code and help correct as needed. Remember I need
the ability to enter the [Policy Number] or at least change the one being
duplicated before it is saved.
The other issue is making sure I use the correct fields and table for steps
7- 9.
Step 7. For Northwinds they use [Forms]![Orders].[Tag] where I am using
[Forms]![Account Information].[Tag] Is this correct?
Step 8. For Northwinds they use NewOrderID:CLng([Forms]![Orders]![OrderID])
where I am using NewPolicy_Number: CLng([Forms]![Account
Information]![Policy_Number]) Is this Correct?
Step 9. For Northwinds They use [OrderID] where I am using [Location ID]
Is this Correct?
As you can see my difficulty is knowing when to use the Primary Key Policy
Number in the main table and when to use Location ID from the subform table.
I know this is long but really need some help and thought is someone could
see it all it might help.
Thanks
work as they say for Northwinds example but I need the ability the enter the
Primary Key and not have it automatically determined. I get index or primary
key can't contain null value.
My main form is 'Account Information' which has Primary Key [Policy Number].
The Subform is 'Location' and has Primary Key [Location ID] and includes
[Policy Number] field.
Here is the code I am using for the Duplicate Button:
Private Sub btnduplicate_Click()
Dim dbs As Database, Rst As Recordset
Dim F As Form
'Return Database variable pointing to current database.
Set dbs = CurrentDb
Set Rst = Me.RecordsetClone
On Error GoTo Err_btnDuplicate_Click
'Tag property to be used later by the append query.
Me.Tag = Me![Policy Number]
'Add new record to end of Recordset object.
With Rst
.AddNew
![Account Name] = Me![Account Name]
!EAP = Me!EAP
![X-Mod] = Me![X-Mod]
![Class Code] = Me![Class Code]
![Nature of Operations] = Me![Nature of Operations]
![Inception Date] = Me![Inception Date]
![Expiration Date] = Me![Expiration Date]
![Account Contact] = Me![Account Contact]
![Account Contact Phone] = Me![Account Contact Phone]
![Account Email] = Me![Account Email]
![Producing Division] = Me![Producing Division]
!Underwriter = Me!Underwriter
![Agency Name] = Me![Agency Name]
![Agency Address] = Me![Agency Address]
![Agency City] = Me![Agency City]
![Agency State] = Me![Agency State]
![Agency Zip Code] = Me![Agency Zip Code]
![Agency Contact] = Me![Agency Contact]
![Agency Email] = Me![Agency Email]
![Agency Phone Number] = Me![Agency Phone Number]
![Controlling Consultant] = Me![Controlling Consultant]
![Service Frequency] = Me![Service Frequency]
.Update 'Save Changes.
.Move 0, .LastModified
End With
Me.Bookmark = Rst.Bookmark
'Run the Duplicate Location append query which selects all
'detail records that have the Policy Number stored in the form's
'Tag property and appends them back to the location table with the
'Policy Number of the duplicated main record form.
DoCmd.SetWarnings False
DoCmd.OpenQuery "Duplicate Location"
DoCmd.SetWarnings True
'Requery the subform to display the newly appended records.
Me![Location Subform].Requery
Exit_btnDuplicate_Click:
Exit Sub
Err_btnDuplicate_Click:
MsgBox Error$
Resume Exit_btnDuplicate_Click:
End Sub
One of my issues is determing where to use [Policy Number] and [Location ID]
since in Northwinds they use [OrderID] for both.
Can you take a look at the code and help correct as needed. Remember I need
the ability to enter the [Policy Number] or at least change the one being
duplicated before it is saved.
The other issue is making sure I use the correct fields and table for steps
7- 9.
Step 7. For Northwinds they use [Forms]![Orders].[Tag] where I am using
[Forms]![Account Information].[Tag] Is this correct?
Step 8. For Northwinds they use NewOrderID:CLng([Forms]![Orders]![OrderID])
where I am using NewPolicy_Number: CLng([Forms]![Account
Information]![Policy_Number]) Is this Correct?
Step 9. For Northwinds They use [OrderID] where I am using [Location ID]
Is this Correct?
As you can see my difficulty is knowing when to use the Primary Key Policy
Number in the main table and when to use Location ID from the subform table.
I know this is long but really need some help and thought is someone could
see it all it might help.
Thanks