Conditional Parameter to open form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I open a form based on three fields. Currently I have all parameters in a OR
kind criteria of the form source. Is it possible that I can use the
parameters to refine my search. eg: I ask user for PA Name or FP Num or Smpl
Num. I want that if user just enters name then i get all records for that
name. If the user types the name and FP Num, i get records for that name
which have the specified FP Num and so on. In short I want to refine my
search with each additional (optional) parameter entered.
 
How are you using the parameters to open the form? Are you entering them on
another form that opens that form? Are you using them as a where condition?
 
I am using another form.

Klatuu said:
How are you using the parameters to open the form? Are you entering them on
another form that opens that form? Are you using them as a where condition?
Click to expand...
 
Rather... I am using another form to obtain the parameters from the user but
I am feeding the data entered by the user in the condition box of the querry
as Forms![FormName]![Text Entered]
 
Okay, but how are you passing the information to the form you are opening?
Maybe you could post your code.
I will be happy to help, but there are a number of ways to do this, and I
would like to see what you are doing now so we can go from there.
 
Well, I don't have much code. What I have is an unbound form frmLookUp with
three text boxes --> PA, FPN, SMPL and a button to open a form frmDDM, which
is based on a table DDM. It essentially shows all fields of table DDM and in
the record source of the form I have table DDM with Criteria on the fields in
frmLookUp. I just use [Forms]![frmLookUp]![PA], []![]![FPN], []![]![SMPL] in
the Criteria fields as OR criteria to open the form showing desired records.
But I am not able to refine the search with this setup. If the user enters
all three fields on the frmLookUp then I need to open frmDDM showing record
for given PA, given FPN and given SMPL whereas now it shows all records for
the PA. If I use those criteria in AND criteria all parameters need to be
entered correctly on frmLookUp and user wont be able to see all records in
DDM for a given PA etc.
Hope it makes sense. Please let me know if I can give more information in
any other form.
 
Okay, gotcha.
I will assume you have a command button on the first form that opens the
other form. So in the Click event of the command button is where you will
need to do this. I will guess that the Criteria row of your query you have
something like:
[PA Name] = Forms!Form1!txtPAName
If my assumptions are correct, then what I would suggest is you create 3
invisible controls on your first form for the Query to reference, and put the
data in them.
Then, in the Click event of your command button
Me.txtPANameQry = Iif(IsNull(Me.txtPAName), "Like *", Me.txtPAName)
Me.txt FPNumQry = Iif(IsNull(Me.txtFPNum), "Like *", Me.txtFPNum)
Me.txtSmplQry = Iif(IsNull(Me.txtSmpl), "Like *", Me.txtSmpl)

Abhijeet said:
Rather... I am using another form to obtain the parameters from the user but
I am feeding the data entered by the user in the condition box of the querry
as Forms![FormName]![Text Entered]

Klatuu said:
How are you using the parameters to open the form? Are you entering them on
another form that opens that form? Are you using them as a where condition?
Click to expand...
Click to expand...
 
hmmm I did it but it doesnt seem to be working for some reason. I guess
feeding a Like * as parameter value does not act same as putting Like * in
criteria.

Klatuu said:
Okay, gotcha.
I will assume you have a command button on the first form that opens the
other form. So in the Click event of the command button is where you will
need to do this. I will guess that the Criteria row of your query you have
something like:
[PA Name] = Forms!Form1!txtPAName
If my assumptions are correct, then what I would suggest is you create 3
invisible controls on your first form for the Query to reference, and put the
data in them.
Then, in the Click event of your command button
Me.txtPANameQry = Iif(IsNull(Me.txtPAName), "Like *", Me.txtPAName)
Me.txt FPNumQry = Iif(IsNull(Me.txtFPNum), "Like *", Me.txtFPNum)
Me.txtSmplQry = Iif(IsNull(Me.txtSmpl), "Like *", Me.txtSmpl)

Abhijeet said:
Rather... I am using another form to obtain the parameters from the user but
I am feeding the data entered by the user in the condition box of the querry
as Forms![FormName]![Text Entered]

Klatuu said:
How are you using the parameters to open the form? Are you entering them on
another form that opens that form? Are you using them as a where condition?

:

Hi,
I open a form based on three fields. Currently I have all parameters in a OR
kind criteria of the form source. Is it possible that I can use the
parameters to refine my search. eg: I ask user for PA Name or FP Num or Smpl
Num. I want that if user just enters name then i get all records for that
name. If the user types the name and FP Num, i get records for that name
which have the specified FP Num and so on. In short I want to refine my
search with each additional (optional) parameter entered.
Click to expand...
Click to expand...
Click to expand...
 
