Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / August 2006

Tip: Looking for answers? Try searching our database.

How do I set up query to look for parameters in a form?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
mCubed - 10 Aug 2006 23:16 GMT
I have followed the tutorial posted by Sal Ricciardi (Using parameters with
queries and reports). The form (dialog box) with combo boxes and command
buttons works great until I try to set the query to look for the parameters
in the form. In design view, I add the following in the criteria field for
each combo box: [Forms]![Sales By Category Form]![Category]. Now this works
as expected until I save the query. After saving the query and returning to
open the form, it is blank. No combo boxes or command buttons. If I return to
the query in design view and remove [Forms]![Sales By Category
Form]![Category] for each combo box, the form returns to its original state.
What am I missing. I am unsing Access 2003.
Allen Browne - 11 Aug 2006 05:30 GMT
There's a bit of a catch 22 here.

When the form opens, it runs the query so it can choose the records to show
in the controls on the form. But the query is trying to read values from the
form so it can choose the records so it can show the values in the form. We
are going round in circles.

That's complicated by the fact that the Detail section of a form goes
completely blank when:
a) there are no records to show, and also
b) no new record can be added.

If the source query is not updatable, that would explain (b). If the query
can't match anything to the combo which has no value yet, that would explain
(a). When this happens, you can still see controls in the Form Header and
Form Footer sections, but they don't work properly.

There has to be a better way to retrieve records for the form. Could you
leave the parameters out of the query, and instead apply a Filter to the
form? For example you might do this in the AfterUpdate event procedure of
the combo:
   If Not IsNull(Me.Category) Then
       Me.Filter = "[CategoryID] = " & Me.Category
       Me.FilterOn = True
   End If
Note that if the CategoryID is a Text field, you will need extra quotes:
       Me.Filter = "[CategoryID] = """ & Me.Category & """"

Applying a Filter in this way is much more flexible, especially where there
are many possible fields that you might want to filter on. You can download
a sample database that demonstrates this approach:
   Search form - Handle many optional criteria
at:
   http://allenbrowne.com/ser-62.html

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have followed the tutorial posted by Sal Ricciardi (Using parameters with
> queries and reports). The form (dialog box) with combo boxes and command
[quoted text clipped - 11 lines]
> state.
> What am I missing. I am unsing Access 2003.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.