Page 1 of 2 12 LastLast
Results 1 to 15 of 22

Thread: Subform (2000)

  1. #1
    Lounger
    Join Date
    Nov 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subform (2000)

    I have a subform inside of a form. I want the user to be able to look up a record on the form and then copy that record to a new record and the information on the subform be copied also. Right now I have a command button set to allow the user to copy the record on the main form but it isn't copying the info from the subform. Any suggestions would be greatly appreciated.

    Thanks,
    Melanie

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Subform (2000)

    This gets rather tricky, depending on your table design. When you say copy that record to a new record, is it going into the same table or a different table? Also, how do you know what record on the subform is to be copied? Or is your design such that you can only have one record on the subform? And do the subform records go into the same table or a new table?

    Generally this sort of thing involves using DAO or ADO in a VBA routine to actually copy the records, although in some cases it can be done using append queries which are relatively complex. Give us some added details, and we'll do our best to help you.
    Wendell

  3. #3
    Lounger
    Join Date
    Nov 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform (2000)

    I have included the database so you will be able to determine what i am trying to achieve. The user will first pick a part number from the main form. After the part number is selected all of the related Quote numbers will appear, the user will find the quote that is most like the one he/she will want to replicate. Once the quote number is selected I would like the user to be able to hit the duplicate record button and another record appear that has all of the same information for the Customer, Quotenumber, machine, and then for the subforms machine and quantity.

    It might be easier to understand if you have some generic information in the fields, I deleted the info so it would be small enough to send.

    Thanks,
    Melanie

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform (2000)

    See if ACC2000: How to Duplicate a Main Form and Its Subform Detail Records helps you on your way. Feel free to come back with questions.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Subform (2000)

    Sorry for not responding sooner - ran into a corrupt VBA project on a client system and then had to run some errands. It looks like Hans has pointed you in the right direction - the solution in the MSKB article uses VBA and DAO to do the trick. Feel free to raise questions about the techniques used if it's new to you. I should also note that it appears that your attached database is in Access97 format - did you do that to reduce the size, or is that the native format?
    Wendell

  6. #6
    Lounger
    Join Date
    Nov 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform (2000)

    Thanks for the help. I was out of the office and will start to work on this, this morning. The current version I am using is 2000, but I converted it to 97 to save space.

    Thanks,
    Melanie

  7. #7
    Lounger
    Join Date
    Nov 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform (2000)

    I receive an error when I try and run the command button, " Object doesn't support this property or method". Also I will have multiple subforms that I will need to duplicate not just the one, how can I incoporate this into the command button.

    Thanks,
    Melanie

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform (2000)

    The control named Machine is one of the tab pages on the tab control, so the line

    !Machine = Me!Machine

    should be

    !Machine = Me!Text53

    You should also set the PartNumberID field, to do this you should have PartNumberID in the main form.

  9. #9
    Lounger
    Join Date
    Nov 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform (2000)

    Attached is the database, I am now receiving an error message stating that it cannot copy the data because there will be a duplicate value in the index, primary key, or relationship?

    Also could you please be a little more specific on this concept: "You should also set the PartNumberID field, to do this you should have PartNumberID in the main form." I added the part number Id but I do not know if this is what you were getting at.

    Thanks a lot for all of the help.

    Melanie

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform (2000)

    You set up PartNumberID correctly, as far as I can see.

    You shouldn't set QuoteID in the code, since it's an AutoNumber field.

    In the append query, you cannot use [Forms]![SubfrmMainInfo]![QuoteID], since SubfrmMainInfo is a subform. Instead, use

    [Forms]![Form1]![TblMainInfo subform]![QuoteID]

    This refers to the subform through the main form. Note that it uses the name of the subform as a control on the main form, not the name of the subform in the database window. The same holds for the other reference to the subform in the query.

  11. #11
    Lounger
    Join Date
    Nov 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform (2000)

    I was able to run the command with out any error messages but it is not coping the records from the machine tab? Any suggestions?

    Thanks,
    Melanie

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform (2000)

    You should remove SavageID from the append query. This field, too, is an AutoNumber field, so it should not be set.

    Note: to investigate problems like this, temporarily disable the On Error GoTo ... line by inserting an apostrophe at the beginning, and do the same for the lines with DoCmd.SetWarnings. That'll enable you to see what the query will or won't do.

  13. #13
    Lounger
    Join Date
    Nov 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform (2000)

    I removed the Savage ID from the query and then received an error stating that I could not duplicate the record because of a duplicate value in the index.

    Suggestions?

    Thanks,

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform (2000)

    One more thing: change

    [Forms]![Form1]![TblMainInfo subform].[Tag]

    to

    [Forms]![Form1]![TblMainInfo subform].[Form].[Tag]

  15. #15
    Lounger
    Join Date
    Nov 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform (2000)

    Still receiving the same error message, I double checked to make sure that I do not have any autonumber fields and I do not but it still does not seem to be working.

    Thanks,
    Melanie

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •