SQL and MS access- filtering of the data on the form

Question

Hi All.

I am using SQL 2005 as backend and MS Access as frontend.

Now, I get all my filtering of the data with views and no problems so far but I came accross some issues.

In access I had a form and on that form I had a field1 which I used to filter the data I wanted in that form with query. Example: Last, First Name or DOB. In Access I used the Expression builder to point the query to that field and I got my filter. Now how do I do it in this new environment since when I create view (In Access) I can not filter on that field.

I was thinking on sp but I am not sure how do i go about it.

Any ideas?

I was thinking on sp but I am not sure how do i go about it.

Answer 1

If you still wanted to keep this form a normal “bound” access form then you could do something like this.

Setup a pass through query that fires your SP. The basic format for that is along the lines of this

EXEC [dbo].[spAgents_with_more_than_X_days_sick_by_Team] @Date_from = N'2009-09-14', @Date_to = N'2010-09-14', @Team_ID = N'TEM1', @Days_sick =5

You would then modify this when opening the form like this

Set qDef = DBEngine(0)(0).QueryDefs("RqryAgents_more_than_X_sicks_detail_2")
With qDef
    .Connect = strSQL_con_string
    .SQL = "EXEC [dbo].[spAgents_with_more_than_X_days_sick_by_Team]"
    .SQL = .SQL & " @Date_from = N'" & Format(Me.txtDate_from, "yyyy-mm-dd") & "', "
    .SQL = .SQL & "@Date_to = N'" & Format(Me.txtDate_to, "yyyy-mm-dd") & "', "
    .SQL = .SQL & "@Team_ID = N'" & Me.txtTeam_ID & "', "
    .SQL = .SQL & "@Days_sick =" & Me.txtDays_sick
End With

This should work just fine however if it was me (and I know it’s not everyone’s preference) but I would make this an unbound form and populate it by firing your SP using ADO to fill a recordset and go from there.

If you want details of how to do that then just ask and I will post an example

EDIT: Code sample added

Dim cmd as new ADODB.Command
Dim dbCon as new ADODB.Connection
Dim rst as new ADODB.Recordset
dbCon.ConnectionString=”Your_Connection_string”
dbCon.open

With cmd
    .ActiveConnection = dbCon
    .CommandText = "spYour SP"
    .CommandType = adCmdStoredProc
    .NamedParameters = True
    .Parameters.Append .CreateParameter("@Your_pram1", adVarChar, adParamInput, 20, Format(Me.txtDate, "yyyy-mm-dd"))
    .Parameters.Append .CreateParameter("@Your_pram2", adSmallInt, adParamInput, 0, Me.cboPhone_skill)
End With

Set rst = cmd.Execute()
With rst
    If .EOF=False then
    Me.txtYour_text_box_1=!Your_SP_field_1
    Me.txtYour_text_box_2=!Your_SP_field_3
    Me.txtYour_text_box_3=!Your_SP_field_2
    End if
End with
Rst.close
Dbcon.close
Set rst=nothing
Set cmd=nothing

Set dbcon=nothing

Answer 2

You don't mention if these text boxes are in fact in the same form with the data and you don't mention if each text box was optional, and if they were not optional, then did you fill out 1, 2 or 3 boxes and then hit a button?

If you just looking to find people with a given lastName, then in the after update event of the un bound lastname box, just go:

Me.RecordSource = "select * from tblCustomers where LastName = '" & me.txtLastName "'"

The above is one line of code. I just don't see the need for so much code as others posted here.

You can expand on your question if you looking to make all 3 boxes optional, or if you looking to have a search based on more then one box, but I can rather tell you that you don't need the amounts of code posted here, but the one line of code as per above will suffice.