RE: Query form coding

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

SteveS,

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

Jay

"SteveS" wrote:

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


"Jay" wrote:

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
.