Open form unfiltered if strWhere is blank

L

LoriO

I open a form with a strWhere result and if there are no selections to filter
by, I give a no criteria message and the form is opening using the last
filter.
I want the form to open unfiltered if there is no criteria.
I am not doing well -
What is the best way to accomplish this?
When I close the form do I need to enter something?

or add something to the code below:

Private Sub OptionDocDetail_Click()
DoCmd.Minimize

Dim strWhere As String
Dim lngLen As Long
strWhere = ""

If (Me.Cbo_AssignedTo) = "" Then
strWhere = ""
ElseIf (Me.Cbo_AssignedTo) <> "" Then
strWhere = strWhere & "([ComboAssign]Like " & "'*" & Me.Cbo_AssignedTo
& "*'" & ") AND "
End If
If (Me.cbo_module) <> "" Then
strWhere = strWhere & "([ModuleName] = " & "'" & Me.cbo_module & "'"
& ") AND "
ElseIf (Me.cbo_module) = "" Then
End If

If (Me.cbo_Type) <> "" Then
strWhere = strWhere & "([Type] = " & "'" & Me.cbo_Type & "'" & ")
AND "
ElseIf (Me.cbo_Type) = "" Then
End If

'Chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria"
Else
strWhere = Left$(strWhere, lngLen)
End If
DoCmd.OpenForm "frm_MT_Doc_Detail", , , strWhere

End Sub
 
S

Steve Sanford

Not quite sure what you are trying to do, but maybe this will help


'-------------beg code--------------------------
Private Sub OptionDocDetail_Click()
DoCmd.Minimize

Dim strWhere As String
Dim lngLen As Long
strWhere = ""

If Nz((Me.Cbo_AssignedTo), "") <> "" Then
strWhere = strWhere & "([ComboAssign]Like '*" & Me.Cbo_AssignedTo & "*')
AND "
End If

If Nz((Me.cbo_module), "") <> "" Then
strWhere = strWhere & "([ModuleName] = '" & Me.cbo_module & "') AND "
End If

If Nz((Me.cbo_Type), "") <> "" Then
strWhere = strWhere & "([Type] = '" & Me.cbo_Type & "') AND "
End If

'Chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria selected"
Else
strWhere = Left$(strWhere, lngLen)
End If

' comment out or delete after testing.
MsgBox strWhere

DoCmd.OpenForm "frm_MT_Doc_Detail", , , strWhere

' to close the minimized form after form "frm_MT_Doc_Detail" is open
' uncomment the next line

' DoCmd.Close acForm, Me.Name, acSaveNo

End Sub
'-------------end code--------------------------


HTH
 
L

LoriO

I definitely learned something new with the Nz - that makes my code much
easier to read but my form is still opening filtered.
I will see if I can explain the problem a little differently:

1. I have a form (Menu) I use as a menu (this is an issue tracking database)
2. On the form (Menu), we can select the person the task is assigned to
(Me.Cbo_AssignedTo) or leave it blank if I want all the records regardless of
who they are assigned to
3. I can pick the Module the task belongs to (Me.cbo_module) or I can leave
it blank
4. One of the menu options is to open a form (Issues) based on the criteria
(strWhere) entered in the 'selection' fields

The issue:
The code below is what determines the recordset I get. For some reason, if
I 'clear' all the selections (assigned to, module), I expect to get ALL
Records
I am not getting all records, the strWhere results from the previous request
are still used and the form (Issues) opens filtered.

I have tried putting in code when I close the Issues form but that was not
working either


Steve Sanford said:
Not quite sure what you are trying to do, but maybe this will help


'-------------beg code--------------------------
Private Sub OptionDocDetail_Click()
DoCmd.Minimize

Dim strWhere As String
Dim lngLen As Long
strWhere = ""

If Nz((Me.Cbo_AssignedTo), "") <> "" Then
strWhere = strWhere & "([ComboAssign]Like '*" & Me.Cbo_AssignedTo & "*')
AND "
End If

If Nz((Me.cbo_module), "") <> "" Then
strWhere = strWhere & "([ModuleName] = '" & Me.cbo_module & "') AND "
End If

If Nz((Me.cbo_Type), "") <> "" Then
strWhere = strWhere & "([Type] = '" & Me.cbo_Type & "') AND "
End If

'Chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria selected"
Else
strWhere = Left$(strWhere, lngLen)
End If

' comment out or delete after testing.
MsgBox strWhere

DoCmd.OpenForm "frm_MT_Doc_Detail", , , strWhere

' to close the minimized form after form "frm_MT_Doc_Detail" is open
' uncomment the next line

' DoCmd.Close acForm, Me.Name, acSaveNo

End Sub
'-------------end code--------------------------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


LoriO said:
I open a form with a strWhere result and if there are no selections to filter
by, I give a no criteria message and the form is opening using the last
filter.
I want the form to open unfiltered if there is no criteria.
I am not doing well -
What is the best way to accomplish this?
When I close the form do I need to enter something?

or add something to the code below:

Private Sub OptionDocDetail_Click()
DoCmd.Minimize

Dim strWhere As String
Dim lngLen As Long
strWhere = ""

