Query form coding

G

Guest

I have a form that has two list fields (namelst and sourcelst) and one button
group (statusfra) for users to choose from in order to customize a report
(trkng_rpt). I want the user to be able to choose any number of names and or
any number of sources and whether the request is open (no date in the status
field of the report), closed (date in the status field) or both. The name
field is an unbound text box on the report that combines the [Last Name],
[First Name], and [Middle Initial] into one text string separated by commas.
The Source and Status are both taken from form controls. Also I would like
to be able to sort the query choices but have not got that far yet because I
cannot seem to get the first part to work right. Here is my code for the
filter buttons:

Private Sub filter_cmd_Click()
Dim varItem As Variant
Dim strName As String
Dim strSource As String
Dim strStatus As String
Dim strFilter as String
' Check if the report is open
If SysCmd(acSysCmdgetObjectState, acReport, "trkng_rpt") <>
acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Criteria String from namelst listbox
For Each varItem In Me.namelst.ItemsSelected
strName = strName & ",'" & Me.namelst.ItemData(varItem)
& "'"
Next varItem
If Len(strName) = 0
strName = "Like '*'"
Else
strName = Right(strName, Len(strName) -1)
strName = "IN(" & strName & ")"
End If
' Criteria String from sourcelst listbox
For Each varItem In Me.sourcelst.ItemsSelected
strSource = strSource & ",'" & Me.sourcelst.ItemData(varItem)
& "'"
Next varItem
If Len(strSource) = 0 Then
strSource = "Like '*'"
Else
strSource = Right(strSource, Len(strSource) -1)
strSource = "IN(" & strSource & ")"
End If
' Criteria String from statusfra frame
Select Case Me.statusfra.Value
Case 1
strStatus = "Is Null"
Case 2
strStatus = "Is Not Null"
Case 3
strStatus = "Like '*'"
End Select
' Filter string
strFilter = "[Name] " & strName &
" AND [Source] " & strSource & _
" AND [Status] " & strStatus
' Apply Filter
With Reports![trkng_rpt]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub remove_fltr_Click()
On Error Resume Next
' Remove Filter
Reports![trkng_rpt].FilterOn = False
End Sub


I can query on any number of sources so that works fine. I can also query
on either open or closed status but when I choose both it seems to remove the
filter because I see everything. The remove filter button works fine.
However, if I choose any name(s) from the list the report is blank. I have
been trying to work this for days now. I separated the name part of the code
from the source and status and made one for just names and one for both
source and status. Both worked fine. It is only when I combine the two that
the name never works. I am thinking that it is becase it is an unbound text
box but I would really like to use the lastname, firstname, mi format used in
the list box. Any and all help will be extremely appreciated. Thanks.
 
G

Guest

Hi Jay,

If you haven't solved this problem, post back. I've looked at your code and
modified it a little.

You are missing a "then" here:
VVV
If Len(strName) = 0
strName = "Like '*'"
Else
Click to expand...


Also, you have named one of your fields "Name" which is a reserved word in
Access and it is not very descriptive; name of the month, a car, a city??
when you are looking thru code, you don't know what you are revering to.
Access can also confused - are you trying to rever to an object name or use
the name property?

.................... The name
field is an unbound text box on the report that combines the [Last Name],
[First Name], and [Middle Initial] into one text string separated by commas.
Click to expand...

I don't understand how this can work. In the code, you are filtering the
recordset (query) on a field "[Name]"; on the report is an unbound control
that concatenates three fields ........

What is the record source for the report? Would you please post the SQL ?


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


Jay said:
I have a form that has two list fields (namelst and sourcelst) and one button
group (statusfra) for users to choose from in order to customize a report
(trkng_rpt). I want the user to be able to choose any number of names and or
any number of sources and whether the request is open (no date in the status
field of the report), closed (date in the status field) or both. The name
field is an unbound text box on the report that combines the [Last Name],
[First Name], and [Middle Initial] into one text string separated by commas.
The Source and Status are both taken from form controls. Also I would like
to be able to sort the query choices but have not got that far yet because I
cannot seem to get the first part to work right. Here is my code for the
filter buttons:

Private Sub filter_cmd_Click()
Dim varItem As Variant
Dim strName As String
Dim strSource As String
Dim strStatus As String
Dim strFilter as String
' Check if the report is open
If SysCmd(acSysCmdgetObjectState, acReport, "trkng_rpt") <>
acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Criteria String from namelst listbox
For Each varItem In Me.namelst.ItemsSelected
strName = strName & ",'" & Me.namelst.ItemData(varItem)
& "'"
Next varItem
If Len(strName) = 0
strName = "Like '*'"
Else
strName = Right(strName, Len(strName) -1)
strName = "IN(" & strName & ")"
End If
' Criteria String from sourcelst listbox
For Each varItem In Me.sourcelst.ItemsSelected
strSource = strSource & ",'" & Me.sourcelst.ItemData(varItem)
& "'"
Next varItem
If Len(strSource) = 0 Then
strSource = "Like '*'"
Else
strSource = Right(strSource, Len(strSource) -1)
strSource = "IN(" & strSource & ")"
End If
' Criteria String from statusfra frame
Select Case Me.statusfra.Value
Case 1
strStatus = "Is Null"
Case 2
strStatus = "Is Not Null"
Case 3
strStatus = "Like '*'"
End Select
' Filter string
strFilter = "[Name] " & strName &
" AND [Source] " & strSource & _
" AND [Status] " & strStatus
' Apply Filter
With Reports![trkng_rpt]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub remove_fltr_Click()
On Error Resume Next
' Remove Filter
Reports![trkng_rpt].FilterOn = False
End Sub


I can query on any number of sources so that works fine. I can also query
on either open or closed status but when I choose both it seems to remove the
filter because I see everything. The remove filter button works fine.
However, if I choose any name(s) from the list the report is blank. I have
been trying to work this for days now. I separated the name part of the code
from the source and status and made one for just names and one for both
source and status. Both worked fine. It is only when I combine the two that
the name never works. I am thinking that it is becase it is an unbound text
box but I would really like to use the lastname, firstname, mi format used in
the list box. Any and all help will be extremely appreciated. Thanks.
Click to expand...
 
G

Guest

SteveS,

Thanks... I have not solved this yet so any help would be greatly appreciated.

Jay

SteveS said:
Hi Jay,

If you haven't solved this problem, post back. I've looked at your code and
modified it a little.

You are missing a "then" here:
VVV
If Len(strName) = 0
strName = "Like '*'"
Else
Click to expand...


Also, you have named one of your fields "Name" which is a reserved word in
Access and it is not very descriptive; name of the month, a car, a city??
when you are looking thru code, you don't know what you are revering to.
Access can also confused - are you trying to rever to an object name or use
the name property?

.................... The name
field is an unbound text box on the report that combines the [Last Name],
[First Name], and [Middle Initial] into one text string separated by commas.
Click to expand...

I don't understand how this can work. In the code, you are filtering the
recordset (query) on a field "[Name]"; on the report is an unbound control
that concatenates three fields ........

What is the record source for the report? Would you please post the SQL ?


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


