Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Aug 2001
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    linking subforms (97)

    Would anyone be willing to look at my database and help me?? It's pretty straightforward. I have a table for Customers with Acct No as primary. I have a second table for Projects with Proj No as primary. The link is one to many using Acct No. My third table is Comments. It includes Autonumbered field, Project No, Date and Comment. Projects and Comments are linked on Proj No.
    On the Entry form I have the customer info - In a Suform I have Projects. Within my Project subform I have another subform called Comments.
    I want Customer Info to show on top, then Project Info with a drop down list so they can pick which project and then under Comments I want it linked with Proj No so whenever they pick a project in Project Info subform a list of dates and comments related to that project shows up.
    The problem is between Projects and Comments - if I choose another Project number it doesn't change down in Comments.
    If anyone can help I can email my database to you. I'm sure it's something simple. Thank you!!!

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking subforms (97)

    You can send it to me if you want. My e-mail is in my profile.
    Francois

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking subforms (97)

    After looking at the database, I add code to the after update event of the combobox Project No
    DoCmd.RunCommand acCmdSaveRecord
    Me.Comments.Form.Requery
    I also modify the primery key of TblCustProjectInfo to add the Acct No
    Francois

  4. #4
    New Lounger
    Join Date
    Aug 2001
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking subforms (97)

    Everything with my Comments subform is working but now I have a problem with the Projects subform. When I change the Project number it's as if I'm updating that record. What I would like is when I select a new project number I get an empty form so I can fill in the rest of the field and make a new record.
    Can anyone help with that??

  5. #5
    New Lounger
    Join Date
    Aug 2001
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking subforms (97)

    I forgot to link my file in case anyone can take a look at it. If you open the Entry Form you'll see I have two subforms and the problem is in the Projects subform. I would like one customer to have many projects and each project to have many comments. If I choose a Project number and fill out all the info, then when I go to choose another Project No it doesn't start a new project record.
    Thanks for any help.
    Attached Files Attached Files

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

    Re: linking subforms (97)

    The combo box "Combo30" in the "Project Info" subform is bound to the field "Project No". (I won't rant about your naming of fields, controls, etc. now.) So if you select a project from the combo box, you change "Project No" for the current record in the subform. In your current setup, if you want to create a new project record, you must go to a new record in the subform, for instance using the navigation buttons at the bottom of the subform, and then select a project from the combo box.

    Another way would be to make the combo box unbound, and write code in the AfterUpdate event handler to create a new record with the selected project number. But you'd have to check that there is no existing record for that project number, so it would be more work.

  7. #7
    New Lounger
    Join Date
    Aug 2001
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking subforms (97)

    Thanks for the quick response. I tried to do the unbound box but didn't really know what code to put in or how to store it. There should not be more than one project with the same number for each customer so that shouldn't be a problem. I want the Project No to be chosen and then the Project Name and Description to come up automatically. That's why I had it in a separate table originally. I'm thinking I could get rid of the Project Info table and put all the info in the Combo box. (although I don't know how I can get it to show like I have it now). Then put a field called Project No in the table called CustProjInfo(where the rest of the info is like Initiation Date, Rebate/Grant, etc.) where I can store the chosen Project No. Then I have put something in the After Update. Does that sound right?

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

    Re: linking subforms (97)

    I have attached a modified (zipped) database with an unbound combo box and code behind it. See if it does what you want.

    Note: I would keep the Project Info table. It's the easiest way to fill the combo box.
    Attached Files Attached Files

  9. #9
    New Lounger
    Join Date
    Aug 2001
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking subforms (97)

    Hans
    This is great and it's working just the way I wanted. Thank you! I could never have written that code you put in, although I wish I understood better just what you did. How is the Project number getting stored since it's an unbound box?
    Anyway, I noticed one problem. I have to give this database to a different department and it's going to be empty to start. They will add customers and all the other info. So I deleted everything I had in there so I could test it again and when you enter a new customer and it's the first time you want to enter a project number, a run-time error occurs. It says "RecordGoToNew" isn't available now. Can you, or anyone else, help me with that?
    Thank you, I really appreciate all this.
    Attached Files Attached Files

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

    Re: linking subforms (97)

    It's easy to add a check for the case that there is no project yet: replace the instruction

    RunCommand acCmdRecordsGoToNew

    by

    If Not Me.NewRecord Then
    RunCommand acCmdRecordsGoToNew
    End If

    The Project number is stored explicitly in the code in the instruction just after this:

    [Project No] = cboProjNo

    sets the field "Project No" to the value selected in the combo box cboProjNo.

  11. #11
    New Lounger
    Join Date
    Aug 2001
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking subforms (97)

    Hans
    Everything is working now and it looks good. Thank you for all the help, you've been great!!

Posting Permissions

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