If (Me.Cbo_AssignedTo) = "" Then
strWhere = ""
ElseIf (Me.Cbo_AssignedTo) <> "" Then
strWhere = strWhere & "([ComboAssign]Like " & "'*" & Me.Cbo_AssignedTo
& "*'" & ") AND "
End If
If (Me.cbo_module) <> "" Then
strWhere = strWhere & "([ModuleName] = " & "'" & Me.cbo_module & "'"
& ") AND "
ElseIf (Me.cbo_module) = "" Then
End If

If (Me.cbo_Type) <> "" Then
strWhere = strWhere & "([Type] = " & "'" & Me.cbo_Type & "'" & ")
AND "
ElseIf (Me.cbo_Type) = "" Then
End If

'Chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria"
Else
strWhere = Left$(strWhere, lngLen)
End If
DoCmd.OpenForm "frm_MT_Doc_Detail", , , strWhere

End Sub
Click to expand...
Click to expand...
 
J

Jeanette Cunningham

Hi Lori,
When you open the form frm_Mt_Doc_Detail without clicking the button on the
menu (by going to the database window and opening the form there), does it
show all the records?

If it doesn't show all the records, open the query the form is based on and
make adjustments there.

If that doesn't fix it, what happens when the button that opens the
frm_Mt_Doc_Detail, has the code changed as follows (temporary change for
testing purposes):
comment out the line that says
DoCmd.OpenForm "frm_MT_Doc_Detail", , , strWhere by putting a single
apostrophe immediately before the D in Docmd



underneath it type the following line


DoCmd.OpenForm "frm_MT_Doc_Detail"

Now open the form using the button on the menu form.

let us know how you get on

Jeanette Cunningham




LoriO said:
I definitely learned something new with the Nz - that makes my code much
easier to read but my form is still opening filtered.
I will see if I can explain the problem a little differently:

1. I have a form (Menu) I use as a menu (this is an issue tracking
database)
2. On the form (Menu), we can select the person the task is assigned to
(Me.Cbo_AssignedTo) or leave it blank if I want all the records regardless
of
who they are assigned to
3. I can pick the Module the task belongs to (Me.cbo_module) or I can
leave
it blank
4. One of the menu options is to open a form (Issues) based on the
criteria
(strWhere) entered in the 'selection' fields

The issue:
The code below is what determines the recordset I get. For some reason,
if
I 'clear' all the selections (assigned to, module), I expect to get ALL
Records
I am not getting all records, the strWhere results from the previous
request
are still used and the form (Issues) opens filtered.

I have tried putting in code when I close the Issues form but that was not
working either


Steve Sanford said:
Not quite sure what you are trying to do, but maybe this will help


'-------------beg code--------------------------
Private Sub OptionDocDetail_Click()
DoCmd.Minimize

Dim strWhere As String
Dim lngLen As Long
strWhere = ""

If Nz((Me.Cbo_AssignedTo), "") <> "" Then
strWhere = strWhere & "([ComboAssign]Like '*" & Me.Cbo_AssignedTo &
"*')
AND "
End If

If Nz((Me.cbo_module), "") <> "" Then
strWhere = strWhere & "([ModuleName] = '" & Me.cbo_module & "') AND "
End If

If Nz((Me.cbo_Type), "") <> "" Then
strWhere = strWhere & "([Type] = '" & Me.cbo_Type & "') AND "
End If

'Chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria selected"
Else
strWhere = Left$(strWhere, lngLen)
End If

' comment out or delete after testing.
MsgBox strWhere

DoCmd.OpenForm "frm_MT_Doc_Detail", , , strWhere

' to close the minimized form after form "frm_MT_Doc_Detail" is open
' uncomment the next line

' DoCmd.Close acForm, Me.Name, acSaveNo

End Sub
'-------------end code--------------------------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


LoriO said:
I open a form with a strWhere result and if there are no selections to
filter
by, I give a no criteria message and the form is opening using the last
filter.
I want the form to open unfiltered if there is no criteria.
I am not doing well -
What is the best way to accomplish this?
When I close the form do I need to enter something?

or add something to the code below:

Private Sub OptionDocDetail_Click()
DoCmd.Minimize

Dim strWhere As String
Dim lngLen As Long
strWhere = ""

If (Me.Cbo_AssignedTo) = "" Then
strWhere = ""
ElseIf (Me.Cbo_AssignedTo) <> "" Then
strWhere = strWhere & "([ComboAssign]Like " & "'*" &
Me.Cbo_AssignedTo
& "*'" & ") AND "
End If
If (Me.cbo_module) <> "" Then
strWhere = strWhere & "([ModuleName] = " & "'" & Me.cbo_module
& "'"
& ") AND "
ElseIf (Me.cbo_module) = "" Then
End If

If (Me.cbo_Type) <> "" Then
strWhere = strWhere & "([Type] = " & "'" & Me.cbo_Type & "'" &
")
AND "
ElseIf (Me.cbo_Type) = "" Then
End If

'Chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria"
Else
strWhere = Left$(strWhere, lngLen)
End If
DoCmd.OpenForm "frm_MT_Doc_Detail", , , strWhere

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Search Form with a SubForm 3
Allen Brownes ECount() 5
Create Report On the Fly 1
Extra ) error in search form 9
Filtering a multiple item form 1
Filtering using two controls 2
Allen Browne's Search Form - Debugging help needed 2
Filter for words at the beginning or end using a Form Command Butt 5

Top