Jay said:
I have a form that has two list fields (namelst and sourcelst) and one button
group (statusfra) for users to choose from in order to customize a report
(trkng_rpt). I want the user to be able to choose any number of names and or
any number of sources and whether the request is open (no date in the status
field of the report), closed (date in the status field) or both. The name
field is an unbound text box on the report that combines the [Last Name],
[First Name], and [Middle Initial] into one text string separated by commas.
The Source and Status are both taken from form controls. Also I would like
to be able to sort the query choices but have not got that far yet because I
cannot seem to get the first part to work right. Here is my code for the
filter buttons:

Private Sub filter_cmd_Click()
Dim varItem As Variant
Dim strName As String
Dim strSource As String
Dim strStatus As String
Dim strFilter as String
' Check if the report is open
If SysCmd(acSysCmdgetObjectState, acReport, "trkng_rpt") <>
acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Criteria String from namelst listbox
For Each varItem In Me.namelst.ItemsSelected
strName = strName & ",'" & Me.namelst.ItemData(varItem)
& "'"
Next varItem
If Len(strName) = 0
strName = "Like '*'"
Else
strName = Right(strName, Len(strName) -1)
strName = "IN(" & strName & ")"
End If
' Criteria String from sourcelst listbox
For Each varItem In Me.sourcelst.ItemsSelected
strSource = strSource & ",'" & Me.sourcelst.ItemData(varItem)
& "'"
Next varItem
If Len(strSource) = 0 Then
strSource = "Like '*'"
Else
strSource = Right(strSource, Len(strSource) -1)
strSource = "IN(" & strSource & ")"
End If
' Criteria String from statusfra frame
Select Case Me.statusfra.Value
Case 1
strStatus = "Is Null"
Case 2
strStatus = "Is Not Null"
Case 3
strStatus = "Like '*'"
End Select
' Filter string
strFilter = "[Name] " & strName &
" AND [Source] " & strSource & _
" AND [Status] " & strStatus
' Apply Filter
With Reports![trkng_rpt]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub remove_fltr_Click()
On Error Resume Next
' Remove Filter
Reports![trkng_rpt].FilterOn = False
End Sub


I can query on any number of sources so that works fine. I can also query
on either open or closed status but when I choose both it seems to remove the
filter because I see everything. The remove filter button works fine.
However, if I choose any name(s) from the list the report is blank. I have
been trying to work this for days now. I separated the name part of the code
from the source and status and made one for just names and one for both
source and status. Both worked fine. It is only when I combine the two that
the name never works. I am thinking that it is becase it is an unbound text
box but I would really like to use the lastname, firstname, mi format used in
the list box. Any and all help will be extremely appreciated. Thanks.
Click to expand...
Click to expand...
 
G

Guest

Hi Jay,

Try the following code:

'*****************
Option Compare Database
Option Explicit

Private Sub filter_cmd_Click()
Dim varItem As Variant
Dim strName As String
Dim strSource As String
Dim strStatus As String
Dim strFilter As String

' Check if the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "trkng_rpt") <>
acObjStateOpen Then
MsgBox "You must open the report first."
' ' ---- You could open the report here ....---

Exit Sub
End If

' Remove Filter
With Reports![trkng_rpt]
.Filter = strFilter
.FilterOn = False
End With

' Criteria String from namelst listbox
For Each varItem In Me.namelst.ItemsSelected
strName = strName & ",'" & Me.namelst.ItemData(varItem) & "'"
Next varItem
If Len(Trim(strName)) > 0 Then
strName = Right(strName, Len(strName) - 1)
strName = "IN(" & strName & ")"
End If

' Criteria String from sourcelst listbox
For Each varItem In Me.sourcelst.ItemsSelected
strSource = strSource & ",'" & Me.sourcelst.ItemData(varItem) & "'"
Next varItem
If Len(Trim(strSource)) > 0 Then
strSource = Right(strSource, Len(strSource) - 1)
strSource = "IN(" & strSource & ")"
End If

' Criteria String from statusfra frame
Select Case Me.statusfra.Value
Case 1
strStatus = "Is Null"
Case 2
strStatus = "Is Not Null"
End Select

' create Filter string
If Len(Trim(strName)) > 0 Then
'**** "Name" is a reserved word in Access and
'**** shouldn't be used as a field name!!!!
' also, its not descriptive - "Name" of what??
' A city? A car?
' Maybe change it to FullName....or ClientName
strFilter = "[Name] " & strName & " AND "
End If

If Len(Trim(strSource)) > 0 Then
strFilter = strFilter & " [Source] " & strSource & " AND "
End If

If Len(Trim(strStatus)) > 0 Then
strFilter = strFilter & " [Status] " & strStatus & " And "
End If

If Len(Trim(strFilter)) > 0 Then
strFilter = Left(strFilter, Len(strFilter) - 5)
' Apply Filter
With Reports![trkng_rpt]
.Filter = strFilter
.FilterOn = True
End With
'-------------------------------------------
'check strFilter
MsgBox strFilter
'-------------------------------------------
End If


End Sub

Private Sub remove_fltr_Click()
On Error Resume Next
' Remove Filter
With Reports![trkng_rpt]
.Filter = strFilter
.FilterOn = False
End With

End Sub
'*******************

Keep me posted...

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


Jay said:
SteveS,

Thanks... I have not solved this yet so any help would be greatly appreciated.

Jay

SteveS said:
Hi Jay,

If you haven't solved this problem, post back. I've looked at your code and
modified it a little.

You are missing a "then" here:
VVV
If Len(strName) = 0
strName = "Like '*'"
Else
Click to expand...


Also, you have named one of your fields "Name" which is a reserved word in
Access and it is not very descriptive; name of the month, a car, a city??
when you are looking thru code, you don't know what you are revering to.
Access can also confused - are you trying to rever to an object name or use
the name property?

.................... The name
field is an unbound text box on the report that combines the [Last Name],
[First Name], and [Middle Initial] into one text string separated by commas.
Click to expand...

I don't understand how this can work. In the code, you are filtering the
recordset (query) on a field "[Name]"; on the report is an unbound control
that concatenates three fields ........

What is the record source for the report? Would you please post the SQL ?


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


Jay said:
I have a form that has two list fields (namelst and sourcelst) and one button
group (statusfra) for users to choose from in order to customize a report
(trkng_rpt). I want the user to be able to choose any number of names and or
any number of sources and whether the request is open (no date in the status
field of the report), closed (date in the status field) or both. The name
field is an unbound text box on the report that combines the [Last Name],
[First Name], and [Middle Initial] into one text string separated by commas.
The Source and Status are both taken from form controls. Also I would like
to be able to sort the query choices but have not got that far yet because I
cannot seem to get the first part to work right. Here is my code for the
filter buttons:

Private Sub filter_cmd_Click()
Dim varItem As Variant
Dim strName As String
Dim strSource As String
Dim strStatus As String
Dim strFilter as String
' Check if the report is open
If SysCmd(acSysCmdgetObjectState, acReport, "trkng_rpt") <>
acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Criteria String from namelst listbox
For Each varItem In Me.namelst.ItemsSelected
strName = strName & ",'" & Me.namelst.ItemData(varItem)
& "'"
Next varItem
If Len(strName) = 0
strName = "Like '*'"
Else
strName = Right(strName, Len(strName) -1)
strName = "IN(" & strName & ")"
End If
' Criteria String from sourcelst listbox
For Each varItem In Me.sourcelst.ItemsSelected
strSource = strSource & ",'" & Me.sourcelst.ItemData(varItem)
& "'"
Next varItem
If Len(strSource) = 0 Then
strSource = "Like '*'"
Else
strSource = Right(strSource, Len(strSource) -1)
strSource = "IN(" & strSource & ")"
End If
' Criteria String from statusfra frame
Select Case Me.statusfra.Value
Case 1
strStatus = "Is Null"
Case 2
strStatus = "Is Not Null"
Case 3
strStatus = "Like '*'"
End Select
' Filter string
strFilter = "[Name] " & strName &
" AND [Source] " & strSource & _
" AND [Status] " & strStatus
' Apply Filter
With Reports![trkng_rpt]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub remove_fltr_Click()
On Error Resume Next
' Remove Filter
Reports![trkng_rpt].FilterOn = False
End Sub