Interesting, it should be the same.

Abhijeet said:
hmmm I did it but it doesnt seem to be working for some reason. I guess
feeding a Like * as parameter value does not act same as putting Like * in
criteria.

Klatuu said:
Okay, gotcha.
I will assume you have a command button on the first form that opens the
other form. So in the Click event of the command button is where you will
need to do this. I will guess that the Criteria row of your query you have
something like:
[PA Name] = Forms!Form1!txtPAName
If my assumptions are correct, then what I would suggest is you create 3
invisible controls on your first form for the Query to reference, and put the
data in them.
Then, in the Click event of your command button
Me.txtPANameQry = Iif(IsNull(Me.txtPAName), "Like *", Me.txtPAName)
Me.txt FPNumQry = Iif(IsNull(Me.txtFPNum), "Like *", Me.txtFPNum)
Me.txtSmplQry = Iif(IsNull(Me.txtSmpl), "Like *", Me.txtSmpl)

Abhijeet said:
Rather... I am using another form to obtain the parameters from the user but
I am feeding the data entered by the user in the condition box of the querry
as Forms![FormName]![Text Entered]

:

How are you using the parameters to open the form? Are you entering them on
another form that opens that form? Are you using them as a where condition?

:

Hi,
I open a form based on three fields. Currently I have all parameters in a OR
kind criteria of the form source. Is it possible that I can use the
parameters to refine my search. eg: I ask user for PA Name or FP Num or Smpl
Num. I want that if user just enters name then i get all records for that
name. If the user types the name and FP Num, i get records for that name
which have the specified FP Num and so on. In short I want to refine my
search with each additional (optional) parameter entered.
Click to expand...
Click to expand...
Click to expand...
 
Can you suggest some other method to use this possibly with another means to
open my DDM form.

Klatuu said:
Interesting, it should be the same.

Abhijeet said:
hmmm I did it but it doesnt seem to be working for some reason. I guess
feeding a Like * as parameter value does not act same as putting Like * in
criteria.

Klatuu said:
Okay, gotcha.
I will assume you have a command button on the first form that opens the
other form. So in the Click event of the command button is where you will
need to do this. I will guess that the Criteria row of your query you have
something like:
[PA Name] = Forms!Form1!txtPAName
If my assumptions are correct, then what I would suggest is you create 3
invisible controls on your first form for the Query to reference, and put the
data in them.
Then, in the Click event of your command button
Me.txtPANameQry = Iif(IsNull(Me.txtPAName), "Like *", Me.txtPAName)
Me.txt FPNumQry = Iif(IsNull(Me.txtFPNum), "Like *", Me.txtFPNum)
Me.txtSmplQry = Iif(IsNull(Me.txtSmpl), "Like *", Me.txtSmpl)

:

Rather... I am using another form to obtain the parameters from the user but
I am feeding the data entered by the user in the condition box of the querry
as Forms![FormName]![Text Entered]

:

How are you using the parameters to open the form? Are you entering them on
another form that opens that form? Are you using them as a where condition?

:

Hi,
I open a form based on three fields. Currently I have all parameters in a OR
kind criteria of the form source. Is it possible that I can use the
parameters to refine my search. eg: I ask user for PA Name or FP Num or Smpl
Num. I want that if user just enters name then i get all records for that
name. If the user types the name and FP Num, i get records for that name
which have the specified FP Num and so on. In short I want to refine my
search with each additional (optional) parameter entered.
Click to expand...
Click to expand...
Click to expand...
 
In the OnClick event that opens the second form, build a WhereCondition from
the three parameter fields, using the following format:

If Not IsNull(Field1) Then
WhCond = "Field1 = '" & Field1 & "'"
End If

If Not IsNull(Field2) Then
WhCond = "Field2 = " & Field1
End If

If Not IsNull(Field3) Then
WhCond = "Field3 = " & Field1
End If

This will build only those parameters that are appropriate. Pass the
resultant string WhCond to the WhereCondition argument of the Docmd.OpenForm
code.

Not that the example for Field 1 includes single quotes around the search
value. These are necessary if the search parameter is a string but not for a
numeric. For a date value, convert it to a string using the Format$ VBA
routine and surround it with # characters

