Filter by form? Find by form? 2nd try.

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

Access 2000. I've posted a couple of times with no replies. I'm not an
access expert, so maybe I'm not able to clarify what I need. I'll try. I
have an employee db. With the usual fields such as [EID] (EmployeeId) and
the [Currentdate] (Date). Lets say I have to go back a week later after an
employee's time sheet has been entered into the db, an error has been
found.. I need to select a specific date and a specific employee id to be on
the form so that I can make changes and correct the error. I need to make
this simple without using Records, filter by form etc. A paradox db I am
converting from has a button that pops up with two textboxes to enter the
[currentdate] which of course may be a week ago, and the employeeId [EID],
click ok and the form is sent to the date and eid as requested. Changes can
now be made. I know that this can be done in Access, which must be better
than Paradox. Please Help .. Thanks...Randy
 
Add a couple of unbound controls to the form (e.g. in the Form Header) where
the user can choose the employee and date.

The example below assumes:
- a combo named cboFilterEID for selecting the employee,
- a text box named txtFilterDate for entering the date,
- a command button named cmdFilter to limit the form to the matching
employee and/or date.

Set the On Click property of the command button to:
[Event Procedure]
Click the Build button (...) beside that.
Access opens a code window.
Set up the procedure to something like the example below.

Note that the example is set up so you can easily add more boxes if you
wish. Each one tacks an " AND " on the end of the phrase, and then the final
" AND " is chopped off at the end. The form is then filtered based on the
non-blank boxes.

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Me.Dirty Then 'Save any changes first.
Me.Dirty = False
End If

If Not IsNull(Me.cboFilterEID) Then
strWhere = strWhere & "([EID] = " & Me.cboFilterEID & ") AND "
End If

If Not IsNull(Me.txtFilterDate) Then
strWhere = strWhere & "([Currentdate] = " & Format(Me.txtFilterDate,
"\#mm\/dd\/yyyy\#") & ") AND "
End If

lngLen = Len(strWhere) - 5 'Without the trailing " AND ".
If lngLen <= 0 Then
MsgBox "No criteria"
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub
 
I tried your suggestion below to the letter. I double checked everything.
What I get is a filtered form that is blank, no record...Any
suggestions...Randy
Allen Browne said:
Add a couple of unbound controls to the form (e.g. in the Form Header)
where the user can choose the employee and date.

The example below assumes:
- a combo named cboFilterEID for selecting the employee,
- a text box named txtFilterDate for entering the date,
- a command button named cmdFilter to limit the form to the matching
employee and/or date.

Set the On Click property of the command button to:
[Event Procedure]
Click the Build button (...) beside that.
Access opens a code window.
Set up the procedure to something like the example below.

Note that the example is set up so you can easily add more boxes if you
wish. Each one tacks an " AND " on the end of the phrase, and then the
final " AND " is chopped off at the end. The form is then filtered based
on the non-blank boxes.

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Me.Dirty Then 'Save any changes first.
Me.Dirty = False
End If

If Not IsNull(Me.cboFilterEID) Then
strWhere = strWhere & "([EID] = " & Me.cboFilterEID & ") AND "
End If

If Not IsNull(Me.txtFilterDate) Then
strWhere = strWhere & "([Currentdate] = " &
Format(Me.txtFilterDate, "\#mm\/dd\/yyyy\#") & ") AND "
End If

lngLen = Len(strWhere) - 5 'Without the trailing " AND ".
If lngLen <= 0 Then
MsgBox "No criteria"
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Randy said:
Access 2000. I've posted a couple of times with no replies. I'm not an
access expert, so maybe I'm not able to clarify what I need. I'll try.
I have an employee db. With the usual fields such as [EID] (EmployeeId)
and the [Currentdate] (Date). Lets say I have to go back a week later
after an employee's time sheet has been entered into the db, an error has
been found.. I need to select a specific date and a specific employee id
to be on the form so that I can make changes and correct the error. I
need to make this simple without using Records, filter by form etc. A
paradox db I am converting from has a button that pops up with two
textboxes to enter the [currentdate] which of course may be a week ago,
and the employeeId [EID], click ok and the form is sent to the date and
eid as requested. Changes can now be made. I know that this can be done
in Access, which must be better than Paradox. Please Help ..
Thanks...Randy
Click to expand...
Click to expand...
 
Add the line:
Debug.Print Me.Filter
after you set the filter.
Run the code.
Open the Immediate Window (Ctrl+G), and look at the statement that printed
there.

Then create a query, and use that as the WHERE clause (by choosing SQL View
from the View menu in query design.) Then you can figure out why this query
returns no records.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Randy said:
I tried your suggestion below to the letter. I double checked everything.
What I get is a filtered form that is blank, no record...Any
suggestions...Randy
Allen Browne said:
Add a couple of unbound controls to the form (e.g. in the Form Header)
where the user can choose the employee and date.

The example below assumes:
- a combo named cboFilterEID for selecting the employee,
- a text box named txtFilterDate for entering the date,
- a command button named cmdFilter to limit the form to the matching
employee and/or date.

Set the On Click property of the command button to:
[Event Procedure]
Click the Build button (...) beside that.
Access opens a code window.
Set up the procedure to something like the example below.

Note that the example is set up so you can easily add more boxes if you
wish. Each one tacks an " AND " on the end of the phrase, and then the
final " AND " is chopped off at the end. The form is then filtered based
on the non-blank boxes.

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Me.Dirty Then 'Save any changes first.
Me.Dirty = False
End If

If Not IsNull(Me.cboFilterEID) Then
strWhere = strWhere & "([EID] = " & Me.cboFilterEID & ") AND "
End If

If Not IsNull(Me.txtFilterDate) Then
strWhere = strWhere & "([Currentdate] = " &
Format(Me.txtFilterDate, "\#mm\/dd\/yyyy\#") & ") AND "
End If

lngLen = Len(strWhere) - 5 'Without the trailing " AND ".
If lngLen <= 0 Then
MsgBox "No criteria"
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub


Randy said:
Access 2000. I've posted a couple of times with no replies. I'm not an
access expert, so maybe I'm not able to clarify what I need. I'll try.
I have an employee db. With the usual fields such as [EID] (EmployeeId)
and the [Currentdate] (Date). Lets say I have to go back a week later
after an employee's time sheet has been entered into the db, an error
has been found.. I need to select a specific date and a specific
employee id to be on the form so that I can make changes and correct the
error. I need to make this simple without using Records, filter by form
etc. A paradox db I am converting from has a button that pops up with
two textboxes to enter the [currentdate] which of course may be a week
ago, and the employeeId [EID], click ok and the form is sent to the date
and eid as requested. Changes can now be made. I know that this can be
done in Access, which must be better than Paradox. Please Help ..
Thanks...Randy
Click to expand...
Click to expand...
Click to expand...
 
Back
Top