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...
 
S

Steve Sanford

"Type" is a reserved word in Access and shouldn't be used to name objects in
Access. And it is not very descriptive; "TYPE" of what? If it is Issue type,
then "TypeOfIssue" or "IssueType" might be a better name. (Also, don't use
spaces.)

Here is a link to a list of reserved words:

http://allenbrowne.com/AppIssueBadWord.html


And here are a couple of links for naming conventions:


http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_20naming.asp

http://www.xoc.net/standards/rvbanc.asp naming

http://www.xoc.net/standards/rvbacc.asp coding



I recreated the table, forms and controls. The code seems to work, but i
have some questions.

What are the row sources for the combo boxes? Are the bound fields numbers
or text?

Is the record source for the form a query or a table? If it is a query, open
the query and check if there is any criteria entered for any of the fields.

Another thing to check - open the form in design view and check the filter
property. If there is an entry, delete it and save the form.



HTH
 
L

LoriO

Thanks - somehow when people use this they must be 'saving' the form when
they exit -
So thanks for the heads up to check 'one more time' that the filter property
is blank. I seem to be clearing it quite often which tells me I did not have
the close button working as it should.

I have added a no save option on close - I think this will fix the issue.
Thanks for the heads up on the reserved fields - once again you have given
me some great help
 
L

LoriO

Thank you - it seems you hit it on the head - made me realize I need to lock
the save options down a little more than I had.

Jeanette Cunningham said:
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.)


:

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
Extra ) error in search form 4
Allen Brownes ECount() 5
Create Report On the Fly 1
Allen Browne's Search Form - Debugging help needed 2
Multiple Search Criteria with AND/OR??? 4
Filter for words at the beginning or end using a Form Command Butt 5
Help with VBA with an unbound form 4

Top