f



Query By Form Using Same Sub Form and Main Form

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
<

0
tomlebold (28)
4/20/2007 5:02:44 PM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

1 Replies
1241 Views

Similar Articles

[PageSpeed] 16

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.Name@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.

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

<tomlebold@msn.com> wrote in message 
news:1177088563.983158.25450@e65g2000hsc.googlegroups.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
> <
> 


0
4/21/2007 2:49:58 AM
Reply:

Similar Artilces:

Hiding Access Main Form/tables/queries/forms/etc
Thank you in advance for any and all assistance. Is there a way to hide the Main Access window with tables, queries, forms etc programmatically? Michael On Sat, 17 Jan 2004 17:17:16 -0600, "PMBragg" <pmbragg@megavision.com> wrote: >Thank you in advance for any and all assistance. > >Is there a way to hide the Main Access window with tables, queries, forms >etc programmatically? > Yes. Take a look here for some API code that will manipulate the Access window. http://www.mvps.org/access/api/api0019.htm - Jim Another way, with probably less control than the...

Forms question
Hi folks, I'm new here and am a neophyte Access 2000 user/developer. A year ago, I barely knew what Access was. I was asked by the people that pay us to begin keeping certain data about the children's crisis program I manage. After a couple of years doing this by hand on paper (Yikes!), I decided that this was ridiculous. Out of neccessity, I had to learn Access and develop a database to track clients and services. So what I know, I've learned from places like this and from the many text books I've picked up. I developed the database and entered almost 3 years of data. We av...

Forms and Form
Hi everyone ! 1) An example from help: "Each Form object has a Controls collection, which contains all controls on the form. You can refer to a control on a form either by implicitly or explicitly referring to the Controls collection. Your code will be faster if you refer to the Controls collection implicitly. The following examples show two of the ways you might refer to a control named NewData on the form called OrderForm: ' Implicit reference. Forms!OrderForm!NewData ' Explicit reference. Forms!OrderForm.Controls!NewData The next two examples show how you might refer to a ...

Sub-forms of Sub-forms
I'm creating a data-entry form for 3 tables. There is a one-to-many relationship between table1 and table2 and a 2nd one-to-many between table2 and table3. What I would like to do is have the user select from a combo box on the main form a record from Table1. That would filter the subform on the main form to just the records from Table2 with the PK from Table1. I've got everything working fine up to this point, but am a bit confused on where to go next. I need a mechanism for the user to pick one of the records from Table2, and be presented with an entry for for all records in Tab...

Form
I have a table TBL_NAMES : IDNAME , Name_person , ETC..... (15 different labels) I have a second table TBL_POINTS : IDPOINTS , LINKNAME_PERSON , POINTS_ALGEBRA , POINTS_GYM , etc.... (25 courses) I have made a form where all the names are listed and when i click on a name another form is opened with that persons name on top and a number of labels and textboxes where i put those points in. So far no problem. BUT... i want one record per person. So the next time i click on his name i would like to see those points and be able to change them but i must not be able to create a new record. T...

Problem with Totals on Sub Forms and Main Forms
I have an access data project (MS-SQL Svr 2000 backend and MS-Access 2003 Front End) that has a sub-form based on a query view. On the sub form I have several column's in which I have a running total on the sub-form footer. This part works fine. My problem is that the main form, on which the sub-form resides, I want to display the total from the sub-form total calculation. I have tried referencing the control on the sub form, i.e. My.Somecontrol = Forms![formname].[controlname] but this does not work. How do you make this works. Do I have to do something different because this is ...

Display recordcount of a sub form in a main form
Hi All, I am using A2 for this database. I have a main form with bound fields of FlightNumber and FlightDate I also have a subform. This subform takes details of a passanger along with other details. I also have a textbox called status in subform. When a passanger's details are entered, status value is set to "confirmed", when passanger calls in to reconfirm flight, status value changes to "Reconfirmed", and when passanger calls in to cancell his/hres flight, status value is set to "cancelled". Sometimes, status textbox may also have a value of "Wait...

