Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Huron is offline Novice
    Windows 10 Access 2003
    Join Date
    Mar 2021
    Posts
    18

    Unhappy Add Select All to combo box

    I wanted to do so that in the combo box only two types of books „Access“ and „All books“ were displayed and transferred to the subordinate form. But I didn't even know how to make "All books..." In Row Source I tried to prescribe such options:

    SELECT DISTINCT Books.[Book type] FROM Books UNION SELECT "All books" from Books GROUP BY [Books].[Book type];



    SELECT [Book type] AS Filter, [Book type] FROM [Books] UNION SELECT 'All books' AS Filter, "All" AS [Book type] FROM [Books];

    SELECT Books.[Book type] FROM [Books] UNION SELECT "All books" FROM Books GROUP BY [Books].[Book type];

    I tried it and so:

    Row Source Type: ValueList
    Row Source: Access;All books

    and I added the Event:

    Private Sub BookType_AfterUpdate()
    If Me.BookType = "All books" Then
    strFilter = "*"
    Else
    strFilter = "'" & Me.BookType & "'"
    End If
    End Sub

    But nothing meaningful happened. Searched the entire Internet, also found nothing...
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,791
    the combo should be based on a table:
    CAPTION, QRY
    All books, qsAll
    SciFi, qsSciFi
    etc...

    the combo should have 2 columns, you can hide the query column.
    when the user picks the caption, apply the query to your combo box AFTERUPDATE event:

    open the query:
    docmd.openquery cboBox

    or filter records in a form based on the query:
    me.recordsource = cboBox

  3. #3
    Bob Fitz's Avatar
    Bob Fitz is online now Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,889
    Huron

    I would recommend that you brush up on db design.
    I would urge you to not to use punctuation marks or special characters in the names of objects. Nor would I use spaces in their names, which will cause you a problem at some point later on.
    You should also refrain from using "reserved" words like "Type" as a name. "BookType" would be much better and would also convey some understanding of what the data is about. "id" is not a very meaningful name for a primary key field, particularly when it is used in more than one table BookID or TypeID would be much better IMHO.
    I would also urge you to adopt a naming convention for your objects.
    Last edited by Bob Fitz; 03-02-2021 at 05:09 AM. Reason: Clarification
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is online now Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,889
    Huron

    In the attached db I have made some changes which you may find useful.
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    Huron is offline Novice
    Windows 10 Access 2003
    Join Date
    Mar 2021
    Posts
    18
    Quote Originally Posted by Bob Fitz View Post
    Huron

    In the attached db I have made some changes which you may find useful.

    Thank you so much for paying attention to my letter. But your pattern doesn't fit me very well, because I need a form with two subforms. I have already found something, but there is a greater lack of knowledge.
    When I select "Access" in the combo box, both subforms react. Everything is great. But the problem is that, when I select the value "All books" in the combo box again, then only the first ML-Sub subform reacts, and the second subform ML-Sub1 remains with the value "Access".

    This is my writing...:\

    First option:

    Private Sub BookType_AfterUpdate()
    Dim strCriteria As String

    If Not Me.BookType = -1 Then strCriteria = "[Type Id]=" & str(Me.BookType)
    With Me.[ML-Sub].Form
    .Filter = strCriteria
    .FilterOn = (Len(strCriteria) > 0)
    End With
    With Me.[ML-Sub1].Form
    .Filter = strCriteria
    .FilterOn = (Len(strCriteria) > 0)
    End With

    End Sub

    Second option:

    Private Sub BookType_AfterUpdate()
    Dim strCriteria As String

    If Not Me.BookType = -1 Then strCriteria = "[Type Id]=" & str(Me.BookType)
    With Me.[ML-Sub].Form
    .Filter = strCriteria
    .FilterOn = (Len(strCriteria) > 0)
    End With

    If Not Me.BookType = -1 Then strCriteria = "[Type Id]=" & str(Me.BookType)
    With Me.[ML-Sub1].Form
    .Filter = strCriteria
    .FilterOn = (Len(strCriteria) > 0)
    End With

    End Sub

  6. #6
    Huron is offline Novice
    Windows 10 Access 2003
    Join Date
    Mar 2021
    Posts
    18

    Attachment

    Attachment
    Attached Files Attached Files

  7. #7
    Huron is offline Novice
    Windows 10 Access 2003
    Join Date
    Mar 2021
    Posts
    18

    Unhappy Pictures of problem

    Attached are photos of the problem...
    Attached Thumbnails Attached Thumbnails A-1.jpg   A-2.jpg  

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,455
    I just tested your database. Selecting Access gives me 7 records.
    When I then select AllBooks, I get 28 records in each subform???

    Click image for larger version. 

