Dataset selection

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a large database in Access 2003 and want to be able to select a
recordset on a number of different criteria. The main table is called
"Projects", and I want to have a form where I can enter one or more of
"Project Manager", "Status", "Location". If for example I enter a Project
Manager name and a Status, but leave Location blank then I want to retrieve
all records for that P.M. and Status, regardless of Location.

I suspect this is easy. Is it ?
 
Do something along the line of:

If (txtbox1 <> "") Then
strFilter = "[ProductName] LIKE ""*" & txtbox1 & "*"""
End If

If (txtbox2) Then
If (strFilter <> "") Then
strFilter = strFilter & " AND "
End If

strFilter = strFilter & "[Field2] = " & txtbox1
End If

....and so on for each field then pass it to the docmd.openform
method like:

DoCmd.OpenForm "frmName", , , strFilter


hth
-Jayyde
 
If you never want to select by Manager OR Location at the same time then it
is easy. Use an IIF statement in the criteria.

IIf([Forms]![YourForm].[YourControl1] Is Null, Like
"*",[Forms]![YourForm].[YourControl1])

If you do not enter anything in a control on the form then it will be NULL.
 
Hi, BeeJay.

The strategy is to build an SQL string based on the user input, write the
string to an invisible textbox control on the form, and use the string as the
Where clause of an OpenForm or OpenReport action.

If you use a naming conventions where each control has a 3-digit prefix plus
the associated field name, you can conveniently loop through all of the
controls. The following example assumes there are combo boxes on the form,
which the criteria must match exactly, or textboxes, in which case, the
critieria will use the Like "*xxx*" modification. WriteFilterString is
called in the AfterUpdate event procedure of each control.

Once all the criteria has been entered, a command button displays the
filtered recordset.

Private Sub WriteFilterString()
Dim intindex As Integer
Dim ctl As Control

On Error Resume Next

'Reinitialize control
Me!txtFilterString = ""

' Loop through all form controls; if there's data, add to filter string
For Each ctl In Me.Controls

If (Nz(ctl.Value) <> 0 And Nz(ctl.Value) <> "") Then
If ctl.ControlType = acComboBox Then

Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.name, Len(ctl.name) -
3)) _
& "]=" & ctl.Value & " AND "

Else
‘ Must be a text box
Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.name, Len(ctl.name) -
3)) _
& "] Like " & "'" & "*" & ctl.Value & "*" &
"'" & " AND "

End If

End If

Next ctl

' Strip end of filter
Me!txtFilterString = Left(Me!txtFilterString, Len(Me!txtFilterString) - 5)

End Sub

‘ Command button code to display filtered recordset
Dim stDocName As String
Dim stWhere As String
Dim stFilterLabel As String

stDocName = ""
stWhere = Me![txtFilterString]

DoCmd.OpenForm stDocName, , , stWhere

Hope that helps.
Sprinks
 
Thanks for the very prompt responses. I had been hoping that there would be
an easy drag-and-drop, or wizard-led approach. However I have done Basic
(albeit GW-Basic was the most recent, several years ago !) so will give it a
go.

Jayyde - when would this be invoked - on user pressing the "Go" command ?

Karl - no, I want to be able to enter any one or more of several fields.

Sprinks - I see the principle, but don't quite understand the logic. You
say to invoke WriteFilterString in the AfterUpdate event proc of each
control, but setting it to "" in each case, therefore deleting anything from
a previous control ? Also wouldn't this strategy cause an incorrect string
if say the user went back and reselected a different value from a field ?
I'd be happy (at least initially) to restrict it to combo boxes. I also
don't understand where the "Me.Controls" comes from - perhaps that's at the
root of my confusion.

Sprinks said:
Hi, BeeJay.

The strategy is to build an SQL string based on the user input, write the
string to an invisible textbox control on the form, and use the string as the
Where clause of an OpenForm or OpenReport action.

If you use a naming conventions where each control has a 3-digit prefix plus
the associated field name, you can conveniently loop through all of the
controls. The following example assumes there are combo boxes on the form,
which the criteria must match exactly, or textboxes, in which case, the
critieria will use the Like "*xxx*" modification. WriteFilterString is
called in the AfterUpdate event procedure of each control.

Once all the criteria has been entered, a command button displays the
filtered recordset.

Private Sub WriteFilterString()
Dim intindex As Integer
Dim ctl As Control

On Error Resume Next

'Reinitialize control
Me!txtFilterString = ""

' Loop through all form controls; if there's data, add to filter string
For Each ctl In Me.Controls

If (Nz(ctl.Value) <> 0 And Nz(ctl.Value) <> "") Then
If ctl.ControlType = acComboBox Then

Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.name, Len(ctl.name) -
3)) _
& "]=" & ctl.Value & " AND "

