Using an unbound Combo Box and two unbound Text boxes on a Form to search a Query

[PeterS]

Hi



I have a Form that I use to search a query that is simply a Start Date and an End Date.

In the Query criteria I have the following;
[Forms]![frmTechnicianService]![txtStartDate] And [Forms]![frmTechnicianService]![txtEndDate]


This works very well. When I enter the start date and the end date and hit the button the query runs 100% and opens the desired report.

MY PROBLEM

I would like to add a Combo Box to my Form so that I can drill down my report. I want to use a Combo Box to select a technician's name and then enter a start date and an end date.

This is my SQL statement

SELECT TechnicianTbl.Technician, ForkliftServiceTbl.ServiceDate, ForkliftTbl.ForkliftNumber, ForkliftServiceTbl.ServiceDetails
FROM TechnicianTbl INNER JOIN (ForkliftTbl INNER JOIN ForkliftServiceTbl ON ForkliftTbl.ForkliftID_Pk = ForkliftServiceTbl.ForkliftID_Fk) ON TechnicianTbl.[TechnicianID-Pk] = ForkliftServiceTbl.Technician
WHERE (((TechnicianTbl.Technician)=Forms!frmTechnicianSe rvice!cboTechnician) And ((ForkliftServiceTbl.ServiceDate) Between Forms!frmTechnicianService!txtStartDate And Forms!frmTechnicianService!txtEndDate));

As soon as I add the Combo Box the report will not populate and it is blank. Both Text Boxes and the Combo Box are Unbound.

Please can you tell me where I am going wrong?

[ranman256]

make the form use a query that shows all records.
then when the items are filled FILTER the results, but you must check the controls to see what is used:

build the 'where' clause by cycling thru all the controls....
it executes after a find button CLICK event
if null, ignore.
if not, apply.

'----------------
sub btnFilter_click()
'----------------
dim sWhere as string 


sWhere = "1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"


If sWhere = "1=1" Then
  Me.FilterOn = False
Else
  Me.Filter = sWhere
  Me.FilterOn = True
End If
end sub

[PeterS]

Thanks ranman
I have to wrap my head around that one. I am a newbie to SQL

Where would I put the code you wrote?

What do you mean by I need to cycle through all the controls and how would I do that?

[accesstos]

Bind your report to a query with this SQL definition:

SELECT TechnicianTbl.Technician, ForkliftServiceTbl.ServiceDate, ForkliftTbl.ForkliftNumber, ForkliftServiceTbl.ServiceDetails FROM TechnicianTbl 
INNER JOIN (ForkliftTbl INNER JOIN ForkliftServiceTbl  ON ForkliftTbl.ForkliftID_Pk = ForkliftServiceTbl.ForkliftID_Fk) 
ON  TechnicianTbl.[TechnicianID-Pk] = ForkliftServiceTbl.Technician

Then, on OnClick event of a command button in the form that you refer, insert those lines of code:

    
    Dim strWHERE As String

    strWHERE = "([TechnicianTbl].[Technician]=" & Me.cboTechnician _
             & ") AND ([ForkliftServiceTbl].[ServiceDate]" _
             & "BETWEEN ((#" & Me.txtStartDate & "#" _
             & ") AND (#" & Me.txtEndDate & "#)));"
             
    Debug.Print strWHERE
    DoCmd.OpenReport "MyReport", acViewPreview, , strWHERE

Replace the "MyReport" with the real name of your report, choose a Technician, insert the start date and the end date, hit the button and let me know if it works.

Good luck with your project!

[PeterS]

Hi accesstos

Thank you for your help. Not sure if it is because of my incompetance but I get the following error when entering the code:

Compile Error! Method or data member not found.

Private Sub Technician_Click()
Dim strWHERE As String
strWHERE = "([TechnicianTbl].[Technician]=" & Me.cboTechnician _
& ") AND ([ForkliftServiceTbl].[ServiceDate]" _
& "BETWEEN ((#" & Me.txtStartDate & "#" _
& ") AND (#" & Me.txtEndDate & "#)));"

Debug.Print strWHERE
DoCmd.OpenReport "MyReport", acViewPreview, , strWHERE
End Sub

[June7]

See if this tutorial helps http://allenbrowne.com/ser-62.html

Is Technician a number or text field? If it is text, need apostrophe delimiters.

Need a space in front of BETWEEN. Remove the semi-colon.

strWHERE = "[TechnicianTbl].[Technician]=" & Me.cboTechnician _
& " AND [ForkliftServiceTbl].[ServiceDate]" _
& " BETWEEN #" & Me.txtStartDate & "# AND #" & Me.txtEndDate & "#"