Results 1 to 7 of 7
  1. #1
    liswalk29 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    3

    Adding Multipe Attachments Causes Duplicates in Dropdown Field

    I am working with an database that someone else designed but I have since taken over. I am tasked with updating a "Document" Tab on the existing form. This form currently consists of a dropdown list of "Product" names. I have added an attachment button that shows the number of files attached. The customer also would like to see the attachment file names. I have included a sub-form which shows the file names but it also causes the drop list to duplicate the "Product" names (ie. 3 attachments for Product - AMT1 will cause AMT1 to appear in the dropdown list 3 times).

    Is there a fix for this or is there another way I need to approach this customer request.

    Thank you


    Lisa

  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
    The form does not dictate what the combobox list shows.

    Is purpose of dropdown to show distinct file names or distinct product names? Perhaps you need 2 comboboxes. If you want second combobox list dependent on first combobox selection, this is called cascading combobox - a common topic.

    Most of us advise not to use Attachment type field as embedding objects in db consumes Access 2GB size limit.
    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
    liswalk29 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    3
    June7,

    Yes the purpose is o show distinct product name. I will take a look at the link you provided and see how I can make this work for the customer as well as keep in mind the size limit

    Thank you.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I did not provide a link but Google should be able to provide a bunch.

    If you want Product to be distinct then can't include attachment file names in the RowSource.

    Why doesn't combobox use Products table as RowSource to list unique product items?
    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.

  5. #5
    liswalk29 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    3
    I'm still working on figuring out how the person before me set up with DB and what their login was. It appears that the form is associated with a query; Record Source = qryProducts. Additionally, the Row Source for the combo box is: SELECT DISTINCT qryProducts.ProductDetailsID, qryProducts.ProductFullName FROM qryProducts WHERE (((qryProducts.CategoryID)=2)) ORDER BY qryProducts.ProductFullName;


    I'm uncertain of how to fix this issue. I could have chose the option to have the use click on a link to grab the files but this is not the route they want to take. They want files uploaded. Thank you.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Not clear to me what the issue is. The SQL you posted does not mention attachment field and uses DISTINCT keyword so the product should not be duplicated. Form and combobox using same data source is odd.

    Could 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.

  7. #7
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    As June pointed out, Many developers avoid using attachment fields.

    The customer also would like to see the attachment file names.
    It is much easier to store attachments in a separate folder and just store the path to the file as text in the database.
    It is much simpler to use a list box with code to iterate through the folder and list the file names.
    If you have more than one folder you use 2 list boxes, 1 listing the folders and 1 listing files in the selected folder (cascading list boxes)

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

Similar Threads

  1. Adding attachments to a table
    By kdbailey in forum Forms
    Replies: 7
    Last Post: 07-30-2014, 11:31 AM
  2. Replies: 8
    Last Post: 12-12-2013, 03:54 PM
  3. Replies: 5
    Last Post: 10-14-2013, 11:53 AM
  4. attachments and adding new records
    By chiefmsb in forum Access
    Replies: 0
    Last Post: 07-06-2011, 01:22 PM
  5. Replies: 0
    Last Post: 10-25-2010, 09:23 AM

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