423,680 Members | 2,394 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,680 IT Pros & Developers. It's quick & easy.

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

P: 1
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:
Expand|Select|Wrap|Line Numbers
  1. Dim x As Integer
  2.  
  3. x = InputBox("Enter minimum shareholders funds in '000's")
  4. If x < 100 Then
  5. MsgBox ("x is too low")
  6. GoTo 20
  7. End If
  8.  
  9. 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?
Nov 23 '17 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,084
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 :
Expand|Select|Wrap|Line Numbers
  1. Dim strFilter As String
  2.  
  3. strFilter = "..."
  4. Me.Filter = strFilter
  5. 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 :
Expand|Select|Wrap|Line Numbers
  1. strFilter = Replace("([Operator]=[Forms]![user]![User]) AND " _
  2.                   & "([Status] = 'Data' AND " _
  3.                   & "([Desc] Like '*%K*') AND " _
  4.                   & "([VarX] > %X)" _
  5.                   , "%K", Forms("User").Keyword)
  6. strFilter = Replace(strFilter, "%X", x)
  7. Me.Filter = strFilter
  8. 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.
Nov 27 '17 #2

Post your reply

Sign in to post your reply or Sign up for a free account.