filters on a tab control 1

[cochise]

I have a tab control. Each tab has a form on the control. Each form has a filter property and a search button. My intention is to have the user hit the search button, enter the 2 field values (always 2) and have the form display that record.

For example, one form (based on a table) has the following filter property: (((tblTimeCard.EmployeeNum)=[Enter Employee Number]) AND ((tblTimeCard.DateWorked)=[Enter Date: mm/dd/yy]))

My search button click event is set to:
DoCmd.RunCommand (acCmdRemoveFilterSort)
DoCmd.RunCommand (acCmdApplyFilterSort)

However, whenever I press the search button, it seems to run through all the filters on every tab. How can I make it so it only runs the filter in the specific forms filter property?

 

[ReluctantDataGuy]

Try using the specific form reference with these commands instead:

Forms![MySubForm].OrderByOn = False
Forms![MySubForm].OrderByOn = True

If the button to execute these commands is on the same form that gets the filter then its easier:

Me.OrderByOn = False
Me.OrderByOn = True

Interesting. I had not seen a filter with embedded parameters like that. I've seen and used them in queries, but never thought to use them in filters.

Although in my defence I've never used filters. I usually set the recordsource:

Dim strSQL as String

strSQL = "Select * From [table name] Where [Field1] = '" & Me.[prmField1] & _
"' And [Field2] = '" & Me.[prmField2] & "'"

Me.RecordSource = strSQL

Note: I'm assuming Field1 and Field2 are text values. If they are numbers, then omit the single quotes.

Also Note: prmField1 and prmField2 are combo boxes that you place on the form so the user can select the values instead of entering them as parameters on the fly. The benefit of doing it that way is that you can see what the returned recordset is based on. When no records come up, the user doesn't have to wonder what values he/she typed into the parameters.

 

[cochise]

I'd like to try your method, however, not sure exactly how to go about it. Right now my record source is set to the underlying table b/c, most often, when the form is opened the user will enter new records. Only occassionally will she use the search feature. So how would you go about setting it up knowing that data entry is required?

 

[ReluctantDataGuy]

Okay, I assume your form's Data Entry property is set to No so that you can add and edit records in the same form.

To make it all work, you'll have two combo boxes for the user to select values for limiting the form's recordset. You'll also have a [Show All] command button to show all records. These items will go in the forms header or footer - NOT in the detail section.

Name the combo boxes cboSelEmployeeNumber and cboSelDateWorked. The RowSource of cboSelEmployeeNumber will probably be the table where Employee data is stored. I usually have two columns in my combo box for Employees. And I set ColumnWidths to 0",2.5" so that the actual Employee Number is hidden - the user selects a name. Here's a sample of what the RowSource might look like:

"Select [EmployeeNumber], [Name] From Employees Order by [Name]"

If you have separate first and last name fields, I usually handle it this way:

SELECT Employees.[EmployeeNumber], IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name]) AS Name
FROM Employees
ORDER BY IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name]);

That gives you the format: LastName, FirstName

and sorts by last, first. It also checks to make sure there's a first name since sometimes my databases have group names too, which are always stored in the [Last Name] field.

Okay, now set the row source for cboSelDateWorked. You could use soemthing like:

SELECT DISTINCT tblTimeCard.DateWorked FROM tblTimeCard
ORDER BY tblTimeCard.DateWorked";

That'll give you all valid dates from the tblTimeCard. Note that you could also use a textbox to get the date, but you'll have to check for a valid date yourself - which is why I think its better to use the combo box which has all valid dates in it - and will always return records if there are some for a given employee.

Okay. Make sure you've got both combo boxes renamed before adding any of the following code.

For cboSelEmployeeNumber:

Private Sub cboSelEmployeeNumber_AfterUpdate()
Call prcFormRS
End Sub

For cboSelDateWorked:

Private Sub cboSelDateWorked_AfterUpdate()
Call prcFormRS
End Sub

Add this code to your form's code window:

Private Sub prcFormRS()

'Set RecordSource of form based on cboSelEmployeeNumber and cboSelDateWorked combo boxes

Dim strRSString As String

strRSString = &quot;Select * From [tblTimeCard] Where [EmployeeNumber] <> 0&quot; 'Assuming no Empl # is zero...

If Not IsNull(Me.cboSelEmployeeNumber) Then
strRSString = strRSString & &quot; And [EmployeeNumber] = &quot; & Me.cboSelEmployeeNumber
End If
If Not IsNull(Me.cboSelDateWorked) Then
strRSString = strRSString & &quot; And [DateWorked] = #&quot; & Me.cboSelDateWorked & &quot;#&quot;
End If

Me.RecordSource = strRSString

End Sub

Finally, you'll add your [Show All] button and add the following code to its OnClick Event:

Note: Don't forget to rename your new command button before adding this code.

Private Sub cmdShowAll_Click()
'Clear RecordSet limits and show all records

Me.RecordSource = &quot;Select * From [tblTimeCard]&quot;
Me.cboSelEmployeeNumber = Null
Me.cboSelDateWorked = Null

End Sub

Note: I modified this code from one of my databases as I went along, so I can't guarentee it'll work as written, but you should be able to debug it and get it working.

Good luck - hope this helps...

 

[cochise]

Thanks. It was extremely helpful.