cascading comboboxes multiple tables (1 Viewer)

[Siegfried]

Dear experts,

I keep struggling with cascading comboboxes.
The examples I find on the internet usually don't match my more complex requirement...
Can somebody please guide me and help me out.
I have a ton of excel files which I want to get rid off by using Access.
Hence I'm trying to create this dbse.

A brief description of what I'm doing:
I'm creating a database which will list all vessel we're managaging, I also want to keep track of changes in our fleet (like vessel
name changes, change of ownership... etc).
I started by giving all vessels an ID (TblVesselIDs).
Each vessel has an account code [VesselCompanyCodeID], a name with a 3 letter code [VesselNameCodeID], and IMO number [ImoID],
an owner [OwnerID] and an active/inactive status [VesselStatusID].
These 5 items make the vessel ID. Refer to attached picture VesselID.jpg

Example:
VesselID VesselcompanyCodeID VesselNameCodeID IMOID OwnerID VesselStatusID
001 780 MV Dolphin - DOL 123456 Neptune Ltd. active
002 785 MV LifterII 999999 Heavy Lifts Inc active

When either one of these 4 items change, then the present vessel ID is set to inactive and a new one is created.
Example:
VesselID VesselcompanyCodeID VesselNameCodeID IMOID OwnerID VesselStatusID
001 780 MV Dolphin - DOL 123456 Neptune Ltd. inactive
003 780 MV Whale - DOL 123456 Neptune Ltd. active
002 785 MV LifterII - LIF 999999 Heavy Lifts Inc inactive
004 800 MV LifterII - LFT 999999 Heavy Cargo Lifts Inc active

I do this so I can search and find things back.
A vessel can change: owner, name and code (rarerly IMO) over time. In above above example the MV Dolphin changed her name to MV Whale
and kept the same letter code as the owner remained the same. Before the code DOL she had code SHO and was named Seahorse.
So doing a search on SHO will result me the MV Seahores which would refer to a change name event to MV Dolphin.
If I would simply change the name then I don't have any record of the old name unless I add a field previous name but if a vessel
changes name 2 or 3 times then it becomes more complex to add this in the same table...
The other vessel information, like technical specifications, communciation details, crewlistings etc, I keep linked with IMO number
as that number won't change. So whenever an ID changes it stays linked with the other information based on the IMO number.

This tabel TblVesselID's has a relation with the tabel TblVesselManagementStatus which gives each vessel an operational status.
So each active vessel from TblVesselIDs has a status in TblVesselManagementStatus.
Refer to attached picuture VesselStatus.jpg.

VesselCurrentStatusID managementstatus: activitystatus: vesselstatus: eventstatus:
1 in management active sailing -
2 in management active sailing change
3 in management active sailing leaving
4 in management active sailing lay up
5 in management active joining take over
6 in management inactive warm -
7 in management inactive warm change
8 in management inactive warm leaving
9 in management inactive cold -
10 in management inactive cold change
11 in management inactive cold leaving
12 in management inactive under repairs -
13 in management inactive immobilized -
14 out of management inactive sold -
15 out of management inactive transferred -

And here's the part I'm stuck. I made a form FrmVesselManagementStatus where I want to be able to add/update the status of a vessel
by using comboboxes. Refer to attached picture FrmVesselManagementStatus.jpg.


Thanks for your help.

Best regards,

Siegfried

 

[JHB]

And what exactly is your problem?

 

[Siegfried]

Godmorgen JHB,

Thanks for your interest in my post.
I'm having problems cascading the comboboxes for vessel's status.
In my form FrmVesselManagementStatus based on TblVesselManagementStatus a vessel gets a status.
TblVesselManagementStatus
VesselID FK
VesselCurrentStatus ID FK
SSS

In my attached dbse I have teh vessel Nord Missisippi set to status 1
which is in management active sailing -.

I was trying to create comboboxes on the form to easily enter/update a vessel's status.
FrmVesselManagementStatus
Tbl.VesselManagementStatus.ManagementStatusID
Tbl.VesselManagementStatus.VesselID
Tbl.VesselManagementStatus.VesselCurrentStatusID

combobox unbound management status
combobox unbound activity
combobox unbound current status
combobox unbound event
combobox unbound status

I managed to link all comboboxes except the last one status.
If the status combobox would work then it should match the value of Tbl.VesselManagementStatus.VesselCurrentStatusID. But unfortunately I still didn't manage to get this working. I'm doing someting wrong but I'm stuck.

Thanks.

Best regards,

Siegfried

 

[JHB]

I don't know if that would give you what you want.

SELECT TblVesselCurrentStatus.VesselCurrentStatusID, TblVesselCurrentStatus.ActiveCurrentStatusID, TblVesselCurrentStatus.CurrentStatusEventID FROM TblVesselCurrentStatus WHERE (((TblVesselCurrentStatus.VesselCurrentStatusID)=[Forms]![FrmVesselManagementStatus]![VesselCurrentStatusID]));

 

[Siegfried]

Hi JHB,

No, I'm not getting a result, when I run the querty I don't get any records.

 

