Results 1 to 15 of 15
  1. #1
    reenieh is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2014
    Posts
    12

    Combo box search from Main Form only showing one record from Subform

    TESTDATABASE.zipTESTDATABASE.zipI am attempting to create a Main Form with Subforms. The main form is created from a simple table (Project Number) containing ContractID, Project Number, etc. The subform (Contracts) contains the ContractID as well as several other fields I want displayed. (Relationship is ContractID) I am looking to display all the CONTRACTS that are associated with each of the PROJECT NUMBERS. My combo box is connected to the PROJECT NUMBERS and as you can see from the screen shot, there are several CONTRACTS associated with each PROJECT NUMBER. However, they do not display. Only the first record from the CONTRACT subform shows. They cannot be selected from the combo box, but I can tab through and see them.



    I have tried so many things and redone it several times, only to get the same result. Help, I'm disparate.Attachment 38496Attachment 38497

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Is combobox UNBOUND?

    How are you filtering subform?

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    reenieh is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2014
    Posts
    12
    Yes, it is unbound and i will try to post again but even stripped down versions seems to big to post as 500 kb limit

  4. #4
    reenieh is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2014
    Posts
    12
    It is attached now

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    As I expected, the subform Master/Child Link properties are set to ContractID. Records for only single contract ID can be displayed.

    Relationship may be backwards. Each contract can associate with only 1 project? If project can have multiple contracts, then where is the master Projects table? Project PK would be saved as FK in Contracts.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    reenieh is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2014
    Posts
    12
    Thanks

    I have removed the master/Child link properties and now it lists every contract and the combo box does not have an effect at all on the subform.

    Relationship is ONE contract to MANY projects, thus I want to select a project and have the system provide all contracts associated with the project.

    Master projects table is the Project Bridge Table which is attached to the combo box. I cannot save the project key in the contracts table because there are many contracts to a project and sometimes many projects to a contract. I have racked my brain to try and figure out how to run these together, but I inherited an imperfect system. I tried setting this db up by putting the project forward but that only makes me have to duplicate contract data. Any suggestions would be helpful.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    It is not a 'master' projects table because the project identifier is repeated. A 'master' table would have a unique record for each project.

    One contract can have many projects? Interesting. However, Bridge table shows many-to-many relationship. Really should have 3 tables: Projects, Contracts, ProjectContractBridge. Customary arrangement for m-to-m would be:
    1. main form bound to Projects, subform bound to Bridge with combobox to select contract
    2. main form bound to Contracts, subform bound to Bridge with combobox to select project

    If you want combobox purpose to be selection of project number, then list distinct project numbers:
    SELECT DISTINCT [ONA Project Number] FROM [Project Bridge Table] ORDER BY [ONA Project Number];

    There is no project number in Contract table so cannot filter records by project. The subform RecordSource would have to be a query that joins tables.
    SELECT [Contract Table].*, [Project Bridge Table].*
    FROM [Contract Table] LEFT JOIN [Project Bridge Table] ON [Contract Table].ContractID = [Project Bridge Table].ContractID;

    Then Master/Child links: ONA Project Number

    Advise not to use spaces nor punctuation/special characters in naming convention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    reenieh is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2014
    Posts
    12
    Okay, thanks. I am not a visual basic person, so I will have to study what you have written as I don't understand it. Here is what I think you are saying.

    In order to attach my projects to my contracts, I need a unique identifier. I could: use the ID and put it in both tables, OR I could do a query and join the two tables and use that query to be the main form and use it for the combo box as well as all the other information that i need. I would also have to do the master/child as ONA Project Number.

    If the above is true, do I have to reorganize all my other tables and queries that I have already completed to include the Project Number Master table and use Master/child project?


    I have based all my tables and queries on the contracts number....I am killing myself trying to complete this for tomorrow.

    UPDATE: I've just run a query that has run them together and that does not work as it doubles up my contract numbers and that is not what I want. I want one contract and a separate list of projects. I want to call up the project and get all the contracts associated with them.....im really struggling and im not an amateur, i've been putting together data bases for a long time, but i think my problem is that I have a many to many situation and Im trying to make it a one to many and that doesn't work

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Well, I modified the form as described and it works. I have not looked at other forms. But yes, I expect will have to modify.

    What other tables? The posted db has only 2.

    Yes, query will list project multiple times for each associated contract, and vice versa, in query joining related tables. I already described conventional approach for many-to-many relationship.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    reenieh is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2014
    Posts
    12
    Thanks, please post what you have changed as I am still going round and round and having the same issues it doesn't matter what I change.

    I have other subforms I stripped out as I cannot post the data to a public space as it contains confidential information. I also have used the conventional approach as listed in your post and one of them is the db i posted. So I am not understanding something that is basic, i fear.

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I said query joining tables would be RecordSource for subform not main form.

    All I did was:

    1. change combobox RowSource

    2. change RecordSource of testcontracttable

    3. change master/child link properties of subform container on TESTTABLE
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    reenieh is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2014
    Posts
    12
    Thanks, please post the db with your changes as I do not understand what you are saying.

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What exactly do you not understand? The instructions are quite explicit.

    I modified form TESTTABLE and its subform as described in posts 7 and 11. There are only 3 changes.

    Actually, there will be a 4th change. Will have to modify ControlSource property of textbox for ControlID because the form RecordSource will now have 2 fields named ControlID. So must specify which one: Contract Table.ContractID

    I prefer you follow the instructions as they are quite simple and direct. Copy/paste the SQL statements I provided into the specified properties.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    reenieh is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2014
    Posts
    12
    I prefer you provide me with the database back as it is not out of the question to ask for this information. I have been explaining to you all along that I do not understand you and have made quite an effort to follow your instruction.

    It would be really helpful to see what you have done, please provide me with a visual as that is the best way for me to learn. I am sure it is not that much trouble.

    I don't understand much of your instructions as there is a gap in my vocabulary as I am self taught.

    Post #7 - I explained i do not know visual basic but WHERE do i post this, there are many many places that visual basic can be posted. Please be specific as to where this information goes. It is all to go in the same place? Are there separate places for this code? Even though I explained to you I didn't understand, you have made no effort to try and explain your instructions in a different way.

    I have created a query to combine the contract table and the bridge table and I can use the 'Original Project Table' as the Main.

    As for your instruction for #11


    I said query joining tables would be RecordSource for subform not main form. WHAT AM I USING FOR THE MAIN FORM - THE MASTER PROJECT TABLE? (I do have a master project table) OR AM I USING THE CONTRACT TABLE OR THE BRIDGING TABLE.


    All I did was:

    1. change combobox RowSource -TO WHAT? WHAT DID YOU CHANGE IT TO. WHAT IS IT ATTACHED TO?
    2. change RecordSource of testcontracttable TO WHAT?

    3. change master/child link properties of subform container on TESTTABLE. TO WHAT?

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The changes are described in post 7. To repeat:

    If you want combobox purpose to be selection of project number, then list distinct project numbers:
    SELECT DISTINCT [ONA Project Number] FROM [Project Bridge Table] ORDER BY [ONA Project Number];


    That is for the combobox on TESTTABLE form.

    There is no project number in Contract table so cannot filter records by project. The subform RecordSource would have to be a query that joins tables.
    SELECT [Contract Table].*, [Project Bridge Table].*
    FROM [Contract Table] LEFT JOIN [Project Bridge Table] ON [Contract Table].ContractID = [Project Bridge Table].ContractID;


    That is for the RecordSource of testcontracttable form.

    Then Master/Child links: ONA Project Number

    That is for the subform container on TESTTABLE form.

    No change was suggested for TESTTABLE main form. However, if you do have a 'master' Projects table, it should be the RecordSource instead of Bridge table. Then there will be only one record on the main form for each project. This is more in line with conventional form/subform arrangement for m-to-m relationship. Also, this 'master' table could then be source for combobox list. Since you did not provide such table in the db, I cannot be more specific.

    This is really quite basic Access functionality.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 02-22-2015, 01:25 PM
  2. Replies: 2
    Last Post: 01-11-2013, 06:51 PM
  3. Replies: 3
    Last Post: 08-22-2012, 03:28 AM
  4. Replies: 16
    Last Post: 02-13-2012, 03:32 PM
  5. Replies: 13
    Last Post: 01-29-2012, 01:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums