On the fly queries in MS Access 2003 (XP)

[suarve]

Hi,

I have three combo boxes on a form. When the user clicks the ok button a query will run with whatever criteria the user has selected from the combo boxes. The code is below, but i cant get it to work

Private Sub cmdOK_Click()

	 Dim db As DAO.Database
	 Dim qdf As DAO.QueryDef
	 Dim strSQL As String

	 Set db = CurrentDb
	 Set  qdf =  db.QueryDefs ("qryStaffListQuery")

	 If IsNull(Me.cboOffice.Value) Then
              		strOffice = " Like '*’”
              	 Else
              		strOffice = "='” & Me.cboOffice.Value & "’ ”
              	 End If

strSQL =  "SELECT tblStaff.* " & _
			  "FROM tblStaff " & _
			  "WHERE tblStaff.Office='" & Me.cboOffice.Value & "' " & _
			  "AND tblStaff.Department='" & Me.cboDepartment.Value & "' " & _
			  "ORDER BY tblStaff.LastName,tblStaff.FirstName;"

End Sub

 

[TrUz]

What error are you getting? Access is funny about using the 'Me' keyword if the control does not have focus. Try without the me keyword and maybe without the .Value. I have not used access forms for a very long time but I am sure I just used to reference the combobox i.e. cboName etc...

TrUz

 

[suarve]

What error are you getting? Access is funny about using the 'Me' keyword if the control does not have focus. Try without the me keyword and maybe without the .Value. I have not used access forms for a very long time but I am sure I just used to reference the combobox i.e. cboName etc...

TrUz

I'm not getting any errors at all The thing just doesn't work. I tried without the keyword too, with no luck....

 

[TrUz]

Can you upload the database so I can take a look or is the data sensetive?

TrUz