List multiple items per multiple list form section

[kubiej21]

I have thrown together a database using the following tables:




---------------------------
ComplexID AutoNumber
ComplexName Text
---------------------------

---------------------------
MachineID AutoNumber
MachineName Text
ComplexID Number
---------------------------

---------------------------
DeviceId AutoNumber
MachineID Number
Description Text
Rack# Text
Section# Text
Color Text
CheckBox Yes/No
NormalPosition Text
Location Text
Verification Text
---------------------------

---------------------------
PnP_NumbersID AutoNumber
P&PNumbers Text
DeviceID Number
---------------------------

This database is to be used for machine lockouts at the plant at which I work. The design that I had in mind was to allow users to select the specific complex and machine they wish to work on, and then select the various devices that are dynamically populated within a multiple list form.

I struggled a bit with getting this to work (Access novice...), but I eventually figured it out (sort of). For each device that is listed in the multiple items form, there can be multiple P&P Numbers associated with a given device. I have tried a number of different methods of trying to list the P&P Numbers, however, I have only managed to list a single one for each lockout device.

What do I need to do in order to get this working properly? I have attached a copy of my database for clarity. (Note that if you examine the database, 16Line 16 will be the only area with data in it)

[rpeare]

Are you looking to be able to do data entry for those on the same form or is it just for reference?

because you have a one to many relationship FROM devices TO PNP you would actually need another subform (if you're going to use bound controls) to be able to do the data entry. If you're just using it for reference you can create a list box that would show the PnP's related to your devices. The problem with either is that you can't use a continuous form (easily) with either method.

I always prefer to limit my data entry screens to one record so I'd take a slightly different tack in that you'd also have to choose the device then you could have a continuous subform for your PnP numbers.

See attached file:Lockout.zip

[kubiej21]

Okay, I see what you are doing. No, I do not need to enter any information, the PnP's are purely for reference. However, I think I would prefer to use a continuous form to list all the available devices, considering that there are thousands of lockout procedures per machine. It would make it easier for the supervisors to scroll through a list, select the devices they would want, and then have a report/tags generated.

I realize it will be more work on my part, but if I can make everyone else's lives a bit easier, I'll do it.

I've also uploaded my original database (I didn't even think to try and compress it before... Its been a long day... ha)

Lockout2.zip

[rpeare]

what's the difference between them scrolling through devices as part of the selection criteria for the form (my example) vs scrolling through the actual devices? The problem is listing your PNP numbers in the form design you have right now is that you can not have a continuous form within a continuous form. Your MAIN record (the device) would have to be a SINGLE FORM and your subform (the PNP records) would have to be CONTINUOUS. In other words you'd only see one DEVICE record and you'd have to scroll through thousands of records one at a time (if indeed there are thousands of devices). With a list box you would have to run some code (I haven't looked into doing that because there seemed to be a much easier solution) if you want to keep your form as it is now.

If your problem is that the combo box doesn't show enough information to make a proper selection you can change the combo box to show a number of columns or change the device selection to a list box (my preferred method)

If you wanted to show the full device record in a list box that's very easy to do and makes scrolling through larger quantities of records much easier, in my opinion, than cycling through records on a form. It also makes it much easier to search for a particular record. Let's say you wanted to find any device that had the characters XXYX in it, a list box makes that super easy and it can group all the results rather than having to go through 1000 records that may be spread by several dozen to several hundred entries.

[kubiej21]

Man.... its unfortunate that Access is so limited. Do you think you could throw out an example for the list box option that you described in your last paragraph?

[kubiej21]

Would there be some way of achieving the functionality that I originally described by means of VBA code? I would think that it would be possible, however, my lack of knowledge in Access and VBA are hindering me...

[rpeare]

Lockout2-Updated.zip

Here's a modified version with a device list box and a search function for the listbox so you don't have to cycle through ALL the records as long as you have a partial description.

[rpeare]

this post was made in error