I can query on any number of sources so that works fine. I can also query
on either open or closed status but when I choose both it seems to remove the
filter because I see everything. The remove filter button works fine.
However, if I choose any name(s) from the list the report is blank. I have
been trying to work this for days now. I separated the name part of the code
from the source and status and made one for just names and one for both
source and status. Both worked fine. It is only when I combine the two that
the name never works. I am thinking that it is becase it is an unbound text
box but I would really like to use the lastname, firstname, mi format used in
the list box. Any and all help will be extremely appreciated. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hey Steve,

Thanks so much for all of your help so far. The report works fine except
when I try to query a name. If I do not select a name, everything runs
great. When I select a name the report displays with only the commas from
the name field. I renamed the text box to FullName and changed the code as
appropriate. The unbound text box control is set to =[Last Name] & "," & " "
& [First Name] & "," & " " & [Middle Initial]. Is this the problem? Thanks
again for all the time you have spent on this.

Jay

SteveS said:
Hi Jay,

Try the following code:

'*****************
Option Compare Database
Option Explicit

Private Sub filter_cmd_Click()
Dim varItem As Variant
Dim strName As String
Dim strSource As String
Dim strStatus As String
Dim strFilter As String

' Check if the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "trkng_rpt") <>
acObjStateOpen Then
MsgBox "You must open the report first."
' ' ---- You could open the report here ....---

Exit Sub
End If

' Remove Filter
With Reports![trkng_rpt]
.Filter = strFilter
.FilterOn = False
End With

' Criteria String from namelst listbox
For Each varItem In Me.namelst.ItemsSelected
strName = strName & ",'" & Me.namelst.ItemData(varItem) & "'"
Next varItem
If Len(Trim(strName)) > 0 Then
strName = Right(strName, Len(strName) - 1)
strName = "IN(" & strName & ")"
End If

' Criteria String from sourcelst listbox
For Each varItem In Me.sourcelst.ItemsSelected
strSource = strSource & ",'" & Me.sourcelst.ItemData(varItem) & "'"
Next varItem
If Len(Trim(strSource)) > 0 Then
strSource = Right(strSource, Len(strSource) - 1)
strSource = "IN(" & strSource & ")"
End If

' Criteria String from statusfra frame
Select Case Me.statusfra.Value
Case 1
strStatus = "Is Null"
Case 2
strStatus = "Is Not Null"
End Select

' create Filter string
If Len(Trim(strName)) > 0 Then
'**** "Name" is a reserved word in Access and
'**** shouldn't be used as a field name!!!!
' also, its not descriptive - "Name" of what??
' A city? A car?
' Maybe change it to FullName....or ClientName
strFilter = "[Name] " & strName & " AND "
End If

If Len(Trim(strSource)) > 0 Then
strFilter = strFilter & " [Source] " & strSource & " AND "
End If

If Len(Trim(strStatus)) > 0 Then
strFilter = strFilter & " [Status] " & strStatus & " And "
End If

If Len(Trim(strFilter)) > 0 Then
strFilter = Left(strFilter, Len(strFilter) - 5)
' Apply Filter
With Reports![trkng_rpt]
.Filter = strFilter
.FilterOn = True
End With
'-------------------------------------------
'check strFilter
MsgBox strFilter
'-------------------------------------------
End If


End Sub

Private Sub remove_fltr_Click()
On Error Resume Next
' Remove Filter
With Reports![trkng_rpt]
.Filter = strFilter
.FilterOn = False
End With

End Sub
'*******************

Keep me posted...

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


Jay said:
SteveS,

Thanks... I have not solved this yet so any help would be greatly appreciated.

Jay

SteveS said:
Hi Jay,

If you haven't solved this problem, post back. I've looked at your code and
modified it a little.

You are missing a "then" here:
VVV
If Len(strName) = 0
strName = "Like '*'"
Else


Also, you have named one of your fields "Name" which is a reserved word in
Access and it is not very descriptive; name of the month, a car, a city??
when you are looking thru code, you don't know what you are revering to.
Access can also confused - are you trying to rever to an object name or use
the name property?


.................... The name
field is an unbound text box on the report that combines the [Last Name],
[First Name], and [Middle Initial] into one text string separated by commas.

I don't understand how this can work. In the code, you are filtering the
recordset (query) on a field "[Name]"; on the report is an unbound control
that concatenates three fields ........

What is the record source for the report? Would you please post the SQL ?


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


:

I have a form that has two list fields (namelst and sourcelst) and one button
group (statusfra) for users to choose from in order to customize a report
(trkng_rpt). I want the user to be able to choose any number of names and or
any number of sources and whether the request is open (no date in the status
field of the report), closed (date in the status field) or both. The name
field is an unbound text box on the report that combines the [Last Name],
[First Name], and [Middle Initial] into one text string separated by commas.
The Source and Status are both taken from form controls. Also I would like
to be able to sort the query choices but have not got that far yet because I
cannot seem to get the first part to work right. Here is my code for the
filter buttons:

Private Sub filter_cmd_Click()
Dim varItem As Variant
Dim strName As String
Dim strSource As String
Dim strStatus As String
Dim strFilter as String
' Check if the report is open
If SysCmd(acSysCmdgetObjectState, acReport, "trkng_rpt") <>
acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Criteria String from namelst listbox
For Each varItem In Me.namelst.ItemsSelected
strName = strName & ",'" & Me.namelst.ItemData(varItem)
& "'"
Next varItem
If Len(strName) = 0
strName = "Like '*'"
Else
strName = Right(strName, Len(strName) -1)
strName = "IN(" & strName & ")"
End If
' Criteria String from sourcelst listbox
For Each varItem In Me.sourcelst.ItemsSelected
strSource = strSource & ",'" & Me.sourcelst.ItemData(varItem)
& "'"
Next varItem
If Len(strSource) = 0 Then
strSource = "Like '*'"
Else
strSource = Right(strSource, Len(strSource) -1)
strSource = "IN(" & strSource & ")"
End If
' Criteria String from statusfra frame
Select Case Me.statusfra.Value
Case 1
strStatus = "Is Null"
Case 2
strStatus = "Is Not Null"
Case 3
strStatus = "Like '*'"
End Select
' Filter string
strFilter = "[Name] " & strName &
" AND [Source] " & strSource & _
" AND [Status] " & strStatus
' Apply Filter
With Reports![trkng_rpt]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub remove_fltr_Click()
On Error Resume Next
' Remove Filter
Reports![trkng_rpt].FilterOn = False
End Sub


