Split form with 2 combobox filters

[Keefay78]

I have a table with staff, contact details and work performed, I created a split form to narrow down who done what and when, I made a split form with 2 comboboxes, 1 for staff (StaffName) and 1 for job (JobPerformed) this way I could look up staff name and pick a particular job performed and see the log.

When I select a staff member (Mr White) from my Staffname combobox the list below changes to all records containing Mr White, when I select Job1 from my JobPerformed combobox it adds all the Job1's performed including other staff members, is there a way to make it so it only selects from those 2 selections?

I'm sure its probably just a matter of adding something to the code but I'm going round in circles now lol

[orange]

staff, contact details and work performed

all in 1 table? If so, sounds like a design issue.

[Keefay78]

all in 1 table? If so, sounds like a design issue.

I had assumed (probably wrongly) it might make it clearer if it wasn't going to be a huge database of staff members, just a few guys doing a few jobs.
I will create a table for staff members and for jobs performed, see if that resolves the issue

[orange]

No. Before that just give us 3 or 4 lines of about the "business " this data base is suppose to support.

[Keefay78]

i have a few doctors doing small procedures, I want to keep a log of which doctor is doing what procedures. We will keep a track of procedures performed and times taken.
We have say 8 doctors and there are around 10 procedures.
If Dr Green requests it we can pull up his name from the combobox, select a procedure say a tonsillectomy for example, I could then choose that on the next combobox and have all Dr Greens Tonsillectomies in a list below

further down the line I will create reports on it for costs/training etc..

hope this clears it up a bit :/

[June7]

Table for staff
Table for procedures
Table for staff procedures

Combobox to select staff, combobox to select procedure (yes, this combobox list can be conditional on staff combobox - this is called Cascading combobox)

Apply filter criteria to StaffProcedures.

Review http://allenbrowne.com/ser-62.html

[Keefay78]

Apologies for rehashing this, only just managing to get on to it again. I could not get anywhere with it so restarted it to try and nail the issue then learn from it

so I have 3 tables:

tblStaff which has staffID(pk) and StaffName
tblProcedure which has procedureID(pk) and ProcedureName
and
tblStaffProcedures which has staffprocedureID(pk) procedureDate procedureName staffName TimeTaken

I have created a data entry form for inputing records, this all works fine.

I have created a Multiple Items form from the tblStaffProcedures table, when I create a combobox in the header to select a staff member it lists multiple entries of the same name and when you select one of the names (Mr White) ON the form it highlights a particular record instead of all the records from Mr White.

[June7]

A control used to input filter criteria must be UNBOUND.

Need to know your combobox properties and code used.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Micron]

tblStaffProcedures which has staffprocedureID(pk) procedureDate procedureName staffName TimeTaken

Close but not quite. I'm assuming that where you put "procedureName" in the junction table that you mean to repeat the actual values from the parent tables. So more like

tblStaffProcedures with staffprocedureID(pk) procedureDate, procedureNameFK, staffNameFK, TimeTaken; where the Fk fields contain the PK values from the related tables. If staff and procedure looks like

staffID	Lname		procedureID	ProcedureName
1	Smith		1	A
2	Brown		2	B
3	Jones		3	C

then the junction looks like

tblStaffProcedure
staffprocedureID	ProcIDfk	staffName
1	1	2
2	2	2
3	3	2

This shows that BROWN has been assigned procedures 1, 2 and 3. I left the other fields out. This method has at least one advantage in that if you change a procedure name, it never matters because the linking fields are PK/FK so the new proc name is what's shown on your form.

[Keefay78]

here is the database, it is very small, just has the basic info to get started.

[Keefay78]

Micron, yeh sorry should have said FK lol

I tried adding 2 new fields in junction table to link the keys from the staff and procedure tables but I think I am tangling myself further lol

I get by lookingh at yours how brown would be assigned, but converting the keys into a name/procedure is new.

[June7]

If you want to do aggregation of the TimeTaken field, would have to first convert the text to a decimal number. Could instead enter decimal into number field. For instance 1 hour 14 minutes would be 1.23.

Otherwise, your table relationships are fine.

Could set a compound index on the FK fields to prevent duplicate pairs.

[Keefay78]

yeh, i managed to get my dates and times going in my main database, took some reading/trying/retrying but I got there :d

I just cannot filter my records using comboboxes, either the combobox does nothing at all or it just puts an arrow next to one of the records, I really need if clicked "job 1" to show all the job 1 records.

I have searched, watched tutorial videos etc.. I must be missing something fundamental as I just cannot make it worked.

[Keefay78]

also meant to add, the data entry one is purely data entry, the tblstaffprocedure form was for record filtering, I want to be able to filter by selection for a job, then run a report of it

adding a second combobox for selecting the staff name will come next but i need to cross this bridge first.

[June7]

Did you review the link provided earlier?

I see you have UNBOUND combobox for staff but not procedure. I don't use macros, only VBA. The above link demonstrates VBA that dynamically builds filter criteria and applies to form Filter property.

Remove name fields from tblStaffProcedure. Users don't need to see pk/fk fields in forms and reports. Use query for search form RecordSource:

SELECT tblStaffProcedure.*, tblStaff.StaffName, tblProcedure.procedureName FROM tblProcedure RIGHT JOIN (tblStaff RIGHT JOIN tblStaffProcedure ON tblStaff.StaffID = tblStaffProcedure.staffnameFK) ON tblProcedure.procedureID = tblStaffProcedure.procedureFK;

Lock textboxes for staff name and procedure name fields so they cannot be edited on search form.

Not seeing a report and code to open filtered report.