[JHB]

I'm shooting a bit in the dark because I'm not really sure what results you want, (so if you can show a screen dump instead of a long text, it would probably help).

 

[Siegfried]

Dear JHB,

I've attached a few pictures to clarify my problem.
Picture FrmVesselManagementStatus.jpg show the from I'm working on with the comboboxes I've created.
Frm Result.jpg shows what I want the form to do.
I now have a field status which I want to replace by comboboxes.
When selecting the comboboxes one by one, top to bottom the end result of the last combobox should match the top status combobox which then can be removed.
I've attached picture of the query so you can see the status results and where they coming from.
The comboboxes I created as follows:
management status unbound cboManagementStatus

Row Source:

SELECT TblManagementSatus.ManagementStatusID, TblManagementSatus.ManagementStatus FROM TblManagementSatus;

Activity unbound cboActivity
Row Source:

SELECT TblActiveManagementX.VesselActiveManagementID, TblVesselActivity.VesselActivity FROM TblVesselActivity
INNER JOIN TblActiveManagementX ON TblVesselActivity.VesselActivityID = TblActiveManagementX.VesselActivityID
WHERE (((TblActiveManagementX.ManagementStatusID)=[Forms]![FrmVesselManagementStatus]![cboManagementStatus]))
ORDER BY TblVesselActivity.VesselActivity;

Current Status unbound cboCurrentStatus
Row Source:

SELECT TblActiveCurrentStatusX.ActiveCurrentStatusID, TblCurrentSubStatus.CurrentSubStatus FROM TblCurrentSubStatus
INNER JOIN TblActiveCurrentStatusX ON TblCurrentSubStatus.CurrentSubStatusID = TblActiveCurrentStatusX.CurrentSubStatusID
WHERE (((TblActiveCurrentStatusX.VesselActiveManagementID)=[Forms]![FrmVesselManagementStatus]![cboActivity]))
ORDER BY TblCurrentSubStatus.CurrentSubStatus;

Event unbound cboEvent
Row Source:

SELECT TblVesselCurrentStatus.VesselCurrentStatusID, TblCurrentStatusEvents.CurrentStatusEvent FROM TblCurrentStatusEvents
INNER JOIN TblVesselCurrentStatus ON TblCurrentStatusEvents.CurrentStatusEventID = TblVesselCurrentStatus.CurrentStatusEventID
WHERE (((TblVesselCurrentStatus.ActiveCurrentStatusID)=[forms]![FrmVesselManagementStatus]![cboCurrentStatus]));

Status unbound cboStatus


B. Rgds,
Siegfried

 

[JHB]

Could it be that?

 

[Siegfried]

Hi JHHB,

Yes, that's correct!
That's how I would like to have it on my FrmVesselManagementStatus.

B. Rgds,

Siegfried

 

[JHB]

Hi JHHB,

Yes, that's correct!
That's how I would like to have it on my FrmVesselManagementStatus.

Okay - finally I got it.
Database attached.

 

[Siegfried]

Hi JHB,

Thanks for your efforts. The comboboxing are cascading correctly but the value is not updating in the tabel?
Refer to attached.

 

[JHB]

Just a stupid question, I have not checked it!
Do the other combo boxes do that, update the respective tables, or is it just a requirement for this combo box?
If so, place the below code in the AfterUpdate event for the cboStatus.

Private Sub cboStatus_AfterUpdate()
  Me.VesselCurrentStatusID = Me.cboStatus
End Sub

 

[Siegfried]

Hi JHB,

No, the other comboboxes don't update the tables, they select a value from those tables.
It's in the Table TblVesselManagementStatus field VesselCurrentStatusID that I have to select a value to give a vessel a status. And the value I'm now entering refers to a combination of several table values.
Once the values are selected in comboboxes: management status, activity, current status and event, then they result in the status.
The status combobox cboStatus should result the value matching the 4 selected criteria.
And the status combox should then correspond to the value in TblVesselManagementStatus.VesselCurrentStatusID.
The combobox cboStatus should automatically result the value based on the other selected comboboxes.
The value is now there in the combobox after copying above code but I still have select it?

B. Rgds,
Siegfried

 

[Siegfried]

To say it short, I'm replacing on the the Form the field VesselCurrentStatusID in Tabel TblVesselManagementStatus by these comboboxes.

 

[JHB]

Try the attached database.

 

[Siegfried]

Perfect!!!!
Thanks for your help JHB!

 

[JHB]

You're welcome, good luck.

 

[Pat Hartman]

I didn't follow the discussion of how to "fix" the combos but it looks like you got an answer. It just seems to me that you are making this harder than it needs to be. Changing the ID of the record when something significant changes is going to cause great complexity in trying to put things together. So far we see only the tables related to the "significant" fields. We don't see anything regarding where the ship is travelling or what it is carrying. If all this database is intended to track is demographic changes, then I guess what you have will work but if you need to hang other stuff off the ship ID, then I would never change the ID of the ship.

I would simply create a history table. If some demographic item changes, create a new history record but do not change the record ID. That is how you will tie the history together later.