Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410

    ComboBox and displaying record source?

    Hello,


    So I have a form with Tab subforms and need to have search options from a few of the fields on the main form.

    They are:
    MemberID
    JobClassID
    ShopID

    Anyway, I am using the setting to find the record when entered/selected in ComboBox.
    However the combo boxes do not display record info.
    Further, when I go from MemberID to JobClassID, the MemberID does not change to what the record is for the entered/selected JobClassID.

    All the other fields change but none of the ComboBoxes do not display the associated results.

    Hope this makes sense?

    So say I enter MemberID 161 and I get info on that record in the fields on the main form and the subforms on the Tabs where related.
    However, the other comboboxes named above do NOT display the data for MemberID 161.
    Then when I go to search on JobClassID, same thing, the other comboboxes do not change to match and not showing where I haven't done a search on yet.

    What do I need to do / add so that these comboboxes results in the related data when one or the other is searched on and display the result?
    Last edited by aellistechsupport; 05-23-2017 at 01:05 PM. Reason: spelling

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    The comboboxes are not bound to the underlying table, like the textboxes are. The easiest workaround is to add bound textboxes for the 4 fields. Kind of a challenge to keep the form from appearing confusing though.

  3. #3
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    I did that but having two for the same field?

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Search comboboxes are not for displaying data in the underlying table. They display search criteria and only do that when dropped down and a selection is made.
    You CAN force them to display data, but you will have to create querys on the fly for each of the combobox's rowsources, depending on the criteria selected from the others.
    You have other problems. None of your tables have a primary key. Therefore you have no relationships established between tables. IMO you need to work on your database table design before getting into form design.

  5. #5
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Sorry, I must have written something incorrectly.

    Code:
    the combo boxes do not display record info.
    Further, when I go from MemberID to JobClassID, the MemberID does not change to what the record is for the entered/selected JobClassID.
    
    All the other fields change but none of the ComboBoxes do not display the associated results.
    So in another words, I need the ComboBoxes to display the results. It is NOT. It's blank initially until search info is entered or selected in the ComboBox.
    I am getting the results in the other fields and subforms.
    However, the selected/entered info in the ComboBox does not display in the other ComboBoxes.

    Meaning, I entered MemberID in that ComboBox and that data remains and the results in the text boxes & subforms BUT the other ComboBoxes are blank.
    AND when going to the other ComboBox, like JobClassID, the data that was originally entered/selected in the MemberID does not change to match what was entered/selected in the JobClassID. It doesn't clear or update.

    NOTE: I AM getting the results from the selection ONLY that the ComboBoxes are NOT displaying the correct info. All the other fields are. That is NOT the problem. Again, the issue is the ComboBoxes themselves not displaying the data set or retaining what was selected when another selection is made from the other ComboBoxes.

    Does that clear it up?

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I understand completely. You don't. Read post #2 again. In design view look at the comboboxes. Right inside them it says UNBOUND. If any control is UNBOUND, it will not draw any data from your tables.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Does that clear it up?
    Unfortunately no. At least not for me. You wrote
    At the moment cascading isn't requested ...
    yet your writing suggests it's needed
    the selected/entered info in the ComboBox does not display in the other ComboBoxes
    . But why would you want what's selected in one combo to appear in another one? The posts also read like when you choose another value (downstream?) you want other combos to change their contents (upstream? downstream?) to something else - maybe to nothing?

    You have to realize we don't know the order of control placement and choices, plus when you flip between "fields" and "controls" it's hard to know if controls are the combos and fields are on the subforms you mentioned. Plus, I don't know how to interpret this double negative other than to think if none do not, then they must all do what you want:
    All the other fields change but none of the ComboBoxes do not display the associated results.
    Let's say the order of combo choices is alphabetical. Sounds like when you choose from comboA, you don't need comboB to filter its list (that would be cascading) but if you pick something from comboB, you want all the others to dump whatever they have in them. That requires a Requery method on every combo except the one that just changes. Sorry if that's not it. In that case, try reviewing your posts and imagine how difficult it can be to make valid suggestions if one cannot see your form, has no idea of the order of events and doesn't grasp the goal. Then try again and we'll see what we can do. We're here to help, although at times it may seem we're being critical.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Sorry, it really does not sound like I am making myself clear.
    Perhaps by adding the not needing the cascading comment threw this off issue.

    The Member data that contains the fields that are set to comboboxes have data in them.
    A Member (MemberID) has a JobClassID, & ShopID. (not always but most do).
    They want to option to search on any one of these fields. Which I am using the ComboBox option so that they only enter or select from existing values.

    As the attempt in my example, say MemberID 161 is entered, and they have a value in JobClassID, and ShopID, these 3 ComboBoxes do not display the related data. ONLY if i have them as fields like the other fields will it show the related value.
    I need them to display the value in the ComboBox as well.
    So for MemberID 161, these example values for the other ComboBoxes are:
    JobClass 234
    ShopID 123

    But these 3 show blank UNLESS I had previously use them for selection.
    So when the value is changed in one or the other ComboBox, it should update as well.
    Let's say I selected/entered JobClassID 156 and we know that the MemberID is 234, etc ... but from my previous selection on above for MemberID 161, instead of updating it to 234 to match the newly selected JobClassID 156, it keeps the MemberID 161 in the ComboBox.

    When I have these 4 not as ComboBoxes, like the other fields, it displays the related data as expected. Just NOT in the ComboBoxes.
    My question is how do I code? these ComboBoxes to display the correct value?
    Last edited by aellistechsupport; 05-23-2017 at 01:06 PM.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If I get it now, this is a bit unusual.
    MemberID 161 is entered, and they have a value in JobClassID, ShopID, and BargainingUnitID, these 3 ComboBoxes do not display the related data.
    This is not relevant, because
    They want to option to search on any one of these fields
    Why worry about what's displayed down stream if it's not going to be used?

    It seems you want the user experience to be like this:
    If I pick from A, I want B and C to populate their row data based on this choice. That is normal cascading, but nothing shows in those controls until I pick a value from their list - because there's seldom one row that results. If there always is, the process makes no sense. Just display those singular values in the resulting records.

    If I pick from B, I want A and C to populate their row data based on this choice. However, the lists in A and C may not be related to this selection, thus they have to be cleared and their lists regenerated. So what good is it to then select from either A or C again, since the nature of the operation requires the db to now do something with the choice I just made - clear it and refresh its list? Plus, the examples given suggest that A holds distinct values (there's only 1 member with the ID that's been chosen) while any value chosen from B can have several members associated with that JobClassID. So which of the many members belonging to the B selection should we display in A? Never mind, since I've already said we have to clear A and regenerate its list.

    I don't see how anything like what you describe can ever work and I don't think you understand the concept of a db search form. Maybe you should Google 'ms access search form' and see if you ought to be taking a different approach. You wrote how do I code? these ComboBoxes to display the correct value? The answer is, you don't. They're for displaying the value chosen from a list, not for populating based on some other choice - especially from the many side of a relationship (JobClass vs MemberID).
    Last edited by Micron; 05-23-2017 at 11:26 AM. Reason: correction

  10. #10
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Here are some screenshots that may be more helpful to what I'm trying to get help with

  11. #11
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    I think I have a different understanding of Cascading
    My understanding of it is it reduces the records to from each selection

    For instance:
    First I select State, say CA
    Then I will only see Cities for CA

    That is not what's happening here nor what I'm trying to do.
    Just display the data for what's been entered or selected in the ComboBox ALL the way, changing the displayed data to also match what's been entered/selected from another ComboBox.

    So for instance, there are, just say 200,000 records on the Members (main form).
    The user is simply searching on any of the ComboBoxes listed.
    It will NOT reduce the records from 200,000, it'll just go to that record where the search has been entered/selected.
    Just need code? to have these ComboBoxes display the records info and that retain the previously entered/selected entry while the other fields changed. And as you can, bound field display the changed value, just not in the ComboBox.

  12. #12
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    You can do this:
    Hide the textboxes and make the comboboxes visible.
    Rename the textboxes and comboboxes in a fashion similar to below.
    The idea is in the after update event of each combobox, you move the contents of the hidden textboxes to the respective comboboxes.
    Code each of the comboboxes as below.

    Code:
    Private Sub cboMembershipID_AfterUpdate()
        Dim rs As DAO.Recordset
        If Not IsNull(Me.JobclassId) Then
            'Save before move.
            If Me.Dirty Then
                Me.Dirty = False
            End If
            'Search in the clone set.
            Set rs = Me.RecordsetClone
            rs.FindFirst "[MembershipID] = '" & cboMembershipID & "'"
            If rs.NoMatch Then
                MsgBox "Not found: filtered?"
            Else
                'Display the found record in the form.
                Me.Bookmark = rs.Bookmark
            End If
            Set rs = Nothing
            cboShopID = txtShopID
            cboJobClassID = txtJobClassID
            cboBargainingUnitID = txtBargainingUnitID
        End If
    End Sub
    This works fine for the membership ID because it is unique.
    The problem is, for example, the shopID afterupdate. The ShopID occurs hundreds of times in the table. Which one do you want to see? The code will select one row of data in the table arbitrarily.

  13. #13
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Ah, thank you! Going to try this now.

    Not sure if the images came through? Not seeing in my post above?

  14. #14
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Ok, played with it a bit to see if I can get it to work.

    What I'm seeing is that it ONLY works when MemberID is selected/entered.
    Meaning, I am finally getting the JobClassID and ShopID comboboxes to display the related value of the MemberID

    However if JobClassID or ShopID is entered/selected, the other combobox along with the MemberID combobox is not updating to the related value.

    So I applied that to JobClassID AfterUpdate replacing the parameters to match.

    It WORKS! THANK YOU!

    However ... ONLY if you enter/select from the ComboBox.
    As you've mentioned, on the JobClassID and ShopID, there are multiple members with same of these IDs.
    I clicked to next record but then the ComboBox data doesn't update /change along with the record like it was doing before.

    How do I add to so it changes when clicking on Next Record for the same JobClassID OR ShopID for the next MemberID record?
    It changes all the other fields as they should. Just the ComboBox issues again on Next Record. THanks again!

  15. #15
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Post #4
    Quote Originally Posted by davegri View Post
    <snip>
    You have other problems. None of your tables have a primary key. Therefore you have no relationships established between tables. IMO you need to work on your database table design before getting into form design.
    I'm not throwing davegri under the bus, because I have to agree with him. I also think you really need to work on the dB design.

    Some of your tables do have a PK, but they are text fields... and you have some compound primary keys.

    You should read this a few times
    Microsoft Access Tables: Primary Key Tips and Techniques
    http://www.fmsinc.com/free/newtips/primarykey.asp

    I am totally astounded at the number of linked tables in the dB. Are they all in use?
    In the dB, I found 13 local tables and 519 linked ODBC tables. I always thought that 255 was the max number of tables allowed. So maybe it must be 255 local tables??


    Would you care to describe what your "business" is???



    BTW, looking at one of the combo boxes, I see that the default property (for an unbound control) is set to a text string " ="Select / Enter Member ID" ". I never realized that could be done.... thanks for that...learned something new.....

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

Similar Threads

  1. Replies: 5
    Last Post: 09-18-2013, 09:15 PM
  2. Replies: 2
    Last Post: 05-13-2011, 02:34 PM
  3. Form not displaying source table data
    By Sarge, USMC in forum Forms
    Replies: 6
    Last Post: 10-25-2010, 07:36 AM
  4. linked table displaying different data than source
    By wil in forum Import/Export Data
    Replies: 1
    Last Post: 08-06-2010, 07:13 AM
  5. ComboBox Row Source Value
    By mpbertha in forum Forms
    Replies: 1
    Last Post: 08-21-2009, 06:34 AM

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