Filter using a Command Button

  • Thread starter Thread starter LDMueller
  • Start date Start date
L

LDMueller

In Access 2003, I have a form named frmEvalWho. On this form I have a
command button and it's coded as follows:
Private Sub cmdEvalAssoc_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim intEval As Variant
Dim intAssoc As Variant
stDocName = "frmAssocEval"
DoCmd.OpenForm stDocName, , , stLinkCriteria
intEval = Evaluator.Value
intAssoc = Associate.Value
Forms!frmAssocEval.Form.Evaluator.Value = intEval
Forms!frmAssocEval.Form.Associate.Value = intAssoc
End Sub

All this works fine to the extent the form opens on the record of what was
entered in the values of Evaluator.Value and Associate.Value. However, at
the bottom of my form, I see it's Record 1 of 139.

Now if I put the prompts in the criteria field of a query instead of using
the code above, I still get the same record, however, it shows Record 1 of 1.

Is there any way to code the form so when the code is ran, I'll end up
getting Record 1 of 1.

I need this to ensure my users won't accidentally go to another record and I
need to be able to code the filter instead of putting the prompt in the query
criteria.

I'm thinking I need to use the the following, but am unsure how to code it.
Filter = "...."
FilterOn = True

I hope all this makes sense. Thanks in advance for your assistance.

LDMueller
 
LD,

You use the stLinkCriteria parameter in the line that has the OpenForm
method, but you are not defining the value for that variable. The other
problem is that when you set the values of Evaluator and Associate you are
changing those values for the first record in the form. The other issue you
should be aware of is that if you use the acDialog window mode of the Open
Form method, the code following the OpenForm will not fire until you close or
hide the form. This is the technique I usually use to ensure that the users
closes the form before doing anything else.

If what you want to do is open the form for Evaluator = intEval and
Associate = intAssociate, then you need to define stLinkCriteria and then
open the form.

stDocName = "frmAssocEval"
stLinkCriteria = "[Evaluator] = " & intEval & " AND [Associate] = " &
intAssociate
docmd.openform stDocName,,,stLinkCriteria,,acDialog
 
First of all I sincerely thank you for helping me.

When I tried to used your code I received a run-time Error '3075':
Syntax error (missing operator) in query express '[Evaluator] = AND
[Associate] = '.

Here's the code I used
Dim stDocName As String
Dim stLinkCriteria As String
Dim intEval As Variant
Dim intAssoc As Variant
stDocName = "frmAssocEval"
stLinkCriteria = "[cboEvaluator] = " & intEval & " AND [cboAssociate] =
" & intAssoc
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog
intEval = cboEvaluator.Value
intAssoc = cboAssociate.Value
Forms!frmAssocEval.Form.Evaluator.Value = intEval
Forms!frmAssocEval.Form.Associate.Value = intAssoc

I believe part of the problem is I didn't provide enough information. So I
relabelled a few fields and will try to explain it better.

I have a table named EvalData with the following fields and a query named
qryEvalData which contains those fields.
Associate
Evaluator
HrRange
Comments

I have a form named cmdEvalAssoc (based on qryEvalData) which contains two
combo boxes Evaluator and Associate.

I have another form named frmEvalWho (based on qryEvalData) which contains
two combo boxes cboEvaluator and cboAssociate and a command button,
cmdEvalAssoc, which I use to open the form cmdEvalAssoc and filter for the
following:
stLinkCriteria = "[AssocInit]=" & "'" & Me![cboAssociate] & "'"
stLinkCriteria = "[Evaluator]=" & "'" & Me![cboEvaluator] & "'"

Individually, the stLinkCriteria works fine, but when I try to combine them
with "and" I get parameter prompts.

Does this information help?

Dale Fye said:
LD,

You use the stLinkCriteria parameter in the line that has the OpenForm
method, but you are not defining the value for that variable. The other
problem is that when you set the values of Evaluator and Associate you are
changing those values for the first record in the form. The other issue you
should be aware of is that if you use the acDialog window mode of the Open
Form method, the code following the OpenForm will not fire until you close or
hide the form. This is the technique I usually use to ensure that the users
closes the form before doing anything else.

If what you want to do is open the form for Evaluator = intEval and
Associate = intAssociate, then you need to define stLinkCriteria and then
open the form.

stDocName = "frmAssocEval"
stLinkCriteria = "[Evaluator] = " & intEval & " AND [Associate] = " &
intAssociate
docmd.openform stDocName,,,stLinkCriteria,,acDialog


----
HTH
Dale



LDMueller said:
In Access 2003, I have a form named frmEvalWho. On this form I have a
command button and it's coded as follows:
Private Sub cmdEvalAssoc_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim intEval As Variant
Dim intAssoc As Variant
stDocName = "frmAssocEval"
DoCmd.OpenForm stDocName, , , stLinkCriteria
intEval = Evaluator.Value
intAssoc = Associate.Value
Forms!frmAssocEval.Form.Evaluator.Value = intEval
Forms!frmAssocEval.Form.Associate.Value = intAssoc
End Sub

