none
Something similar to "VLookup" in Excel RRS feed

  • Question

  • I have a combo box in a form that displays a list of selections from one table and the selection itself is bound to another table. The primary key of the selected item is displayed in this table, how can I have the entire row of data from the selected field displayed in this table. Or am I going about this all wrong.

    In the end I need to be able to reference the record that has been selected in the combo box in a table

    IE

    Primary Key, Field 1, Field 2, etc...

    Monday, November 23, 2015 6:37 PM

Answers

  • Hi Rob,

    >> For example when the user selects the property number in the combo box I would like both the property number and name displayed in this table in two fields so that i can use this information in a query to generate reports.

    Based on this, it seems you want to filter the report according the combobx, am I right? If so, I suggest you set all the data as the Report data source, and use DoCmd.OpenReport with filter expression in Combo14_Change event.

    Here is a simple code:

    Private Sub Combo14_Change()
    Debug.Print "Name= " & "'" & Me.Combo14.Value & "'"
    DoCmd.OpenReport "FilterTable", acViewReport, , "Name= " & "'" & Me.Combo14.Value & "'"
    End Sub

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, November 24, 2015 2:55 AM

All replies

  • Hi,

    In Access, you typically use forms as a user interface. You shouldn't let your users see the tables. As such, storing the ID field of the lookup table is all you need. When you want to display all the other information about that ID, you can use a query to join the lookup table to pull all the additional fields to show on your form or report. Hope that helps...

    Monday, November 23, 2015 7:02 PM
  • The tables are hidden as the users use the Forms as the interface. I have a hidden table that I would like to update with the current selection on a combo box I am able to only get it to display the primary key from the selection is there a way to have the other information displayed in this table as well?

    For example when the user selects the property number in the combo box I would like both the property number and name displayed in this table in two fields so that i can use this information in a query to generate reports.

    Monday, November 23, 2015 7:11 PM
  • Hi,

    As I was saying earlier, there's no need to store more than just the ID in the table. If you need the other information in another query or report, just JOIN the query with the lookup table. Does that make sense? For example, let's say the name of your hidden table is tblProducts with a ProductID and ProductName fields. So, to display the product name in your report, just use the following query:

    SELECT tblOrders.CustomerName, tblOrders.ProductID, tblProducst.ProductName
    FROM tblOrders
    INNER JOIN tblProducts
    ON tblOrders.ProductID=tblProducts.ProductID

    Hope that helps...


    • Edited by .theDBguy Monday, November 23, 2015 8:01 PM
    Monday, November 23, 2015 8:00 PM
  • Getting a little better... how about a query based on a combobox selection?
    Monday, November 23, 2015 8:06 PM
  • If the combobox is also MVF, the using the Value field in the RowSource should also work. If you can send me a copy of your file, I could try it out for you.
    Monday, November 23, 2015 8:27 PM
  • Hi Rob,

    >> For example when the user selects the property number in the combo box I would like both the property number and name displayed in this table in two fields so that i can use this information in a query to generate reports.

    Based on this, it seems you want to filter the report according the combobx, am I right? If so, I suggest you set all the data as the Report data source, and use DoCmd.OpenReport with filter expression in Combo14_Change event.

    Here is a simple code:

    Private Sub Combo14_Change()
    Debug.Print "Name= " & "'" & Me.Combo14.Value & "'"
    DoCmd.OpenReport "FilterTable", acViewReport, , "Name= " & "'" & Me.Combo14.Value & "'"
    End Sub

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, November 24, 2015 2:55 AM