Results 1 to 10 of 10
  1. #1
    Carloj is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    24

    Dlookup in multi value

    I got a product table, in this product table is a supplierfield. This field is a multi value field. Now i got a dlookup in my orderform. So when a product is put in it looks the supplier up. Now i got a error with my code:
    Code:
    ComboBoxSupplier = DLookup("[Product_SupplierID]", "tblProduct", "[ProductID]=" & [ComboBoxProduct])
    I guess the dlookup function can only search for one value. Is there a dlookup function for multi value's?

  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
    Better to not use multi-value field.

    Whether you use multi-value or not, the product has multiple suppliers - which one should the DLookup return?
    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
    Carloj is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    24
    It doesn't really matter which one it returns, it is possible to edit them after.

    What is the reason why it's better to not use multi-value fields? and what would be a better option?

  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
    Multi-value field involves a hidden dependent table. Better to build a normal table and manage the relationship yourself. If you need to understand how to use multi-value field in query - review https://support.office.com/en-us/art...3-b6624e1e323a.

    You would have to build query that expands the multi-value field then run DLookup on that query.

    No other database platform uses this type of field. Migrating to another platform would be difficult.

    They are just plain aggravating.
    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
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    what would be a better option?
    create a join table.

    tblproducts
    ProductPK
    ProductName



    tblSuppliers
    SupplierPK
    SupplierName



    lnkProdSupplier
    ProdSupplierPK
    ProductFK - link to ProductPK in tblProducts
    SupplierFK - link to SupplierPK in tblSuppliers

    The last table is the equivalent to the hidden dependent table June mentions but can also contain other information such as price, minimum order quantity etc

    multivalue fields are intended to be used for short values that don't change such as days of the week, months of the year - but as June says, they are not updateable to other systems so most serious developers don't use them

  6. #6
    Carloj is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    24
    I understand it, but how do i make this possible in one combobox in a form?

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    are you needing to select multiple suppliers for any given product?

  8. #8
    Carloj is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    24
    Yeh, i got a from where i can put in a new product. Sometimes it got multiple suppliers (up to 10).

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    either use a multi select listbox or a subform.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you want to continue with multi-value field, use a multi-select combobox for data entry.

    If you eliminate multi-value field and create dependent table, use a subform to enter multiple records with a combobox to select single supplier in each record.
    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. Multi-dimensional/Multi-type Array
    By Ellpee in forum Programming
    Replies: 5
    Last Post: 01-28-2016, 05:58 PM
  2. Replies: 1
    Last Post: 02-27-2014, 03:43 PM
  3. Replies: 2
    Last Post: 08-31-2012, 02:20 PM
  4. Dlookup with multi Criteria
    By rkalapura in forum Forms
    Replies: 2
    Last Post: 06-15-2012, 06:18 PM
  5. Replies: 1
    Last Post: 06-17-2011, 12:59 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