Re: main form with subform and combo boxes

On Jul 23, 1:26 pm, Dave <djwest...@xxxxxxxxx> wrote:
On Jul 20, 11:17 am, "Phil Stanton" <p...@xxxxxxxxxxxxxxxxxxx> wrote:





Hi Dave. All is revealed.

The solution I would try is not elegant, but might work.
Create a query Query1 based on all your tables (I gather relationships
exist) with the output fields for your subform. Assuming cboServer is
unbound and has its SQL something like "SELECT ServerName, ServerID FROM
Servers ORDER BY ServerName" and that the bound column is Column2
Then for Query1 Add Expr1:Forms!RD!cboServer where you need 2 criteria 0 or
<>0. Then in the ServerID Column in your query against the 0 criteria you
need 'Like "*" and the <>0 criteria, Forms!RD!cboServer.
Then create a Query2 based on Query1 and set up an Expr1:Forms!RD!cboPolicy,
same Criteria of 0 or <>0 and in the policyID column in the same way you
need respectively Like "*" or Forms!RD!cboPolicy. Same with Query3. Base
tour subform on Query3.
If it is any help I knocked up a sample DB to see if it works (Addresses)
which I cold send you

Phil

"Dave" <djwest...@xxxxxxxxx> wrote in message

news:1184857296.020735.279150@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