Limit Sub Form records based on Main Form
I have an option group called Issue_Type on my main form F1_Member_Demographics_Main. When I select one of the 3 options on the main form from the option group Issue_Type I want to limit the sub form F2_Member_Issues records based on the combo box Issue_Code_T1. Option Group Issue_Type on main form: 1=Complaint 2=Grievance 3=Appeal Combo Box on sub form: 1=Complaint 2=Grievance 3=Appeal So if I select Complaint from the option group on the main form I only want to see Complaints on the sub form etc. Thanks in Advance Gary BEETHOVEN wrote: > I have an option group called Issue_Type on my main form > F1_Member_Demographics_Main. When I select one of the 3 options on the > main form from the option group Issue_Type I want to limit the sub > form F2_Member_Issues records based on the combo box Issue_Code_T1. > > Option Group Issue_Type on main form: > 1=Complaint > 2=Grievance > 3=Appeal > > Combo Box on sub form: > 1=Complaint > 2=Grievance > 3=Appeal > > So if I select Complaint from the option group on the main form I only > want to see Complaints on the sub form etc. > > Thanks in Advance > Gary Forms!MainFormName!SubFormName.Form.Filter = _ "Issue_Type = " & Me.Issue_Type Forms!MainFormName!SubFormName.Form.Filter = True Salad wrote: > BEETHOVEN wrote: > >> I have an option group called Issue_Type on my main form >> F1_Member_Demographics_Main. When I select one of the 3...

Mulitple Main Forms Created by Sub Form Entries
Hi I'm hoping this is a problem just because I'm a beginner at Access, and so someone can help I have created a database of companies, with a subform for contacts. (seperate tables) It worked perfectly until I got more than one contact for a company! If I add a 2nd contact to the subform, when I go back into the database, Ive got 2 records of the main form for the company, with the subform, showing. Its not adding a second record to the company table, that just says I have 2 contacts attached, which is what I want. How do I stop the record, when I view the form , duplicating its...

Continuous Forms with sub-form
I have a need to create a form containing schedule detail with a sub-form containing a crosstab of date detail. I would like to display it as a continuous form so the user can see related schedules together with the dates. Is there a way to get Access to do this? It won't allow continuous forms with sub-forms. Filling unbound controls on the main form only show the current record's date information. I would prefer not to use a big un-normalized temp table. On Wednesday, December 18, 2013 7:10:34 AM UTC-6, Ron Paii wrote: > I have a need to create a form containing schedule detail with a sub-form > > containing a crosstab of date detail. I would like to display it as a > > continuous form so the user can see related schedules together with the > > dates. Is there a way to get Access to do this? It won't allow continuous > > forms with sub-forms. Filling unbound controls on the main form only show > > the current record's date information. I would prefer not to use a big > > un-normalized temp table. Use a datasheet? Ron Paii wrote: > I have a need to create a form containing schedule detail with a > sub-form containing a crosstab of date detail. I would like to > display it as a continuous form so the user can see related schedules > together with the dates. Is there a way to get Access to do this? It > won't allow continuous forms with sub-forms. Fil...

form access to form excel
Hello, How may I pass in VBA a value from a control in a access 2003 form to a control in a excel 2003 form ? I try this but it does'nt run: (oApp=word.application) oApp.Forms("saisie").tbnom.Value = Form_Form1.tbnom.Value & " " & Form_Form1.tbprenom.Value Thanks. ...

