none
Question on Query Builder RRS feed

  • Question

  • Hello:

    I have an item master that get's imported nightly from a corporate Non-Access database that contains the full set of over 1000 items.  I'm building a report that requires a subset of those items.  In a separate table, I'm going to have the user enter the item numbers of selected items to include in the report.

    The table of selected items only contains 1 field:  The Item number.  They might only want 80 items to show up on the report (out of a total of 1000 available).

    My objective is to have the actual item name pop up when the item number is entered in this single field table.  I tried linking the selection table to the actual item master using the query builder, but it doesn't bring up the item name when a new item is entered.

    What would be a good way... as soon as the user enters an item number, the item description displays.  If I use a "continuous" form with an unbound field next to it and populate it with a name using the find feature, the name only shows up when the user enters the item, but not on any previously entered items since there is no event.

    Ideas?

    Thanks,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com



    • Edited by RichLocus Tuesday, September 12, 2017 4:47 PM
    Tuesday, September 12, 2017 4:45 PM

Answers

  • My objective is to have the actual item name pop up when the item number is entered in this single field table.


    If you bind a form, in continuous forms view, to the Selection table, you can bind a combo box to the ItemNumber column and set it up to return the ItemDescription from the ItemMaster table in a second, e.g.

    Name:    cboItem

    RowSource:
    SELECT ItemNumber, ItemDescription,
    FROM ItemMaster
    ORDER BY ItemNumber;

    ControlSource:    ItemNumber
    BoundColumn:    1
    ColumnCount:     2
    ColumnWidths:   2cm;6cm
    ListWidth:           8cm

    Adjust the ColumnWidths dimensions to get the best fit.  The ListWidth property should be the sum of the ColumnWidths dimensions.  By virtue of the AutoExpand property the combo box will go progressively to the first match as the user enters each character of the ItemNumber.

    Add an unbound text box to the form's detail section, with a ControlSource property of:

    =cboItem.Column(1)

    The Column property is zero-based, so this will show the second column, ItemDescription, when an item is selected in the combo box.  Unlike assigning a value to an unbound text box, this will show the relevant description for the selected item number in each row.


    Ken Sheridan, Stafford, England

    • Marked as answer by RichLocus Tuesday, September 12, 2017 11:54 PM
    Tuesday, September 12, 2017 10:08 PM

All replies

  • Hi Rich,

    Not sure I understand. If you create a query joining the two tables, you should be able to drag the item name field from the one table so that when the user enters an item number matching a record in the first table, the item name should show up (I think).

    Hope it helps...

    Tuesday, September 12, 2017 6:22 PM
  • Hello dbGuy:

    I'll experiment with your suggestion.
    Update:  Can't make it work.  Will upload a simple example.

    Thanks,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com




    • Edited by RichLocus Tuesday, September 12, 2017 8:19 PM
    Tuesday, September 12, 2017 8:08 PM
  • My objective is to have the actual item name pop up when the item number is entered in this single field table.


    If you bind a form, in continuous forms view, to the Selection table, you can bind a combo box to the ItemNumber column and set it up to return the ItemDescription from the ItemMaster table in a second, e.g.

    Name:    cboItem

    RowSource:
    SELECT ItemNumber, ItemDescription,
    FROM ItemMaster
    ORDER BY ItemNumber;

    ControlSource:    ItemNumber
    BoundColumn:    1
    ColumnCount:     2
    ColumnWidths:   2cm;6cm
    ListWidth:           8cm

    Adjust the ColumnWidths dimensions to get the best fit.  The ListWidth property should be the sum of the ColumnWidths dimensions.  By virtue of the AutoExpand property the combo box will go progressively to the first match as the user enters each character of the ItemNumber.

    Add an unbound text box to the form's detail section, with a ControlSource property of:

    =cboItem.Column(1)

    The Column property is zero-based, so this will show the second column, ItemDescription, when an item is selected in the combo box.  Unlike assigning a value to an unbound text box, this will show the relevant description for the selected item number in each row.


    Ken Sheridan, Stafford, England

    • Marked as answer by RichLocus Tuesday, September 12, 2017 11:54 PM
    Tuesday, September 12, 2017 10:08 PM
  • If you create a query joining the two tables, you should be able to drag the item name field from the one table so that when the user enters an item number matching a record in the first table, the item name should show up (I think).
    True, but to get it to work properly it's important that an enforced relationship is created between the two tables and that the keys are indexed appropriately.

    Ken Sheridan, Stafford, England

    Tuesday, September 12, 2017 10:15 PM