Query By Form Using Same Sub Form and Main Form

Query By Form Using Same Sub Form and Main Form

Post by tomlebol » Sun, 22 Apr 2007 02:02:44


Having problems displaying query results from combo boxes on a sub
form, which is on the same form that is used to select criteria.
This has always worked form me when displaying query results on
another main and sub form.
The requery on the sub form and refresh comands on the main form do
not work when the form is first displayed and when the selection
criteria is changed.
Should I be doing a refresh and then repaint of the sub form.

' Creates saved query in the database container for the sub form
Call BuildSql("")

' The following commented line correctly displays query results on a
seperate sub form that has the results from the criteria selected on
the form currently in use.
'Docmd.OpenForm "MainFormSeachResults

' The following two lines attempt to display the results on a sub form
which is on the same form where the criteria is selected:

Me.frmSubFormResults.Requery
Me.Refresh
<
 
 
 

Query By Form Using Same Sub Form and Main Form

Post by Don Levert » Sun, 22 Apr 2007 11:49:58

i Tom,

I often use an unbound main form to collect criteria, then show the results
in a datasheet-style subform.

I create the SQL string using code, and then use that string as the
Recordsource for the subform directly (no need to save it as as query in the
the database container)

As an added bonus, the subform refreshes automatically ... no need to
"Refresh" or "Repaint" anything.

Here's some sample code to illustrate:
- lstFieldNames is a listbox containing field names ... which is one of the
choices in the listbox's Row Source Type property
- optCriteriaType is an option group that offers comparison choices (i.e.
"<", ">", "Like", "Contains", etc..)
- txtCompareValue is a textbox that you type your comparison text criteria
into.
Note that the data type is tested to see if it is numeric, date, or text ...
and gets "wrapped" in the appropriate formatting symbols.
============================
Option Compare Database
Option Explicit
Dim MySQL As String
Dim whr As String

Public Sub GetSQL()

'--------------- Step One ------------------
'-------- Build the SELECT portion ---------
MySQL = ""
MySQL = MySQL & "SELECT tblContacts.* "
MySQL = MySQL & "FROM tblContacts"

'--------------- Step Two ------------------
'- Determine data type of the compare value -
'-------- and format it accordingly. --------
Dim CV
CV = (Me.txtCompareValue)

If IsNumeric(CV) Then
CV = CLng(CV)
ElseIf IsDate(CV) Then
CV = "#" & CDate(CV) & "#"
Else
CV = Chr(34) & CV & Chr(34)
End If
'--------------- Step Three ------------------
'-- Insert the selected comparison operator ---
'-- and ready the string for assembly into ----
'------------- the WHERE statement ------------
whr = ""
Select Case Me.optCriteriaType
Case 1 'Equal To
whr = whr & " = "
whr = whr & CV
Case 2 'Greater Than
whr = whr & " > "
whr = whr & CV
Case 3 'Less Than
whr = whr & " < "
whr = whr & CV
Case 4 'Like _____
'WHERE (((tblContacts.ContactName) Like "D" & '*'));
whr = whr & " Like "
whr = whr & CV
whr = whr & " & '*'"
Case 5 'Contains ____
'WHERE (((tblContacts.ContactName) Like '*' & "on" & '*'));
whr = whr & " Like "
whr = whr & "'*' & "
whr = whr & CV
whr = whr & " & '*'"
Case Else
whr = ""
End Select
'--------------- Step Four ------------------
'--- IF the user has entered any Criteria ----
'--- construct a WHERE clause and insert -----
'-------- it into the SQL statement ----------
If Len(whr) > 0 Then
MySQL = MySQL & " WHERE (((tblContacts."
MySQL = MySQL & Me.lstFieldNames & ")"
MySQL = MySQL & whr & " ))"
End If
'--------------- Step Five ------------------
'----- "Close out" the SQL statement ---------
'-- There may be those that argue that this --
'-- step is unnessecary, but it's just good --
'------- programming practice, IMHO ----------
MySQL = MySQL & " ;"
'Debug.Print MySQL

'--------------- Step Six ---------------------
'- Use this SQL statement as the RecordSource -
'- for the subform (and for a report, too, if you'd like a "hardcopy" of
the results) -

Me.sbfContacts.Form.RecordSource = MySQL


End Sub


--
--
HTH,
Don
=============================
E-Mail (if you must) XX