new to forms; question about 'requeries' (?)

[mark kubicki]

(excuse my ignorance; I'm a self taught XLVBA office geek now transferring
to ACCESS, and this is my first time around the block...)


I have the following
TABLE - tblTable, w/ fields: [ Type, Manufacturer, Product, Notes...]
QUERY - qryTable, based on tblTable and includes fields: [Type,
Notes...], the criteria of the filed [Type] is set to a combo box on the
frmForm ( [forms]![frmForm]![type] ) with its row source : [tblTable]![Type]
FORM - frmForm, this is a "pop-up (modal) " form, where the user will
select a value from a combo box, and the remaining fields "ought" to fill in
automatically;
it includes:
- combo box cboType, row source [tblTable]![Type]
- textbox tbxManufacturer, control source
[qryTable]![Notes]
- ...

I am keeping both the frmForm and qryTable open (qryTable does not need to
be, but I am doing so to watch it's status...)

when I select a [Type] at frmFrom!cboType, I do not see the qryTable update
until I close it and then re-open it (?)
when qryTable is updated the text boxes on frmForm which use the fields in
qryTable as their control source do not show values (they show #Notes? as a
value..)

I suspect that I am missing some requery commands, and after update
events... (and have not been able to find a working combo...)


this may be too big a question to ask in a forum, but , for me, it is also
an "key problem" in that its answer will help clarify much of the "big
picture" of how ACCESS thinks...
thanks in advance,
mark

 

[Albert D.Kallal]

You have a query open just sitting there....

The fact that the query has a filter/criteria based on a form.

Having a open query and just looking at it is of NO VALUE from a application
point of view.

You might have 10 forms, 10 queries, and 10 reports all open, and all based
on that one criteria.

You change the value of the criteria/combo, access can then start searching
all of the code, and applications on your computer to some how magically
figure out what is attached to that query. The whole process works the other
way around -- ie: you as a developer need to specify, or execute a query on
what objects you want this new "view" or cartel to be shown.

So, in place of opening a report, and then changing the value of the comb
box, you would build your application such that you select the value in the
combo box, and then launch the report to view the results. You then close
the report..and perhaps select another value in the combo box..and then
launch the report again. It makes NO SENSE as a developer to try and figure
out all of the open reocrdsets, forms, reports and hope they somehow know
that a the criteria for the filter was change. So, it is just a issue of
"flow", and how you as a developer naturally design your application. When
you take the correct approch, then type of problem simply goes away...

So, you general "design" approach will take care of this.

Further, for most reports, you can/should build a nice prompt form...

The normal approach is to build a un-bound form (a un-bound form is a form
that is NOT attached to a table - these forms are typicaly desiged for user
interface face stuff like promtps, print buttions etc).

The following screen shots are all un-bound forms, and they simply prompt
the user for informaton.

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The above shold give you some ideas

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query. And, NO FORMS conditions are
placed in the query.

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions in the sql.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.

However, using the where clause as above, or a forms! expression as you have
does not change the general idea that this problem is eliminated by the
"flow"....eg: select criteria...then view report......not change
criteria..and hope report knows it changed!! (so, in effect, we design the
user interface to force the user to open the report each time the criteria
is changed. If you run/use any software you have now...you will instantly
realize that this how your software was built and designed for the last 40
years....