I can query on any number of sources so that works fine. I can also query
on either open or closed status but when I choose both it seems to remove the
filter because I see everything. The remove filter button works fine.
However, if I choose any name(s) from the list the report is blank. I have
been trying to work this for days now. I separated the name part of the code
from the source and status and made one for just names and one for both
source and status. Both worked fine. It is only when I combine the two that
the name never works. I am thinking that it is becase it is an unbound text
box but I would really like to use the lastname, firstname, mi format used in
the list box. Any and all help will be extremely appreciated. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Jay,

What is the row source for the "namelst" list box on the search form?

What is the record source of the report?

Please post the SQL.....


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


Jay said:
Hey Steve,

Thanks so much for all of your help so far. The report works fine except
when I try to query a name. If I do not select a name, everything runs
great. When I select a name the report displays with only the commas from
the name field. I renamed the text box to FullName and changed the code as
appropriate. The unbound text box control is set to =[Last Name] & "," & " "
& [First Name] & "," & " " & [Middle Initial]. Is this the problem? Thanks
again for all the time you have spent on this.

Jay

SteveS said:
Hi Jay,

Try the following code:

'*****************
Option Compare Database
Option Explicit

Private Sub filter_cmd_Click()
Dim varItem As Variant
Dim strName As String
Dim strSource As String
Dim strStatus As String
Dim strFilter As String

' Check if the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "trkng_rpt") <>
acObjStateOpen Then
MsgBox "You must open the report first."
' ' ---- You could open the report here ....---

Exit Sub
End If

' Remove Filter
With Reports![trkng_rpt]
.Filter = strFilter
.FilterOn = False
End With

' Criteria String from namelst listbox
For Each varItem In Me.namelst.ItemsSelected
strName = strName & ",'" & Me.namelst.ItemData(varItem) & "'"
Next varItem
If Len(Trim(strName)) > 0 Then
strName = Right(strName, Len(strName) - 1)
strName = "IN(" & strName & ")"
End If

' Criteria String from sourcelst listbox
For Each varItem In Me.sourcelst.ItemsSelected
strSource = strSource & ",'" & Me.sourcelst.ItemData(varItem) & "'"
Next varItem
If Len(Trim(strSource)) > 0 Then
strSource = Right(strSource, Len(strSource) - 1)
strSource = "IN(" & strSource & ")"
End If

' Criteria String from statusfra frame
Select Case Me.statusfra.Value
Case 1
strStatus = "Is Null"
Case 2
strStatus = "Is Not Null"
End Select

' create Filter string
If Len(Trim(strName)) > 0 Then
'**** "Name" is a reserved word in Access and
'**** shouldn't be used as a field name!!!!
' also, its not descriptive - "Name" of what??
' A city? A car?
' Maybe change it to FullName....or ClientName
strFilter = "[Name] " & strName & " AND "
End If

If Len(Trim(strSource)) > 0 Then
strFilter = strFilter & " [Source] " & strSource & " AND "
End If

If Len(Trim(strStatus)) > 0 Then
strFilter = strFilter & " [Status] " & strStatus & " And "
End If

If Len(Trim(strFilter)) > 0 Then
strFilter = Left(strFilter, Len(strFilter) - 5)
' Apply Filter
With Reports![trkng_rpt]
.Filter = strFilter
.FilterOn = True
End With
'-------------------------------------------
'check strFilter
MsgBox strFilter
'-------------------------------------------
End If


End Sub

Private Sub remove_fltr_Click()
On Error Resume Next
' Remove Filter
With Reports![trkng_rpt]
.Filter = strFilter
.FilterOn = False
End With

End Sub
'*******************

Keep me posted...

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


Jay said:
SteveS,

Thanks... I have not solved this yet so any help would be greatly appreciated.

Jay

:

Hi Jay,

If you haven't solved this problem, post back. I've looked at your code and
modified it a little.

You are missing a "then" here:
VVV
If Len(strName) = 0
strName = "Like '*'"
Else


Also, you have named one of your fields "Name" which is a reserved word in
Access and it is not very descriptive; name of the month, a car, a city??
when you are looking thru code, you don't know what you are revering to.
Access can also confused - are you trying to rever to an object name or use
the name property?


.................... The name
field is an unbound text box on the report that combines the [Last Name],
[First Name], and [Middle Initial] into one text string separated by commas.

I don't understand how this can work. In the code, you are filtering the
recordset (query) on a field "[Name]"; on the report is an unbound control
that concatenates three fields ........

What is the record source for the report? Would you please post the SQL ?


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


:

I have a form that has two list fields (namelst and sourcelst) and one button
group (statusfra) for users to choose from in order to customize a report
(trkng_rpt). I want the user to be able to choose any number of names and or
any number of sources and whether the request is open (no date in the status
field of the report), closed (date in the status field) or both. The name
field is an unbound text box on the report that combines the [Last Name],
[First Name], and [Middle Initial] into one text string separated by commas.
The Source and Status are both taken from form controls. Also I would like
to be able to sort the query choices but have not got that far yet because I
cannot seem to get the first part to work right. Here is my code for the
filter buttons:

Private Sub filter_cmd_Click()
Dim varItem As Variant
Dim strName As String
Dim strSource As String
Dim strStatus As String
Dim strFilter as String
' Check if the report is open
If SysCmd(acSysCmdgetObjectState, acReport, "trkng_rpt") <>
acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Criteria String from namelst listbox
For Each varItem In Me.namelst.ItemsSelected
strName = strName & ",'" & Me.namelst.ItemData(varItem)
& "'"
Next varItem
If Len(strName) = 0
strName = "Like '*'"
Else
strName = Right(strName, Len(strName) -1)
strName = "IN(" & strName & ")"
End If
' Criteria String from sourcelst listbox
For Each varItem In Me.sourcelst.ItemsSelected
strSource = strSource & ",'" & Me.sourcelst.ItemData(varItem)
& "'"
Next varItem
If Len(strSource) = 0 Then
strSource = "Like '*'"
Else
strSource = Right(strSource, Len(strSource) -1)
strSource = "IN(" & strSource & ")"
End If
' Criteria String from statusfra frame
Select Case Me.statusfra.Value
Case 1
strStatus = "Is Null"
Case 2
strStatus = "Is Not Null"
Case 3
strStatus = "Like '*'"
End Select
' Filter string
strFilter = "[Name] " & strName &
" AND [Source] " & strSource & _
" AND [Status] " & strStatus
' Apply Filter
With Reports![trkng_rpt]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub remove_fltr_Click()
On Error Resume Next
' Remove Filter
Reports![trkng_rpt].FilterOn = False
End Sub


I can query on any number of sources so that works fine. I can also query
on either open or closed status but when I choose both it seems to remove the
filter because I see everything. The remove filter button works fine.
However, if I choose any name(s) from the list the report is blank. I have
been trying to work this for days now. I separated the name part of the code
from the source and status and made one for just names and one for both
source and status. Both worked fine. It is only when I combine the two that
the name never works. I am thinking that it is becase it is an unbound text
box but I would really like to use the lastname, firstname, mi format used in
the list box. Any and all help will be extremely appreciated. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Steve,

the row source for "namelst" list box is a query:
SELECT NAME_qry.NAME from Name_qry ORDER BY [NAME];

I guess I should change name to maybe FullName.

