Results 1 to 11 of 11
  1. #1
    Davide is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    38

    many-to.many input form


    Hello everyone
    I have two simple tables which are in a n-to-m relationship (see the attached DB). I need to create a form for the cross-ref table, which shows two fields (norm and destination) of the table Norm, and the parameter description (and unit of measure UM) from table Parameter. These two tables contain master data. The user should be able to select one record from the first table and one from the second to join them in the cross-ref table, and then add the threshold. Consider that Norm and Parameter may have hundreds of records, and that once the relationships are created, they will rarely be modified (if ever); some may be added, but Threshold contains master data, essentially. The current solution, frmThreshold, does not look so good, from a functional perspective. The user should be able to consolidate the inputs. Any suggestions? Thanks a lot.
    Attached Files Attached Files

  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
    Looks like many-to-many relationship between Parameter and Norm with Threshold as junction table. Assuming Parameter can have many Norm and each norm can associate with multiple Parameter.

    A single form with two comboboxes is one way to accomplish data entry. Why do you think it 'does not look so good'?

    Other options:

    1. main form bound to Parameter and subform bound to Threshold with a combobox for Norm

    2. main form bound to Norm and subform bound to Threshold with a combobox for Parameter
    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
    Davide is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    38
    Hi June
    and thanks for your reply. I will try your second suggestion, and eventually get back here.
    I think my solutions has two flaws: (i) you cannot see the second field in the combo and (ii) it all looks quite unstable.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    i) - did you set the column count to something other than 1? (property sheet>format tab)
    If the total width of your columns exceeds the width of the combo you'll get a horizontal scroll bar in the list so you might have to adjust your combo width to suit if you don't want that.

    ii) can't help with this one. Don't understand how or why.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  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
    (i) both descriptive fields show in the dropdown of both comboboxes, only one field can be displayed in the box
    text in Norm fields is very long so some is cut off

    (ii) again, what do you mean by 'unstable'
    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
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    (i) yes, lost track of what I was seeing vs envisioning. Turns out I should have suggested concatenating the UM and Parameter fields but binding to ID field. Another reason to not use a table for a combo, I guess.
    My fingers didn't obey my brain.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Davide

    Your Parameter table is wrong, as you have records as shown in the attachment.

    You should have 2 tables to manage this data.

    Your tables should be:-

    tblParameters
    -ParameterID - PK - Autonumber
    -Parameter

    tblParameterDetails
    -ParameterDetailID - PK - Autonumber
    -ParameterID - Number - LongInteger - FK (Linked to PK in tblParameters)
    - Details
    Attached Thumbnails Attached Thumbnails Parameter.JPG  
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Your Parameter Data Input Form would then look like this>
    Attached Thumbnails Attached Thumbnails Parameter Form.JPG  
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  10. #10
    Davide is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    38
    No it is not. Besides, I just marked the thread you linked as solved.

  11. #11
    Davide is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    38
    Quote Originally Posted by Micron View Post
    No, it is not. Besides, I just marked it as solved

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

Similar Threads

  1. Replies: 1
    Last Post: 07-13-2021, 11:42 PM
  2. Replies: 5
    Last Post: 06-24-2019, 04:45 AM
  3. Replies: 1
    Last Post: 11-04-2014, 12:07 PM
  4. Replies: 6
    Last Post: 10-27-2014, 08:05 PM
  5. Input Forms - How To Input Multiple Fields/Records?
    By butterbescotch in forum Forms
    Replies: 1
    Last Post: 04-04-2013, 06:30 AM

Tags for this Thread

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