Problem with recordsetclone after form is filtered (1 Viewer)

[BJF]

Hello everyone,

I don't understand why this happens - please help!

I have a form to look up customer info. The form has a combo box where i can select a customer and it take me to the record via vba, something like this

Me.FilterOn = False

Me.RecordsetClone.FindFirst "[acct] = " & Me![Combo890]
Me.Bookmark = Me.RecordsetClone.Bookmark

Just today i added a search button next to the primary contact field which lets my users search for a contact only to filter the form. the code for that button uses the filter and works great.

the problem i found is if they use the filter and then go to use the combobox the filter remains on and the combobox wont work, so i added as you can see above, a filteron = false, however now it take 2 uses of the combobox to find the chosen record because the first time it seems to only turn off the filter - why is that?
if i select from the combobox a second time, it works fine since the filter is off. do i need to add something to the code to make it turn off the filter and then finish the rest of the code ???

thanks for any suggestions

 

[Jon]

Welcome to the forums! We are the most active Microsoft Access community on the internet by far, with posts going back over 20 years!

Here are just a couple of tips for you:

1. Feel free to ask any question you like, however basic you may feel it is, or even if it has been answered before. Our expert members thrive on helping you out!

2. If you prefer a dark theme to the forums, just go to the bottom left of this forum and click "Default style". You will then see a selection of themes to choose from. I like Shades of Blue.

3. If you like any of the answers you get, feel free to click the "Like" link on the bottom right hand corner of the post. If you hover over the Like link, you can even choose the type of smiley.

Above all, hang around here, have fun, learn stuff and join in.

 

[theDBguy]

Hi. I moved your thread out of the Intro Forum.

With regards to your question, maybe try also adding Me.Filter="", just in case.

 

[BJF]

thanks DBguy - i havent been on the site in a while and i dont see how to get to the different sections anymore - used to be a list on the right side for macros, forms,tables, etc. where is that?

 

[theDBguy]

thanks DBguy - i havent been on the site in a while and i dont see how to get to the different sections anymore - used to be a list on the right side for macros, forms,tables, etc. where is that?

When you click on "Forums" on the top menu, you get to this page. From there, you can click on "Post thread..." button on the right.

 

[BJF]

DBguy - i tried your suggestion but i get the same thing, so the behavior is that after the form has a filter on, the combobox serach take twice to work, the first time it removes the filter, then if i select from it again it goes to the selected record. How can i make this happen in one move?

I know i can unclick the filter before i use the combobox if the form has been filtered, but my users will need it to be automatic

 

[MajP]

i found is if they use the filter and then go to use the combobox the filter remains on and the combobox wont work

In this case what do you mean that it does not work? If search is not working that is one thing, but this sounds to me like the combo is bound which it should not be.

 

[BJF]

When you click on "Forums" on the top menu, you get to this page.

i just tried it a nd found where it is located, thank you so much , now i know where to post to!!

 

[BJF]

In this case what do you mean that it does not work? If search is not working that is one thing, but this sounds to me like the combo is bound which it should not be.

everything works fine, the combobox searches records fine, and my new filter form by contact works fine. the problem is that if i use the filter by contact first it leaves the filter on. so if my user searches a contact when they open the form , gets info they need, then decides to move the form to another customer by using the combobox, the combobox wont work because th filter is still on. so i added filteron = false to the combobox code before it searches for the record. however, when selecting the record it removes the filter first and leaves my form at the top of the recordset instead of continuing the code to find the selected record. but is slect from the combobox a second time (now that the filter has been removed) it works fine. i need this to happen in one move (selection from the combobox) not two times.

 

[theDBguy]

DBguy - i tried your suggestion but i get the same thing, so the behavior is that after the form has a filter on, the combobox serach take twice to work, the first time it removes the filter, then if i select from it again it goes to the selected record. How can i make this happen in one move?

I know i can unclick the filter before i use the combobox if the form has been filtered, but my users will need it to be automatic

Hi. I just gave it a try using the following code, and the combo worked first time, each time.

Private Sub cboSearch_AfterUpdate()
Me.FilterOn = False
Me.Recordset.FindFirst "id=" & Me.cboSearch

End Sub

 

[BJF]

Hi. I just gave it a try using the following code, and the combo worked first time, each time.

Private Sub cboSearch_AfterUpdate()
Me.FilterOn = False
Me.Recordset.FindFirst "id=" & Me.cboSearch

End Sub

