Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    maxmaggot is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Location
    Ireland
    Posts
    91

    Question Find a record using combo box using different table for values


    I have a combobox that filters expense types based on an option the user selects. I have recently updaetd the database structure. the expense type names are now in a related table and the expenses table just stores the ID for each of these expenses. The problem is the combobox to find the records now only displays the IDs in the Expenses tabel. Is there a way to show the names from the related table and filter the records.

    I have tried a few things bt I'm a bit lost on this as I have never encountered it before. Any ideas? Thank you.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Add both fields to the row source of the combo, make sure the ID is the bound column, and use the Column Widths property to hide the ID. The combo wizard should walk you through it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    maxmaggot is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Location
    Ireland
    Posts
    91

    Thank you - nearly there

    Thank you very much,

    It is picking up the correct values now but when I choose one of the drop down values it displays the wrong filtered results. I've attached two pngs if that helps.

    Am I missing something simple

    I can PM db as there is private info in it if that helps.
    Last edited by maxmaggot; 07-02-2013 at 05:43 PM. Reason: Additional Indo

  4. #4
    maxmaggot is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Location
    Ireland
    Posts
    91
    Any ideas anyone? I'm sure its a small thing now but these small things always catch me then I kick myself when it is pointed out to me.

  5. #5
    maxmaggot is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Location
    Ireland
    Posts
    91

    Exclamation Nearly got it

    I can get the combo to filter the record to the correct type but it only shows the first couple of expense types instead of filtering all of the records.

    Please see attached screen shots of query and macro

  6. #6
    maxmaggot is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Location
    Ireland
    Posts
    91
    Any luck anybody. I think it is a filteron=true that needs to go into a macro. Trying to figure out how to do that.

  7. #7
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    OOOO - linking a field [Expenses].[Expense Type] to [Expense Type].[ID] when there's also an [Expense Type].[ExpenseType] field. You like to live dangerously.

    For your own sanity, you should change the names in table [Expense Type] to something less confusing. Of course, I'd put "tbl" on the front of the table names and kill the internal spaces as well. Here's what I'd do -
    Code:
    [Expense Type]  tblExpenseType
    ID              ExpenseID 
    ExpenseType     ExpenseDesc
    Okay, so that query will return... a single record for each Expense Type in Expenses, with two columns of the value of ID, and one column of the Expense Description from [ExpenseType], I think.

    And assuming you have the syntax right, your macro will then search for the next record where [Expense Type] = {the ExpenseID selected}

    Are you trying to filter the results, or are you trying to move to a record? Shouldn't that macro action be ApplyFilter?

  8. #8
    maxmaggot is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Location
    Ireland
    Posts
    91
    HA HA, lesson learnt painfully about table names.. I have a combo with a macro for find record but that should be apply filter?

    thanks for the help.

    I was a bit cheeky and PM'd you. If you haven't time to look at it don't worry. I've a deadline of tomorrow and it's the issue that is most visible to the end user.
    Last edited by maxmaggot; 07-03-2013 at 12:42 PM. Reason: Update

  9. #9
    maxmaggot is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Location
    Ireland
    Posts
    91
    I applied the filter but I'm new to it and I'm getting errors. Would it be possible to PM the db to you to look at. I'm nervous at this stage of messing it up. It took ages to get the Expenses table right.

  10. #10
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Ummm.

    Rule Number One - back up your database often. I make a copy with the date and an alpha character after each successful mod. For example, the third today would be "mydb 2013-0703C.accdb"

    Rule Number Two - do all fumbling and testing in a play version of the database, not the prod and not the backup.

    Rule Number Three - when you figure out how to make it work, save that play database, then take a fresh copy of the prod database, make your changes there, and test thoroughly. If it works, back up the new prod database after the fix, and move on to your next item on your punch list.

    Apply some policies like this that will work for you, and you won't have to be so nervous about screwing up. I screw up all the time. No harm, no foul. Usually, I learn something useful. Occasionally, I never know what happened.

  11. #11
    maxmaggot is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Location
    Ireland
    Posts
    91
    Ok Thanks to Dal Jeanis I figured out that i'm looking for a filter macro rather than find a record. Still figuring out how to use the damn thing. I'm not enjoying these learning curves.

    Thansk to Dal for pointing me in the right direction

  12. #12
    maxmaggot is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Location
    Ireland
    Posts
    91
    Just a quick addon that has cropped up. Since I changed DB design I cannot get a running total for the current expense type. Here is the code I am trying
    Code:
    Public Function UpdateRunningExpenseTotal()
        Me.txtRunningTotalExpenseType = Nz(DSum("[Amount]", "Expenses", Me.[Expense Type] = Me.cboExpenseType.Column(0)))
    End Function
    I think Me.[Expense Type] should erfer to the table ExpenseType and the field ID but I can't get it working.

    I'm trying to get a total amount that the company has spent on a particular expense so far. It was working until I normalised the db. If anyone can look at this while looking at the other issue that would be great.

    Sorry for all the requests.
    Last edited by maxmaggot; 07-03-2013 at 07:43 PM. Reason: Addition

  13. #13
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Okay, the first thing to know about DSum and all its aggregate function cousins is that the fields that you pass it must be text strings.

    The strings will be evaluated *before* you call the DSum function, and then DSum will magically build those exact terms into an SQL query string.

    Your last term probably needs to compare something that is on the database to something else. So that last term should be coded something like
    Code:
    "[Expense Type] = " & Me.cboExpenseType.Column(0)
    and will be evaluated to a string that reads
    Code:
    "[Expense Type] = 13"
    If the column were a text field instead of a number, then that last term should be coded something like
    Code:
    "[Expense Type] = '" & Me.cboExpenseType.Column(0) & "'"
    and will be evaluated to a string that reads
    Code:
    "[Expense Type] = 'Party Supplies'"
    If the column were a date field instead of a number, then that last term should be coded something like
    Code:
    "[Expense Type] = #" & Me.cboExpenseType.Column(0) & "#"
    and will be evaluated to a string that reads (assuming American Date order)
    Code:
    "[Expense Type] = #07/04/2013#"
    One of those strings will be passed and concatenated onto the end of the "WHERE" clause of the SQL that will calculate DSum for you.

    So, the function DSum("[a]", "[b]","[c] = d") gets transformed into the equivalent SQL call of
    Code:
    SELECT SUM([a]) FROM [b] WHERE [c] = d;

  14. #14
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Okay, I have your database. What was the precise issue?

  15. #15
    maxmaggot is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Location
    Ireland
    Posts
    91
    It was filtering the expense type using the combo box at the top. I put in the macro but I can't get it working. Thanks for the advice

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-12-2012, 03:52 AM
  2. Combo to find record on report
    By djclntn in forum Reports
    Replies: 2
    Last Post: 04-03-2012, 02:54 PM
  3. VBA: Find Record from in table from form
    By shimmy84 in forum Programming
    Replies: 6
    Last Post: 03-19-2012, 10:51 PM
  4. best way to find a record in a linked table
    By BRV in forum Programming
    Replies: 14
    Last Post: 11-10-2011, 09:31 AM
  5. Replies: 5
    Last Post: 11-13-2010, 04:56 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