How to display the value of a variable on a form or report

[gdaniels]

Hello. I am trying to display on a form or report what a user inputs
for search parameters. I have working VBA code that creates a
variable called CriteriaVal and contains the values of all the user
inputs. So far so good.

Next I need to display the value of CriteriaVal in an unbound text
field, let's call it Criteria, on a form or report. I'm stuck on
getting the unbound text field, Criteria, to display the value in the
variable CriteriaVal. The subroutine follows:

Private Sub cmdcity_Click()
Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim CriteriaVal As String
Set db = CurrentDb
Set qdf = db.QueryDefs("results")

strSQL = "SELECT expnew.* " & _
"FROM expnew " & _
"WHERE expnew.city='" & Me.cbocity.Value & "' " & _
"AND expnew.st='" & Me.cbostb.Value & "' " & _
"AND Expnew.[# units]>=" & Me.bminunits.Value & " " & _
"AND Expnew.[# units]<=" & Me.bmaxunits.Value & " " & _
"AND Expnew.[year built]>=" & Me.bminblt.Value & " " & _
"AND Expnew.[year built]<=" & Me.bmaxblt.Value & " " & _
"AND Expnew.year>=" & Me.bminyr.Value & " " & _
"AND Expnew.year<=" & Me.bmaxyr.Value & " " & _
"ORDER BY expnew.city;"
qdf.SQL = strSQL


DoCmd.OpenQuery "results"
DoCmd.OpenForm "Master Form"

With Forms![Master Form]

.Criteriaval.Value = _
"CITY: " & Me.cbocity.Value & ";" _
& " STATE: " & Me.cbostb.Value & ";" _
& " # OF UNITS: " & Me.bminunits.Value _
& " TO " & Me.bmaxunits.Value & ";" _
& " BUILT: " & Me.bminblt.Value _
& " TO " & Me.bmaxblt.Value & ";" _
& " EXPENSE YEARS: " & Me.bminyr.Value _
& " TO " & Me.bmaxyr.Value & _


End With

DoCmd.Close acForm, Me.Name
Set qdf = Nothing
Set db = Nothing
End Sub




Thanks in advance for any suggestions.

Gary

 

[Marshall Barton]

Hello. I am trying to display on a form or report what a user inputs
for search parameters. I have working VBA code that creates a
variable called CriteriaVal and contains the values of all the user
inputs. So far so good.

Next I need to display the value of CriteriaVal in an unbound text
field, let's call it Criteria, on a form or report. I'm stuck on
getting the unbound text field, Criteria, to display the value in the
variable CriteriaVal. The subroutine follows:

Private Sub cmdcity_Click()
Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim CriteriaVal As String
Set db = CurrentDb
Set qdf = db.QueryDefs("results")

strSQL = "SELECT expnew.* " & _
"FROM expnew " & _
"WHERE expnew.city='" & Me.cbocity.Value & "' " & _
"AND expnew.st='" & Me.cbostb.Value & "' " & _
"AND Expnew.[# units]>=" & Me.bminunits.Value & " " & _
"AND Expnew.[# units]<=" & Me.bmaxunits.Value & " " & _
"AND Expnew.[year built]>=" & Me.bminblt.Value & " " & _
"AND Expnew.[year built]<=" & Me.bmaxblt.Value & " " & _
"AND Expnew.year>=" & Me.bminyr.Value & " " & _
"AND Expnew.year<=" & Me.bmaxyr.Value & " " & _
"ORDER BY expnew.city;"
qdf.SQL = strSQL


DoCmd.OpenQuery "results"
DoCmd.OpenForm "Master Form"

With Forms![Master Form]

.Criteriaval.Value = _
"CITY: " & Me.cbocity.Value & ";" _
& " STATE: " & Me.cbostb.Value & ";" _
& " # OF UNITS: " & Me.bminunits.Value _
& " TO " & Me.bmaxunits.Value & ";" _
& " BUILT: " & Me.bminblt.Value _
& " TO " & Me.bmaxblt.Value & ";" _
& " EXPENSE YEARS: " & Me.bminyr.Value _
& " TO " & Me.bmaxyr.Value & _

You could open the form/report using the OpenArgs argument:

DoCmd.OpenForm "Master Form",
OpenArgs:= "CITY: " & Me.cbocity.Value & ";" _
& " STATE: " & Me.cbostb.Value & ";" _
& " # OF UNITS: " & Me.bminunits.Value _
& " TO " & Me.bmaxunits.Value & ";" _
& " BUILT: " & Me.bminblt.Value _
& " TO " & Me.bmaxblt.Value & ";" _
& " EXPENSE YEARS: " & Me.bminyr.Value _
& " TO " & Me.bmaxyr.Value

Then the master form/report can fill its text box by using a
line of code in main form's Load (or Report Header section's
Format) event procedure:

Me.Criteriaval = Me.OpenArgs