By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,595 Members | 3,721 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,595 IT Pros & Developers. It's quick & easy.

Query By Form Using Same Sub Form and Main Form

P: n/a
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
<

Apr 20 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Hi 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) My*****@Telus.net

Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code samples are also Access97- based
unless otherwise noted.

================================================== ========================

<to*******@msn.comwrote in message
news:11*********************@e65g2000hsc.googlegro ups.com...
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
<

Apr 21 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.