Reporting on fields on a form

[richard]

Hi

I have a form (frmFindAddressByJoborSite) which I have set to be a continous
form which only has one field (AssistFMJobNo) linked to a table
TblFindAddressByJobNo.
This form is for the user to find more than one address by Job No at a time
by entering multiple job numbers using the continuous form and then running
a report which uses the Job No entered to query tblNapsWork and filter as
appropriate.
However I am now trying to add a further field which is the AssistSiteRef,
so that the user can enter either the AssistFMJobNo or the AssistSiteRef and
the report can then pull data based on either of the above fields.
The problem I am having is this

1 I am relatively new and don't know if this is th best way to achieve my
end goal
2 As both AssistFMJobNo and AssistSiteRef are stored in the same table
then the relationship to tblNapsWork means that the query pulls up no records
unless I specify both numbers not just one
3 To get around the relationship problem I thought of entering the
AssistSiteRef into another table off the same form, but as the form is
continuous Access will not allow me to do this

If anyone has any thoughts or comments they would be appreciated

Richard

 

[Ken Sheridan]

Richard:

Rather than using a form and inserting rows into a auxiliary table why not
use two multi-select list boxes on a single unbound form, lstAssistFMJobNo
and lstAssistSiteRef say. You can then build a filter for the report in the
Click event procedure of a button on the form.

First set up the list boxes to list the AssistFMJobNo and AssistSiteRef
values from the tblNapsWork table in order and set the MultiSelect property
of each list box to Simple or Extended as desired; the former allows multiple
selection/deselection by simple clicking on each item one by one, the latter
allows multiple selection by cttl+click or shift+click in the standard
Windows way.

The button would loop through the ItemsSelected collection of each list box
and built a string expression which can then be used as the WhereCondition
setting of the OpenReport method:

Dim varItem As Variant
Dim strAssistFMJobNoList As String
Dim strAssistSiteRefList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.lstAssistFMJobNo

' loop through lstAssistFMJobNo list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strAssistFMJobNoList = strAssistFMJobNoList & "," &
ctrl.ItemData(varItem)
Next varItem

' remove leading comma
strAssistFMJobNoList = Mid(strAssistFMJobNoList, 2)

strCriteria = "AssistFMJobNo In(" & strAssistFMJobNoList & ")"
End If

' if any AssistFMJobNo items selected insert an
' OR operator in criteria string expression
If len(strCriteria) > 0 Then
strCriteria = strcriteria & " Or "
End If

Set ctrl = Me.lstAssistSiteRef

' loop through lstAssistSiteRef list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strAssistSiteRefList = strAssistSiteRefList & "," &
ctrl.ItemData(varItem)
Next varItem

' remove leading comma
strAssistSiteRefList = Mid(strAssistSiteRefList, 2)

strCriteria = strCriteria & "AssistSiteRef In(" &
strAssistSiteRefList & ")"
End If

' inform user if no items selected
If Len(strCriteria) = 0 Then
MsgBox "No items selected.", vbExclamation, "Invalid Operation"
Else
' open report
DoCmd.OpenReport "YourReportNameGoesHere", _
View:=acViewPreview, _
WhereCondition:=strCriteria
End If

The above assumes that both the AssistFMJobNo and AssistSiteRef columns in
the table are of number data types. If they were text data types then the
code would need amending to include quotes characters around the values:

strAssistFMJobNoList = strAssistFMJobNoList & ",""" & ctrl.ItemData(varItem)
& """"

and:

strAssistSiteRef = strAssistSiteRef & ",""" & ctrl.ItemData(varItem) & """"

Using list boxes like this the user can select as few or as many items from
either or both and open the report filtered by those selections. The report
would now be based on the tblNapsWork table, without any need to include the
auxiliary tblFindAddressByJobNo table in the underlying RecordSource.

You could of course have two buttons on the form, one to preview the report
(as above) and one to print it (with View:=acViewnormal), or you could
include a check box or option group in the form to select whether to preview
or print the report and amend the code so that it previews or prints
depending on the value of the check box or option group.

If you want a button to clear the selections in the list boxes put this in
its Click event procedure:

Dim n As Integer

For n = 0 To Me.lstAssistFMJobNo.ListCount - 1
Me.lstAssistFMJobNo.Selected(n) = False
Next n

For n = 0 To Me.lstAssistSiteRef.ListCount - 1
Me.lstAssistSiteRef.Selected(n) = False
Next n

Ken Sheridan
Stafford, England

 

[richard]

Ken

Thanks for the reply, however I am receiving and error message when I run
the code and it is saying

Run time error 3075

Extra ) in query expression '(jobnumber In(1267)Or)'.

Jobnumber is the name of the field in the tblNapsWork and 1267 was the ref I
selected in the list box

If you could reply it would be appreciated

Thanks

Richard

 

[Ken Sheridan]

I think I see the problem. The code as written allows for a user selecting
from the second list only or from both, but not from the first list only.
Here's an amended version:

Dim varItem As Variant
Dim strAssistFMJobNoList As String
Dim strAssistSiteRefList As String
Dim strCriteria1 As String, strCriteria2 As String
Dim strFilter As String
Dim ctrl As Control

Set ctrl = Me.lstAssistFMJobNo

' loop through lstAssistFMJobNo list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strAssistFMJobNoList = strAssistFMJobNoList & _
"," & ctrl.ItemData(varItem)
Next varItem

' remove leading comma
strAssistFMJobNoList = Mid(strAssistFMJobNoList, 2)

strCriteria1 = "JobNumber In(" & strAssistFMJobNoList & ")"
End If

Set ctrl = Me.lstAssistSiteRef

' loop through lstAssistSiteRef list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strAssistSiteRefList = strAssistSiteRefList & _
"," & ctrl.ItemData(varItem)
Next varItem

' remove leading comma
strAssistSiteRefList = Mid(strAssistSiteRefList, 2)

strCriteria2 = "AssistSiteRef In(" & _
strAssistSiteRefList & ")"
End If

' if items selected from both lists insert an
' OR operator in criteria string expression
If Len(strCriteria1) > 0 _
And Len(strCriteria2) > 0 Then
strFilter = strCriteria1 & " Or " & strCriteria2
Else
strFilter = strCriteria1 & strCriteria2
End If

' inform user if no items selected
If Len(strFilter) = 0 Then
MsgBox "No items selected.", vbExclamation, "Invalid Operation"
Else
' open report
DoCmd.OpenReport "YourReportNameGoesHere", _
View:=acViewPreview, _
WhereCondition:=strFilter
End If

I've changed the column name to JobNumber for the first list box but left it
as AssistSiteRef for the second. You might need to change it to whatever the
actual column name is.

Note the spaces around the operator " Or " when inserting it into the string
expression if selections are made from both lists. By the look of the error
message you might not have included them.

Ken Sheridan
Stafford, England