VBA Code not working

[ipisors]

Since I've had a hard time understanding your explanations, and I don't think we've been provided with enough comprehensive details (or they're changing every time you post) to be expected to provide you with the RIGHT solution, I'm just going to post what I would do with my own made-up scenario. You can surely tweak it to fit yours.

This should be very simple. First, I'd never use Me.Filter. Rather, set the recordsource explicitly, then requery. This leaves no doubt.

Let's say I have a form bound to a table called TBL_ORDERS. But I want to filter it to where CUSTOMER_NAME Like "*Johnson*".



And I had a text box where the user could type that. I might have this behind a command button.

Me.Recordsource="SELECT * FROM TBL_ORDERS WHERE CUSTOMER_NAME LIKE " & CHR(34) & "*" & ME.TXTCRITERIA & "*" & CHR(34)
Me.Requery

[data808]

Since its too difficult for me to accomplish exactly what I want, I've compromised my database to make it work for me. The last thing I would like though is to make the filter partial for this code:

Private Sub cmdFilter_Click()

Me.Filter = "[initials:]='" & Me.txtboxFilter & "'"
Me.FilterOn = True
Me.Requery

End Sub

Private Sub cmdundofilter_Click()

Me.FilterOn = False
Me.Requery

End Sub

If the user enters partial or all of the value in the text box, I would like those records to show which contain all or part of the value entered. After this I'm pretty much done.

Thank you so much for all the great help.

[June7]

Try:

Me.Filter = "[initials:] LIKE '*" & Me.txtboxFilter & "*'"

[data808]

Thanks. I'll try that out. Sorry for changing the details every time I post. I just can't seem to get things to work which makes me think of other ways to do it. A lot of the problems I have is to do with a print preview button which I create from the control wizard. If I do a filter that uses a form to enter criteria (criteria form), then goes to another form to show the filtered results (split form), then click a print preview button from that split form, it will change the focus back to my criteria form and the print preview screen is now behind it. I should also mention that all windows are maximized all the time because I am trying to hide what's happening under the hood from the user. Navigation pane, ribbon, I got rid of the close and minimize buttons in the control box at the top right corner of the window, etc... I am trying to control everything through the user interface to make them never see anything but these forms. I have quit app buttons on probably every form so they can exit that way. I got links to all the respective forms that they need on every form. So anyway, getting back to the print preview problem. In order to see the print preview window, I can either close the criteria form that is now the focus on my screen, or hit the maximize button, which is one of the few things I left the user to use, to resize the window. If I close the criteria form and see the print preview screen and I need to make adjustments because the fields are not fitting on the paper size, I then close print preview screen, make the adjustments and then click the print preview again and now it does not work because the criteria form has been closed and from what it looks like to me, the criteria form is still associated with the filtered results (split form) that has the print preview button.

I've been told that reports are better for printing but I've tried that route and it doesn't work for me because you can't interact with a report. There is no user interface where I can put buttons for the user to navigate back to the primary forms. This makes the experience not flow the way I want it to. I really do want it to flow like an application that doesn't even look like Microsoft Access is being used. Which is why I stuck with split forms to show the results in a datasheet type of view at the bottom and have the buttons to navigate through the forms with other buttons such as print preview on the top in the form's header. So based on this, I have other queries that are similar and that all use a split form to view the results. I just need to change some of the VBA code to make it work the way I want it too and I should be done with it soon. Hopefully.

Thanks for the understanding.

[June7]

Report in Report View can be interactive.

[data808]

Ok thanks. That worked for my text fields. I have a number field and I tried the same code but got an error. What do I need to do differently? I got rid of the delimiters. But no luck.

[June7]

The LIKE operator and wildcard will work for number fields in Access query object. Apparently not with constructed SQL statements. Why would users enter a partial number?

[data808]

if they only knew a portion of the number but i guess you're right. i dont really see how this would be useful. right now i am just trying to think of every scenario for how a user would search and probably some of them will not be useful. still trying to get an understanding of how things work. this database is gonna be my reference on how to do certain things. im going to use this to refer back to over and over for future databases. i want to try and fit as much different scenarios into it as possible so it can be filled with things i might use later. again thanks for all the help. this site is a life saver. do you just reply to posts as a hobby?

[data808]

Dang it! Well I'll try that route again some other time maybe. Unless I have the patients to go back and redo all my split forms and turn them into reports in report view. Thanks for the tip.

I did watch that video link you sent on how to make multiple search unbound text boxes. It was very useful and I am actually using it for one of my forms. Is there somewhere else to find good videos besides YouTube?

[June7]

Yes, this keeps me off the streets. I learn new stuff and share what little I know. Keeps my knowledge base refreshed. Sometimes I look at something I coded years and wonder "what was I thinking" or "wow, that was brilliant".

[data808]

I just went back to some of the saved reports I have when I was going that route and I did have buttons in the header of the report. So you were right, however, it reminded me why I didn't like reports. The header is not frozen so the buttons vanish once you scroll down the report. Plus when I went to print preview it did not give me print options. It just changed the report in its own window with no options to print. There must be something wrong that I did because why would print preview do that? So I guess the report way for me was too confusing to go on. I did save all the reports I made though so maybe I can pick it up again later and fix it but for now the split form will have to do.

[data808]

Hey June7,

I been trying to get this filter to work but this time with a combo box. I pretty much did everything the same except instead of using an unbound text box, I am using an unbound combo box with a value list. I keep getting an error. The error message says:

Run-Time error '3075':
Syntax error in string in query expression '[Genre:]=80's'.

What am I doing wrong?

[June7]

How are you using the combobox?

Is it referenced in a query object? Post the exact SQL statement of the query

Or are you constructing an SQL string in VBA? Show the code.

[data808]

Hey June7,

I got it to work half way. Now I can select my values that are listed in the drop down but it will only work with the first choice. This is for movie genres. The first choice listed is for 80's. If I select that then click the filter button, it will only show me the 80's movies. However, if I select any other value and click the filter button, no records will show. The datasheet is blank at the bottom of the split form. Here are some of the things going on in the form:

This is the row source for the combo box. These are in the value list:
"80's";"Action";"Anime";"Children & Family";"Comedy";"Documentary";"Drama";"Foreign";" Horror";"Independant";"Music & Musicals";"Romance";"Sci-Fi & Fantasy";"Sports & Fitness";"Television"

This is the code behind the filter and undo filter buttons:

Private Sub cmdFilter_Click()

Me.Filter = "[Genre:] Like '*" & Me.cmbByGenre & "*'"
Me.FilterOn = True
Me.Requery

End Sub

Private Sub cmdUndoFilter_Click()

Me.FilterOn = False
Me.Requery

End Sub

[June7]

That's odd. When I try to filter on a value with an apostrophe, it generates that error. I don't see how your 80's criteria is working. Either remove the apostrophe or change code to:

Me.Filter = "[Genre:] Like '*" & Replace(Me.cmbByGenre, "'", "''") & "*'"

Apostrophe is a special code character in SQL. If you want the literal apostrophe character, have to double it. This forces the SQL to process it as text, not a code. The same goes for other special characters, such as * and #.