The record source for the report is:
SELECT Personnel_tbl.[Last Name], Tracking_tbl.[DAC], Tracking_tbl.Source,
Tracking_tbl.ReqReceived, Tracking_tbl.EntACAT, Tracking_tbl.EntBy,
Tracking_tbl.InsSent, Tracking_tbl.SentBy, Tracking_tbl.FormRet,
Tracking_tbl.RetTo, Tracking_tbl.ReqFor, Tracking_tbl.ForBy,
Tracking_tbl.PermFor, Tracking_tbl.ForBy, Tracking_tbl.DtgRec,
Tracking_tbl.RecBy, Tracking_tbl.Status, Tracking_tbl.CloBy,
Tracking_tbl.ACATUp, Tracking_tbl.UpBy, Tracking_tbl.Comments,
Tracking_tbl.Record AS Tracking_tbl_Record, Personnel_tbl.[First Name],
Personnel_tbl.[Middle Initial], Personnel_tbl.Record AS Personnel_tbl_Record
FROM Personnel_tbl INNER JOIN Tracking_tbl ON
Personnel_tbl.ACATrec=Tracking_tbl.ACTrec;

Thanks again for the help

Jay

SteveS said:
Hi Jay,

What is the row source for the "namelst" list box on the search form?

What is the record source of the report?

Please post the SQL.....


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


Jay said:
Hey Steve,

Thanks so much for all of your help so far. The report works fine except
when I try to query a name. If I do not select a name, everything runs
great. When I select a name the report displays with only the commas from
the name field. I renamed the text box to FullName and changed the code as
appropriate. The unbound text box control is set to =[Last Name] & "," & " "
& [First Name] & "," & " " & [Middle Initial]. Is this the problem? Thanks
again for all the time you have spent on this.

Jay

SteveS said:
Hi Jay,

Try the following code:

'*****************
Option Compare Database
Option Explicit

Private Sub filter_cmd_Click()
Dim varItem As Variant
Dim strName As String
Dim strSource As String
Dim strStatus As String
Dim strFilter As String

' Check if the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "trkng_rpt") <>
acObjStateOpen Then
MsgBox "You must open the report first."
' ' ---- You could open the report here ....---

Exit Sub
End If

' Remove Filter
With Reports![trkng_rpt]
.Filter = strFilter
.FilterOn = False
End With

' Criteria String from namelst listbox
For Each varItem In Me.namelst.ItemsSelected
strName = strName & ",'" & Me.namelst.ItemData(varItem) & "'"
Next varItem
If Len(Trim(strName)) > 0 Then
strName = Right(strName, Len(strName) - 1)
strName = "IN(" & strName & ")"
End If

' Criteria String from sourcelst listbox
For Each varItem In Me.sourcelst.ItemsSelected
strSource = strSource & ",'" & Me.sourcelst.ItemData(varItem) & "'"
Next varItem
If Len(Trim(strSource)) > 0 Then
strSource = Right(strSource, Len(strSource) - 1)
strSource = "IN(" & strSource & ")"
End If

' Criteria String from statusfra frame
Select Case Me.statusfra.Value
Case 1
strStatus = "Is Null"
Case 2
strStatus = "Is Not Null"
End Select

' create Filter string
If Len(Trim(strName)) > 0 Then
'**** "Name" is a reserved word in Access and
'**** shouldn't be used as a field name!!!!
' also, its not descriptive - "Name" of what??
' A city? A car?
' Maybe change it to FullName....or ClientName
strFilter = "[Name] " & strName & " AND "
End If

If Len(Trim(strSource)) > 0 Then
strFilter = strFilter & " [Source] " & strSource & " AND "
End If

If Len(Trim(strStatus)) > 0 Then
strFilter = strFilter & " [Status] " & strStatus & " And "
End If

If Len(Trim(strFilter)) > 0 Then
strFilter = Left(strFilter, Len(strFilter) - 5)
' Apply Filter
With Reports![trkng_rpt]
.Filter = strFilter
.FilterOn = True
End With
'-------------------------------------------
'check strFilter
MsgBox strFilter
'-------------------------------------------
End If


End Sub

Private Sub remove_fltr_Click()
On Error Resume Next
' Remove Filter
With Reports![trkng_rpt]
.Filter = strFilter
.FilterOn = False
End With

End Sub
'*******************

Keep me posted...

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


:

SteveS,

Thanks... I have not solved this yet so any help would be greatly appreciated.

Jay

:

Hi Jay,

If you haven't solved this problem, post back. I've looked at your code and
modified it a little.

You are missing a "then" here:
VVV
If Len(strName) = 0
strName = "Like '*'"
Else


Also, you have named one of your fields "Name" which is a reserved word in
Access and it is not very descriptive; name of the month, a car, a city??
when you are looking thru code, you don't know what you are revering to.
Access can also confused - are you trying to rever to an object name or use
the name property?


.................... The name
field is an unbound text box on the report that combines the [Last Name],
[First Name], and [Middle Initial] into one text string separated by commas.

I don't understand how this can work. In the code, you are filtering the
recordset (query) on a field "[Name]"; on the report is an unbound control
that concatenates three fields ........

What is the record source for the report? Would you please post the SQL ?


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


:

I have a form that has two list fields (namelst and sourcelst) and one button
group (statusfra) for users to choose from in order to customize a report
(trkng_rpt). I want the user to be able to choose any number of names and or
any number of sources and whether the request is open (no date in the status
field of the report), closed (date in the status field) or both. The name
field is an unbound text box on the report that combines the [Last Name],
[First Name], and [Middle Initial] into one text string separated by commas.
The Source and Status are both taken from form controls. Also I would like
to be able to sort the query choices but have not got that far yet because I
cannot seem to get the first part to work right. Here is my code for the
filter buttons:

Private Sub filter_cmd_Click()
Dim varItem As Variant
Dim strName As String
Dim strSource As String
Dim strStatus As String
Dim strFilter as String
' Check if the report is open
If SysCmd(acSysCmdgetObjectState, acReport, "trkng_rpt") <>
acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Criteria String from namelst listbox
For Each varItem In Me.namelst.ItemsSelected
strName = strName & ",'" & Me.namelst.ItemData(varItem)
& "'"
Next varItem
If Len(strName) = 0
strName = "Like '*'"
Else
strName = Right(strName, Len(strName) -1)
strName = "IN(" & strName & ")"
End If
' Criteria String from sourcelst listbox
For Each varItem In Me.sourcelst.ItemsSelected
strSource = strSource & ",'" & Me.sourcelst.ItemData(varItem)
& "'"
Next varItem
If Len(strSource) = 0 Then
strSource = "Like '*'"
Else
strSource = Right(strSource, Len(strSource) -1)
strSource = "IN(" & strSource & ")"
End If
' Criteria String from statusfra frame
Select Case Me.statusfra.Value
Case 1
strStatus = "Is Null"
Case 2
strStatus = "Is Not Null"
Case 3
strStatus = "Like '*'"
End Select
' Filter string
strFilter = "[Name] " & strName &
" AND [Source] " & strSource & _
" AND [Status] " & strStatus
' Apply Filter
With Reports![trkng_rpt]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub remove_fltr_Click()
On Error Resume Next
' Remove Filter
Reports![trkng_rpt].FilterOn = False
End Sub


I can query on any number of sources so that works fine. I can also query
on either open or closed status but when I choose both it seems to remove the
filter because I see everything. The remove filter button works fine.
However, if I choose any name(s) from the list the report is blank. I have
been trying to work this for days now. I separated the name part of the code
from the source and status and made one for just names and one for both
source and status. Both worked fine. It is only when I combine the two that
the name never works. I am thinking that it is becase it is an unbound text
box but I would really like to use the lastname, firstname, mi format used in
the list box. Any and all help will be extremely appreciated. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Jay,

