B
BFN_E-Subscriptions
I have an Access 2003 database which is designed to track sales
activity. Contacts, Appointments, and Quotes.
I originally wrote single forms, query driven, with a "locked in"
reference for each set of queries, for each salesperson. As we added
salespeople I found myself copying and editing many multiples of
queries for each new salesperson, and the database quickly balooned out
of control.
I changed direction, and created a start form where the person selected
their name from a combo box, and then the form minimized and went
invisible. I then created a tabbed form with query driven subforms for
each of contacts, appointments, and quotes, where the queries were all
referenced from the combo box selection on the "invisible" form. This
worked great, with the exception that the db had to be closed and
reopened to allow for selecting to view the data using different
criteria.
I then thought, what about adding an extra tab to the form, which had
the same combo box selection, and then have the rest of the query
driven subforms take their criteria from the combo box on the first
tab. The problem is, of course, that when the form opens there is no
criteria selected on the first tab, and so the form locks up on
nothing, and regardless how I try to Requery controls, or forms, no
code or placement I try will cause the subforms (or the queries which
drive them) to Requery, and sync up with the criteria selected in the
combo box on the first tab after the form is opened.
I can always stick to my invisible form, however this is a bit clunky,
and I'd sure like to get the more elegant solution to work, but it
obviously takes more code experience than I possess at this time, or
else the solution to making the other subforms on the remaining tabs
"Reload" or "Requery", or "????????" is just out of my reach right now.
Can anyone advise, is there a proper VBA code command and sequence I
can put in to cause those various queries that drive the subforms to
"Re-run" and take their parameters from the selection in the combo box
on the first tab, upon a change in the criteria chosen in that combo
box? If so, can I force all forms on a given tab to "refresh", or do I
need to target the subforms themselves? I would think from a
programming standpoint one would want each form to "Refresh" upon
selection of the tab that form is on, but perhaps the entire multi
tabbed form can be forced to refresh, along with all subforms on all
tabs, with some ultra simple, and brilliant VBA code.
Can anyone advise, and please remember I'm a bit shaky on interpreting
syntax, though if you can explain the syntax in dummies terms, I can
find my way around in the form well enough to place it where it's
required to go, just identify which property, where.
Form name: F_5Tab_Combined
Tab names: Start, Contacts, Appointments, Quotes_SF, Quotes_LF
Combo Box on Start tab which I want to use to select criteria
reference: S_Person_Ref This combo box is pointed at a Query:
Q_S_PersonSort.
Subforms:
Contacts Tab: F_SF_Contacts Driven by Query: Q_SFF_Contacts
(Contacts Table)
Appointments Tab: F_SF_Appts Driven by Query: Q_SFF_Appts
(Appointments Table)
Quotes_SF Tab: F_SF_QuotesSF Driven by Query: Q_SFF_QuotesSF (Quotes
Table)
Quotes_LF Tab: F_SF_QuotesLF Driven by Query: Q_SFF_QuotesLF
(Quotes Table)
activity. Contacts, Appointments, and Quotes.
I originally wrote single forms, query driven, with a "locked in"
reference for each set of queries, for each salesperson. As we added
salespeople I found myself copying and editing many multiples of
queries for each new salesperson, and the database quickly balooned out
of control.
I changed direction, and created a start form where the person selected
their name from a combo box, and then the form minimized and went
invisible. I then created a tabbed form with query driven subforms for
each of contacts, appointments, and quotes, where the queries were all
referenced from the combo box selection on the "invisible" form. This
worked great, with the exception that the db had to be closed and
reopened to allow for selecting to view the data using different
criteria.
I then thought, what about adding an extra tab to the form, which had
the same combo box selection, and then have the rest of the query
driven subforms take their criteria from the combo box on the first
tab. The problem is, of course, that when the form opens there is no
criteria selected on the first tab, and so the form locks up on
nothing, and regardless how I try to Requery controls, or forms, no
code or placement I try will cause the subforms (or the queries which
drive them) to Requery, and sync up with the criteria selected in the
combo box on the first tab after the form is opened.
I can always stick to my invisible form, however this is a bit clunky,
and I'd sure like to get the more elegant solution to work, but it
obviously takes more code experience than I possess at this time, or
else the solution to making the other subforms on the remaining tabs
"Reload" or "Requery", or "????????" is just out of my reach right now.
Can anyone advise, is there a proper VBA code command and sequence I
can put in to cause those various queries that drive the subforms to
"Re-run" and take their parameters from the selection in the combo box
on the first tab, upon a change in the criteria chosen in that combo
box? If so, can I force all forms on a given tab to "refresh", or do I
need to target the subforms themselves? I would think from a
programming standpoint one would want each form to "Refresh" upon
selection of the tab that form is on, but perhaps the entire multi
tabbed form can be forced to refresh, along with all subforms on all
tabs, with some ultra simple, and brilliant VBA code.
Can anyone advise, and please remember I'm a bit shaky on interpreting
syntax, though if you can explain the syntax in dummies terms, I can
find my way around in the form well enough to place it where it's
required to go, just identify which property, where.
Form name: F_5Tab_Combined
Tab names: Start, Contacts, Appointments, Quotes_SF, Quotes_LF
Combo Box on Start tab which I want to use to select criteria
reference: S_Person_Ref This combo box is pointed at a Query:
Q_S_PersonSort.
Subforms:
Contacts Tab: F_SF_Contacts Driven by Query: Q_SFF_Contacts
(Contacts Table)
Appointments Tab: F_SF_Appts Driven by Query: Q_SFF_Appts
(Appointments Table)
Quotes_SF Tab: F_SF_QuotesSF Driven by Query: Q_SFF_QuotesSF (Quotes
Table)
Quotes_LF Tab: F_SF_QuotesLF Driven by Query: Q_SFF_QuotesLF
(Quotes Table)