Command Button to Find Text Within a Date Range

[kdowney]

OK, my knowledge of VBA is EXTREMELY limited, and I need help, please. I have an existing database form where a command button already exists that searches for data on any given field. Here's the "event procedure" text that shows up for the "on click" properties (I didn't create this):

***********************************************
Sub Find_Record_Click()
On Error GoTo Err_Find_Record_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Find_Record_Click:
Exit Sub

Err_Find_Record_Click:
MsgBox Err.Description
Resume Exit_Find_Record_Click

End Sub
************************************************

How can I modify this (or create something completely new) so that when the command button is clicked, it not only searches for text within the field, but narrows the search down to records that were entered within a given date range?

I do have a "DATE ENTERED" field.

Thanks for any and all help.

 

[FancyPrairie]

You could filter the form based on the item you want. For example,

Me.Filter "strField = '" & txtFieldOnForm & "' AND dtmEntered Between #" & dte1onForm & "# And #" & dte2onForm & "#"
Me.FilterOn = True

 

[kdowney]

I added the code you sent, but got a compile error. The first place Access stopped was at "txtFieldOnForm"

Any suggestions?

Here's what my code looks like now:

******************************************************
Private Sub Find_Record_With_Date_Range_Click()
On Error GoTo Err_Find_Record_With_Date_Range_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Me.Filter "strField = '" & txtFieldOnForm & "' AND dtmEntered Between #" & dte1onForm & "# And #" & dte2onForm & "#"
Me.FilterOn = True

Exit_Find_Record_With_Date_Range_Click:
Exit Sub

Err_Find_Record_With_Date_Range_Click:
MsgBox Err.Description
Resume Exit_Find_Record_With_Date_Range_Click

End Sub
******************************************************

Thanks again for any help!

 

[FancyPrairie]

You need to change the names of the fields so they match what's on your form and table. For example:

strField ... Field in your table that you will contain the text for which you are searching

txtFieldOnForm ... Name of text box on your form that contains the text the user enters. The value entered here will be the value you search on.

dtmEntered ... Represents the date/time in you table that you will be looking at in determing if the record falls within this date range.

dte1OnForm ... Name of text box on your form. It represents the begin date in the date range (user will enter this)

dte2OnForm ... Name of text box on your form. It represents the end date in the date range (user will enter this)

 

[kdowney]

Sorry for being so dense, but I'm still not sure I'm understanding this code.

for "txtFieldOnForm" ----- I want this search to work on ANY text field on my form ..... if the cursor is in a field, I want the search to be perfomed on that field. I think what you're telling me is that I need to specify the field they can search on. Is this right? Is there a way to do the search the way I want?

Also, on the "dte1OnForm" and "dte2OnForm" ...... this is one field (DATE), which is the date entered field. I don't think I can use the "between" operator on this, can I? Do I have to use a "greater than" and "less than"?

I'm really confused. Sorry. Thanks again for all your help.

 

[kdowney]

Sorry for being so dense, but I'm still not sure I'm understanding this code.

for "strField" ----- I want this search to work on ANY text field on my form ..... if the cursor is in a field, I want the search to be perfomed on that field. I think what you're telling me is that I need to specify the field they can search on. Is this right? Is there a way to do the search the way I want?

Also, on the "dte1OnForm" and "dte2OnForm" ...... this is one field (DATE), which is the date entered field. I don't think I can use the "between" operator on this, can I? Do I have to use a "greater than" and "less than"?

I'm really confused. Sorry. Thanks again for all your help.

 

[FancyPrairie]

I don't know why I was using the filter command above. But, you could used if you have several records that match your criteria. However, to do what you want, you need to know:

1. What field the cursor was on prior to selecting the command button and make sure that the previous control represents a text box and not something like a command button. The following code will tell you that.

Dim ctl as Control

Set ctl = Screen.PreviousControl

If (ctl.ControlType = acTextBox) Then
msgbox ctl.Name 'Name of text box field
msgbox ctl.Value 'Value of the text box
msgbox ctl.ControlSource 'Name of the field bound to the control
End IF

2. You also need to know whether the field you are searching for represents a string or a number (in the code for the search, strings need to be surrouned by a single quote). And what the name of that field is (ctl.ControlSource).

3. If you only have one date shown on the form, then the Beetween clause isn't what you need.

4. If you want the user to select the command button a second time to find the next record that matches the same criteria, then you need to set focus back to the text box.

Here's the code that should do what you want (assuming all of the fields your are searching on represent strings)
(Put his code in the OnClick event of your command button)

Dim ctl As Control

Set ctl = Screen.PreviousControl

If (ctl.ControlType = acTextBox) Then
Me.Recordset.FindNext ctl.ControlSource & " = '" & ctl.Value & "' And NameOfDateFieldInYourTable = #" & [Date Entered] & "#"
ctl.SetFocus
End if