Using a SQL Statement as RecordSource for a Form

[kopy]

I'm trying to use a combo box on a form as the parameter to filter a form (frmDataEntry). The field that I want to filter is actually on a subform (subSpecimenData) and is called [Tissue Site]. The code runs but it doesn't filter the recordset based on the value of the combo box.

Any help will be greatly appreciated, Kopy
===================================

Private Sub cboTSUSite_AfterUpdate()

Dim stDocName, strSQL As String
Dim stLinkCriteria As String

stDocName = "frmDataEntry"
DoCmd.OpenForm stDocName, , , stLinkCriteria

strSQL = "SELECT tblPatients.*, tblTissueSamples.*"
strSQL = strSQL & "FROM tblPatients RIGHT JOIN tblTissueSamples ON tblPatients.[Pt MRN] = tblTissueSamples.[Pt MRN]"
strSQL = strSQL & " WHERE (((tblTissueSamples.[Tissue Site])=[forms]![frmLookUp]![cboTSUSite]));"

Forms![frmDataEntry].RecordSource = strSQL
Me.Requery

DoCmd.Maximize

End Sub

 

[TheAceMan1]

How are ya kopy . . .

For starters a space is missing in your SQL statement and it appears you need to concatenate the combobox in your where clause:

[blue]    strSQL = "SELECT tblPatients.*, tblTissueSamples.*"
    strSQL = strSQL & "[COLOR=red red] [/color]FROM tblPatients RIGHT JOIN tblTissueSamples ON tblPatients.[Pt MRN] = tblTissueSamples.[Pt MRN]"
    strSQL = strSQL & " WHERE (((tblTissueSamples.[Tissue Site])=[red][b]'" & forms!frmLookUp!cboTSUSite & "'));"[/b][/red][/blue]

Next you say:

[blue][purple]The field that I want to filter is actually on a subform[/purple] (subSpecimenData) and is called [Tissue Site]. [/blue]

This indicates you want to filter the subform. However, you don't reference the subform, you reference the mainform when changing the [blue]RecordSource[/blue].

Making corrections I come up with:

[blue]   Dim SQL As String, sfrm As Form
   
   DoCmd.OpenForm "frmDataEntry"
   DoCmd.Maximize
   DoEvents
   Set [purple][b]sfrm[/b][/purple] = Forms!frmDataEntry!subSpecimenData.Form
   
   SQL = "SELECT tblPatients.*, tblTissueSamples.* " & _
         "FROM tblPatients " & _
         "RIGHT JOIN tblTissueSamples " & _
         "ON tblPatients.[Pt MRN] = tblTissueSamples.[Pt MRN] " & _
         "WHERE ([tblTissueSamples].[Tissue Site]='" & forms!frmLookUp!cboTSUSite & "');"
   [purple][b]sfrm[/b][/purple].RecordSource = SQL
   
   Set [purple][b]sfrm[/b][/purple] = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]