Name:	WithAccess.PNG 
Views:	17 
Size:	87.0 KB 
ID:	44441

    Click image for larger version. 

Name:	WithAllBooks.PNG 
Views:	19 
Size:	99.6 KB 
ID:	44442

  9. #9
    Huron is offline Novice
    Windows 10 Access 2003
    Join Date
    Mar 2021
    Posts
    18
    But you chose "All books" after you selected "Access"...?
    When I open the main form, then in subforms I get such numbers - 28/28.
    When I select "Access" in the Combobox, I get such numbers - 7/7.
    But when I choose the value of "All books" in the Combobox again, I get such numbers - 28/7.
    And must be 28/28...

  10. #10
    Huron is offline Novice
    Windows 10 Access 2003
    Join Date
    Mar 2021
    Posts
    18

    Picture of problem

    This problem (28/7) is seen in the second picture.
    Attached Thumbnails Attached Thumbnails A-2.jpg  

  11. #11
    Bob Fitz's Avatar
    Bob Fitz is online now Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,889
    Huron
    Thank you so much for paying attention to my letter. But your pattern doesn't fit me very well, because I need a form with two subforms.
    I understand that you feel that you need two subforms and you may be correct about that. Without an understanding of your reasoning I can not comment on that any further.
    Since you have chosen to totally disregard the advice I offered in my original post and have chosen to discard the changes to object names etc that I made in the db I posted I have no wish to examine the db any further.

    Good luck with your project.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,455
    Huron,

    I was using your comment
    When I select "Access" in the combo box, both subforms react. Everything is great. But the problem is that, when I select the value "All books" in the combo box again, then only the first ML-Sub subform reacts, and the second subform ML-Sub1 remains with the value "Access".
    as a guide.

    I opened your form and Selected Access, then selected AllBooks. As per my last post #8 the values were 1 and 28. And both subforms worked as I expected and displayed in the post.

    Why do you need 2 subforms that appear to display the same thing???

  13. #13
    Huron is offline Novice
    Windows 10 Access 2003
    Join Date
    Mar 2021
    Posts
    18
    The first subform display data from one table and the second subform display data from another table. The data in the tables are completely different, except for one value. And this general value is indicated in the combo box. And therefore, an example where both the combo box and the data itself are in the same tape form is not suitable. In this example, what I sent you is just the essence of what needs to be done. And excuse me, for what I may not be quite correct in explaining, but I try as hard as I can...

  14. #14
    Huron is offline Novice
    Windows 10 Access 2003
    Join Date
    Mar 2021
    Posts
    18
    Quote Originally Posted by orange View Post
    Huron,

    I was using your comment
    as a guide.

    I opened your form and Selected Access, then selected AllBooks. As per my last post #8 the values were 1 and 28. And both subforms worked as I expected and displayed in the post.

    Why do you need 2 subforms that appear to display the same thing???
    It really won't be two same things. This is only an example of the very essence of the problem. In fact, the first subform will display data from one table and the second subform will display data from another table. The data in the tables are completely different, except for one value. And this general value is indicated in the combo box....

  15. #15
    Huron is offline Novice
    Windows 10 Access 2003
    Join Date
    Mar 2021
    Posts
    18
    Quote Originally Posted by Bob Fitz View Post
    Huron

    I understand that you feel that you need two subforms and you may be correct about that. Without an understanding of your reasoning I can not comment on that any further.
    Since you have chosen to totally disregard the advice I offered in my original post and have chosen to discard the changes to object names etc that I made in the db I posted I have no wish to examine the db any further.

    Good luck with your project.

    Your example is very beautiful and I will definitely find him a worthy application... Thank you very much again...

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

Similar Threads

  1. Combo Box won't select from list
    By Heathey94 in forum Access
    Replies: 11
    Last Post: 09-14-2016, 04:57 PM
  2. Combo to select all, need a little help
    By vicsaccess in forum Access
    Replies: 7
    Last Post: 06-20-2016, 12:47 PM
  3. how to add select all option in combo box
    By Vaibhav2015 in forum Forms
    Replies: 6
    Last Post: 09-30-2015, 10:09 AM
  4. Replies: 3
    Last Post: 06-09-2013, 05:35 PM
  5. Combo box select from two fields
    By cnestg8r in forum Access
    Replies: 0
    Last Post: 10-31-2008, 10:05 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 - Senior Forums