New to Access VBA.
Regarding the Requery function, I've been trying to get that sucker to work all day and nothing. For a listbox named List28, my code is:
I put this code in the OnClick event handler of a button that I use on the form containing the listbox. When I click the button it's supposed to execute the code I gave above. Nothing. No updates to the listbox at all. Yet the Listbox has a complete SQL statement in its Rowsource property. So, why doesn't the call to Requery cause the Listbox to execute its SQL statement and display its results?
Thanks for any help.
P.S. Worn out searching the Web with lousy search results; hence the reason for asking you all for some help.
Originally posted by The Byte
Is there any new data that that supposed to show up in the list box?
Yes. I'm taking, as input, several desired values for a particular field. With those values, the SQL statement (in the Rowsource property) is filled in, and the results of that query are to re-populate my listbox. No appending: all data is to be replaced with the new query results each time the button is clicked.
More specifically, my input area consists of 6 edit boxes where the user types in, up to, 6 desired values for this particular field. The values in all 6 of the edit boxes (null allowed) are used in the WHERE criteria of my SELECT statement. I was hoping the call to Requery, in the button's on-click event, would re-trigger that SQL statement each time, but apparently not.
I'm not actually using standard SQL-type parameters in my SQL statement, but am, instead, directly using the names of those edit boxes, as my "pseudo" parameters. I.e. no ":<parameter name>" syntax is used; just the names of the edit boxes. Could that be the problem? Wouldn't think so since those edit-box names seem to be within scope.
To The_Byte and Mike,
Thanks so much you guys. I finally got it working. Due to my own fault, I failed to tell you that the info I was originally querying was not the edit boxes themselves, but an existing populated table, and was just using the edit-box values as the parameters in the WHERE part of my SQL statement. However, both of your suggestions greatly helped make me think down some different trails, and after some more experimenting, voila! I guess several minds are better than one.
Let me share with you my mistake in case you come across another dummy like me who doesn't have some of the Access & VBA basics down. In my Rowsource property, I used the built in query editor to help me build my query. Well, in the "Criteria" part of the query editor, I put my criteria using the following syntax: <field name>=<edit-box name>. What I didn't notice was that when I typed the field name on that line, the editor thought I was creating a brand new user-defined field instead of seeing it as a field within my original table, and therefore added that field with quotes around it as a brand new field in the query. So, my application was trying to match my edit-box values to that new, useless field. No wonder, it couldn't find anything in the original table; it wasn't even looking at the table, but at this new mysterious field. When I took the quotes off of that new field-name, it finally recognized it as a valid field within my original table. Then, it worked.
Boy, I guess there needs to be a book called "Troubleshooting Guide on Access & VBA for Mr. DUH". Thanks again you guys.