Problems linking form combo box to report

[novice2011]

Hi, I am a total novice at access but have been tasked with designing a database. I have most of it sort but one area is baffling me.
This is probably simple to seasoned users but please bear with me.

I am trying to filter results by staff division.

The options appear in a combo box which I have linked to a table. This combo box then appears on a form (staff_division). It is linked to a query (StaffDivision) which takes results from a table (QueryLog).
I would like these results to be filtered into the report (Queries by Staff Division).

At the minute my code looks like this:

Private Sub Staff_Division_Click()
On Error GoTo Err_Staff_Division_Click
Dim stDocName As String
Dim stLinkCriteria As String
Dim st As String
Dim mydb As Database
Dim myquery As QUERYDEF
Dim myrs As Recordset

st = "SELECT QueryLog.QueryID, QueryLog.AQPQNumber, QueryLog.RerunID, QueryLog.[Customer Name], QueryLog.[Description], QueryLog.[Date approved], QueryLog.[Codes Used], QueryLog.[Staff Division], QueryLog.[Department/ Business], QueryLog.[Date Received], QueryLog.[Received by], QueryLog.[Confidentiality]"


st = st & " FROM QueryLog"
st = st & " WHERE QueryLog.[Staff Division] like ('" & ListDivision & "') AND QueryLog.[Date Approved] Between cvdate('" & beginningdate & "') And cvdate('" & endingdate & "');"
Set mydb = CurrentDb
mydb.QueryDefs.Delete "StaffDivision"
Set myquery = mydb.CreateQueryDef("StaffDivision", st)
Set myrs = mydb.OpenRecordset("StaffDivision")
myrs.MoveLast
' Set norec = myrs.RecordCount
myrs.close
Set mydb = Nothing

stDocName = "Queries by Staff Division"
DoCmd.OpenReport stDocName, acPreview
Exit_Staff_Division_Click:
Exit Sub
Err_Staff_Division_Click:
MsgBox Err.description
Resume Exit_Staff_Division_Click

End Sub


I have looked this problem up extensively online but unfortunately I dont really understand VBA code so can not apply the solutions to this.
Any help at all would be graciously appreciated.

[ssanfu]

Are "beginningdate" and "endingdate" controls on the form "staff_division"?

[novice2011]

Thank you for your reply!

Sorry- i found a form that worked and more or less just copied it. Have removed dates from the equation and the report will now open but it still does not filter the requested results; instead it brings up all the results?

[ssanfu]

Most of the code won't isn't applicable for what you are trying to do.

The SQL of the query "StaffDivision" should be:

SELECT QueryLog.QueryID, QueryLog.AQPQNumber, QueryLog.RerunID, QueryLog.[Customer Name], QueryLog.[Description], QueryLog.[Date approved], QueryLog.[Codes Used], QueryLog.[Staff Division], QueryLog.[Department/ Business], QueryLog.[Date Received], QueryLog.[Received by], QueryLog.[Confidentiality] FROM QueryLog

Notice that there is not a WHERE clause. The "ORDER BY" clause is not needed because the sort order in a report is set within the report.

The recordsource for the form should be the query "StaffDivision".


The code for the button that opens the report should be:

Private Sub BUTTON_NAME_Click()
On Error GoTo Err_Staff_Division_Click

   Dim stDocName As String
   Dim stLinkCriteria As String

   'this is the report name
   stDocName = "Queries by Staff Division"

   'this is the criteria (the WHERE clause)
   If Len(Trim((Me.[Staff Division] & "")) > ) then
      stLinkCriteria = "[Staff Division] Like '" & Me.ListDivision & "'"
   End If

   DoCmd.OpenReport stDocName, acPreview,, stLinkCriteria 

Exit_Staff_Division_Click:
    Exit Sub
Err_Staff_Division_Click:
   MsgBox Err.description
   Resume Exit_Staff_Division_Click
End Sub

I can't tell if "[Staff Division]" is text or a number; if "[Staff Division]" is a long, the line should be:

stLinkCriteria = "[Staff Division] = " & Me.ListDivision