Duplicating Main Form And Its Subform Detail Records in Access 97

[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

 

[John Vinson]

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.

Well, nowhere in your code do you even try to do anything with [Policy
Number]. What do you want to change it TO? You will need to have
*some* source of a new number, if you're not using an Autonumber.

If the field is numeric (you don't say - it might be text), consider a
line

rs![Policy Number] = NZ(DMax("[Policy Number]", "[table-name]")) + 1

amongst the other new-record data.

 

[bdehning]

John,

It is text. I tried the code but got mismatch error. I put under the
addnew section?

Is there a way to prompt a msg box to input the Policy Number? In most case
the Policy Number is like NWC99999999 , where adding 1 to it might work but
the first 3 letters could change as well which is why if a prompt to fill in
the field could be created that would be best.

Can you please supply more help.

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.

Well, nowhere in your code do you even try to do anything with [Policy
Number]. What do you want to change it TO? You will need to have
*some* source of a new number, if you're not using an Autonumber.

If the field is numeric (you don't say - it might be text), consider a
line

rs![Policy Number] = NZ(DMax("[Policy Number]", "[table-name]")) + 1

amongst the other new-record data.

 

[bdehning]

John,

I have not recived a response to my last post. Any one have an idea to what
I need to do?

John,

It is text. I tried the code but got mismatch error. I put under the
addnew section?

Is there a way to prompt a msg box to input the Policy Number? In most case
the Policy Number is like NWC99999999 , where adding 1 to it might work but
the first 3 letters could change as well which is why if a prompt to fill in
the field could be created that would be best.

Can you please supply more help.

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.

Well, nowhere in your code do you even try to do anything with [Policy
Number]. What do you want to change it TO? You will need to have
*some* source of a new number, if you're not using an Autonumber.

If the field is numeric (you don't say - it might be text), consider a
line

rs![Policy Number] = NZ(DMax("[Policy Number]", "[table-name]")) + 1

amongst the other new-record data.