Linking Combo Boxes

[Adam]

I posted here last week, and tried everyones help along with searching the
forums, but have yet to figure it out.

I have a table (MODU) with 13 columns, each a different characteristic of
offshore drilling rigs. Im trying to create a form that has 13 combo boxes,
one for each table column. I need to be able to choose any combo box, select
something, and the other 12 combos use what was chosen to filter whats in
them. Then be able to choose another random combo box, choose something, the
rest filter, and so forth through all 13 combo boxes.

I would post the codes i have thus far, but i have about 3 different
approaches, each from someone differents take on the situation.

Any help is appreciated.

 

[Jeff Boyce]

If we weren't following your posts (and responses) from 'last week', we
probably don't have enough specific information to offer specific
suggestions yet.

It all starts with the data. Consider posting a description of the data
structure you are now using.

If your model actually includes a 'junction' among 13 elements, but they are
all interdependent, there might be another way to model the relationships.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Adam]

Well to be honest, i dont think anything suggested met the criteria i need to
meet. I had been using no visual basic and had my form set up, and i could
run through all 13 combo boxes and it would work as long as i went in order
(1-13). Without using visual basic, i dont think its possible (?) to be able
to jump to any combo and select something and expect the rest to meet the
criteria. So, myself not being educated in visual basic code, do not know how
to go about doing this. If it helps, they can be combo or list boxes, as long
as i can go in any order through all the boxes.

 

[Jeff Boyce]

I still don't have any idea what data you're talking about, and how it is
related.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Adam]

Sorry, it can hard to explain a situation like this, but ill try again anyways.

I have a Spreadsheet with 13 columns, each column a different
specification for offshore drilling rigs. So i have 13 different combo boxes.

I have set up a form with 13 combo boxes, each pertaining to a column in my
table. The first three combo boxes are Rig Name, Owner, and World Region. I
have it working thus far to where i can use the drop down menu of combo box
(CB) 1 and choose a Rig Name out of the drop down. I can then proceed to CB 2
and it takes CB 1 info and narrows down the list to the Owner of the Rig in
CB 1. I can proceed through all 13 CB as longas i go in order and it will
work fine.

What i need to do is have this set up to where i can go in a random order
through the 13 combo boxes.

Thanks.

 

[Jeff Boyce]

Adam

First things first...

Access is a relational database, not a "spreadsheet on steroids". If you
created an Access table directly from a spreadsheet, there's a pretty good
chance your data is not well-normalized. Who cares?, you ask? Access
cares! Access has some nifty features and functions that don't work
particularly well (or easily) if you feed it 'sheet data.

I can easily visualize a form on which selecting a RigName brings up
informationa bout that Rig's Owner and World Region, but why would you need
to SELECT them from a combobox if you already know the RigName?

Carried further, I still don't have any idea how knowing a RigName would
'narrow down' your remaining comboboxes, mainly because I don't know what
they refer to.

At this point, since it doesn't seem like you're willing to provide that
additional information, I'm fresh out of (general) ideas.

Perhaps other newsgroup readers can offer suggestions...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Adam]

Jeff,

An Access form im trying to create is for lookign up project numbers, rig
headings, world region, etc when only knowing some information about the
project in general such as the rig name, or how many mooring lines it has.
The oil rigs are mobile, so they move and change heading/location, so knowing
the rig name would narrow the search by bringing up a few different headings
and locations and other criteria.

I understand the problem in trying to help me through this, as feedback from
others has left me in a position where i feel this near impossible with my
limited skill in VB and the fact im trying to create a form that does a lot.

The closest i have to a working form (which still does not output anything
useful but in theory, and my logic shows it should be correct) is the
following (where MODU is my table; Rig_Name, Owner, Rig_Heading, Mooring_Type
are my first 4 combo boxes, as im only trying to get the first 4 working
before i tackle all 13):


Private Sub Rig_Name_AfterUpdate()
If IsNull(Me.ActiveControl) Then
[OWNER].RowSource = "SELECT MODU.Mooring_Type,
MODU.Rig_headingMODU.Owner, MODU.Rig_Name" & _
"FROM MODU" & _
"WHERE [Rig Name] is not null" & _
"WHERE MODU.Rig_Name = '" & [Forms]![MODULIST]![Rig Name] & "';"
Else
[OWNER].RowSource = "SELECT MODU.Mooring_Type, MODU.Rig_Heading,
MODU.Owner, MODU.Rig_Name FROM MODU;"
End If
End Sub


Again, thanks for any and all help.

 

[Adam]

Disregard the "WHERE [Rig Name] is not null" & _" portion of my statement,
shouldnt be there.