All this works fine to the extent the form opens on the record of what was
entered in the values of Evaluator.Value and Associate.Value. However, at
the bottom of my form, I see it's Record 1 of 139.

Now if I put the prompts in the criteria field of a query instead of using
the code above, I still get the same record, however, it shows Record 1 of 1.

Is there any way to code the form so when the code is ran, I'll end up
getting Record 1 of 1.

I need this to ensure my users won't accidentally go to another record and I
need to be able to code the filter instead of putting the prompt in the query
criteria.

I'm thinking I need to use the the following, but am unsure how to code it.
Filter = "...."
FilterOn = True

I hope all this makes sense. Thanks in advance for your assistance.

LDMueller
Click to expand...
Click to expand...
 
I'm still not sure what event is firing the code that you referenced in your
latest message, or what form it is being fired from.

Since the combo box values appear to be text values, and the individual
stLinkCriteria rows appear to work individually, then try the following:

stLinkCriteria = "[AssocInit]=" & "'" & Me![cboAssociate] & "'" _
& " AND " _
& "[Evaluator]=" & "'" & Me![cboEvaluator] & "'"

If you still get a parameter prompt, indicate what they are asking for in
your next post.

You should be able to delete the following lines:

intEval = cboEvaluator.Value
intAssoc = cboAssociate.Value
Forms!frmAssocEval.Form.Evaluator.Value = intEval
Forms!frmAssocEval.Form.Associate.Value = intAssoc


----
HTH
Dale



LDMueller said:
First of all I sincerely thank you for helping me.

When I tried to used your code I received a run-time Error '3075':
Syntax error (missing operator) in query express '[Evaluator] = AND
[Associate] = '.

Here's the code I used
Dim stDocName As String
Dim stLinkCriteria As String
Dim intEval As Variant
Dim intAssoc As Variant
stDocName = "frmAssocEval"
stLinkCriteria = "[cboEvaluator] = " & intEval & " AND [cboAssociate] =
" & intAssoc
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog
intEval = cboEvaluator.Value
intAssoc = cboAssociate.Value
Forms!frmAssocEval.Form.Evaluator.Value = intEval
Forms!frmAssocEval.Form.Associate.Value = intAssoc

I believe part of the problem is I didn't provide enough information. So I
relabelled a few fields and will try to explain it better.

I have a table named EvalData with the following fields and a query named
qryEvalData which contains those fields.
Associate
Evaluator
HrRange
Comments

I have a form named cmdEvalAssoc (based on qryEvalData) which contains two
combo boxes Evaluator and Associate.

I have another form named frmEvalWho (based on qryEvalData) which contains
two combo boxes cboEvaluator and cboAssociate and a command button,
cmdEvalAssoc, which I use to open the form cmdEvalAssoc and filter for the
following:
stLinkCriteria = "[AssocInit]=" & "'" & Me![cboAssociate] & "'"
stLinkCriteria = "[Evaluator]=" & "'" & Me![cboEvaluator] & "'"

Individually, the stLinkCriteria works fine, but when I try to combine them
with "and" I get parameter prompts.

Does this information help?

Dale Fye said:
LD,

You use the stLinkCriteria parameter in the line that has the OpenForm
method, but you are not defining the value for that variable. The other
problem is that when you set the values of Evaluator and Associate you are
changing those values for the first record in the form. The other issue you
should be aware of is that if you use the acDialog window mode of the Open
Form method, the code following the OpenForm will not fire until you close or
hide the form. This is the technique I usually use to ensure that the users
closes the form before doing anything else.

If what you want to do is open the form for Evaluator = intEval and
Associate = intAssociate, then you need to define stLinkCriteria and then
open the form.

stDocName = "frmAssocEval"
stLinkCriteria = "[Evaluator] = " & intEval & " AND [Associate] = " &
intAssociate
docmd.openform stDocName,,,stLinkCriteria,,acDialog


----
HTH
Dale



LDMueller said:
In Access 2003, I have a form named frmEvalWho. On this form I have a
command button and it's coded as follows:
Private Sub cmdEvalAssoc_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim intEval As Variant
Dim intAssoc As Variant
stDocName = "frmAssocEval"
DoCmd.OpenForm stDocName, , , stLinkCriteria
intEval = Evaluator.Value
intAssoc = Associate.Value
Forms!frmAssocEval.Form.Evaluator.Value = intEval
Forms!frmAssocEval.Form.Associate.Value = intAssoc
End Sub

All this works fine to the extent the form opens on the record of what was
entered in the values of Evaluator.Value and Associate.Value. However, at
the bottom of my form, I see it's Record 1 of 139.

Now if I put the prompts in the criteria field of a query instead of using
the code above, I still get the same record, however, it shows Record 1 of 1.

Is there any way to code the form so when the code is ran, I'll end up
getting Record 1 of 1.

