VBA form filtering syntax

[N1755L]

Hi everyone,

This database is experimental, with made-up data, but eventually, there would be very many records, and the aim would be to enter a auto manufacturer year, from this data the records are filtered so that only the records matching the year will show, then a combo allows me to enter a specific auto manufacturer, so now the only records showing will be the ones matching the entered year AND the selected manufacturer. From then I would proceed to select an auto manufacturer model from another combo box, and so on.

The form as it is at present does filter properly after I've entered the year, but after that, I need to filter again after having additionally selected a manufacturer from a combo box.

So, at first I filter by year, then I filter by year AND by manufacturer.

The syntax to filter by one parameter is fairly simple, but the syntax to filter by more than one parameter gets a bit more complex by way of parentheses.

I can filter the form by year and by manufacturer, and I've left the filter string commented out in the VB code, but that string is "static" with pre-defined/hard-coded parameters. I've tried to modify the functional filter string by removing the static values and adding variables to it (year and manufacturer), but can't figure out the proper mix/placement of quotation marks (would I even need single quotes in there?).

I've spent hours rearranging the quotation marks, but can't seem to figure out the proper positioning.

Am I on a wild-goose chase?

Lemme guess, I should be using a different method to achieve the same results... using recordsets or such? I think I tried a recordset approach a few days ago and couldn't get anywhere, right away as the compiler entered the code, I was getting something about a type not defined or something?


Thanking you in advance,

Richard


P.S. For crying out loud! I forgot to save it in 2003 format or something... sorry.

[pbaldy]

For 2 fields? Along the lines of:

Me.Filter = "qryAutoModelFilters2.fAModelYear =" & ystr & " And qryAutoModelFilters2.fAutoManufacturer = '" & cboManu & "'"

[N1755L]

For 2 fields? Along the lines of:

Me.Filter = "qryAutoModelFilters2.fAModelYear =" & ystr & " And qryAutoModelFilters2.fAutoManufacturer = '" & cboManu & "'"

Thanks Paul,

I'm getting this:



I've seen a lot of that screen today

Just noticed, what's happening to the variables?

[N1755L]

Hang on, guess I must not have selected anything from the combo box last time, 'cause now I get this:



The string I have in the code is:

Me.Filter = "qryAutoModelFilters2.fAModelYear =" & ystr & " And qryAutoModelFilters2.fAutoManufacturer = '" & cboManu & "'"

Looks like we're very nearly there... the only thing is the year string now... but also notice from the error msg that there might be a quotation problem looming with regard to the combo box variable?

[pbaldy]

Not sure where you have the code, but in the manufacturer code in the sample you aren't setting the year variable. It is set in the year code, but its scope is limited to that sub since it's declared there.

[N1755L]

Not sure where you have the code, but in the manufacturer code in the sample you aren't setting the year variable. It is set in the year code, but its scope is limited to that sub since it's declared there.

Gimme 5 mins to get my Klingon decoder to decypher the above sentences, then look at the database again... brb...

[N1755L]

Not sure where you have the code, but in the manufacturer code in the sample you aren't setting the year variable. It is set in the year code, but its scope is limited to that sub since it's declared there.

Brilliant Mr. Worf! Thank you! Works a charm!

Now, as I add a 3rd, and then a 4th variable, I'm going to be scratching myself to baldness... could you explain the use of the quotation marks you have here? Where might I find clear, concise, logical explanations of the proper use of these buggers? As I was trying to figure it out all day, I looked at many examples, at many snippets of code but couldn't find the rhyme or reason for the placement of the nasties.

Thank you! Thank you! I'm jumping in my seat here looking at the filtered results of my form! Hehe... I'm jumping and giggling like an idiot... thanks a bunch, I think?

[pbaldy]

Sorry, thought you spoke Klingon.

Jason has a decent tutorial here:

http://www.baldyweb.com/BuildSQL.htm

By the way, the way you're writing it every criteria is required. Check out how the sample db in that link adds to the WHERE clause conditionally, allowing the user to pick and choose which criteria they use. It's a little dated, but I use that basic technique fairly often. Depending on the situation, I either put all the filtering code behind a button or create a function that does it, and call that function from each control's after update event so it filters as they fill things out.

[N1755L]

Sorry, thought you spoke Klingon.

Jason has a decent tutorial here:

http://www.baldyweb.com/BuildSQL.htm

By the way, the way you're writing it every criteria is required. Check out how the sample db in that link adds to the WHERE clause conditionally, allowing the user to pick and choose which criteria they use. It's a little dated, but I use that basic technique fairly often. Depending on the situation, I either put all the filtering code behind a button or create a function that does it, and call that function from each control's after update event so it filters as they fill things out.

I'm going to try to master what you explain in that article. Thanks.

Should I start a new thread to ask about the "Dim"-'ing of the variables? I had to re-"Dim" the "ystr" variable in the second Sub to make the filter work... I expect there would be a way to "Dim" the variable once so that it would maintain its value throughout the form events? Should I have code in my VBA to "un-Dim" the variables when done with them to prevent problems/free resources?

How's my Klingon coming along?

[N1755L]

Just occurred to me, I think the term is "declaring variables"... pretty sure... so then, should they be un-declared, or cleared, once you're done the code section? If so, then I wouldn't want to declare them "globally" as I was suggesting then, 'cause I couldn't clear them without breaking the code for my form then, could I? Instead I'd have to declare and then clear them at each Sub of the form, correct?

I'm going to try to master what you explain in that article. Thanks.

Should I start a new thread to ask about the "Dim"-'ing of the variables? I had to re-"Dim" the "ystr" variable in the second Sub to make the filter work... I expect there would be a way to "Dim" the variable once so that it would maintain its value throughout the form events? Should I have code in my VBA to "un-Dim" the variables when done with them to prevent problems/free resources?

How's my Klingon coming along?

[pbaldy]

Variables like that don't need to be cleared or undeclared. Your problem is one of scope. If you wanted to use that variable in any sub on the form, declare it at the top of the module above the first sub. If you wasn't to use it anywhere in the db, declare it in a standard module, not in a form module.

[N1755L]

I was Googling about declaring or undeclaring variables and wasn't finding anything about whether or not they should be cleared or undeclared.

Thanks for the clarification.