If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Duplicating Main Form And Its Subform Detail Records in Access 97
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 -- Brian |
#2
|
|||
|
|||
On Sun, 22 Aug 2004 08:33:01 -0700, "bdehning"
wrote: 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. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
#3
|
|||
|
|||
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. "John Vinson" wrote: On Sun, 22 Aug 2004 08:33:01 -0700, "bdehning" wrote: 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. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
#4
|
|||
|
|||
John,
I have not recived a response to my last post. Any one have an idea to what I need to do? "bdehning" wrote: 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. "John Vinson" wrote: On Sun, 22 Aug 2004 08:33:01 -0700, "bdehning" wrote: 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. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
Thread Tools | |
Display Modes | |
|
|