Results 1 to 11 of 11
  1. #1
    MikeN is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Posts
    39

    Listbox to select record

    Alright, I feel like I have looked all over to try to figure out why my listbox won't open up a specific (selected) record. Here's what I have:

    I have two listboxes (lstClients and lstProjects). Making a selection in lstClients populates the associated projects in lstProjects. I'm using On Dbl Click to open up the details of the selected options. The double-click for lstClients opens a form (NewClient) that has the client information - this action is working as desired with:
    Code:
        DoCmd.OpenForm "NewClient", , , "[UserID] = " & lstClients.Column(0)
    However, it just opens a new record when I try to do the same with lstProjects to open the form (Projects)
    Code:
        DoCmd.OpenForm "Projects", , , "[ProjectID] = " & lstProjects.Column(0)
    The listboxes use SELECT Clients.UserID, Clients.FirstName,... (for lstClients) and SELECT Projects.ProjectID, Clients.UserID (as filter criteria between the first and second list box), Project.Name,... (for lstProjects) with column 1 being the bound column for both listboxes. The listboxes also are MultiSelect: None if that has any influence. UserID and ProjectID are also numeric.



    Any thoughts on why the double-click action in lstProjects ONLY opens a new record versus firing the WHERE clause?

  2. #2
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    sounds like the problem is with your projects form - check that it is not set to dataentry

    for multiselect listboxes you need code to scan through the list to see what is selected, so I'm surprised either works - otherwise if a user has selected two or more clients or projects, how does access decide which to open?

  3. #3
    MikeN is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Posts
    39
    Ajax,

    I looked into that shortly after I posted because I also realized that the Projects form wasn't allowing me to 'scroll through' the dummy records that are in the table either - the navigation bar only shows 1 (the current) record versus 1 of X. The Data Entry property is set to "No." The list boxes are only single-select.

    Quote Originally Posted by Ajax View Post
    sounds like the problem is with your projects form - check that it is not set to dataentry

    for multiselect listboxes you need code to scan through the list to see what is selected, so I'm surprised either works - otherwise if a user has selected two or more clients or projects, how does access decide which to open?

  4. #4
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    first post
    Any thoughts on why the double-click action in lstProjects ONLY opens a new record versus firing the WHERE clause?
    second post
    the navigation bar only shows 1 (the current) record versus 1 of X
    if you have set a criteria on the openform, that's what you get

    first post
    The listboxes also are MultiSelect
    second post
    The list boxes are only single-select.
    please try to be accurate - I take it that you now have the answer to your question

  5. #5
    MikeN is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Posts
    39
    Sorry if there is a miscommunication. No, I have not found out why the same Where clause fires as desired on one listbox (lstClients) and not the other (lstProjects) when the forms open. The Where clause is based on an ID (albeit separate IDs from a different table) in both clauses. It seems like the criteria format would be the same with the objects (form to open, ID name that they need, etc) being respective of what I'm wanting it to do.

    LstClients opens the NewClient form with the results displayed for the client that was selected in the list (works as desired). LstProjects opens the Project form as a new record versus executing the Where clause and opening it with the associated Project information from the project that was selected in the list (this is the one that's not working).

    second post

    the navigation bar only shows 1 (the current) record versus 1 of X

    This was in response to you suggesting the Data Entry property may be incorrect.
    When I opened the form manually from the navigation pane (versus through commands), I realized that only the current (blank) record was showing up as a "1 of 1" in the navigation bar at the bottom of the form. The table that is linked to the Projects form does have data that was manually entered into the table, but these records do not show up as "1 of 5" in the navigation bar when I manually open the from from the navigation pane. So, this lead me to think that maybe the Data Entry was "Yes" before you responded the first time - but it wasn't.

    first post
    The listboxes also are MultiSelect


    second post
    The list boxes are only single-select.

    The first post said the listboxes are Multiselect:None
    Which is why I thought I was clarifying it when I said they are single-select in the second post.

    Again, sorry for any miscommunication.

  6. #6
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    OK I can see how the confusion has arisen.

    So back to basics

    this

    "[UserID] = " & lstClients.Column(0)

    only needs to be this

    "[UserID] = " & lstClients

    and to clarify the difference between a filter and a criteria.

    A criteria sets the number of rows returned - so only one record is returned in this case since the ID is (should be) unique- and it is a criteria you are using in docmd.openform

    a filter reduces an existing dataset (2 of 5, whatever) and when remove the full recordset is returned

    so my guess is that your code is not setting a value (or at least a valid one) for projectID as you think

    suggest remove or comment out any error suppression you have until the issue is resolved and set a breakpoint in your code just before the openform line to so the code stops and you can hover over projectID to see the value. If this points to the solution then job done.

    If not, provide the sql to your project combo rowsource and the code in the double click event in full - copy and paste it, don't just type it again - typing again results in possible differences which are blind alleys

  7. #7
    MikeN is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Posts
    39
    Quote Originally Posted by Ajax View Post
    OK I can see how the confusion has arisen.

    "[UserID] = " & lstClients.Column(0)

    only needs to be this

    "[UserID] = " & lstClients
    Alright, I changed the lstClients On Dbl Click and it still opens the records associated with the selection. I also changed it for lstProjects (the one that's been giving me troubles) and it still isn't working...it still opens the form with as a new record. Here is the On Dbl Click VBA for both:
    Code:
    Private Sub lstClients_DblClick(Cancel As Integer)
        DoCmd.OpenForm "NewClient", , , "[UserID] = " & lstClients
    End Sub
    
    
    Private Sub lstProjects_DblClick(Cancel As Integer)
        DoCmd.OpenForm "Projects", , , "[ProjectID] = " & lstProjects
    End Sub
    Here is the RowSource for lstClients. Column 1 is the bound column.
    Code:
    SELECT Clients.UserID, Clients.FirstName, Clients.LastName, Clients.University FROM Clients WHERE (((Clients.FirstName) Like "*" & Forms!SelectClient!txtKeyword.TEXT & "*")) Or (((Clients.LastName) Like "*" & Forms!SelectClient!txtKeyword.TEXT & "*")) ORDER BY Clients.LastName;
    Here is the RowSource for lstProjects. lstProjects is filtered based on the selection from lstClients. Column 1 is the bound column.
    Code:
    SELECT Projects.ProjectID, Projects.UserID, Projects.ProjectResearcher, Projects.ProjectTitle FROM Clients INNER JOIN Projects ON Clients.UserID = Projects.UserID WHERE (((Projects.UserID)=[Forms]![SelectClient]![lstClients]));
    I also repaired/compacted and attached the database in case that would help. Attachment 23530
    Last edited by MikeN; 01-29-2016 at 08:10 AM. Reason: Forgot attachement

  8. #8
    MikeN is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Posts
    39
    I'm still stumped. Anyone have any suggestions?

  9. #9
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the problem is the recordsource for your project table has two tables - and there are no records in one of them so not records are returned.

    Either remove the IACUC table (preferred - rule is one form, one table, your IACUC table should be in a subform) or change the join to a left join (but then you cannot add new records)

  10. #10
    MikeN is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Posts
    39
    Quote Originally Posted by Ajax View Post
    the problem is the recordsource for your project table has two tables - and there are no records in one of them so not records are returned.

    Either remove the IACUC table (preferred - rule is one form, one table, your IACUC table should be in a subform) or change the join to a left join (but then you cannot add new records)
    Thanks Ajax! I have no clue what I was thinking when I went that route. It might have been because I was originally just making it for IACUC data and then one of my coworkers suggested that I include all the other stuff too, so I just tried to "add on" without thinking it out. That was it. I moved the IACUC fields into the Projects table and got rid of the IACUC table. Sorry again for the early on mistakes during our discussion. Thanks for working with me on this!

  11. #11
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    glad to help

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

Similar Threads

  1. Replies: 17
    Last Post: 03-09-2016, 11:30 AM
  2. how to de-select items in a listbox
    By CharbelKahi in forum Programming
    Replies: 1
    Last Post: 06-23-2014, 07:05 AM
  3. Replies: 2
    Last Post: 12-21-2012, 01:57 PM
  4. Replies: 1
    Last Post: 09-10-2012, 11:21 PM
  5. Replies: 1
    Last Post: 03-15-2011, 03:53 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