i tried your code, my code was slightly different so i switched to your code but get the same exact thing. see code posted. Once again. this is happening if i use my filter button for contact. so for example, i filter the form by a contact name and it returns on the filter and the recordset shows 2 records. but now i want to switch to another record and using the combobox. when i do this it brings my form to record#1, which in effect has removed the filter and set the recordset to the top, but did not bring me to the record i selected, now i select again from the combobox and it works fine (because its starting from a point of no filter being on) It doesnt make sense to me.

this is your code which i just replaced into my combobox but get the same result

Me.FilterOn = False
Me.Recordset.FindFirst "acct=" & Me.Combo890

this is the code i was using which i ticked inactive

'Me.RecordsetClone.FindFirst "[acct] = " & Me![Combo890]
'Me.Bookmark = Me.RecordsetClone.Bookmark

 

[theDBguy]

i tried your code, my code was slightly different so i switched to your code but get the same exact thing. see code posted. Once again. this is happening if i use my filter button for contact. so for example, i filter the form by a contact name and it utrns on the filter and the recordset shows 2 records. but now i want to switch to another record and using the combobox. when i do this it brings my form to record#1, which in effect has removed the filter and set the recordset to the top, but did not bring me to the record i selected, now i select again from the combobox and it works fine (because its starting from a point of no filter being on) It doesnt make sense to me.

this is your code which i just replaced into my combobox but get the same result

Me.FilterOn = False
Me.Recordset.FindFirst "acct=" & Me.Combo890

this is the code i was using which i ticked inactive

'Me.RecordsetClone.FindFirst "[acct] = " & Me![Combo890]
'Me.Bookmark = Me.RecordsetClone.Bookmark

Okay, I think the only difference with what I did and what you're doing is you said you have a Filter Button. If so, can you please post the code for that one? Thanks.

 

[BJF]

Okay, I think the only difference with what I did and what you're doing is you said you have a Filter Button. If so, can you please post the code for that one? Thanks.

Dim S As String
S = InputBox("Please enter name (You can enter First name, Last name, or any part of the name)", "Primary Contact")
If S = "" Then Exit Sub

Me.Filter = "PrimaryContact LIKE ""*" & S & "*"""
Me.FilterOn = True

 

[MajP]

Can you post your real code? My guess you are using the wrong event. Did you use the after update like @theDBguy ?

 

[BJF]

Dim S As String
S = InputBox("Please enter name (You can enter First name, Last name, or any part of the name)", "Primary Contact")
If S = "" Then Exit Sub

Me.Filter = "PrimaryContact LIKE ""*" & S & "*"""
Me.FilterOn = True

Hi DBguy - were you able to recreate the problem using the code i posted?

 

[theDBguy]

Dim S As String
S = InputBox("Please enter name (You can enter First name, Last name, or any part of the name)", "Primary Contact")
If S = "" Then Exit Sub

Me.Filter = "PrimaryContact LIKE ""*" & S & "*"""
Me.FilterOn = True

Unfortunately, I have the following code now on my test form, and the combo still hasn't failed.

Private Sub cboSearch_AfterUpdate()
Me.FilterOn = False
Me.Recordset.FindFirst "id=" & Me.cboSearch

End Sub

Private Sub cmdFilter_Click()
Me.Filter = "id=" & InputBox("Enter ID")
Me.FilterOn = True

End Sub

PS. @MajP has a good point. Are you using the same events as I am?

 

[MajP]

Was hoping to see the event you are using. Is it the after update?

 

[BJF]

Unfortunately, I have the following code now on my test form, and the combo still hasn't failed.

Private Sub cboSearch_AfterUpdate()
Me.FilterOn = False
Me.Recordset.FindFirst "id=" & Me.cboSearch

End Sub

Private Sub cmdFilter_Click()
Me.Filter = "id=" & InputBox("Enter ID")
Me.FilterOn = True

End Sub

ok thank you for trying, i will have to troubleshoot a different way - my form is not allowing it for some reason

 

[BJF]

Was hoping to see the event you are using. Is it the after update?

Hi, yes i am using afterupdate on my combobox - DBguy has tried my code on a sample form and gotten it to work, i however can not so i will have to troubleshoot differently -thanksfor the reply- if you want to see the code its been posted in this thread

 

[theDBguy]

ok thank you for trying, i will have to troubleshoot a different way - my form is not allowing it for some reason

Okay, good luck. If you can share a sample copy of your db, we might be able to help you hunt down the problem.