On Jul 18, 4:18 pm, "Phil Stanton" <p...@xxxxxxxxxxxxxxxxxxx> wrote:
I must be missing something, but why are you using more than 2 tables (1
for
person, contact info and birth details, and the other for address
details).
If only 1 person lives at the address, you would only need 1 table.
Why are you using combo boxes to display static information rather than
text
boxes.
Why are you using a subform to display (apparently) much the same
information.
It appears that you just need a main form to display the persons details
(Use 1 combo box to select the person you require) and possibly (if you
have
a number of people living at one address, a list box from which to
select
an address for the person concerned.

HTH

Phil

"Dave" <djwest...@xxxxxxxxx> wrote in message

news:1184772332.437080.115110@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Hello all,

First I'd like to apologize...This post was meant to be put in my
previous post, but I tried many times without success to reply within
my previous post. Now here goes...

I have a main form (RD Form) with 4 combo boxes (i.e. cbo1, cbo2, etc)
and a subdata*** (the subform...let's call it subInfo) below the
combo
boxes on the RD Form. I hope this eliminates any confusion of the
interface
I am working with.

The 4 combo boxes have pre-populated criteria that update each other
depending on selections of the combo boxes. For example, user selects
the name Dave from cbo1. The other 3 combo boxes are now updated to
Dave's Address, Contact Info, and State of Birthplace (cbo2, cbo3,
cbo4,
respectively). So now that the user has selected Dave as a name, the
other 3 combo boxes are now auto-updated with some of Dave's personal
information (these pieces of information are stored in separate but
linked tables).

With this process of auto-updating the combo boxes using SQL Select
statements in the afterupdate sub procedure of the combo boxes, I am
now
wanting the criteria of combo boxes to be displayed with the rest of
the
information tied to the name Dave (pulling this info straight from a
table) in subInfo. And even when some of the combo boxes aren't
selected I'd want all choices of the unselected combo box(es) that are
tied to the name Dave to be put in the subInfo. The subInfo layout
should be like a spread*** or like a table in Access.

And I'm thinking some of this can be done in subInfo by specifying
the columns of subInfo that I want the data to be put in? I hope this
is clear.

Thanks for your help!

Dave- Hide quoted text -

- Show quoted text -

Phil, thanks for responding. I meant for the object names specified
above to be substitutes for my real objects, but I would have to agree
it would setup for one pretty poor functionality/normalcy. I
should've used my names and instances straight up.

To All: This is long so bear with me, but I believe it gets my point
across.

Here is what my project looks like:

I have one main "DR Form." On this form are 4 combo boxes -
cboServer, cboPolicy, cboDB, and cboApp (these combo's stand for
Server Name, Backup Policy, Database Name, and Application Name).
Each of these 4 objects are or could be primary keys for each of their
respective tables (DBs and Apps are, for now, in 1 table named
Application - so there are 3 main tables).

For instance, Server Name is found in my Server List table (inventory
of all servers with IP, MAC, Status, stakeholder...etc). Database
Name and App Name are actually in 1 table, along with the name of the
DB server that these guys reside on (along with fields like App
Contact and DB Type). So the DB server name is directly linked to the
same Server Name in the Server List table. Then the Backup Policy
Name is housed in the Policy table along with start/end dates of
backup, size in KB, retention, schedule name, to name a few. In the
Policy table are the Server Names that are being backed up on certain
Policies, and this is matched back to servers in the Server List
table.

I hope my layout is not redundant, but here's the initial reason for
my post:

Functionality--> I allow the user to select from cboServer. They
select a server and the cboApp and cboDB and cboPolicy are all updated
with only the instances that are directly tied to the Server Name
selected (using SQL Select statements in the afterupdate event of the
other 3 combo boxes). This also applies in the same manner if the
user selects from cboApp, cboPolicy, and cboDB.

What I am wanting is a live feed directly to my subdata*** (layout
similar to a table in Access or spread*** in Excel). If a user
selected a Server Name in cboServer, I would want the Server Name
along with any DBs, Apps, or Policies tied to that Server Name to be
populated into the subdata*** (located below the combo boxes on the
DR Form) in a "realtime, live" manner.

So if I had just selected a Server Name, I would want it to show up in
the subdata*** (SDS for short) along with every instance of a DB,
App, and/or Policy tied to that Server Name. As the user selects more
criteria from the remaining combo boxes, the reigns are tightened and
the SDS is automatically updated on-the-fly. Reason being there are
many DBs & Apps & Policies attached to 1 Server, so at first, after
the user selected a Server Name, there would be 1 server name, many
DB's, many Apps, and many Policies displayed in the SDS. Once the
user selects 1 Server Name and 1 DB, there would now be multiple
instances of Apps and Policies (multiple instances depend on if
selected DB ran on many Apps or just 1 specific App and if selected
Server Name was being backed up on multiple Policies or one Policy).

Can this be done by specifying the column of the SDS I want the
specific data to populate? If so, where would this be written? I I'm
not sure where to start.

Thanks for reading my post(s) and for all of your help!

Dave- Hide quoted text -

- Show quoted text -

Phil - I think your sample database would help much, I'm not sure I
completely follow your solution.
For the time being, I've replaced the subform (SDS) with a bunch of
txt boxes for each field from each table. It seems to be somewhat
working this way, but I am in no way opposed to using my original idea
with the subform. Thanks for your help,
Dave- Hide quoted text -

- Show quoted text -

Following my previous posts above, I have 3 tables of information
feeding into 4 combo boxes on my main form (DR Form). I have as many
list boxes (acting somewhat as text boxes) as there are fields in each
one of the 3 tables. Once selecting from the combo box, I have all
the combo boxes, using afterupdate, in their own order populating
their respective list boxes. These text boxes are directly correlated
to the combo box selection using SQL. Here is an example:

lstServername.RowSource = "Select [Server Name] From [Server List]
Where [Server List].[Server Name] = '" & Me.cboServer & "';"

I have my functionality working now.

So when I select something from 1 of the combo boxes, say cboServer
(where I have server names listed), the information in the other 3
combo boxes decrease and get specific to holding only the information
tied to that specific selected Server.

Here's my question to you all:
When I select from one of the combo boxes, how do I get the remaining
unselected combo boxes to display the first instance that can be
chosen (instead of having the user select from the combo box to
display the one they want), disregarding whether or not they have 1 or
multiple instances to choose from?

thank you all!

Dave

.