Main Form / Sub Form search issues with combo boxes
Hello- I have a main form "Customers", with two sub forms "Items" & "Transactions"= .. The forms are all linked through the key fields of ShipDate & AccountNum. T= ransactions form is linked to Customers, and Items is linked to Transaction= s. The account number combo box in the header of the Customer form functions f= ine. I have combo boxes on the headers of the subforms that work when used = on the subform alone, but when used as a part of the Customers form, they w= ill not work correctly. The Transactions subform has an Invoice # combo box= , and the Items subform has a Sequence # combo box. I cant get the records in all 3 forms to display the intended record when u= sing the subform search boxes. The VBA for the controls is shown below. Customer Form (Main) Private Sub cboFindAccount_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs =3D Me.Recordset.Clone rs.FindFirst "[AccountNum] =3D " & Str(Me![cboFindAccount]) Me.Bookmark =3D rs.Bookmark End Sub For the Transaction subform Private Sub cboFindInvoice_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs =3D Me.Recordset.Clone rs.FindFirst "[InvoiceNum] =3D " & Str(Me![cboFindInvoice]) Me.Bookmark =3D rs.Bookmark End Sub For the Items subform Private Sub cboFindSequence_AfterUpdate() ' Fi...

Making MS Access Form as a standalone form like in VB
Well, its sames difficult for me; I love access so much and wish to make it perform extra task. I prepared an HTML form but can not connect it to my access table and so I think it is possible to make an access form a standalone form for user input. Can anyone assist. My mail add is dianafather@aim.com. *** Sent via Developersdex http://www.developersdex.com *** Oscar, why don't you just code up a VB form for user input and connect it to an Access backend? To my knowledge, you can't force an Access form to exist outside of Access. Your users would have to open the Access file to ent...

Accessing data from one form from main form in Visual Basic 6
How do I extract data from a form into the main form for processing? For instance, I have several image files in a form called images(images.frm), indexed from 0 to 99, and I want to import these into a variable array in my main form, main (form1.frm). How do I code a control to get these images into an array in my main form from the images.frm? TIA JC wrote: > How do I extract data from a form into the main form for > processing? For instance, I have several image files in a > form called images(images.frm), indexed from 0 to 99, and > I want to import these into a variable...

Form size question with Access forms, remote desktop, and MS Surface Pro
Hi, I know this is a stretch to ask this question, but: We have an Access 2010 application hosted using Remote Desktop. When we access forms from the desktop they look like they should. And from an iPad. However, a client is using their Surface Pro the forms are so tiny that she can't use them when accessing our application "on the cloud". Otherwise, they look fine on a Surface Pro when running it off the cloud. Does anyone have an idea? I do not think there is anything that the people that are hosting our software can do. -paulw On Monday, 29 February 2016 17:30:11 UTC, PW wrote: > Hi, > > I know this is a stretch to ask this question, but: > > We have an Access 2010 application hosted using Remote Desktop. When > we access forms from the desktop they look like they should. And from > an iPad. However, a client is using their Surface Pro the forms are > so tiny that she can't use them when accessing our application "on the > cloud". Otherwise, they look fine on a Surface Pro when running it > off the cloud. > > Does anyone have an idea? I do not think there is anything that the > people that are hosting our software can do. > > -paulw It will cost you, but have a look at a program called "ShrinkerStrether from Peter's Software (http://www.peterssoftware.com/ss.htm) This should shrink or stretch forms and all their controls to match the scr...

first form is ignored, second form is forms[0] why?
Does anyone recognize this situation and know what causes it ? The page starts with just a search form. When you do a search the page now has a search results form, a save botton, and the search form at the bottom. There is also a link to add another form with a dropdown. If you add the third form the page has the search results form, the form with the dropdown and the search form, in that order. The save button is set to submit the search results, which is always forms[0]. If the page has all 3 forms, it works fine. The first form is submitted,and it is the correct form. If however, the ...

Populate key from form to sub-form
I'm trying to figure out how to bring the key field from a parent form to populate the corresponding field in the sub-form during data entry, or to filter sub-form records during data viewing and updating. Can anyone help me? Bob Bob Mikels wrote: > I'm trying to figure out how to bring the key field from a parent form to > populate the corresponding field in the sub-form during data entry, or to > filter sub-form records during data viewing and updating. > > Can anyone help me? Use the subform's Link Master Fields & Link Child Fields properties. See...

