Combo Box not showing the correct Field

[Atlascycle]

I do have more data to put in the data base, so I should work in the forms first.



I have Several Forms Created:

frmEnergySources: I created this Form to input the Energy source Name and Type

frmInformationEntry: this form will be deleted

frmLOTO: This form is to start inputting the information into the LOTO Table, I think that this one might be able to be built upon.

frmLOTODevices: Created to move the LOTO Devices information from the original LOTO table to the Junction table tblLOTODevices.

frmLOTOelecControl: Created to Move the Electrical Energy Control information from the Original LOTO table to the tblElectricalControl.

frmLOTOET: Will be deleted

frmLOTOOpControls: created to transfer the Operator Controls information to the new Table tblOperatorControls Can be Deleted.

frmLOTOOpControls Subform: Subform for frmLOTOOpControls.

frmLOTOShutdown: will be deleted

frmLOTOStartup: Will Be Deleted

frmLOTOVerify: Will be Deleted

frmPanels: Created to assign Panel locations to the Panels

frmPnlLoc: Created to Populate tblPnlLoc

frmPressureRelief: Created to assign the pressure Relief values to the LOTO table records

frmProcedures: I created this from to help me to see how the Shutdown, verify and start up tables were combined

frmStartup, frmVerify, tblLOTOShutDown Subform, tblLOTOStartup Subform, tblLOTOVerify Subform, tblLOTODevices, LOTO2, LOTO Subform, LOTOInforEntryForm, and tblElectricalControl are no longer needed.

Starting with frmLOTO I am not sure how to link the tblDevices and the Junction Table tblLOTOdevices in order to choose the devices needed.

[jzwp11]

Starting with frmLOTO I am not sure how to link the tblDevices and the Junction Table tblLOTOdevices in order to choose the devices needed.

You would create a form based on tblLOTODevices and in that form you would use a combo box based on the devices table.

You would then use the form created above as a subform in frmLOTO. If you have your relationships setup, Access will automatically link the subform to the main form via the primary key--foreign key relationship.

[Atlascycle]

You would create a form based on tblLOTODevices and in that form you would use a combo box based on the devices table.

You would then use the form created above as a sub form in frmLOTO. If you have your relationships setup, Access will automatically link the sub form to the main form via the primary key--foreign key relationship.

I have this Done, I also tried to add a Text Box for the Operator controls But was unable to get it to work correctly, I then Added another sub form for the energy sources and the Operator Controls. It is not Very pretty but it is functional.

Next would be to Bring the Shutdown, Verification and the Start up procedures into the form. I would assume that this will require a Query?

Current State is Attached.

[jzwp11]

Since you have a many-to-many relationship between LOTO and operator controls, you would need a subform based on the junction table and you would use a combo box to populate the control just as you did with the devices; a simple text box will not work. To make things look a little more organized, you could use the tab control and put each subform on a tab.

Next would be to Bring the Shutdown, Verification and the Start up procedures into the form. I would assume that this will require a Query?

Yes, a query or actually 3 (one for each type). You can then create 3 corresponding subforms and place each of those on a tab, or you can have a button that opens another form that shows the segregated procedure. It is really up to you how you want to present the data.

[Atlascycle]

I have a form put together, but I ran in to needing to add a new procedure Step, What would you recommend to be able to add a record to the source table?

[jzwp11]

I have a form put together, but I ran in to needing to add a new procedure Step, What would you recommend to be able to add a record to the source table?

You would typically use a basic form tied to the source table to enter new records. However, if you want the ability to add new records to the various source tables on the fly while you are using the LOTO form that is a little different. For example, let's say that you are looking at one of the combo box lists in one of the subforms on the LOTO form and you discover that a choice you want is not there and you need to add it to the underlying table of the combo box. You can with Visual Basic for Application code in the On Not in List event of the combo box to add the record to the combo box's underlying data source.

Allen Browne shows how to do that here. Allen's site shows how to add a record where you only specify 1 particular field, but what happens if you need to add data to multiple fields for the record such as for a procedure? You will want to check out Roger Carlson's site here; look in the alphabetical index under NotInList...

[Atlascycle]

Thank you again for all of your Help, I will have a look at Allen Browne's Site and also Roger Carlson's site.


Jason

[jzwp11]

You're welcome

[Atlascycle]

Need to Backtrack a minute, When I created the Queries for the Shut down, verify, and Start up procedures, I set the Criteria for the Field fkProcTypeID to limit the results to the respective procedure types, then created the Subform from the query. When i click the combo box in the sub form to choose the procedure that I would like it is listing every record from the tblprocedures and not limiting it to the procedure type. This is the SQL View for the Query:

SELECT tblLOTOProcedures.pkLOTOProcID, tblLOTOProcedures.fkLOTOID, tblLOTOProcedures.longSeqNo, tblLOTOProcedures.fkProcID, tblProcedures.fkProcTypeID
FROM tblProcedureTypes INNER JOIN (tblProcedures INNER JOIN tblLOTOProcedures ON tblProcedures.pkProcID = tblLOTOProcedures.fkProcID) ON tblProcedureTypes.pkProcTypeID = tblProcedures.fkProcTypeID
WHERE (((tblProcedures.fkProcTypeID)=2))
ORDER BY tblLOTOProcedures.fkLOTOID, tblLOTOProcedures.longSeqNo;

I created the Query in Design View, but thought that it would be simpler to post the SQL View. Is it Possible to Show only the specfic procedure types in the combo Box?

[jzwp11]

My apologies, I wasn't clear in my earlier post. The subform still needs to be based on the junction table (lotoprocedures) or more correctly a query that filters the junction tables based on the procedure type as you show.


You will also need to base the combo box's row source on a query that filters based on procedure type or uses the procedure type field in the form's control source to filter the combo box as well. I'm trying to think if that is possible.

[jzwp11]

My thought of filtering the combo box's row source using the procedure type field of the subform won't work nicely, so I would just use a query for the combo box and filter via the query.

[Atlascycle]

Do you Mean to use the Query Builder on the Combo Box or Build a separate Query all together?

[jzwp11]

Either way will work.

[Atlascycle]

I think that i am ready to move on to the report.

I would like to be able to open the Report, Select the machine and have it display it in print preview mode.

So I started by creating a Query from the Equipment_TBL, Location, EquipmentClass, and the EquipmentType Tables that included the following Fields, MachineID, Make, Model, and Serialno from the Equipment_TBL, EquipmentClass from the EquipmentClass Table, EquipmentType from the EquipmentType Table, and Location From the Location Table.

If I run the Query it works, but as soon as I Add the LOTO Table I get the type Mismatch in Expression Error message.

I also tried to Build the query starting with the LOTO Table and as soon as I add the Equipment_TBL I will get the Same Error.

[jzwp11]

In the database that has the linked tables (from one of your earlier posts), the Machine ID field in Equipment_TBL is text whereas the machine # field in the LOTO table is numeric. You have to make the join between the ID field of Equipment_TBL and the fkETMachine_ID field of the LOTO table.

With respect to your report, I would have an unbound form with a combo box that has the machines and a button. Use the combo box to select the machine & then the button to open the report based on what was selected in the combo box.