What is the SQL of the query 'NAME_qry'?

Is the relationship between Personnel_tbl and Tracking_tbl a one-to-many
1 ----> many
Personnel_tbl Tracking_tbl

Personnel_tbl.Record is the primary key in Personnel_tbl and
Tracking_tbl.Record is the foreign key to Personnel_tbl?


(BTW, 'Record' is also a reserved word in Access. 'Record_ID' or 'lngRecord'
is a better name for the field)

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


Jay said:
Steve,

the row source for "namelst" list box is a query:
SELECT NAME_qry.NAME from Name_qry ORDER BY [NAME];

I guess I should change name to maybe FullName.

The record source for the report is:
SELECT Personnel_tbl.[Last Name], Tracking_tbl.[DAC], Tracking_tbl.Source,
Tracking_tbl.ReqReceived, Tracking_tbl.EntACAT, Tracking_tbl.EntBy,
Tracking_tbl.InsSent, Tracking_tbl.SentBy, Tracking_tbl.FormRet,
Tracking_tbl.RetTo, Tracking_tbl.ReqFor, Tracking_tbl.ForBy,
Tracking_tbl.PermFor, Tracking_tbl.ForBy, Tracking_tbl.DtgRec,
Tracking_tbl.RecBy, Tracking_tbl.Status, Tracking_tbl.CloBy,
Tracking_tbl.ACATUp, Tracking_tbl.UpBy, Tracking_tbl.Comments,
Tracking_tbl.Record AS Tracking_tbl_Record, Personnel_tbl.[First Name],
Personnel_tbl.[Middle Initial], Personnel_tbl.Record AS Personnel_tbl_Record
FROM Personnel_tbl INNER JOIN Tracking_tbl ON
Personnel_tbl.ACATrec=Tracking_tbl.ACTrec;

Thanks again for the help

Jay

SteveS said:
Hi Jay,

What is the row source for the "namelst" list box on the search form?

What is the record source of the report?

Please post the SQL.....


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


Jay said:
Hey Steve,

Thanks so much for all of your help so far. The report works fine except
when I try to query a name. If I do not select a name, everything runs
great. When I select a name the report displays with only the commas from
the name field. I renamed the text box to FullName and changed the code as
appropriate. The unbound text box control is set to =[Last Name] & "," & " "
& [First Name] & "," & " " & [Middle Initial]. Is this the problem? Thanks
again for all the time you have spent on this.

Jay

:

Hi Jay,

Try the following code:

'*****************
Option Compare Database
Option Explicit

Private Sub filter_cmd_Click()
Dim varItem As Variant
Dim strName As String
Dim strSource As String
Dim strStatus As String
Dim strFilter As String

' Check if the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "trkng_rpt") <>
acObjStateOpen Then
MsgBox "You must open the report first."
' ' ---- You could open the report here ....---

Exit Sub
End If

' Remove Filter
With Reports![trkng_rpt]
.Filter = strFilter
.FilterOn = False
End With

' Criteria String from namelst listbox
For Each varItem In Me.namelst.ItemsSelected
strName = strName & ",'" & Me.namelst.ItemData(varItem) & "'"
Next varItem
If Len(Trim(strName)) > 0 Then
strName = Right(strName, Len(strName) - 1)
strName = "IN(" & strName & ")"
End If

' Criteria String from sourcelst listbox
For Each varItem In Me.sourcelst.ItemsSelected
strSource = strSource & ",'" & Me.sourcelst.ItemData(varItem) & "'"
Next varItem
If Len(Trim(strSource)) > 0 Then
strSource = Right(strSource, Len(strSource) - 1)
strSource = "IN(" & strSource & ")"
End If

' Criteria String from statusfra frame
Select Case Me.statusfra.Value
Case 1
strStatus = "Is Null"
Case 2
strStatus = "Is Not Null"
End Select

' create Filter string
If Len(Trim(strName)) > 0 Then
'**** "Name" is a reserved word in Access and
'**** shouldn't be used as a field name!!!!
' also, its not descriptive - "Name" of what??
' A city? A car?
' Maybe change it to FullName....or ClientName
strFilter = "[Name] " & strName & " AND "
End If

If Len(Trim(strSource)) > 0 Then
strFilter = strFilter & " [Source] " & strSource & " AND "
End If

If Len(Trim(strStatus)) > 0 Then
strFilter = strFilter & " [Status] " & strStatus & " And "
End If

If Len(Trim(strFilter)) > 0 Then
strFilter = Left(strFilter, Len(strFilter) - 5)
' Apply Filter
With Reports![trkng_rpt]
.Filter = strFilter
.FilterOn = True
End With
'-------------------------------------------
'check strFilter
MsgBox strFilter
'-------------------------------------------
End If


End Sub

Private Sub remove_fltr_Click()
On Error Resume Next
' Remove Filter
With Reports![trkng_rpt]
.Filter = strFilter
.FilterOn = False
End With

End Sub
'*******************

Keep me posted...

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


:

SteveS,

Thanks... I have not solved this yet so any help would be greatly appreciated.

Jay

:

Hi Jay,

If you haven't solved this problem, post back. I've looked at your code and
modified it a little.

You are missing a "then" here:
VVV
If Len(strName) = 0
strName = "Like '*'"
Else


Also, you have named one of your fields "Name" which is a reserved word in
Access and it is not very descriptive; name of the month, a car, a city??
when you are looking thru code, you don't know what you are revering to.
Access can also confused - are you trying to rever to an object name or use
the name property?


.................... The name
field is an unbound text box on the report that combines the [Last Name],
[First Name], and [Middle Initial] into one text string separated by commas.

I don't understand how this can work. In the code, you are filtering the
recordset (query) on a field "[Name]"; on the report is an unbound control
that concatenates three fields ........

What is the record source for the report? Would you please post the SQL ?


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


:

I have a form that has two list fields (namelst and sourcelst) and one button
group (statusfra) for users to choose from in order to customize a report
(trkng_rpt). I want the user to be able to choose any number of names and or
any number of sources and whether the request is open (no date in the status
field of the report), closed (date in the status field) or both. The name
field is an unbound text box on the report that combines the [Last Name],
[First Name], and [Middle Initial] into one text string separated by commas.
The Source and Status are both taken from form controls. Also I would like
to be able to sort the query choices but have not got that far yet because I
cannot seem to get the first part to work right. Here is my code for the
filter buttons:

Private Sub filter_cmd_Click()
Dim varItem As Variant
Dim strName As String
Dim strSource As String
Dim strStatus As String
Dim strFilter as String
' Check if the report is open
If SysCmd(acSysCmdgetObjectState, acReport, "trkng_rpt") <>
acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Criteria String from namelst listbox
For Each varItem In Me.namelst.ItemsSelected
strName = strName & ",'" & Me.namelst.ItemData(varItem)
& "'"
Next varItem
If Len(strName) = 0
strName = "Like '*'"
Else
strName = Right(strName, Len(strName) -1)
strName = "IN(" & strName & ")"
End If
' Criteria String from sourcelst listbox
For Each varItem In Me.sourcelst.ItemsSelected
strSource = strSource & ",'" & Me.sourcelst.ItemData(varItem)
& "'"
Next varItem
If Len(strSource) = 0 Then
strSource = "Like '*'"
Else
strSource = Right(strSource, Len(strSource) -1)
strSource = "IN(" & strSource & ")"
End If
' Criteria String from statusfra frame
Select Case Me.statusfra.Value
Case 1
strStatus = "Is Null"
Case 2
strStatus = "Is Not Null"
Case 3
strStatus = "Like '*'"
End Select
' Filter string
strFilter = "[Name] " & strName &
" AND [Source] " & strSource & _
" AND [Status] " & strStatus
' Apply Filter
With Reports![trkng_rpt]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub remove_fltr_Click()
On Error Resume Next
' Remove Filter
Reports![trkng_rpt].FilterOn = False
End Sub


I can query on any number of sources so that works fine. I can also query
on either open or closed status but when I choose both it seems to remove the
filter because I see everything. The remove filter button works fine.
However, if I choose any name(s) from the list the report is blank. I have
been trying to work this for days now. I separated the name part of the code
from the source and status and made one for just names and one for both
source and status. Both worked fine. It is only when I combine the two that
the name never works. I am thinking that it is becase it is an unbound text
box but I would really like to use the lastname, firstname, mi format used in
the list box. Any and all help will be extremely appreciated. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Steve,

Me again. I have been busy with other things but want to get this kicked in
the rear. The table is one to many. One personnel will have many "tracking
records." The personnel_tbl.record is the key for the personnel table and
the tracking_tbl.record is the key for the tracking table. Not sure if this
is the best way to do it but I got it to work like that. All of the tracking
records show for each personnel record selected.

The SQL for the query is Name: [Last Name] & "," & [First Name] & ","
[Middle Initial]

I have tried to include the query when using the wizard to design the report
but I keep getting an error saying I have used a table or query that is based
on a table or query (something like that) and when I remove the query fields
from the list it allow me to continue with the report design. So I decided
use the name fields that the query is based on but only hide them (once I get
the report to work) and make a unbound text field that duplicates the query
results (combines the names). I am not sure if trying to have a form query
an unbound text field in a report is possible. Thanks for all of the time
and help you have given to me.

Jay

SteveS said:
Jay,

What is the SQL of the query 'NAME_qry'?

Is the relationship between Personnel_tbl and Tracking_tbl a one-to-many
1 ----> many
Personnel_tbl Tracking_tbl

Personnel_tbl.Record is the primary key in Personnel_tbl and
Tracking_tbl.Record is the foreign key to Personnel_tbl?


(BTW, 'Record' is also a reserved word in Access. 'Record_ID' or 'lngRecord'
is a better name for the field)

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


Jay said:
Steve,

the row source for "namelst" list box is a query:
SELECT NAME_qry.NAME from Name_qry ORDER BY [NAME];

I guess I should change name to maybe FullName.

The record source for the report is:
SELECT Personnel_tbl.[Last Name], Tracking_tbl.[DAC], Tracking_tbl.Source,
Tracking_tbl.ReqReceived, Tracking_tbl.EntACAT, Tracking_tbl.EntBy,
Tracking_tbl.InsSent, Tracking_tbl.SentBy, Tracking_tbl.FormRet,
Tracking_tbl.RetTo, Tracking_tbl.ReqFor, Tracking_tbl.ForBy,
Tracking_tbl.PermFor, Tracking_tbl.ForBy, Tracking_tbl.DtgRec,
Tracking_tbl.RecBy, Tracking_tbl.Status, Tracking_tbl.CloBy,
Tracking_tbl.ACATUp, Tracking_tbl.UpBy, Tracking_tbl.Comments,
Tracking_tbl.Record AS Tracking_tbl_Record, Personnel_tbl.[First Name],
Personnel_tbl.[Middle Initial], Personnel_tbl.Record AS Personnel_tbl_Record
FROM Personnel_tbl INNER JOIN Tracking_tbl ON
Personnel_tbl.ACATrec=Tracking_tbl.ACTrec;

Thanks again for the help

Jay

SteveS said:
Hi Jay,

What is the row source for the "namelst" list box on the search form?

What is the record source of the report?

Please post the SQL.....


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


:

Hey Steve,

Thanks so much for all of your help so far. The report works fine except
when I try to query a name. If I do not select a name, everything runs
great. When I select a name the report displays with only the commas from
the name field. I renamed the text box to FullName and changed the code as
appropriate. The unbound text box control is set to =[Last Name] & "," & " "
& [First Name] & "," & " " & [Middle Initial]. Is this the problem? Thanks
again for all the time you have spent on this.

Jay

:

Hi Jay,

Try the following code:

'*****************
Option Compare Database
Option Explicit

Private Sub filter_cmd_Click()
Dim varItem As Variant
Dim strName As String
Dim strSource As String
Dim strStatus As String
Dim strFilter As String

' Check if the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "trkng_rpt") <>
acObjStateOpen Then
MsgBox "You must open the report first."
' ' ---- You could open the report here ....---

Exit Sub
End If

' Remove Filter
With Reports![trkng_rpt]
.Filter = strFilter
.FilterOn = False
End With

' Criteria String from namelst listbox
For Each varItem In Me.namelst.ItemsSelected
strName = strName & ",'" & Me.namelst.ItemData(varItem) & "'"
Next varItem
If Len(Trim(strName)) > 0 Then
strName = Right(strName, Len(strName) - 1)
strName = "IN(" & strName & ")"
End If

' Criteria String from sourcelst listbox
For Each varItem In Me.sourcelst.ItemsSelected
strSource = strSource & ",'" & Me.sourcelst.ItemData(varItem) & "'"
Next varItem
If Len(Trim(strSource)) > 0 Then
strSource = Right(strSource, Len(strSource) - 1)
strSource = "IN(" & strSource & ")"
End If

' Criteria String from statusfra frame
Select Case Me.statusfra.Value
Case 1
strStatus = "Is Null"
Case 2
strStatus = "Is Not Null"
End Select

' create Filter string
If Len(Trim(strName)) > 0 Then
'**** "Name" is a reserved word in Access and
'**** shouldn't be used as a field name!!!!
' also, its not descriptive - "Name" of what??
' A city? A car?
' Maybe change it to FullName....or ClientName
strFilter = "[Name] " & strName & " AND "
End If

If Len(Trim(strSource)) > 0 Then
strFilter = strFilter & " [Source] " & strSource & " AND "
End If

If Len(Trim(strStatus)) > 0 Then
strFilter = strFilter & " [Status] " & strStatus & " And "
End If

If Len(Trim(strFilter)) > 0 Then
strFilter = Left(strFilter, Len(strFilter) - 5)
' Apply Filter
With Reports![trkng_rpt]
.Filter = strFilter
.FilterOn = True
End With
'-------------------------------------------
'check strFilter
MsgBox strFilter
'-------------------------------------------
End If


End Sub

Private Sub remove_fltr_Click()
On Error Resume Next
' Remove Filter
With Reports![trkng_rpt]
.Filter = strFilter
.FilterOn = False
End With

End Sub
'*******************

Keep me posted...

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


:

SteveS,

Thanks... I have not solved this yet so any help would be greatly appreciated.

Jay

:

Hi Jay,

If you haven't solved this problem, post back. I've looked at your code and
modified it a little.

