Help on user-defined form to query main table

[GIJoeFigure]

I have gotten tips on how to set up docmd.applyfilter statements but I still need to question the site experts more. Sorry! I do not necessarily need just code tips, but insight into making a form that queries a table and produces results consistent with the query but returns the result records with all the column entries, just like they are listed on the table. I have been having difficulty with this because I have been using statements that specifically query a maximum of 7 columns of the record data out of 30+ columns, and the results I've gotten are just those 7 columns, I didn't even get the record name column, just the filtered attributes. I'll post code below. Thank you all again!

Private Sub Execute_Click()
'~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Original code from PJStephenson below. Adapted for dicom headers
'[URL unfurl="true"]http://www.tek-tips.com/gviewthread.cfm/pid/705/qid/885583[/URL]
'
'WhereClause = ""
'If Me!checkbox1 = True Then
'WhereClause = WhereClause & "[Field1] = '" & Me![Field1] & "' AND "
'End If
'Repeat for all fields you want to add to the where clause

'Chop off the last AND
'If Len(WhereClause) > 0 Then
'WhereClause = Left(WhereClause, Len(WhereClause) - 4)
'End If
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim WhereClause As String

WhereClause = ""

If Me!ProToggle = True Then
WhereClause = WhereClause & "[ViewPosition] = '" & Me![ViewPosition] & "' AND "
' TextGo.Text = WhereClause
End If

If Me!ttToggle = True Then
WhereClause = WhereClause & "[DataType] = '" & Me![DataType] & "' AND "
End If

If Me!ICToggle = True Then
WhereClause = WhereClause & "[ImageComments] = '" & Me![ImageComments] & "' AND "
End If

If Me!BPToggle = True Then
WhereClause = WhereClause & "[BodyPartExamined] = '" & Me![BodyPartExamined] & "' AND "
End If

If Me!PPToggle = True Then
WhereClause = WhereClause & "[PatientPosition] = '" & Me![PatientPosition] & "' AND "
End If

If Me!IToggle = True Then
WhereClause = WhereClause & "[InstitutionName] = '" & Me![InstitutionName] & "' AND "
End If

If Me!FPToggle = True Then
WhereClause = WhereClause & "[DataPath] Like '*\" & Me![DataPath] & "*' AND "
End If

'Chop off the last AND
If Len(WhereClause) > 0 Then
WhereClause = Left(WhereClause, Len(WhereClause) - 4)
End If
resp = MsgBox(WhereClause & " Is this correct?", vbYesNoCancel)

' Now complete and use the completed where statement to filter table entries
If resp = 2 Then Exit Sub
If resp = 7 Then
    WhereClause = InputBox("What is the statement changes you would like?", , WhereClause)
    If WhereClause = "" Then Exit Sub
End If

Forms!RunDicomQuery.RecordSource = "Sample Dicom Table"
DoCmd.ApplyFilter , WhereClause
' Problems with sql syntax or filter?
End Sub

J. Handfield
Interning Scientist
Eastman Kodak Research Labs
Rochester, NY 14650

 

[Zion7]

Not sure offhand, but you can try, instead of Apply filter command...

Forms!RunDicomQuery.Form.Filter = WhereClause
Forms!RunDicomQuery.FilterOn = True

 

[PJStephenson]

one of my favorite ways of doing what dboulos suggested is to set global variables with the where clause and have the onopen event of a form or report check if a filter is to be set then set it.
in a module declare the globals:

Public strFilter As String 
Public blnSetfilter As Boolean

in your code set the variables before opening the form:

strFilter = WhereClause
blnSetfilter = True
docmd.OpenForm "frmYourForm"

then have the following code in the onopen event of the form:

Private Sub Form_Open(Cancel As Integer)
If blnSetfilter = True Then
    Me.FilterOn = True
    Me.Filter = strFilter
Else
    Me.FilterOn = False
    Me.Filter = ""
End If
End Sub

This is esecially useful if printing or exporting forms or reports but is you only want to view them try putting the where clause in the docmd.openform command eg

DoCmd.OpenForm "frmYourForm", acNormal, , WhereClause

Hope this is of interest
Peter

 

[GIJoeFigure]

Thanks for your input. I did find a different way to do it from a MSDN article on Query By Form method and using QueryDef objects. I could possibly use your method in the future though. Thanks