Abhijeet said:
Can you suggest some other method to use this possibly with another means to
open my DDM form.

Klatuu said:
Interesting, it should be the same.

Abhijeet said:
hmmm I did it but it doesnt seem to be working for some reason. I guess
feeding a Like * as parameter value does not act same as putting Like * in
criteria.

:

Okay, gotcha.
I will assume you have a command button on the first form that opens the
other form. So in the Click event of the command button is where you will
need to do this. I will guess that the Criteria row of your query you have
something like:
[PA Name] = Forms!Form1!txtPAName
If my assumptions are correct, then what I would suggest is you create 3
invisible controls on your first form for the Query to reference, and put the
data in them.
Then, in the Click event of your command button
Me.txtPANameQry = Iif(IsNull(Me.txtPAName), "Like *", Me.txtPAName)
Me.txt FPNumQry = Iif(IsNull(Me.txtFPNum), "Like *", Me.txtFPNum)
Me.txtSmplQry = Iif(IsNull(Me.txtSmpl), "Like *", Me.txtSmpl)

:

Rather... I am using another form to obtain the parameters from the user but
I am feeding the data entered by the user in the condition box of the querry
as Forms![FormName]![Text Entered]

:

How are you using the parameters to open the form? Are you entering them on
another form that opens that form? Are you using them as a where condition?

:

Hi,
I open a form based on three fields. Currently I have all parameters in a OR
kind criteria of the form source. Is it possible that I can use the
parameters to refine my search. eg: I ask user for PA Name or FP Num or Smpl
Num. I want that if user just enters name then i get all records for that
name. If the user types the name and FP Num, i get records for that name
which have the specified FP Num and so on. In short I want to refine my
search with each additional (optional) parameter entered.
Click to expand...
Click to expand...
Click to expand...
 
The solution you posted will not include all three fields. It will only
include the where for the last field that is not null. In addition, in the
last two conditions, the wrong field is being referenced. To get all three,
you need the following modifications:
Dim WhCond1 as String
Dim WhCond2 as String
Dim WhCond3 as String
Dim WhCond as String

WhCond1 = Iif(IsNull(Field1),"", "Field1 = '" & Field1 & "'")
WhCond2 = Iif(IsNull(Field2),"", "Field2 = '" & Field2 & "'")
WhCond3 = Iif(IsNull(Field3),"", "Field3 = '" & Field3 & "'")
If WhCond1 <> "" Then
WhCond = WhCond1
End If

If WhCond2 <> "" Then
If WhCond = "" Then
WhCond = WhCond2
Else
WhCond = WhCond & " AND " & WhCond2
End If
End If

If WhCond3 <> "" Then
If WhCond = "" Then
WhCond = WhCond3
Else
WhCond = WhCond & " AND " & WhCond3
End If
End If

Keithr said:
In the OnClick event that opens the second form, build a WhereCondition from
the three parameter fields, using the following format:

If Not IsNull(Field1) Then
WhCond = "Field1 = '" & Field1 & "'"
End If

If Not IsNull(Field2) Then
WhCond = "Field2 = " & Field1
End If

If Not IsNull(Field3) Then
WhCond = "Field3 = " & Field1
End If

This will build only those parameters that are appropriate. Pass the
resultant string WhCond to the WhereCondition argument of the Docmd.OpenForm
code.

Not that the example for Field 1 includes single quotes around the search
value. These are necessary if the search parameter is a string but not for a
numeric. For a date value, convert it to a string using the Format$ VBA
routine and surround it with # characters

Abhijeet said:
Can you suggest some other method to use this possibly with another means to
open my DDM form.

Klatuu said:
Interesting, it should be the same.

:

hmmm I did it but it doesnt seem to be working for some reason. I guess
feeding a Like * as parameter value does not act same as putting Like * in
criteria.

:

Okay, gotcha.
I will assume you have a command button on the first form that opens the
other form. So in the Click event of the command button is where you will
need to do this. I will guess that the Criteria row of your query you have
something like:
[PA Name] = Forms!Form1!txtPAName
If my assumptions are correct, then what I would suggest is you create 3
invisible controls on your first form for the Query to reference, and put the
data in them.
Then, in the Click event of your command button
Me.txtPANameQry = Iif(IsNull(Me.txtPAName), "Like *", Me.txtPAName)
Me.txt FPNumQry = Iif(IsNull(Me.txtFPNum), "Like *", Me.txtFPNum)
Me.txtSmplQry = Iif(IsNull(Me.txtSmpl), "Like *", Me.txtSmpl)