Else
‘ Must be a text box
Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.name, Len(ctl.name) -
3)) _
& "] Like " & "'" & "*" & ctl.Value & "*" &
"'" & " AND "

End If

End If

Next ctl

' Strip end of filter
Me!txtFilterString = Left(Me!txtFilterString, Len(Me!txtFilterString) - 5)

End Sub

‘ Command button code to display filtered recordset
Dim stDocName As String
Dim stWhere As String
Dim stFilterLabel As String

stDocName = ""
stWhere = Me![txtFilterString]

DoCmd.OpenForm stDocName, , , stWhere

Hope that helps.
Sprinks

BeeJayEff said:
I have a large database in Access 2003 and want to be able to select a
recordset on a number of different criteria. The main table is called
"Projects", and I want to have a form where I can enter one or more of
"Project Manager", "Status", "Location". If for example I enter a Project
Manager name and a Status, but leave Location blank then I want to retrieve
all records for that P.M. and Status, regardless of Location.

I suspect this is easy. Is it ?
Click to expand...
Click to expand...
 
I have something really similar being called when a search button gets
clicked. Could happen inside the actual click event, I just had way more
than 3 search criteria to check for so it would have been messy ;)


BeeJayEff said:
Thanks for the very prompt responses. I had been hoping that there would
be
an easy drag-and-drop, or wizard-led approach. However I have done Basic
(albeit GW-Basic was the most recent, several years ago !) so will give it
a
go.

Jayyde - when would this be invoked - on user pressing the "Go" command ?

Karl - no, I want to be able to enter any one or more of several fields.

Sprinks - I see the principle, but don't quite understand the logic. You
say to invoke WriteFilterString in the AfterUpdate event proc of each
control, but setting it to "" in each case, therefore deleting anything
from
a previous control ? Also wouldn't this strategy cause an incorrect
string
if say the user went back and reselected a different value from a field ?
I'd be happy (at least initially) to restrict it to combo boxes. I also
don't understand where the "Me.Controls" comes from - perhaps that's at
the
root of my confusion.

Sprinks said:
Hi, BeeJay.

The strategy is to build an SQL string based on the user input, write the
string to an invisible textbox control on the form, and use the string as
the
Where clause of an OpenForm or OpenReport action.

If you use a naming conventions where each control has a 3-digit prefix
plus
the associated field name, you can conveniently loop through all of the
controls. The following example assumes there are combo boxes on the
form,
which the criteria must match exactly, or textboxes, in which case, the
critieria will use the Like "*xxx*" modification. WriteFilterString is
called in the AfterUpdate event procedure of each control.

Once all the criteria has been entered, a command button displays the
filtered recordset.

Private Sub WriteFilterString()
Dim intindex As Integer
Dim ctl As Control

On Error Resume Next

'Reinitialize control
Me!txtFilterString = ""

' Loop through all form controls; if there's data, add to filter string
For Each ctl In Me.Controls

If (Nz(ctl.Value) <> 0 And Nz(ctl.Value) <> "") Then
If ctl.ControlType = acComboBox Then

Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.name,
Len(ctl.name) -
3)) _
& "]=" & ctl.Value & " AND "

Else
' Must be a text box
Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.name,
Len(ctl.name) -
3)) _
& "] Like " & "'" & "*" & ctl.Value & "*"
&
"'" & " AND "

End If

End If

Next ctl

' Strip end of filter
Me!txtFilterString = Left(Me!txtFilterString, Len(Me!txtFilterString) -
5)

End Sub

' Command button code to display filtered recordset
Dim stDocName As String
Dim stWhere As String
Dim stFilterLabel As String

stDocName = ""
stWhere = Me![txtFilterString]

DoCmd.OpenForm stDocName, , , stWhere

Hope that helps.
Sprinks

BeeJayEff said:
I have a large database in Access 2003 and want to be able to select a
recordset on a number of different criteria. The main table is called
"Projects", and I want to have a form where I can enter one or more of
"Project Manager", "Status", "Location". If for example I enter a
Project
Manager name and a Status, but leave Location blank then I want to
retrieve
all records for that P.M. and Status, regardless of Location.

I suspect this is easy. Is it ?
Click to expand...
Click to expand...
Click to expand...
 
BeeJay,

I've been using this approach for years without error.

Me.Controls is the collection of controls on the current form. This
includes labels, graphic elements, textboxes, combo boxes, etc., so the code
first checks for control type.

Whenever ANY of the data entry fields change, the txtFilterString is set to
blank, and the code loops through all of the controls on the form, building
an entirely *new* string. This approach takes advantage of the loop, and
avoids having to modify an existing string using the Mid and/or InStr
functions, and dealing with the separate cases of a user changing a value to
a new one, or deleting an entered value, which would need to be handled
differently.

