Passing Values to WHERE Clause in Subform Recordsource 2

[larrydavid]

Hello,

When I try to pass parameters to the from the controls on the main form I get a popup box for "Enter Parameter Value".

I'm trying to send values to the WHERE clause of a recordsource (VBA) query of a subform on the click event of a command button.

The button is on the main form.

The subform and main form are not linked, the main form is a tab control on which the subform sits.

I have two textboxes (Start Date) and (End Date) on the main form which I am trying to pass as date parameter values to the recordsource of the subform:

Private Sub cmdRefreshHistory_Click()

 Dim intPaidDtDiff As Integer
 
 intPaidDtDiff = DateDiff("D", [Form_FRM_New TEST].txtStartDate, [Form_FRM_New TEST].txtEndDate)
 
 'check if date range is 90 days or less
 If intPaidDtDiff <= 90 Then
    
    If IsNull([Form_FRM_New TEST].txtStartDate = True) Or IsNull([Form_FRM_New TEST].txtEndDate = True) Then
       MsgBox "Please select a Start and End date."
    Else
       [Form_FRM_New TEST].tbl_Audit_Trail_subform.Form.RecordSource = "SELECT DISTINCTROW tbl_Audit_Trail.ID, tbl_Audit_Trail.Clmno, tbl_Audit_Trail.[Field Name], tbl_Audit_Trail.[Old Value], tbl_Audit_Trail.[New Value], tbl_Audit_Trail.TimeStamp, tbl_Audit_Trail.[User ID], tbl_Audit_Trail.Region      FROM tbl_Audit_Trail      WHERE (((tbl_Audit_Trail.TimeStamp) Between [Form_FRM_New TEST].txtStartDate.Value And [Form_FRM_New TEST].txtStartDate.Value))      ORDER BY tbl_Audit_Trail.TimeStamp DESC;"
    End If
 Else
    MsgBox "Please select 90 days or less."
    [Form_FRM_New TEST].txtStartDate.SetFocus
 End If

I have the recordsource property for the subform set by default (load last 30 days), which works fine when the tab/subform load:

'[Form_FRM_New ARIS].[tbl_Audit_Trail subform].Form.RecordSource = "SELECT DISTINCTROW tbl_Audit_Trail.ID, tbl_Audit_Trail.Clmno, tbl_Audit_Trail.[Field Name], tbl_Audit_Trail.[Old Value], tbl_Audit_Trail.[New Value], tbl_Audit_Trail.TimeStamp, tbl_Audit_Trail.[User ID], tbl_Audit_Trail.Region FROM tbl_Audit_Trail WHERE (((tbl_Audit_Trail.TimeStamp)>Date()-30)) ORDER BY tbl_Audit_Trail.TimeStamp DESC;"

I've also tried different syntax variations for the (Between [txtStartDate] And [txtEndDate]) parameters, for instance, Me.Parent!txtStartDate.Value.

Once I get this I'm good, so any help would be greatly appreciated.

Thanks,
Larry

 

[MajP]

At a minimum
[Forms]![YourFormName]![YourControlName]

 

[lameid]

[Form_FRM_New TEST].txtStartDate.Value is not good syntax to reference a form in a query...

Try something like...
Forms![Form_FRM_New TEST]!txtStartDate

And if that is on the main form you could just concatentate the literals in and skip the parameters altogether.

[green]...[/green]
WHERE (((tbl_Audit_Trail.TimeStamp) Between #" & _ Me!txtStartDate & "# And #" & Me!txtStartDate & "#))" & _ 
"ORDER BY tbl_Audit_Trail.TimeStamp DESC;"

 

[lameid]

Oops missed an enter in there...

[red]...[/red]WHERE (((tbl_Audit_Trail.TimeStamp) Between #" & _ 
Me!txtStartDate & "# And #" & Me!txtStartDate & "#))" & _ 
"ORDER BY tbl_Audit_Trail.TimeStamp DESC;"

 

[larrydavid]

THANK YOU!!!