Form Variable

[Drake12]

Well here is an update to the earlier post. I figured out a few things I was doing wrong and here is the updated code:

Private Sub Command0_Click()
Dim strReport As String 'Name of report to open.
Dim strField1 As String 'Name of your Harvest field.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "Contractor's Weekly Summary Report"
strField = "HARV"
strField1 = "DATE1"


' Debug.Print strWhere 'For debugging purposes only.
If Not IsNull(Me.cboHARV) Then 'block combo box
strWhere = "[HARV] = """ & Me.cboHARV & """"
End If


If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField1 & " < " & Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField1 & " > " & Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere = strField1 & " Between " & Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If


DoCmd.OpenReport strReport, acViewPreview, , strWhere
Me.Visible = False
End Sub


Private Sub Command1_Click()
DoCmd.Close acForm, Me.Name
End Sub


The last and final thing I need to accomplish on this form is to let the user input a variable which will be used in a calculation query and thus reported on the report we are trying to generate here. I have created a text box on the form and need to store a variable [CHAPPX] in a query. I know it is not much but what code do I need to include to do this??

Thanks

 

[TheAceMan1]

How are ya Drake12 . . . . .

Where the variable appears in the query, replace with:

[blue]   Nz(Forms!YourFormName!CHAPPX,0)[/blue]

Note: the code assumes [blue]CHAPPX is not an divisor[/blue].

If you have any problem with results, post the query.

See Ya! . . . . . .

 

[Drake12]

TheAceMan1, thanks for the reply. I got a syntax error when I replaced CHAPPX with the code that you gave me. This is the end of the SQL for the query where CHAPPX is used. Also what do I put in for the row source for the text box I am using on the form? I just need the user to input a variable in the text box and have all the fields in the query * by it and reported.

Thanks for your time.




([SBOX]*[CRATE])*[CHAPPX] AS CHAPP, [GROSSPR]-[CHAPP] AS NETPR, [Fruit Table].[CHECK#], [Fruit Table].CHAPPX FROM [Fruit Table];

 

[TheAceMan1]

Drake12 . . . . .

Did you replace [blue]YourFormName[/blue] with the actual name of the form?

If your talking a [blue]subform[/blue], let me know [blue]what level subform[/blue] . . . as code referece has to change.

See Ya! . . . . . .