Better still, the code is portable--as long as you use the naming
convention, you can use the code unaltered for other similar situations in
other applications. You should be able to cut and paste it into your form
module exactly as is, assuming you use the same control names and conventions.

If you wish to use a different or no naming convention, you can also use the
Tag property of the control to hold the field name by which you wish to
filter. Also, the code had assumed that only combo boxes and textboxes were
used. To handle option groups, checkboxes, and others, change the If...Then
Statement to a Select Case ctl.ControlType statement to handle each
separately.

I also normally include another button on the form to clear all criteria:

' Reset all controls & move the cursor to the first control
For Each ctl In Me.Controls
If (ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox) Then
ctl.Value = Null
End If
Next ctl
Me![YourFirstControl].SetFocus

Good luck.

Sprinks

BeeJayEff said:
Thanks for the very prompt responses. I had been hoping that there would be
an easy drag-and-drop, or wizard-led approach. However I have done Basic
(albeit GW-Basic was the most recent, several years ago !) so will give it a
go.

Jayyde - when would this be invoked - on user pressing the "Go" command ?

Karl - no, I want to be able to enter any one or more of several fields.

Sprinks - I see the principle, but don't quite understand the logic. You
say to invoke WriteFilterString in the AfterUpdate event proc of each
control, but setting it to "" in each case, therefore deleting anything from
a previous control ? Also wouldn't this strategy cause an incorrect string
if say the user went back and reselected a different value from a field ?
I'd be happy (at least initially) to restrict it to combo boxes. I also
don't understand where the "Me.Controls" comes from - perhaps that's at the
root of my confusion.

Sprinks said:
Hi, BeeJay.

The strategy is to build an SQL string based on the user input, write the
string to an invisible textbox control on the form, and use the string as the
Where clause of an OpenForm or OpenReport action.

If you use a naming conventions where each control has a 3-digit prefix plus
the associated field name, you can conveniently loop through all of the
controls. The following example assumes there are combo boxes on the form,
which the criteria must match exactly, or textboxes, in which case, the
critieria will use the Like "*xxx*" modification. WriteFilterString is
called in the AfterUpdate event procedure of each control.

Once all the criteria has been entered, a command button displays the
filtered recordset.

Private Sub WriteFilterString()
Dim intindex As Integer
Dim ctl As Control

On Error Resume Next

'Reinitialize control
Me!txtFilterString = ""

' Loop through all form controls; if there's data, add to filter string
For Each ctl In Me.Controls

If (Nz(ctl.Value) <> 0 And Nz(ctl.Value) <> "") Then
If ctl.ControlType = acComboBox Then

Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.name, Len(ctl.name) -
3)) _
& "]=" & ctl.Value & " AND "

Else
‘ Must be a text box
Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.name, Len(ctl.name) -
3)) _
& "] Like " & "'" & "*" & ctl.Value & "*" &
"'" & " AND "

End If

End If

Next ctl

' Strip end of filter
Me!txtFilterString = Left(Me!txtFilterString, Len(Me!txtFilterString) - 5)

End Sub

‘ Command button code to display filtered recordset
Dim stDocName As String
Dim stWhere As String
Dim stFilterLabel As String

stDocName = ""
stWhere = Me![txtFilterString]

DoCmd.OpenForm stDocName, , , stWhere

Hope that helps.
Sprinks

BeeJayEff said:
I have a large database in Access 2003 and want to be able to select a
recordset on a number of different criteria. The main table is called
"Projects", and I want to have a form where I can enter one or more of
"Project Manager", "Status", "Location". If for example I enter a Project
Manager name and a Status, but leave Location blank then I want to retrieve
all records for that P.M. and Status, regardless of Location.

I suspect this is easy. Is it ?
Click to expand...
Click to expand...
Click to expand...
 
My post will allow one OR the other if the other is null. If there is entry
in the forms then it is all And'd together.

BeeJayEff said:
Thanks for the very prompt responses. I had been hoping that there would be
an easy drag-and-drop, or wizard-led approach. However I have done Basic
(albeit GW-Basic was the most recent, several years ago !) so will give it a
go.

Jayyde - when would this be invoked - on user pressing the "Go" command ?

Karl - no, I want to be able to enter any one or more of several fields.

Sprinks - I see the principle, but don't quite understand the logic. You
say to invoke WriteFilterString in the AfterUpdate event proc of each
control, but setting it to "" in each case, therefore deleting anything from
a previous control ? Also wouldn't this strategy cause an incorrect string
if say the user went back and reselected a different value from a field ?
I'd be happy (at least initially) to restrict it to combo boxes. I also
don't understand where the "Me.Controls" comes from - perhaps that's at the
root of my confusion.

Sprinks said:
Hi, BeeJay.

