silly problem with rs updating unnecessarily

hey, i have studied this for hours and yet, i cannot solve the problem. What i have is 6 comboboxes. Now, if any of these comboboxes text values is equal to 'Any', i change the text value in then combobox to '%'. This way i can do a sql query such as:

rs.source = "select * from Cars where Car_Colour like '" & combo1.text & "'"

Now this works fine, but i then have to change the comboboxes value back to 'Any'. This then results in the rs being empty and therefore i cant movenext, moveprevious or anything else. Closing the rs before changing them back to 'Any' also clears the rs.

Has anyone got a way to keep the rs as it is when i do the query, yet still b able to change the comboboxes text back to 'Any'. Any help would be GREATLY appreciated as i'm tearing my hair out over this :( thanks :thumb:

BIOSTALL

Sounds like you are using bound controls - which I am not familiar with...

But you should be able to do:

VB Code:

rs.source = "select * from Cars where Car_Colour like '" & IIF(combo1.text="ANY","%",combo1.text) & "'"

Nope, its still doing the same thing :confused: What i've done is attached my project. The form i am having trouble with is frmSelectCar in the cmdSearch_Click() sub. I've added a temporary datagrid onto the form just to show what the rs contains.

Note how the only time it fails (rs is cleared) is when cmbMake is equal to 'Any'. If cmbMake is not equal to 'Any' then the rs will remain populated and i can then move through the records. If it is equal to 'Any' then whatever is in rs is emptied :ehh: I just cant figure it out so if someone wouldnt mind looking through my code, i would be very grateful :thumb: Thanks, BIOSTALL

PS. If you would like to know anything else (eg. anything you dont understand about my project), just ask :)

Hi,

In your cmdsearch_click() event you have

If cmbMake.Text = "Any" Then
cmbMake.AddItem "%", 0
cmbMake.RemoveItem (1)
cmbMake.ListIndex = 0
'When the code gets to here it jumps to Private Sub cmbMake_Click()
End If
This is where the problem is. Why it's doing it I don't know.
It's not on the first calling but on the second where you are asking for "%" it changes the cmdModel
so when it comes back it's allready "Any"

Hope this helps will try more later

Fishy

Right, when i was changing the values from '%' back to 'Any', what i've done is put this in the cmdSearch_LostFocus() and it works fine :thumb: Thanks for your help Fishy.