calling a form sub from another form
Hi All This might sound simple, but I'm stumped. In essence I have a 2 form app that connects to an SQL db and extracts info from it. This works fine. The problem is with my 2nd form. The 'run through' is that the user can select an inv no from a combo box on form1 and when they select one, I auto-extract info from a db relating to this inv no, concat the various data fields with 2 other text fields (contains footer info) that are on form 2 and present it in a large text box on form 1. This also works fine. The problem is that the user may be previewing the large text box da...

Link Pop up Form to main form
I have a main form and use a combo box to open a pop up form. The combo box lists customers and only one customer requires the fields that are found on the pop up form. I can get the form to pop up however I do not know how to link the pop up form data to the main form record. On Oct 16, 6:54 am, CJONES <craig...@hotmail.com> wrote: > I have a main form and use a combo box to open a pop up form. The > combo box lists customers and only one customer requires the fields > that are found on the pop up form. > > I can get the form to pop up however I do not know how to link the pop > up form data to the main form record. What else does the combo box do? Does it select a record for editing, the majority of whose fields reside on the main form? If that is the case, then you could set the rowsource of the popup form to the rowsource of the main form. Store the rowsource of the main form to a string variable and pass it in the openargs to the popup form. In the pop-up forms' form_load event, assign it to the popup forms' rowsource. On Oct 16, 11:22 am, OldPro <rrossk...@sbcglobal.net> wrote: > On Oct 16, 6:54 am, CJONES <craig...@hotmail.com> wrote: > > > I have a main form and use a combo box to open a pop up form. The > > combo box lists customers and only one customer requires the fields > > that are found on the pop up form. > > > I can get the form to pop up however I do not know how to link t...