The strategy is to build an SQL string based on the user input, write the
string to an invisible textbox control on the form, and use the string as the
Where clause of an OpenForm or OpenReport action.

If you use a naming conventions where each control has a 3-digit prefix plus
the associated field name, you can conveniently loop through all of the
controls. The following example assumes there are combo boxes on the form,
which the criteria must match exactly, or textboxes, in which case, the
critieria will use the Like "*xxx*" modification. WriteFilterString is
called in the AfterUpdate event procedure of each control.

Once all the criteria has been entered, a command button displays the
filtered recordset.

Private Sub WriteFilterString()
Dim intindex As Integer
Dim ctl As Control

On Error Resume Next

'Reinitialize control
Me!txtFilterString = ""

' Loop through all form controls; if there's data, add to filter string
For Each ctl In Me.Controls

If (Nz(ctl.Value) <> 0 And Nz(ctl.Value) <> "") Then
If ctl.ControlType = acComboBox Then

Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.name, Len(ctl.name) -
3)) _
& "]=" & ctl.Value & " AND "

Else
‘ Must be a text box
Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.name, Len(ctl.name) -
3)) _
& "] Like " & "'" & "*" & ctl.Value & "*" &
"'" & " AND "

End If

End If

Next ctl

' Strip end of filter
Me!txtFilterString = Left(Me!txtFilterString, Len(Me!txtFilterString) - 5)

End Sub

‘ Command button code to display filtered recordset
Dim stDocName As String
Dim stWhere As String
Dim stFilterLabel As String

stDocName = ""
stWhere = Me![txtFilterString]

DoCmd.OpenForm stDocName, , , stWhere

Hope that helps.
Sprinks

BeeJayEff said:
I have a large database in Access 2003 and want to be able to select a
recordset on a number of different criteria. The main table is called
"Projects", and I want to have a form where I can enter one or more of
"Project Manager", "Status", "Location". If for example I enter a Project
Manager name and a Status, but leave Location blank then I want to retrieve
all records for that P.M. and Status, regardless of Location.

I suspect this is easy. Is it ?
Click to expand...
Click to expand...
Click to expand...
 
Thanks again folks - very impressive.

Jayyde -I do actually have more than just three criteria, but want to keep
it simple and scaleable.
Whenever ANY of the data entry fields change, the txtFilterString is set to
blank, and the code loops through all of the controls on the form, building
an entirely *new* string. This approach takes advantage of the loop, and
avoids having to modify an existing string using the Mid and/or InStr
functions, and dealing with the separate cases of a user changing a value to
a new one, or deleting an entered value, which would need to be handled
differently.
Click to expand...

Why not do this just once, when the user leaves the form by pressing the
"Select" control ?
 
I got this same thing working yesterday.

1) Base your form on a query (not directly on the table)
2) The WHERE clause of the sql behind the query should be:

WHERE (FieldID = [Forms]![FormName]![UnboundControl] Or
[Forms]![FormName]![UnboundControl] Is Null)

To include more than one criteria append it to the end of the WHERE clause
with an AND. ie:

WHERE (FieldID = [Forms]![FormName]![UnboundControl] Or
[Forms]![FormName]![UnboundControl] Is Null) and (Field2ID =
[Forms]![FormName]![UnboundControl2] Or [Forms]![FormName]![UnboundControl2]
Is Null)

As an example, here is the SQL behind my query:

SELECT PlantHireDetails.TransactionID, PlantHireDetails.PlantID,
PlantHireDetails.HireDate, PlantHireDetails.EmployeeID,
PlantHireDetails.JobNumber, PlantHireDetails.ExpectedReturnDate,
PlantHireDetails.ReturnDate
FROM PlantHireDetails
WHERE (TransactionID = [Forms]![PlantHireDetailsSearch]![TransactionIDFind]
Or [Forms]![PlantHireDetailsSearch]![TransactionIDFind] Is Null) and
(EmployeeID = [Forms]![PlantHireDetailsSearch]![EmployeeIDFind] Or
[Forms]![PlantHireDetailsSearch]![EmployeeIDFind] Is Null) and (PlantID =
[Forms]![PlantHireDetailsSearch]![PlantIdFind] Or
[Forms]![PlantHireDetailsSearch]![PlantIDFind] Is Null) and (JobNumber =
[Forms]![PlantHireDetailsSearch]![JobNumberFind] Or
[Forms]![PlantHireDetailsSearch]![JobNumberFind] Is Null);

3) Use a command button to requery the form or requery in the AfterUpdate
event of each control.

The only real downside I have found to doing it like this is when all the
fields are empty, ALL records are returned. If you have a large recordset it
can take some time to retrieve all the records.

Thanks to Ofer for his help with the SQL.

Dave
 
Back
Top