Results 1 to 5 of 5
  1. #1
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119

    set record display based on button selection

    Hi guys,
    I have a small database with 4 tables (Item, ItemCategory, Sale, SaleDetails). And 2 forms :FRM_Sales as the parents form and FRM_SaleDetails as a subform.
    I also have 2 buttons "FOOD ITEMS" and "DRINK ITEMS" which i placed on the subform FRM_SaleDetails.

    Now i want to be able to do the following:

    - When about to enter a new record, if i click on the button "FOOD ITEMS", the combo box "ItemName" should display only food items. The same should happen when i click on "DRINK ITEMS" button.

    - when records already exist, then when clicking on "FOOD ITEMS" button only food related records should be displayed. The same should happen when i click on "DRINK ITEMS" button.



    Can someone help me out?

    Kindly see the attached sample database to better understand what i mean. SampleDb2.zip

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    To filter records by category, will need the CatID in the subform's RecordSource. Do not need the Sale table in that query. Instead of referencing query object, could put SQL statement in the RecordSource.

    SELECT SALE_DETAILS.*, Item.*, [QtyOrdered]*[ItemPrice] AS Total FROM Item INNER JOIN SALE_DETAILS ON Item.ItemID = SALE_DETAILS.ItemID;

    Autonumber PK usually has no meaning to users and does not need to be displayed.

    Rename the combobox to cbxItem.

    Then code in the button Click events like:

    Code:
    Private Sub CmdDrink_Click()
    Me.cbxItem.RowSource = "SELECT ItemID, ItemName FROM Item WHERE CatID=2 ORDER BY ItemName;"
    Me.cbxItem.Requery
    Me.Filter = "CatID=2"
    Me.FilterOn = True
    End Sub
    
    Private Sub CmdFood_Click()
    Me.cbxItem.RowSource = "SELECT ItemID, ItemName FROM Item WHERE CatID=1 ORDER BY ItemName;"
    Me.cbxItem.Requery
    Me.Filter = "CatID=1"
    Me.FilterOn = True
    End Sub
    Alternatively, could have two subforms placed on pages of a Tab control. Each subform would have its Filter and FilterOn properties set in design to filter the appropriate category. The combobox could also have static filter criteria. No VBA needed. But this would mean building two forms that are identical, just with different filter properties. This would simplify the grand totals you want to calculate on the main form. Otherwise, you will need DSum() domain aggregate functions to provide those calcs.

    Heineken Beer is coded as a food item.
    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
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    @ June7
    Thank you very much. It works as expected.

    Can you please help me out with the FoodItem Total, DrinkItem Total as well as the Grand Total?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    =DSum("Total","Qry_Sale_Details","Sale.SaleID=" & [SaleID] & " AND CatID=1")

    =DSum("Total","Qry_Sale_Details","Sale.SaleID=" & [SaleID] & " AND CatID=2")

    =[TxtTotalFood]+[TxtTotalDrink]

    Be sure to add CatID field to the query.
    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
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    @ June7
    Everything works great, Thank you for your time and concern. I really appreciate.

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

Similar Threads

  1. Replies: 3
    Last Post: 06-03-2015, 10:16 AM
  2. Replies: 3
    Last Post: 11-05-2014, 02:43 AM
  3. More Info" button based on Combo Box selection
    By kriskeven in forum Access
    Replies: 1
    Last Post: 05-21-2012, 02:23 PM
  4. Display image based on combobox selection?
    By 10 Gauge in forum Forms
    Replies: 2
    Last Post: 09-15-2011, 07:42 AM
  5. Replies: 3
    Last Post: 12-06-2010, 06:35 PM

Tags for this Thread

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