I'm using VB6, Access 2000 and I'm connecting to the database via an Ado Control.
I'm writing a program to contain athlete information. The field to be filtered actually has the name "2001_Reg_Nr" or "2002_Reg_Nr" etc. which stands for "Registration Number" for that particular year. That is obviously the athlete's registration number for the year.
The recordset is populated from the database using SQL. The different years come from different tables.
The user selects the particular year that they want to filter from a combobox populated with the available years. All the years stay visible in the datagrid. The combobox's name is cboYearToFilter. When they've made their selection, I assign the column to filter by using the following:
strYear = ltrim(rtrim(cboYearToFilter)) & "_Reg_Nr"
The recordset might then look like this
where the value in strYear is the column that the filter should use.
I then have an autocomplete combobox with the name cboNumber, which is populated with the available registration numbers from the year selected in cboYearToFilter. All of this is done in cboYearToFilter.Change.
The following statement keeps crashing on me:
adoAthlete.Recordset.Filter = strYear & " LIKE '" & cboNumber.Text & "'"
This statement should be used in cboNumber.Change, which executes every time it changes and therefore show only the athletes with that particular number taken from cboNumber.Text. (I have another filter on another form that works perfectly. On that form however, I use the column name directly in the filter)
The message VB6 gives me is:
"Run-time error "3001": Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."
My conclusion is that VB6 does not recognise the value in strYear and is therefore looking for a column by that name.
Unfortunately the program needs to be dynamic (meaning the client must be able to just change the column to filter).
How to make VB6 recognise the value in strYear as the column to filter is the question? I've tried everything I can think of.