I will put a link to a small database with one form so you can see what is the problem.
Database with employees and departments table. Each table has an autovalue PK, which users should see as less as possible, as they should not try to manipulate it. As there is an n:n relation between the two tabels, I create an assignments table which has, besides its own id-PK, not much else but the FK’s to both employees and departments.
Create a form on the assignments table, and change the text boxes for the FK fields to list boxes which pickup the empname and deptname from their tables.
That works OK for filling in assignments, but when the employees table starts to fill up, the necessity comes up to be able to search e.g. a certain employee by her/his name. But using the standard search tool in forms (the magnifying glass) one can oknly search in the id’s. I understand that. But that’s a nuisance in the end. I added (not in the odb example I link toà to add a subform that displays the empname, but then there are no records to search for except the one which is linked to the main form. Is there a standard solution for this that does not involve macro’s???
link text
Hello,
Take a look at the attached sample and see if this fits your needs. Used a filter (search) table for the main table and then the Assignment records for the sub form.
Sample ------ emp.odb
In the right column of the AssignmentsSearch form, chose a name from the list box. Then press button to get applicable records. If none are set, you are positioned at a new record for that choice.
I think I understand what you have done here and apparently it works OK. Tx for that. But it is only a half solution in the way that I cannot search on the departments in this way independently from the employee search. As far as i understand this, I would need to create a second search form for that???
Unfortunately it was not clear that a search by department was also part of the question.
Using this method (common) you would need a separate search. For other measures it may require macros.
While I considered your solution, which is fine. I bookmarked this page to not forget what you proposed. But in the process, I came to another idea: make the employees a read-only master form , and the assignments a subform linked to the master allowing all operations. In that way i can use the standard search facility Base forms provide.
Why didn’t I think of that in the first place???