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!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.
.
Thanks a lot!
Now I just gotta back track what you did TY TY TY TY TY
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.
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?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.
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.
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?
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.
Coooool! THANKS
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
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.
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.
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
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.