UnBound Combo Box (A2k2)

Topic

New Reply

I’ve got a form with only an UnBound Combo Box and a cancel button. If an entry is selected in the combo box, another form opens with a filter set to that value.

If Cancel button is selected, I want the same form to open without the filter set.

I’ve been using a variable strFilt on the Open Event of form 2 to check.

DoCmd.OpenForm “frmQuery”, , , , , acDialog

strFilt = Forms!frmQuery![cboSelectID]

If IsNull(strFilt) Then
Me.FilterOn = False
Else
Debug.Print strFilt
Me.Filter = “[Word]=Forms!frmQuery![cboSelectID]”
Me.FilterOn = True

This works perfectly when I have a value in the combo box, but I get an Invalid use of Null in this line

strFilt = Forms!frmQuery![cboSelectID]

if there is no entry in the combo box.

Can anyone tell me where I’m going wrong please.

Thanks

Reply | Quote

Replies

I presume that strFilt is a string variable and not a variant. You cannot store a null value in a string.
You will either have to change it to a variant or change some code, changing to variant is the easiest of the two options.
HTH
Pat

Reply | Quote

Sorry, I didn’t say that, but it is a string. Your answer solved this problem.

(but see below)

Thanks

Colin

Reply | Quote

Is there are reason you’r opening the form and then filtering it instead of passing a WhereCondition in the OpenForm? That would eliminate the need for the code in the form entirely.

Reply | Quote

Yes there is!

Now the next question is “Is the reason valid?”

Here I’m a bit less clear (or to put it another way, I haven’t really got a clue because I don’t really know enough about what I’m doing)!!!.

Basically, I want to filter based on the users’ input. There is a list of locations, and I want to user to select from the list then open the file with the filter set to the users choice.

However there are other ways to use the main form, so I wanted a cancel button on the combo box form, so that the main form would open with no filter set, and at the first record.

I was trying to set code to check if a value had been selected and then do an if then on the, main form.

Pat’s solution has solved that problem perfectly, but as ever, leads me into another one.

As I’m running this on the Open event (there are other actions on Activate, Load, Current etc so based on what’s already written, it has to be there). The GotoRecord command isn’t available as the form is not yet open. So I need another solution in any event.

I don’t know if I could have done this totally differently

Colin

Reply | Quote

I know you want to filter based on user input. The question is where the input is coming from. If it’s always coming from the other form, then how does the user tell the interface to display a different set of records? If they always go back to the form, then you can use the Wherecondition argument each time to reopen the form with a different set of records, and that is much faster than applying a filter.

The form will always open at the first record in its recordset. You don’t have to do anything to force that. If you filter down (or use the wherecondition) to open the form at the particular record the user specifies, then you still don’t have to force anything. Maybe we could help more if you explained a bit further.

Reply | Quote

Hi and thanks for the feedback. I have no doubt that I am probably not using the best way to get to this data, and I appreciate your help very much. This is the only way to learn.

My main form contains data about a number of different places. On this form, the user (mostly me) wants to find a specific record to read the data. However as there are some thousands of records, it needed some sort of search facility.

Each record when entered is saved with one or more locations (there may be a need for 1, 4, 5 or more), to aid the subsequent search. However it is also possible that the user wants to search the text entry using Find for a complete search of all records. This way, the user has a choice to find all records that contain an individual word.

I expect the main use of the form is to filter by user selection.

Incidentally, I also have an option group which filters by date of record entry with 7 preset dates (today, yesterday etc). There is also an option group to select the 7 main locations which are used from time to time. These two Option Groups are placed on top of each other with the Visible set to a true/false toggle depending on which is selected from another button. The choices for both option groups come from a table which allows the user to vary the time periods, or locations if other ones are more relevant.

My main form gets its data from a SQL which selects each record with each location, so that if you Filter on say London, you might bring up record (Autonumber) 124, and if you filter on New York, it might also bring up the same (base) record 124, althought it will be with a different location in the query.

In my simple mind, I thought that the best way was to get a second form to get the user input, and transfer that to the main form. I then have several buttons on the main form allowing a new filter (unfortunately this requires a close and then open of the main form), or a Find, or a pre-set filter by date/location

I did not know how to use the Where option in the Open Form to get user Input. Unfortunately, I am totally self taught (and obviously not very well), and I suspect like most beginners, fall back on what I have done in the past.

Hope this explains sufficiently, but if not, let me know

Reply | Quote

I forgot to add that as the main field is a memo field and fairly lengthy, I decided that a form/sub-form was not really a suitable way to operate.

Reply | Quote

Just discovered that all I needed was an “Location = [Enter Key Word]” in the Where statement and it does what I want.

I can’t seem to get it to work with a Like statement though.
I’ve tried various combinations based on

“JWord = ‘Like [Enter word] & ” * “‘”

but they don’t seem to like any of them

One more question for you.

Is there a way to get a custom dialog box for the Enter Parameter question in the Where statement?

As the rest is all designed to the same look & feel, I would like this box to be the same

Thanks

Reply | Quote

The problem is that “=” and “Like” are both operators and you use one or the other but not both. Try

"jword Like [Enter Key Word] & '*'"

Reply | Quote