You are missing a "then" here:
VVV
If Len(strName) = 0
strName = "Like '*'"
Else


Also, you have named one of your fields "Name" which is a reserved word in
Access and it is not very descriptive; name of the month, a car, a city??
when you are looking thru code, you don't know what you are revering to.
Access can also confused - are you trying to rever to an object name or use
the name property?


.................... The name
field is an unbound text box on the report that combines the [Last Name],
[First Name], and [Middle Initial] into one text string separated by commas.

I don't understand how this can work. In the code, you are filtering the
recordset (query) on a field "[Name]"; on the report is an unbound control
that concatenates three fields ........

What is the record source for the report? Would you please post the SQL ?


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


:

I have a form that has two list fields (namelst and sourcelst) and one button
group (statusfra) for users to choose from in order to customize a report
(trkng_rpt). I want the user to be able to choose any number of names and or
any number of sources and whether the request is open (no date in the status
field of the report), closed (date in the status field) or both. The name
field is an unbound text box on the report that combines the [Last Name],
[First Name], and [Middle Initial] into one text string separated by commas.
The Source and Status are both taken from form controls. Also I would like
to be able to sort the query choices but have not got that far yet because I
cannot seem to get the first part to work right. Here is my code for the
filter buttons:

Private Sub filter_cmd_Click()
Dim varItem As Variant
Dim strName As String
Dim strSource As String
Dim strStatus As String
Dim strFilter as String
' Check if the report is open
If SysCmd(acSysCmdgetObjectState, acReport, "trkng_rpt") <>
acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Criteria String from namelst listbox
For Each varItem In Me.namelst.ItemsSelected
strName = strName & ",'" & Me.namelst.ItemData(varItem)
& "'"
Next varItem
If Len(strName) = 0
strName = "Like '*'"
Else
strName = Right(strName, Len(strName) -1)
strName = "IN(" & strName & ")"
End If
' Criteria String from sourcelst listbox
For Each varItem In Me.sourcelst.ItemsSelected
strSource = strSource & ",'" & Me.sourcelst.ItemData(varItem)
& "'"
Next varItem
If Len(strSource) = 0 Then
strSource = "Like '*'"
Else
strSource = Right(strSource, Len(strSource) -1)
strSource = "IN(" & strSource & ")"
End If
' Criteria String from statusfra frame
Select Case Me.statusfra.Value
Case 1
strStatus = "Is Null"
Case 2
strStatus = "Is Not Null"
Case 3
strStatus = "Like '*'"
End Select
' Filter string
strFilter = "[Name] " & strName &
" AND [Source] " & strSource & _
" AND [Status] " & strStatus
Click to expand...
Click to expand...
Click to expand...
 
S

SteveS

Jay,

I've been trying to understand how things are linked. Here goes......

So you have a form (name unknown to me) with two list boxes (namelst and
sourcelst), an option group (statusfra), a button (filter_cmd) and a report
(trkng_rpt). There is an unbound text box on the form that is for ????

----
The record source for the list box "namelst" is:

"SELECT NAME_qry.NAME from Name_qry ORDER BY [NAME];" which is based on a query
(NAME_qry) which concatenates the last name, first name and middle initial from
table "Personnel_tbl".


I would have the query "NAME_qry" be

"SELECT [Last Name] & "," & [First Name] & ","
[Middle Initial] AS NAME from Name_qry ORDER BY [NAME];"

then set the list box record source to

"NAME_qry"


----
The record source for the list box "sourcelst" is:

(I don't know) **but seems to give the desired records**

----
The option frame works.

----
The report record source seems to give the desired records.

----
The code for the button (create the filter) works if no names have been
selected in the names list box.

If names are selected in the name list box and sources are selected in the
source list box and the Status selected is "IS NULL", then he filter generated
looks something like:

"[Name] IN (a list of name) AND [Source] IN (a list of sources) AND [Status] IS
NULL"

----

The report record source (reformatted for ease of reading) is:

SELECT
Tracking_tbl.[DAC],
Tracking_tbl.Source,
Tracking_tbl.ReqReceived,
Tracking_tbl.EntACAT,
Tracking_tbl.EntBy,
Tracking_tbl.InsSent,
Tracking_tbl.SentBy,
Tracking_tbl.FormRet,
Tracking_tbl.RetTo,
Tracking_tbl.ReqFor,
Tracking_tbl.ForBy,
Tracking_tbl.PermFor,
Tracking_tbl.ForBy,
Tracking_tbl.DtgRec,
Tracking_tbl.RecBy,
Tracking_tbl.Status,
Tracking_tbl.CloBy,
Tracking_tbl.ACATUp,
Tracking_tbl.UpBy,
Tracking_tbl.Comments,
Tracking_tbl.Record AS Tracking_tbl_Record,
Personnel_tbl.[Last Name],
Personnel_tbl.[First Name],
Personnel_tbl.[Middle Initial],
Personnel_tbl.Record AS Personnel_tbl_Record


FROM Personnel_tbl INNER JOIN Tracking_tbl ON
Personnel_tbl.ACATrec=Tracking_tbl.ACTrec;

----



Looking at the report record source and the filter string, I see the fields for
[Source] and [Status] in each.

But the filter also has a [Name] field, which is not found in the record
source. Not only do you not nave a field [Name] in the report record source,
you don't *any* field where the first name, last name and MI are concatenated!!





(Question: What are you going to do when there are 4 John O. Smiths??? Or
three James J. Browns?? Don't you have a unique Personnel_ID number or even an
autonumber for the table?)




To sort the report, you use "Sorting and Grouping" from the VIEW menu when in
the report design view, not the report query.


--Repeat warning.... RECORD and NAME are Access reserved words--


Does this make sense??


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

Me again. I have been busy with other things but want to get this kicked in
the rear. The table is one to many. One personnel will have many "tracking
records." The personnel_tbl.record is the key for the personnel table and
the tracking_tbl.record is the key for the tracking table. Not sure if this
is the best way to do it but I got it to work like that. All of the tracking
records show for each personnel record selected.

The SQL for the query is Name: [Last Name] & "," & [First Name] & ","
[Middle Initial]

I have tried to include the query when using the wizard to design the report
but I keep getting an error saying I have used a table or query that is based
on a table or query (something like that) and when I remove the query fields
from the list it allow me to continue with the report design. So I decided
use the name fields that the query is based on but only hide them (once I get
the report to work) and make a unbound text field that duplicates the query
results (combines the names). I am not sure if trying to have a form query
an unbound text field in a report is possible. Thanks for all of the time
and help you have given to me.

Jay

:

Jay,

What is the SQL of the query 'NAME_qry'?

Is the relationship between Personnel_tbl and Tracking_tbl a one-to-many
1 ----> many
Personnel_tbl Tracking_tbl

Personnel_tbl.Record is the primary key in Personnel_tbl and
Tracking_tbl.Record is the foreign key to Personnel_tbl?


(BTW, 'Record' is also a reserved word in Access. 'Record_ID' or 'lngRecord'
is a better name for the field)
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

Row Source in Form crashes Access 6
Filter by form with beginning date and enddate, where calender we 1
Create Report On the Fly 1
Simplify Code-Delete Query 3
Run-time error 430: Class does not support Automation..... 4
Access 2007, Passing parameters over to a query 4
Writing SQL in VBA 1
Filtering Unbound Subform from Main form with combo boxes HELP 2

Top