Page 4 of 5 FirstFirst 12345 LastLast
Results 46 to 60 of 71

Multi Select Listbox parameter for Query

  1. #46
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37

    Quote Originally Posted by ItsMe View Post
    I have a solution for you. I will post it soon.
    Thanks!

  2. #47
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    Rather than have the form open a query, I created another form and have the first form open a second form. It is better to use forms as a graphic interface vs. tables and queries. You can use the form's properties to control things like edits, additions, etc.

    I edited your query so it no longer looks for the form or list box (parameterized query no longer).

    The code gets a little complicated, especially when dealing with text searches.


    .
    Attached Files Attached Files

  3. #48
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37
    Quote Originally Posted by ItsMe View Post
    Rather than have the form open a query, I created another form and have the first form open a second form. It is better to use forms as a graphic interface vs. tables and queries. You can use the form's properties to control things like edits, additions, etc.

    I edited your query so it no longer looks for the form or list box (parameterized query no longer).

    The code gets a little complicated, especially when dealing with text searches.


    .
    Sweet! THIS WORKS!
    Thanks a lot!
    Now I just gotta back track what you did TY TY TY TY TY

  4. #49
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    Things to consider:

    Eliminated the criteria in the query (just a simple SELECT query)
    Created a form and used the query as the RecordSource for the form
    Deleted the text inside the control button's On Click field in the events tab to eliminate the embedded macro
    Create a click event handler in VBA.
    Added code to open the new form.
    Added code to close the first form.

  5. #50
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37
    Quote Originally Posted by ItsMe View Post
    Things to consider:

    Eliminated the criteria in the query (just a simple SELECT query)
    Created a form and used the query as the RecordSource for the form
    Deleted the text inside the control button's Control Source Property to eliminate the embedded macro
    Create a click event handler in VBA.
    Added code to open the new form.
    Added code to close the first form.
    I forgot to ask you. Can you tell me how exactly you deleted the text inside the control button's control Source Property??? Which control button are you talking about? The command button?

  6. #51
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    Yes, the command button. An easy way would be to delete the control and create a new one. I mispoke when I said control source. I meant to say delete the text from the On Click field in the events tab. I will edit my post.

  7. #52
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37
    Okay. I thought if you just changed the on click event from "Embedded code" to "Event procedure" It would delete it on its own.
    So I should just delete the command button and create a new one without anything attached. and just insert the code on click event right?

  8. #53
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    You can delete the control, yes. You can also erase the text inside of the field for the On Click event. I have tried to create VBA event handlers where an embedded macro was before and these are the two methods I use.

    In order to create the Event handler in VBA you should click the ellipses(...) next to the appropriate event and then select "Code Builder". This will generate the beginning line of code and the last line of code for your Sub Procedure. Insert your code between the first and last lines.

  9. #54
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37
    Coooool! THANKS

  10. #55
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37
    So I have come across another problem now
    I have 2 list boxes in the form of the original DB
    So imagine if there was another field say colors. which did not neccessarily needed a multi select (it would be great if it did), (I realized its not as simple as adding criteria in the query infact you mentioned you took out the criteria), How would I go about that?
    I added the new database in here to clarify what I mean.
    I am new to these forms & Since you already solved the original problem, would you like me to create a new thread or is posting here okay?MultiSelectTest.zip

  11. #56
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37
    Also I noticed it only filters 3 states.
    I am looking at the code to figure out other where I can increase this but I don't realy know. It's a little weird lol.. I have a feeling you might be surprised at this too

  12. #57
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37
    Quote Originally Posted by KBAR12 View Post
    Also I noticed it only filters 3 states.
    I am looking at the code to figure out other where I can increase this but I don't realy know. It's a little weird lol.. I have a feeling you might be surprised at this too
    NVM this. There was a massive spell error in all my databases I had. Wow good thing I saw this now... Just finished fixing all of it.

  13. #58
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    The code is designed to manage as many selections as the machine's/app's memory will allow.

    Go ahead and create another thread regarding dependent cascading comboboxes and or dependent cascading listboxes. Do a search and try to study up on it so you can ask the correct questions and understand the answers better.

    The fact that you are using text as a PK for your states table will continue to cause hardships when writing code to search for said text vs. writing code to search for long integers or numbers. Consider using the table of states I provided earlier and storing its PK value in your other table, I believe the State field in Table1.

  14. #59
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37
    I don't understand what you mean by using text as a PK for states table. It doesn't have a PK??? or is it that when there is no PK the first field becomes PK?
    I can just throw in a auto# as PK in the table. But what difference would that really make???
    I edited the sample database and created another field called "color"
    In the Form that opens after clicking the command button in States Form, I added another textbox where the control source is that Color. And in the states Form I added another Listbox for Color.
    So Now I basically want to filter not just by states but by color as well. So maybe NJ but also only Green in NJ.

    I have attached the DB in this comment (I tried to play around with the code... It wasn't causing any errors, but wasn't filtering the color either so I took it out. I figured it was wrong anyways)

    MultiSelectTest.zip

  15. #60
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    It is pretty simple. Use the table provided to you by me and store that table's PK in the relative FK of Table1. The difference being a Normalized DB that is more manageable and a Non-Normalized DB that is difficult to develop Public Interfaces for.

Page 4 of 5 FirstFirst 12345 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Multi-Select Listbox
    By RayMilhon in forum Forms
    Replies: 5
    Last Post: 03-04-2014, 11:54 AM
  2. Search using a multi select listbox
    By noobaccess in forum Access
    Replies: 13
    Last Post: 12-04-2012, 07:06 AM
  3. Replies: 6
    Last Post: 11-02-2012, 12:48 PM
  4. multi select listbox
    By crowegreg in forum Forms
    Replies: 3
    Last Post: 07-28-2012, 01:48 PM
  5. Replies: 11
    Last Post: 09-22-2011, 01:13 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
  •  
Tech Forums: Microsoft Office Forums