Combo-boxes and Parameter Queries

[StephenH]

Hi

I've got an interesting problem that I'm not sure how to solve. A while back I managed to get a custom filter to work on a form. See code below:

'------------------------------------------------------------
' cmdApplyFilter_Click
'
'------------------------------------------------------------
Private Sub cmdApplyFilter_Click()

Dim filterStr As String

If Me.cboFilterAssetGroup.ListIndex <> -1 Then filterStr = filterStr & "[Asset Group] = '" & Me.cboFilterAssetGroup & "' And "
If Me.cboFilterCalibrated.ListIndex <> -1 Then filterStr = filterStr & "[Calibrated?] = '" & Me.cboFilterCalibrated & "' And "
If Me.cboFilterPatTested.ListIndex <> -1 Then filterStr = filterStr & "[PAT Tested?] = '" & Me.cboFilterPatTested & "' And "
If Me.cboFilterHub.ListIndex <> -1 Then filterStr = filterStr & "[Hub] = '" & Me.cboFilterHub & "' And "
If Me.cboFilterLocation.ListIndex <> -1 Then filterStr = filterStr & "[Location] = '" & Me.cboFilterLocation & "' And "
If Me.cboFilterUser.ListIndex <> -1 Then filterStr = filterStr & "[User] = '" & Me.cboFilterUser & "' And "

If Len(filterStr) > 0 Then
    filterStr = Left(filterStr, Len(filterStr) - 4)
    Me.Filter = filterStr
    Me.FilterOn = True
End If

End Sub

yeah I know, should label the code Anyway, the code works as expected: a user choose however many combo-boxes they want for the filter, click on the filter button and the records get sorted as such. However, I am finding that two of my boxes are asking Parameters - cboFilteredCalibration and cboFilteredPatTested. When you select either YES, NO, N/A (or other ones that are added into the table) and press the filter button, I get a parameter check. Entering the chosen option doesn't apply the filter and leaving the box blank treats it as a non-option, so no records appear.

how can I remove the Parameter queries that appear?

 

[pr2-eugin]

I don't know if this is a mistake; but in your code you have..

If  Me.[COLOR=Red][B]cboFilterCalibrated[/B][/COLOR].ListIndex <> -1 Then filterStr = filterStr  & "[Calibrated?] = '" & Me.cboFilterCalibrated & "' And "
If  Me.[COLOR=Blue][B]cboFilterPatTested[/B][/COLOR].ListIndex <> -1 Then filterStr = filterStr  & "[PAT Tested?] = '" & Me.cboFilterPatTested & "' And "

In your description you have..

....However, I am finding that two of my boxes are asking Parameters - cboFilteredCalibration and cboFilteredPatTested.

They seem to be different don't you think? Are you sure you have not misspelt the controls?

 

[vbaInet]

However, I am finding that two of my boxes are asking Parameters - cboFilteredCalibration and cboFilteredPatTested. When you select either YES, NO, N/A (or other ones that are added into the table) and press the filter button, I get a parameter check.

And what this all really mean?

 

[StephenH]

@pr2-eugin: That's my mistake on typing out the post. Oops. I've checked the controls and the coding and they match up

@vbaInet: Basically in both of these boxes I have the following options to choose from:
*blank*
YES
NO
N/A
This allows a user to filter out records that match whether items are calibrated/PAT tested or not, or whether it's not needed.

 

[vbaInet]

What parameter do you get?
What is the SQL of your form's Record Source?

 

[StephenH]

What parameter do you get?
What is the SQL of your form's Record Source?

The parameter is supposed to be what you choose from the combo-box, but instead it asks for one.

Currently I have no RecordSource code on the form. The main source is one table called "Register"

 

[vbaInet]

Please be specific. We need to know exact details of what the message box says.
What is the difference between a Record Source and a Main Source? Look at the properties of your form and you'll see a property called Record Source.

 

[StephenH]

Please be specific. We need to know exact details of what the message box says.
What is the difference between a Record Source and a Main Source? Look at the properties of your form and you'll see a property called Record Source.

The RecordSource says Register, which is the name of the table of data.

The parameter box in questions is as follows:
title: Enter Parameter Value
text: Calibrated? / PAT Tested? (depends which combo box you try and filter from)
OK and Cancel buttons. Clicking Cancel gives Runtime 2001 error and the filter isn't applied.

 

[vbaInet]

Don't worry about the title and buttons. It's just the message I was after.

It would appear that those two fields are not present in the Register table.

 

[StephenH]

Don't worry about the title and buttons. It's just the message I was after.

It would appear that those two fields are not present in the Register table.

I double-checked my table and renamed the relevant columns, and the filter now works. Many thanks

 

[vbaInet]

Good to hear!