Access is asking for multiple parameter value requests for the same variable

[CameronJV]

Hi everyone,

I'm not a VBA expert and this has me stumped.

I am defining an integer, I then use inputbox to get the user defined value.

When this runs, the user enters the value when prompted. Immediately after this the user asked to enter the parameter value for the same integer (shf).

Immediately after this, they are asked for exactly the same parameter value.

Immediately after this, it carries on and does the job.

Any ideas?

Here's the simple code:

Dim x As Integer
 
x = InputBox("Enter minimum shareholders funds in £'000's")
If x < 100 Then
MsgBox ("x is too low")
GoTo 20
End If
 
DoCmd.SetFilter WhereCondition:="[Operator]=[Forms]![user]![User]" & " AND [Status] = ""Data""" & " AND [Desc] Like ""*"" & [Forms]![user]![Keyword] & ""*""" & " AND [VarX] > X"

If it helps, I have seen that shf is then used twice in the same sub routine but why has it forgotten the value?

[NeoPa]

I can only guess that shf is a Field value in the Record Source of your Form or Report object. It doesn't appear anywhere I can see in your Filter string.

I'm not sure what you're trying to filter but a more recommended approach would be to set the Form or Report object's .Filter property directly. From code associated with the object you can use the format :

Dim strFilter As String
 
strFilter = "..."
Me.Filter = strFilter
Me.FilterOn = (Me.Filter > "")

Your use of X in the filter string is incorrect. The filter value is a SQL format command and as you have it would make it try to refer to a variable called X from the expression manager. As x, as you have it, is a VBA variable with scope limited to the procedure your code is found in, it won't have any idea how to reference it.

You need to pass the value of x into the string itself and get it to use that. Something like :

strFilter = Replace("([Operator]=[Forms]![user]![User]) AND " _
                  & "([Status] = 'Data' AND " _
                  & "([Desc] Like '*%K*') AND " _
                  & "([VarX] > %X)" _
                  , "%K", Forms("User").Keyword)
strFilter = Replace(strFilter, "%X", x)
Me.Filter = strFilter
Me.FilterOn = (Me.Filter > "")

PS. You'll notice I didn't use the concatenator statement (&) to join multiple string literals together as you have. I use it only to allow a long line to be split across multiple lines. Doing it within a line gives no benefits apart from making your code more difficult to read.