How can I "catch" query parameters and pass it to the form?

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

Guest

I have a query and when I run it, two dialog pops up asking to enter two
values. Base on these values, the query will return all records meeting the
conditions. Now, I want to "catch" these values and use them as criteria to
manipulate data from the query and show corresponding records on the form.
Can you show me how to do it?
Thank you,
 
Tim,

If you mean you'd like to open a form displaying results from the query, set
the form's RecordSource to the query, being sure to include all fields in the
query that are in the current RecordSource. You can do this on the fly, say,
attached to a button, by:

stDocName = "YourFormName"
DoCmd.OpenForm stDocName
Forms!YourFormName.RecordSource = "BatchesQuery"

Hope that helps.

Sprinks
 
Hi Sprinks:
Thank you for your help. Actually, it is a little bit more difficult. When
the query returns the records set, say 10 records, I would like all of them
are displayed on the single form, in a text box: txtString (or text boxes)
with the code (sample) as following,
If Me![Student] = "1st Grade" Then
Me.txtString = "Your class" & [Value1] & " on " & [Value2] & " #"
& [Value3] & "."
ElseIf Me![Student] = "2nd Grade" Then
Me.txtString = "Your class " & [Value1] & " by " & [Value2] & " ."
Else
Me.txtString = " "
End If

The problem is the form just show the first value of Student and then the
same value is showed for all records. But I need to show various statement on
the txtString based on the query. In addtion, txtString is empty if [Student]
value is empty. Can you please show me how to do that?
Thank you a lot. T.
 
Hi, Tim.

This is most easily done with a custom function. Place an unbound textbox
in the Detail section of your form.

Select View, Code to enter the form's code module, and define the following
function. Select...Case is more convenient when there are more than two
cases of condition:

Private Function MyString() As String

Select Case Me![Student]
Case "1st Grade"
MyString = "Your class" & Me![Value1] & " on " & Me![Value2] &
Me![Value3] & "."
Case "2nd Grade"
MyString = "Your class " & [Value1] & " by " & [Value2] & " ."
Case Else
MyString = " "

End Select

End Function

Then set the textbox' ControlSource to:

=MyString()

Sprinks

Tim said:
Hi Sprinks:
Thank you for your help. Actually, it is a little bit more difficult. When
the query returns the records set, say 10 records, I would like all of them
are displayed on the single form, in a text box: txtString (or text boxes)
with the code (sample) as following,
If Me![Student] = "1st Grade" Then
Me.txtString = "Your class" & [Value1] & " on " & [Value2] & " #"
& [Value3] & "."
ElseIf Me![Student] = "2nd Grade" Then
Me.txtString = "Your class " & [Value1] & " by " & [Value2] & " ."
Else
Me.txtString = " "
End If

The problem is the form just show the first value of Student and then the
same value is showed for all records. But I need to show various statement on
the txtString based on the query. In addtion, txtString is empty if [Student]
value is empty. Can you please show me how to do that?
Thank you a lot. T.


Sprinks said:
Tim,

If you mean you'd like to open a form displaying results from the query, set
the form's RecordSource to the query, being sure to include all fields in the
query that are in the current RecordSource. You can do this on the fly, say,
attached to a button, by:

stDocName = "YourFormName"
DoCmd.OpenForm stDocName
Forms!YourFormName.RecordSource = "BatchesQuery"

Hope that helps.

Sprinks
Click to expand...
Click to expand...
 
Hi Sprinks,
Thank you again for your help. Your function MyString() makes sense to me. I
will test it very soon. Here is one more question, since the query will get
all records that meet conditions, say, 10 records, how would all of they are
displayed on the text box, txtString? I would like to show all of these 10
values.

Sprinks said:
Hi, Tim.

This is most easily done with a custom function. Place an unbound textbox
in the Detail section of your form.

Select View, Code to enter the form's code module, and define the following
function. Select...Case is more convenient when there are more than two
cases of condition:

Private Function MyString() As String

Select Case Me![Student]
Case "1st Grade"
MyString = "Your class" & Me![Value1] & " on " & Me![Value2] &
Me![Value3] & "."
Case "2nd Grade"
MyString = "Your class " & [Value1] & " by " & [Value2] & " ."
Case Else
MyString = " "

End Select

End Function

Then set the textbox' ControlSource to:

=MyString()

Sprinks

Tim said:
Hi Sprinks:
Thank you for your help. Actually, it is a little bit more difficult. When
the query returns the records set, say 10 records, I would like all of them
are displayed on the single form, in a text box: txtString (or text boxes)
with the code (sample) as following,
If Me![Student] = "1st Grade" Then
Me.txtString = "Your class" & [Value1] & " on " & [Value2] & " #"
& [Value3] & "."
ElseIf Me![Student] = "2nd Grade" Then
Me.txtString = "Your class " & [Value1] & " by " & [Value2] & " ."
Else
Me.txtString = " "
End If

The problem is the form just show the first value of Student and then the
same value is showed for all records. But I need to show various statement on
the txtString based on the query. In addtion, txtString is empty if [Student]
value is empty. Can you please show me how to do that?
Thank you a lot. T.


Sprinks said:
Tim,

If you mean you'd like to open a form displaying results from the query, set
the form's RecordSource to the query, being sure to include all fields in the
query that are in the current RecordSource. You can do this on the fly, say,
attached to a button, by:

stDocName = "YourFormName"
DoCmd.OpenForm stDocName
Forms!YourFormName.RecordSource = "BatchesQuery"

Hope that helps.

Sprinks


:

I have a query and when I run it, two dialog pops up asking to enter two
values. Base on these values, the query will return all records meeting the
conditions. Now, I want to "catch" these values and use them as criteria to
manipulate data from the query and show corresponding records on the form.
Can you show me how to do it?
Thank you,
Click to expand...
Click to expand...
Click to expand...
 
Hi jahoobob:
Thank you for your help. You are right about my first question. Sprinks
gives me direction to solve a little more difficult issue.
T.
 
Hi Sprinks,
I tested it and it worked. Thank you very much! About the "show all
records", I just change the property of the form to "continuous form" and it
took care of the issue.
Thank you again.
Tim

Tim said:
Hi Sprinks,
Thank you again for your help. Your function MyString() makes sense to me. I
will test it very soon. Here is one more question, since the query will get
all records that meet conditions, say, 10 records, how would all of they are
displayed on the text box, txtString? I would like to show all of these 10
values.

Sprinks said:
Hi, Tim.

This is most easily done with a custom function. Place an unbound textbox
in the Detail section of your form.

Select View, Code to enter the form's code module, and define the following
function. Select...Case is more convenient when there are more than two
cases of condition:

Private Function MyString() As String

Select Case Me![Student]
Case "1st Grade"
MyString = "Your class" & Me![Value1] & " on " & Me![Value2] &
Me![Value3] & "."
Case "2nd Grade"
MyString = "Your class " & [Value1] & " by " & [Value2] & " ."
Case Else
MyString = " "

End Select

End Function

Then set the textbox' ControlSource to:

=MyString()

Sprinks

Tim said:
Hi Sprinks:
Thank you for your help. Actually, it is a little bit more difficult. When
the query returns the records set, say 10 records, I would like all of them
are displayed on the single form, in a text box: txtString (or text boxes)
with the code (sample) as following,
If Me![Student] = "1st Grade" Then
Me.txtString = "Your class" & [Value1] & " on " & [Value2] & " #"
& [Value3] & "."
ElseIf Me![Student] = "2nd Grade" Then
Me.txtString = "Your class " & [Value1] & " by " & [Value2] & " ."
Else
Me.txtString = " "
End If

The problem is the form just show the first value of Student and then the
same value is showed for all records. But I need to show various statement on
the txtString based on the query. In addtion, txtString is empty if [Student]
value is empty. Can you please show me how to do that?
Thank you a lot. T.


:

Tim,

If you mean you'd like to open a form displaying results from the query, set
the form's RecordSource to the query, being sure to include all fields in the
query that are in the current RecordSource. You can do this on the fly, say,
attached to a button, by:

stDocName = "YourFormName"
DoCmd.OpenForm stDocName
Forms!YourFormName.RecordSource = "BatchesQuery"

Hope that helps.

Sprinks


:

I have a query and when I run it, two dialog pops up asking to enter two
values. Base on these values, the query will return all records meeting the
conditions. Now, I want to "catch" these values and use them as criteria to
manipulate data from the query and show corresponding records on the form.
Can you show me how to do it?
Thank you,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top