Turn combo box w/ two columns to 2 combos

[shariq1989]

I am working on a database project for a shipping company. There are 6 different vessels that need to go through various inspections. Some inspections are the same for all boats, some are unique to a certain vessel. I have the following tables:



-Inspections which lists ALL inspections with their time intervals. Fields: "InspectionID", "InspectionName", "InspectionInterval"
-Vessels which has the 6 different vessels. "VesselID", "VesselName"
-VesselInspections which contains VesselIDs with InspectionIDs next to them to associate all vessels with the inspections they need. Example:
"Vessel1":"Inspection1"
Vessel1":"Inspection2"
Vessel2":"Inspection2"
Fields: "VesselID", "InspectionID"
-InspectionHistory which records all inspections ever performed. "VesselInspectionsID", "DateCompleted"


I have a form called "FrmInspectionHistory" with two fields:
Select VesselInspection (combo box) and Date Completed (text box). The combo box contains a query that pulls up VesselID, VesselName, InspectionID and InspectionName but only displays VesselName and InspectionName when used. I wanted to streamline the process even further and have the two boxes. First the VesselName is selected in the first combo, which uses TblVesselInspections to pull up the inspections relevant to that particular vessel in the next combo. This should populate TblInspectionHistory accordingly.

[pbaldy]

Is this what you're after?

Baldy-Cascading Combos

[shariq1989]

Yup. Just gotta figure out how to get it to work with my query. Thanks, Paul

[pbaldy]

No problem; post back if you get stuck.

[shariq1989]

Sooo I am stuck.. I got the first combo displaying the vessel name and after a selection is made, it does allow the second combo to display only the relevant inspections. And requery works as well. So all in all, cascading is working flawlessly. The problem I am having now is that I am not sure what my control source for the second combo (cmbInspectionID) should be. I want it to use the InspectionID selected in the box to record the PK (VesselInspectionID) for that inspection into the InspectionHistory table. However, when I selected VesselInspectionID as my control source, it just stored the InspectionID into the VesselInspectionID column.. I dont know if what I just type up can even be comprehended, but i tried to have it make as much sense as possible..

[pbaldy]

I would expect the row source of the second combo to include the VesselInspectionID field. It would be the bound column, but could be hidden. That would put that value into the table.

[shariq1989]

So that worked wonderfully. The only problem I saw was that when I hit the "Previous Record button", the combo boxes didnt populate like they do for other my forms. But that shouldn't be too big of an issue, I'm guessing.. since that information should be viewed in reports?

[pbaldy]

Cascading combos don't work well in continuous or datasheet forms, and as you note they can look goofy when changing records on a single form because the row source doesn't get updated. You can probably get around that with code in the current event to set both of them as appropriate to the data in the record being viewed.

As to your reports question, generally speaking forms are for data entry/edit, reports for presentation.

[shariq1989]

So I get to fix the issue... since that would be the only way to edit..

[pbaldy]

Yup; post back if you get stuck, or post the db here.

[shariq1989]

So I completely understand what is going on but just cant put my head around what to type into the form's current event. I tried requeries and they didnt work. I know the solution is exactly what the guys posted in the forum linked below.. I just cant grasp it enough to put into my own.

http://msgroups.net/microsoft.public...bo-issue/76728

another guy said:
"

Refresh Record in the On Current of the form and in the On Focus of the 2nd drop
down box ... and it works perfectly"

[pbaldy]

Untested, but I'm visualizing setting the first combo to the appropriate vessel, then re-querying the second. If you don't get it, can you post the db here?

[shariq1989]

What would be the best way of sending it? Its currently too big to send. I'm debating making a copy and deleting all the irrelevant tables and forms or just compressing the copy.

[pbaldy]

Normally if you do a compact/repair and then zip, it will be small enough to attach here.

[shariq1989]

That is an AWESOME feature!