Filtering on a Subform

[MSAccessOldTimer]

Let me see if I can explain what I am trying to do. I have a single field, continuous subform, that tracks safety locks issued to Employees. There are over 6,200 individual employee records, and for the lock subform, almost 5,000 have (0) locks issued to them. The remaining employees may have between 1 to 7 individual locks issued to them. The subform tracks the locks assigned to each employee, and each lock has a unique ID code.



frmEmployees is fed from tlbEmployees
frmLockSub is fed from tblLocks

Usually we can track locks based on knowing the employee information, and the employee record is filtered to get that information. However, when a lock magically shows up, we need to locate the owner of that lock backwards by being able to filter the lock ID number in the subform. The issue I am having is that the filter will only filter on the locks for that particular employee and not all of the locks in the [tblLocks] table. For example, if I have a lock ID that I know is in employee record 682, and I am looking for that lock by filtering on the subform in employee record 2, it will return no results.

Should I consider a separate search box, with command button, that will search for the lock ID in [tblLocks] and then go to that specific record?

[pbaldy]

I can think of a couple of ways. Simplest if the main form is bound to the employee table is probably to add a "find a record" combo. Its row source would be a query or SQL that joined the employee and locks tables. Employee ID would be the hidden bound column, the lock ID would be the visible column that a user could search on. Selecting a lock should move the form to that employee's record.

[June7]

Yes, you need a search control to located employee record associated with LockID.

How does a lock 'magically' show up? You mean this is actually a physical piece of hardware like a gym locker lock? How can anyone manage 5,000 locks?

[MSAccessOldTimer]

pbaldy - thanks for the reply. I will set up the combo as you indicated, based off of the query tying the 2 tables together. I assume this will populate the combo box with all of the lock ID's. Would there be a way to use a blank control field where the ID number could be typed in and then find a record based on pressing 'enter' or a command button

[MSAccessOldTimer]

A lock will be turned in, or if an employee separates from the company, their manager may turn it in. If we don't know, or have the employees name, then we need to search for the lock by its ID number. We are a very large manufacturing facility and these locks are used to lock out/tag out equipment, power sources, and so forth, when it needs to be serviced, repaired, etc. And yes, there is a LOT of locks that are in the system.

[pbaldy]

pbaldy - Would there be a way to use a blank control field where the ID number could be typed in and then find a record based on pressing 'enter' or a command button

Sure, it would just be more code. You could adapt this, dropping the bit opening the second form and referring to the current form in all the lines that reference the second form. In the NoMatch, I'd throw up a "not found" message box instead of opening the second form.

[pbaldy]

Oops, forgot the link:

http://www.baldyweb.com/Bookmark.htm

[MSAccessOldTimer]

Thanks! I will check out the link you sent me, and the use of a 2nd form. Had not thought of that option.

[pbaldy]

I wasn't thinking of a second form, but it would certainly work if appropriate to your situation.

[MSAccessOldTimer]

You replied: I can think of a couple of ways. Simplest if the main form is bound to the employee table is probably to add a "find a record" combo. Its row source would be a query or SQL that joined the employee and locks tables. Employee ID would be the hidden bound column, the lock ID would be the visible column that a user could search on. Selecting a lock should move the form to that employee's record.

When I add a combo box and select the "find a record", it is only allowing me to choose a field from tblEmployee's. I do not have the option to select my new query. Should I cancel the combo wizard and manually point to the query?

[MSAccessOldTimer]

I should mention, the combo box is in the header of the main form - should I put it on the subform?

[pbaldy]

No, the header is the appropriate place. I'd let it create the combo, and then modify the row source and perhaps the column count and widths properties. Can you attach the db here?

[MSAccessOldTimer]

Not sure I can. All of the tables are in SQL and I am working on the front-end only. I can see the tables, but have not way to download them back into the db, as I am working in the db remotely.

I believe I need to run an "AfterUpdate" function to match up the lock field with the number I select in my combo box.

[pbaldy]

If you use the wizard it will create the after update code or macro. If when you use the wizard point to 2 fields, employee ID and name (or whatever). Then when it's done and working, replace the row source with your query that returns employee ID and lock ID. I think it will then work as you want.

[MSAccessOldTimer]

The wizard does not generate an AfterUpdate code or macro. It only asks where you want to store the selection, either in a table, or remembering the value for future use, which is what I want, however it does not go past that point in terms of what you want to do with that value.

I will pick back up on this tomorrow. Thanks for all you help!