I am trying to retrieve calls logged from an SQL database using MS Access. The required tables are linked ODBC tables in the Access DB that I have set up.
I have a form with a sub form that I am trying to retrieve data with, my main problem is that I have two combo boxs on the main form. What I want to do is get the sub-form to refresh using the values in the combo boxs, the twist is that I will also need to be able to enter one particular combo box with a null value.
My query is basically using a customer and an assignee combo boxs.
I will need to be able to accept nulls in the customer box, so if the assignee is populated then retrieve for the assignee group listed in the combo box.
I can manage to get either all of teh call slogged to the group(Assignee) but not for the customer, or for the customer but not the group.
Here is the SQL that Access is generating.
PARAMETERS [Forms]![All_Envisage_calls].[CustomersCbo] Text ( 255 );
SELECT dbo_HD_Call.Call, dbo_HD_Call.Customer, dbo_HD_Call.Status, dbo_HD_Call.Assignee, dbo_HD_Call.LoggedBy, dbo_HD_Call.DateRaised, dbo_HD_Call.DateCompleted, dbo_HD_Call.Request, dbo_HD_Call.Action, dbo_HD_Call.SysDateChanged
WHERE (((dbo_HD_Call.Assignee)=Forms!All_Envisage_calls. AssigneesCbo)
And ((dbo_HD_Call.Customer) Like Forms!All_Envisage_calls.CustomersCbo) Is Null)
Or (((dbo_HD_Call.Customer)=Forms!All_Envisage_calls. CustomersCbo))
ORDER BY dbo_HD_Call.Call DESC;
At a quick glance, if you want the sub form to follow the main form, setup "Parent-Child" relations for the sub form.
Select the sub form and one of it's properties is "Link Child Fields" - click the elipsis (the little button with 3 dots) on the right side of the property field that appears when you select the property. This let's you setup which field(s) on the sub form are related to the parent form.
When the record on the parent form is changed, the sub form is automatically filtered to match.