Filter items in combobox (1 Viewer)

[lbs]

Hello experts

I've got a difficult question)

I have these tables:

table_Consultants which has a field Cons_id. This table contains all consultants.

table_Dates. This table contains all dates for departmental rotas

table_absentees. This table has a field Date which is related one-to many to table_dates and a filed Cons which is related on-to many to table_Consultants. This table has records of absent consultants on each day. There may be one, many or none consultant absent on each day.

table_MDT_reg. This table contains a field Cons related one-to-many to table_consultants, a field Date related one-to many to table_dates and a field MDT also related to another table with types of MDSs.

What I want to achieve is When I open a drop down box (conbobox) for Consultants (filed Cons in table_MDT_reg), I want to limit Consultants only to those who are not absent on that day, eg available.

Is it possible at all?

Many thanks

this is the source for a combobox items in

 

[Ranman256]

Yes.
In the afterUpdate event of the date cbo, filter the cboConsult,
Use the query the looks at the date as a filter...

CboDate_afterupdate()
If isnull(cboDate) then
   CboConsult.sourceObject ="qsAllCons"
Else
  CboConsult.sourceObject ="qsFltCons"
End if

 

[moke123]

SourceObject or Rowsource?
I believe Ranman meant Rowsource.

 

[lbs]

Yes.
In the afterUpdate event of the date cbo, filter the cboConsult,
Use the query the looks at the date as a filter...

CboDate_afterupdate()
If isnull(cboDate) then
   CboConsult.sourceObject ="qsAllCons"
Else
  CboConsult.sourceObject ="qsFltCons"
End if

Thank you Ranman256. The thing is I need this filter in one form for MDT_reg and the absentees are in another table. So how can I get around this?

 

[lbs]

I am still struggling to find a solution. How can I filter combo box items so that only consultants available (not absent) on the day appear on the list.

 

[jdraw]

Can you post a copy(zip format) of the database with only a few records to highlight the problem?

 

[lbs]

Can you post a copy(zip format) of the database with only a few records to highlight the problem?

Yes, I attached the db.
There are certain consultants registered absent in Absentees (t_r_11_cons_absense). When I allocate consultants to MDTs (t_r_10_MDT_reg) I want that in the combobox for consultants I see only thise consultants who are not absent on that day. Similar for Activities and SPR supervision - in dropdown list only available connsultants on that day.

Many thanks

 

[jdraw]

Where exactly do you do this allocation?

When I allocate consultants to MDTs (t_r_10_MDT_reg) I want that in the combobox for consultants I see only thise consultants who are not absent on that day.

You have several tables and forms and know your set up intimately, but readers do not.
Any explicit instructions would be helpful.

In general terms, to find the consultants who are not absent on a specific day
AvailableConsultants = AllConsultants - ThoseNotAvailableOnSpecificDay

 

[lbs]

Where exactly do you do this allocation?



You have several tables and forms and know your set up intimately, but readers do not.
Any explicit instructions would be helpful.

Sorry for confusion

If you go to the Main Main Menu form and click MDTs. There will be a subform MDT register. There will be a combobox Connsultant. Inn that combobox dropdown list I'd like to see Connsultants not absent on that day.

 

[MajP]

Private Sub cons_Enter()
  Dim strSql As String
  strSql = "Select cons_id, grade FROM t_s_01_consultants WHERE grade = 'cons' AND Active = True AND "
  strSql = strSql & "Cons_ID NOT IN (SELECT Cons_abs FROM t_r_11_cons_absense WHERE date_abs = #" & Format(Me.Parent![date], "mm/dd/yyyy") & "#)"
  strSql = strSql & " ORDER BY cons_id"
  Me.cons.RowSource = strSql
End Sub

The query should look like

SELECT cons_id,
       grade
FROM   t_s_01_consultants
WHERE  grade = 'cons'
       AND active = true
       AND cons_id NOT IN (SELECT cons_abs
                           FROM   t_r_11_cons_absense
                           WHERE  date_abs = #12/16/2019#)
ORDER  BY cons_id

 

[lbs]

MajP,
Many many thanks. It works fantastically. I can now use the same for other forms.

 

[MajP]

May want to review these queries. Very useful
https://www.w3schools.com/sql/sql_in.asp

 

[jdraw]

Glad you have it resolved.