Results 1 to 8 of 8

Lookup a value from another table

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

    Lookup a value from another table


    I got a problem with my order form. In this order form is an option to select a product code. Now i want to show the product name in a textbox below. The form itself is based on the order table but the productname is in the product table. Is there a way (without vba) to get the name when the productcode is selected?

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,144
    Show us what you have so far. We can not guess your set up and details.
    Post a copy of the database with instructions to get to the form and issue.

  3. #3
    Carloj is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    24
    I got the following:

    I select the product code like this:
    Click image for larger version. 

Name:	order.PNG 
Views:	28 
Size:	10.3 KB 
ID:	35038
    And the yellow marking should show the product name. I got this expression now:
    Code:
    SELECT ArtikelID, Artikel_naam FROM tblArtikel WHERE (ArtikelID=Forms![Formnieuweorder]!Keuzelijst220);
    (Keuzelijst220 is the combo box with productcode.)

    The tables are like this:
    Click image for larger version. 

Name:	err.PNG 
Views:	26 
Size:	14.8 KB 
ID:	35039

  4. #4
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    1,545
    Use this as datasource for the textbox. It's unclear from your post whether the table name is tblArtikel or tblArtikelen. This code assumes its tblArtikel and
    your ArtikelID is alphanumeric, like 100295-1
    Code:
    =Dlookup("Artikel_naem","tblAtrikel","ArtikelID='" & Keuzelijst220 & "'")
    Last edited by davegri; 08-09-2018 at 07:20 AM. Reason: clarif

  5. #5
    Carloj is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    24
    It isn't working for me.

    I had this as dlookup, but both (this one and yours) give me #FOUT.
    Code:
    =DLookUp("[Artikel_naam]";"tblAtrikel";"[ArtikelID]=" & [Form]![Keuzelijst220] & "")

  6. #6
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    1,545
    You said without VBA, but I don't see a way to do that with a macro.
    Assume the name of the textbox you want to update is txtProductName

    There are two ways the ProductName can change:
    1. When you change the ProductID with the combobox.
    2. As you scroll thru the records on the form.

    Each of these will require VBA code to update the textbox.
    First, the combobox change:

    Code:
    Private Sub Keuzelijst220_AfterUpdate()
        txtProductName =DLookUp("[Artikel_naam]";"tblAtrikel";"[ArtikelID]='" & [Keuzelijst220] & "'")
    End Sub
    Then scrolling:

    Code:
    Private sub Form_Current()
        If not isnull(Keuzelijst220) then
            Call Keuzelijst220_AfterUpdate
        end if
    end sub
    If you feel you are unable to insert the code properly, post you DB and we can get a working copy back to you in short order.

    Also just noticed that you did not apply the Dlookup in post #4 properly. You left out the single quotes:
    Code:
    =Dlookup("Artikel_naem","tblAtrikel","ArtikelID='" & Keuzelijst220 & "'")
    
    
    You might try that again before trying the VBA.
    Last edited by davegri; 08-10-2018 at 07:21 AM. Reason: syntax

  7. #7
    Carloj is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    24
    Took me some time but i found my fault. The table is called "tblArtikel" instead of "tblAtrikel". That's the reason why it wouldn't work for me.

    Thanks alot!

  8. #8
    Join Date
    Apr 2017
    Posts
    717
    Why not to display product name in combobox?
    Have the RowSource Property for combo like
    (when you want product/article ID not displayed)
    Code:
    SELECT ArtikelID, Artikel_naam FROM tblArtikel;
    or like (when you want product/article ID displayed along with name)
    Code:
    SELECT ArtikelID, ArtikleID & ": " & Artikel_naam AS ArtikelNaam FROM tblArtikel;
    Set for combo ColumnCount to 2, BoundColumn to 1, ControlSource to ArticleID, and ColumnWidths to "0;2.5".

    Another thing to consider - are you sure you never have several products to order? When you want to order several products with same order, you need orders table where order header info (order number, order and delivery dates, customer/supplier, etc.) is stored, and order rows table, where for every order, one or several rows with ordered product ID's, product quantities and prices, etc. are stored. The order rows table also has order numbers as Foreign Key. Then you can have a continuous subform on order form, linked to order form through order ID, where all products ordered for current order are displayed. Any product added into subform will have product ID automatically inserted.

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

Similar Threads

  1. Replies: 10
    Last Post: 01-07-2018, 01:24 PM
  2. Replies: 3
    Last Post: 06-02-2017, 02:49 PM
  3. Replies: 5
    Last Post: 01-23-2015, 02:38 PM
  4. Replies: 5
    Last Post: 06-25-2014, 08:19 AM
  5. Replies: 8
    Last Post: 03-22-2012, 07:48 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
  •  
Tech Forums: Microsoft Office Forums