Results 1 to 8 of 8
  1. #1
    UserX is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jun 2014
    Posts
    30

    Is it possible to bind combo box to a specific row in a table

    Hello again,



    I've attached a sample database. In this database, I'd like to have the combo boxes under the Week Ending label populate the data for the fruit name to the left of each box. For example, if I choose Week Ending 2 in the box next to Banana, I'd like to have the boxes to the right of the combo box auto populate with the data in the table. I already have started some coding, but I'm not getting what I want to see.

    Here's the problem: the codes will only display the top row of data in the 9-7 and 9-14 tables, respectively. That is, if I choose week 2 for Banana and week 2 for Orange, the numbers that populate will be the same. Instead of this, what I would like to see happen is that if week 2 is selected for Orange, then have the second row of data, from the 9-7 table, populate into the textboxes on the Products form.

    Also, if there is a better approach than what I have started, please share these ideas, too.

    thank you.
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,605
    Products form has Query3 as RecordSource. Query3 doesn't exist.

    What is this db for? Why do you have separate tables for weekly data?

    How is Access supposed to 'know' to retrieve the record you perceive as the second row? Access tables do not have an intrinsic order. They are not like a spreadsheet which has row and column cell coordinate referencing. Tables are a 'bucket'. Imagine records as Lego blocks. Entering records into a table is like dropping blocks in bucket. Retrieving records (or blocks) requires criteria parameters, such as:

    "Give me all the blue, rectangular blocks."; not "Give me the second block dropped into the bucket." Now, if the blocks were marked with an identifier (1, 2, 3, etc) before they were dropped in, then I could look at each block until I found the one with 2.

    In Access, queries with parameters are used to retrieve records.

    SELECT * FROM table WHERE ID=2;

    or

    SELECT * FROM table WHERE City="New York";

    So, essentially, the answer is No.
    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
    UserX is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jun 2014
    Posts
    30
    June7,
    Sorry for not responding sooner. I was tasked with a more pressing matter that devoured my time. Now that I can take a moment to address your questions, here we go...

    First off, the names of fruits are obviously place holders for actual data, but to create a simpler sample database, I used fruits instead of the actual record 'names'.
    As for the weekly data separated into two tables, this is because I was testing some theories with Access. The end result doesn't need to have separate tables for weekly data.

    What I'm trying to do is allow users to make week-ending selection from the combobox, and then based upon that selection, display the data for that row. For example,
    If a user wanted to see what the 'numbers' for bananas for the week selected (let's call it week 1 for simplicity), then week 1's data would be pulled from the table, and shown in the text boxes next to "Banana". For the record, I'm not stuck on the method I started with.

    Recently, I tried a different approach. Instead of conditional statements in the After Update expression of the combobox, I've tried the dlookup expression in the textboxes. I've learned that this method will not work in calling more than one record at a time. Meaning, if I select week 1 in row one, I receive "#error" in every other textbox of row 2 (and any other row, if there were more). Similarly, if I try to have row 2 data show, it will, but at the sacrifice of row 1. Attached is a revised sample, in case you don't understand my feeble attempt to explain the complication.

    Now this brings me to my next questions:

    1. Is there a different way to use the dlookup expression to call forth more than one record at a time from a single table?; or
    2. Am I using the wrong method altogther?
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,605
    Domain aggregate functions (DSum, DCount, DAvg) are one way to display summary data but they can be slow performers on forms and reports.

    DLookup will return single value from first record that meets criteria. Yours fail because they use ID field as search criteria. The ID in Produce table is an autonumber field and has no relationship to the ID field in ProduceNames which is also an autonumber field. Both ID fields are defined as primary key yet neither is used as foreign key. You are saving the actual descriptive text into Produce table.

    The combobox RowSource should be:
    SELECT Produce FROM ProduceNames ORDER BY Produce;

    And the correct expression in textbox is:

    =DLookUp("[Dates]","Produce","Produce = '" & [Combo0] & "'")

    A form could be based on an aggregate (GROUP BY) Totals query.

    Even better may be a report using its Grouping & Sorting features with aggregate calcs in footer section. Report allows display of detail records as well as summary calculations.

    Forms and reports can have filter criteria applied.

    If you are not already familiar with all these basic Access functionalities, perhaps you should go through an introductory tutorial book before committing your db design.
    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
    UserX is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jun 2014
    Posts
    30
    I gotta hand it to you, June7... Not only are you expedious in your replies, but you also explained why my expression structure failed. I will take your advice (and codes ) that I may learn more about Access programming. Thank you again for your help.

  6. #6
    UserX is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jun 2014
    Posts
    30

    Is dlookup the correct method for this?

    June7,

    Sorry to bother you again, but now that I finally understand the limitation of dlookup, I'm wondering if there is a better method to attain my goal. I've attached a new db for review. In this db, I want to point out, again, that I'm not stuck on the way the forms are structured, so if you've got a better suggestion, I'd like to hear it. That is, if the subform to show all names is a poor choice, please share your thoughts on a better method. Similarly to the combobox and textbox routine. If there is a better way to call the data from the table in a easier/cleaner method, I'm all ears.

    As you explained previously, dlookup will return the first record matching the criteria specified in the expression. I had marked this thread solved because I thought I understood where I went wrong, as it turns out, I didn't. I learned two valuable lessons: 1. Despite having the expression/syntax correct, the dlookup kept showing "#error" because one of my field types was "Date/Time"; and 2. After having changed the "Week Ending" field from "Date/Time" to "Text", the dlookup will still always return the first record on the table because it's the first match.

    On to the problem...

    I want the text boxes to autofill with the values from the "balances" table, based on the date selection from the combobox. Because of dlookup's limitation for returning the first matching record, currently, any combox with "9/7/2014" will return the first record from the balances table. Instead, what I want to happen is to be able to see the budget figures of all names in the list, whether it be for last week, this week, or any other date in the future specified, or any combination thereof.

    Example:

    If I want to see Ryu's figures for last week, I would select "9/7/2014" from the combobox. Also, if I wanted to see Ken's figures from last week, I would select "9/7/2014" from the combobox.

    To reiterate the problem, if I were to do as describe above, both Ryu and Ken would have identical values in all textboxes in their rows, respectively.
    Attached Files Attached Files

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,856
    I apologize in advance. I am not trying to trash you, but there are a lot of things wrong in this dB. I fix a couple of them, but not the major items.

    At the top of every module (standard or form) should be these two lines:
    Option Compare Database
    Option Explicit


    "Option Explicit" helps ensure that all variables are declared.

    Object names should only be letters and numbers. No spaces or punctuation (underscore is the exception).
    Names in all caps is like shouting. I use camel back : "WeekEnding" is just as easy to read as "WEEK ending" without the problems due to the space. "WEEK_ending" would also be acceptable.

    In table "tblClients", "ClientID" is a text type field, while in "Balances", "ClientID" is a Long.
    They are supposed to be related but the fields are of different types. Can't do that. I fixed that in the attached dB.

    I don't understand why "ID" is the PK, but you are using "ClientID" to related the tables.



    Now, as to why the DLookup() wasn't returning the correct amounts. There are 10 records with "9/7/2014". You weren't providing a client number, so DLookup() returned the first record it found matching the parameter.

    The form "Budget" returns the correct amounts, but I added a lot of VBA. Clicking on a name puts the Client id into the yellow box (which normally would be hidden).

    In this case, the subform is (IMO) a little clunky, so I created a 2nd form "Budget2".
    "Budget2" utilizes a list box for the client names. I also added a CLEAR button on both forms.


    Crystal has a good "Basics" site http://www.accessmvp.com/strive4peace/
    Roger has a tutorial that is useful http://www.rogersaccesslibrary.com/forum/forum46.html
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  8. #8
    UserX is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jun 2014
    Posts
    30
    Thanks for your reply, Steve. I'm learning as I go. I've kind of been thrown to the wolves here with these projects. The reason for the PK mix up is because I'm trying to learn how table relationships work. I thought that by somehow linking the two fields would help me, but as you can see, I don't know what I'm doing. I'll take a look at your modifications.


    Edit: Steve, you are the man. I had a feeling that dLookup wasn't the way to go here. I really like the mods you made. Thank you so much for your time and help. Thanks for the links, too. Time to do some reading.
    Last edited by UserX; 09-10-2014 at 07:06 PM. Reason: To say "thanks!"

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

Similar Threads

  1. Help new user in a bind
    By jagsfan4life in forum Programming
    Replies: 1
    Last Post: 04-22-2014, 07:13 AM
  2. Bind one combo box to another one
    By Zamani_2012 in forum Forms
    Replies: 1
    Last Post: 08-12-2012, 09:51 AM
  3. Text Box Won't Bind
    By HowardOfOcal in forum Forms
    Replies: 10
    Last Post: 01-30-2012, 02:57 PM
  4. 1,000,000 number bind
    By Lathian in forum Access
    Replies: 7
    Last Post: 09-12-2011, 01:43 PM
  5. Replies: 6
    Last Post: 06-13-2011, 12:14 PM

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 - Senior Forums