Results 1 to 10 of 10

Dlookup in multi value

  1. #1
    Carloj is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    22

    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
    45,289
    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?
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  3. #3
    Carloj is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    22
    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
    45,289
    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.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  5. #5
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,313
    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
    22
    I understand it, but how do i make this possible in one combobox in a form?

  7. #7
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,313
    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
    22
    Yeh, i got a from where i can put in a new product. Sometimes it got multiple suppliers (up to 10).

  9. #9
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,313
    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
    45,289
    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.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

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, 04:58 PM
  2. Replies: 1
    Last Post: 02-27-2014, 02:43 PM
  3. Replies: 2
    Last Post: 08-31-2012, 01:20 PM
  4. Dlookup with multi Criteria
    By rkalapura in forum Forms
    Replies: 2
    Last Post: 06-15-2012, 05:18 PM
  5. Replies: 1
    Last Post: 06-16-2011, 11:59 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
  •  
Tech Forums: Microsoft Office Forums