Linking Pop up form to Main form
I have a main form with a combo box where if a specific customer is selected a popup form opens and additional information required for this customer is entered into the form. Data from the main form and popup form is stored in the same table. I have a field called certification that I would like to use to link the forms. Greetings, what you need to do is to open the popup form in dialog mode (which is probably already the case). In the popup form you need to add a public property (Get) and assign it the desired value. Note: a form is just a class module except that it has a graphical user interface (GUI - the GUI thing happens way under the hood for Access). In the popup you can create a public property anywhere in the Form's code module (not a standard module or standalone class module). In my sample I am setting a Certification property to a Long datatype. If your Certification is a string, then set the property to String '--code for popup form Public Property Get Certification() As Long Certification = CLng(txtCertification.value) End Property Private Sub cmdOK_Click() Me.Visible = False End Sub In the popup form here I have a textbox called txtCertification. A user enters information here then clicks an OK button (cmdOK) which only hides (Me.Visible = False) the form (don't unload the form - it has to be running so that you can read the value from its property). In the calling form you would call the popup like this: '--code for ma...

Forms/Subforms in MS Access Database
Why do my Forms/Subforms in my MS Access Database not appear? All I see is the boxes where they should show?! What did I do wrong? Thanks, John zufie wrote: > Why do my Forms/Subforms in my MS Access Database not appear? > > All I see is the boxes where they should show?! > > What did I do wrong? -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Probably there isn't a name of a form in the sub-form's Source Object property. Also, the Link Child Fields and the Link Master Fields properties may be blank - you must enter the names of the link fields, they hold the field names that link the data between the forms. -- MGFoster:::mgf00 <at> earthlink <decimal-point> net Oakland, CA (USA) ** Respond only to this newsgroup. I DO NOT respond to emails ** -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBSSsUjoechKqOuFEgEQL+dQCfYUSwXFtPj0RRSZO+q3J8lQCD0/UAniRz JKu0h9AG0BAg8W3wC/0BC7B3 =Kvva -----END PGP SIGNATURE----- On Nov 24, 2:54=A0pm, MGFoster <m...@privacy.com> wrote: > zufie wrote: > > Why do my Forms/Subforms in my MS Access Database not appear? > > > All I see is the boxes where they should show?! > > > What did I do wrong? > > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Probably there isn't a name of a form in the sub-form's Source Object > property. =A0Also, the Link Child Fields and the Link Master Fields > properties...

ms access Form query help
the code below is from ms access 97 vb. I have imported the access project into Access 2002. I am getting all kinds of errors in the code now. I am trying to run a query from a form. This is the first error i get: It has a problem with the "OpenQueryDef" in the third line below. Dim MyDB As Database, recset As Recordset Set MyDB = DBEngine.Workspaces(0).Databases(0) Set thequery = MyDB.OpenQueryDef("MainQuery") If anyone could help me or give some advice that would be awsome. thanks, scott Skoter: Usually it's good to give a little more detail about the ...

Access Form --> Hyperlink to Excel --> Return to Original Access Form ?
I have an Access 2000 form that has a Hyperlink that opens an Excel 2000 worksheet located in the same network folder as the Access MDB. The Hyperlink works great. When users close the Excel File, they must choose the correct open Access form on the Windows Desktop Taskbar to return to the Access form that had the Hyperlink. Is there a way to: 1. Open the Access Form 2. Launch the Hyperlink to the Excel Workbook 3. Exit Excel, then 4. Return to the original Access form automatically - without clicking anything (such as the the TaskBar) I just need help with #4. Or, is there a better ...

Problem with ControlTip Text on form/sub-form
Hi All, If utilized as a sub-form, the ControlTip Texts of all the labels (you know...you can hover the mouse over either the control itself, or the label for that control) flickers like crazy. Hover the mouse over the control itself and it's fine. However, if I put the mouse over the short distance between 2 controls, it also will flicker. Get the mouse solidly over one or the other control and it's fine. Also, this sub-form, if used as just a form by itself, is fine. No flickering either on the label, or "between" 2 controls. Anyone know why this would be happe...

Web resources about - Query By Form Using Same Sub Form and Main Form - comp.databases.ms-access

Query - Wikipedia, the free encyclopedia
Text is available under the Creative Commons Attribution-ShareAlike License ;additional terms may apply. By using this site, you agree to the ...

Qubole Debuts Presto-As-A-Service, Based On Facebook-Created Open-Source Interactive Query System
Facebook announced last November that it would open-source interactive query system Presto , and now cloud big data platform provider Qubole ...

faroo_p2p: FAROO Search: Spelling correction, Query completion and Instant search http://t.co/TgHECDe5 ...
faroo_p2p: FAROO Search: Spelling correction, Query completion and Instant search http://t.

Query Strings - Quora
Quora is your best source for knowledge.

A Query A Quest A Quotation - Flickr - Photo Sharing!
One of a series of library education posters designed by Peabody Visual Aids in the 1930s and 1940s, which I salvaged from a throw-away pile ...

US to query China on passport maps
The US will raise concerns with China over a new map in Chinese passports which details claims to disputed maritime territory.

Australia to query Chinese government over South China Sea claims
Foreign Minister Julie Bishop to issue a &quot;please explain&quot; to Beijing over its island-building program in the South China Sea, amid ...

Passenger 'absolutely humiliated' by Jetstar pregnancy query
Passenger 'absolutely humiliated' by Jetstar pregnancy query

Photos query over student who died in 40m cliff fall
Police are investigating whether a French student who plunged 40 metres off a cliff in the Royal National Park was taking or posing for photographs ...

'Pies query Cloke frees after AFL loss
Collingwood coach Nathan Buckley stresses Travis Cloke's lopsided free-kick count was not the No.1 issue after they lost to Richmond by five ...

Resources last updated: 3/20/2016 11:27:45 PM