I need this to ensure my users won't accidentally go to another record and I
need to be able to code the filter instead of putting the prompt in the query
criteria.

I'm thinking I need to use the the following, but am unsure how to code it.
Filter = "...."
FilterOn = True

I hope all this makes sense. Thanks in advance for your assistance.

LDMueller
Click to expand...
Click to expand...
Click to expand...
 
That was exactly what I needed. I sincerely thank you for your time.

LDMueller

Dale Fye said:
I'm still not sure what event is firing the code that you referenced in your
latest message, or what form it is being fired from.

Since the combo box values appear to be text values, and the individual
stLinkCriteria rows appear to work individually, then try the following:

stLinkCriteria = "[AssocInit]=" & "'" & Me![cboAssociate] & "'" _
& " AND " _
& "[Evaluator]=" & "'" & Me![cboEvaluator] & "'"

If you still get a parameter prompt, indicate what they are asking for in
your next post.

You should be able to delete the following lines:

intEval = cboEvaluator.Value
intAssoc = cboAssociate.Value
Forms!frmAssocEval.Form.Evaluator.Value = intEval
Forms!frmAssocEval.Form.Associate.Value = intAssoc


----
HTH
Dale



LDMueller said:
First of all I sincerely thank you for helping me.

When I tried to used your code I received a run-time Error '3075':
Syntax error (missing operator) in query express '[Evaluator] = AND
[Associate] = '.

Here's the code I used
Dim stDocName As String
Dim stLinkCriteria As String
Dim intEval As Variant
Dim intAssoc As Variant
stDocName = "frmAssocEval"
stLinkCriteria = "[cboEvaluator] = " & intEval & " AND [cboAssociate] =
" & intAssoc
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog
intEval = cboEvaluator.Value
intAssoc = cboAssociate.Value
Forms!frmAssocEval.Form.Evaluator.Value = intEval
Forms!frmAssocEval.Form.Associate.Value = intAssoc

I believe part of the problem is I didn't provide enough information. So I
relabelled a few fields and will try to explain it better.

I have a table named EvalData with the following fields and a query named
qryEvalData which contains those fields.
Associate
Evaluator
HrRange
Comments

I have a form named cmdEvalAssoc (based on qryEvalData) which contains two
combo boxes Evaluator and Associate.

I have another form named frmEvalWho (based on qryEvalData) which contains
two combo boxes cboEvaluator and cboAssociate and a command button,
cmdEvalAssoc, which I use to open the form cmdEvalAssoc and filter for the
following:
stLinkCriteria = "[AssocInit]=" & "'" & Me![cboAssociate] & "'"
stLinkCriteria = "[Evaluator]=" & "'" & Me![cboEvaluator] & "'"

Individually, the stLinkCriteria works fine, but when I try to combine them
with "and" I get parameter prompts.

Does this information help?

Dale Fye said:
LD,

You use the stLinkCriteria parameter in the line that has the OpenForm
method, but you are not defining the value for that variable. The other
problem is that when you set the values of Evaluator and Associate you are
changing those values for the first record in the form. The other issue you
should be aware of is that if you use the acDialog window mode of the Open
Form method, the code following the OpenForm will not fire until you close or
hide the form. This is the technique I usually use to ensure that the users
closes the form before doing anything else.

If what you want to do is open the form for Evaluator = intEval and
Associate = intAssociate, then you need to define stLinkCriteria and then
open the form.

stDocName = "frmAssocEval"
stLinkCriteria = "[Evaluator] = " & intEval & " AND [Associate] = " &
intAssociate
docmd.openform stDocName,,,stLinkCriteria,,acDialog


----
HTH
Dale



:

In Access 2003, I have a form named frmEvalWho. On this form I have a
command button and it's coded as follows:
Private Sub cmdEvalAssoc_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim intEval As Variant
Dim intAssoc As Variant
stDocName = "frmAssocEval"
DoCmd.OpenForm stDocName, , , stLinkCriteria
intEval = Evaluator.Value
intAssoc = Associate.Value
Forms!frmAssocEval.Form.Evaluator.Value = intEval
Forms!frmAssocEval.Form.Associate.Value = intAssoc
End Sub

All this works fine to the extent the form opens on the record of what was
entered in the values of Evaluator.Value and Associate.Value. However, at
the bottom of my form, I see it's Record 1 of 139.

Now if I put the prompts in the criteria field of a query instead of using
the code above, I still get the same record, however, it shows Record 1 of 1.

Is there any way to code the form so when the code is ran, I'll end up
getting Record 1 of 1.

I need this to ensure my users won't accidentally go to another record and I
need to be able to code the filter instead of putting the prompt in the query
criteria.

I'm thinking I need to use the the following, but am unsure how to code it.
Filter = "...."
FilterOn = True

I hope all this makes sense. Thanks in advance for your assistance.

LDMueller
Click to expand...
Click to expand...
Click to expand...
 
Back
Top