:

Rather... I am using another form to obtain the parameters from the user but
I am feeding the data entered by the user in the condition box of the querry
as Forms![FormName]![Text Entered]

:

How are you using the parameters to open the form? Are you entering them on
another form that opens that form? Are you using them as a where condition?

:

Hi,
I open a form based on three fields. Currently I have all parameters in a OR
kind criteria of the form source. Is it possible that I can use the
parameters to refine my search. eg: I ask user for PA Name or FP Num or Smpl
Num. I want that if user just enters name then i get all records for that
name. If the user types the name and FP Num, i get records for that name
which have the specified FP Num and so on. In short I want to refine my
search with each additional (optional) parameter entered.
Click to expand...
Click to expand...
Click to expand...
 
Thanks a lot guys! I was almost making up my mind to start creating 3!
querries -- 1 for each case of search field, but you guys saved my day.
Thanks again.
Regards,
Abhijeet Desai

Klatuu said:
The solution you posted will not include all three fields. It will only
include the where for the last field that is not null. In addition, in the
last two conditions, the wrong field is being referenced. To get all three,
you need the following modifications:
Dim WhCond1 as String
Dim WhCond2 as String
Dim WhCond3 as String
Dim WhCond as String

WhCond1 = Iif(IsNull(Field1),"", "Field1 = '" & Field1 & "'")
WhCond2 = Iif(IsNull(Field2),"", "Field2 = '" & Field2 & "'")
WhCond3 = Iif(IsNull(Field3),"", "Field3 = '" & Field3 & "'")
If WhCond1 <> "" Then
WhCond = WhCond1
End If

If WhCond2 <> "" Then
If WhCond = "" Then
WhCond = WhCond2
Else
WhCond = WhCond & " AND " & WhCond2
End If
End If

If WhCond3 <> "" Then
If WhCond = "" Then
WhCond = WhCond3
Else
WhCond = WhCond & " AND " & WhCond3
End If
End If

Keithr said:
In the OnClick event that opens the second form, build a WhereCondition from
the three parameter fields, using the following format:

If Not IsNull(Field1) Then
WhCond = "Field1 = '" & Field1 & "'"
End If

If Not IsNull(Field2) Then
WhCond = "Field2 = " & Field1
End If

If Not IsNull(Field3) Then
WhCond = "Field3 = " & Field1
End If

This will build only those parameters that are appropriate. Pass the
resultant string WhCond to the WhereCondition argument of the Docmd.OpenForm
code.

Not that the example for Field 1 includes single quotes around the search
value. These are necessary if the search parameter is a string but not for a
numeric. For a date value, convert it to a string using the Format$ VBA
routine and surround it with # characters

Abhijeet said:
Can you suggest some other method to use this possibly with another means to
open my DDM form.

:

Interesting, it should be the same.

:

hmmm I did it but it doesnt seem to be working for some reason. I guess
feeding a Like * as parameter value does not act same as putting Like * in
criteria.

:

Okay, gotcha.
I will assume you have a command button on the first form that opens the
other form. So in the Click event of the command button is where you will
need to do this. I will guess that the Criteria row of your query you have
something like:
[PA Name] = Forms!Form1!txtPAName
If my assumptions are correct, then what I would suggest is you create 3
invisible controls on your first form for the Query to reference, and put the
data in them.
Then, in the Click event of your command button
Me.txtPANameQry = Iif(IsNull(Me.txtPAName), "Like *", Me.txtPAName)
Me.txt FPNumQry = Iif(IsNull(Me.txtFPNum), "Like *", Me.txtFPNum)
Me.txtSmplQry = Iif(IsNull(Me.txtSmpl), "Like *", Me.txtSmpl)

:

Rather... I am using another form to obtain the parameters from the user but
I am feeding the data entered by the user in the condition box of the querry
as Forms![FormName]![Text Entered]

:

How are you using the parameters to open the form? Are you entering them on
another form that opens that form? Are you using them as a where condition?

:

Hi,
I open a form based on three fields. Currently I have all parameters in a OR
kind criteria of the form source. Is it possible that I can use the
parameters to refine my search. eg: I ask user for PA Name or FP Num or Smpl
Num. I want that if user just enters name then i get all records for that
name. If the user types the name and FP Num, i get records for that name
which have the specified FP Num and so on. In short I want to refine my
search with each additional (optional) parameter entered.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top