Need help with Dynamic Report code

Garindan

Registered User.
Local time
Today, 20:09
Joined
May 25, 2004
Messages
250
Hi all, firstly i'm very new at coding so please forgive me if i sound unexperienced, i am :)

I have a report which lists customer details along with details of their last purchase. It is the first dynamic report i have done. It lists customer title, surname, house number, street, area, town/city, tel, then purchase date, season, products and cost.

I have a form which i'm using as a filter which has two multi-select list boxes, one listing areas from tblAreas and the other listing town/cities from tblTown_Cities. Then an apply filter button and a remove filter button. I followed the tutorial from this page - http://www.fontstuff.com/access/acctut19.htm#dialog3

Here's a pic -
frm9od.jpg


Except i tried to change it as i want it to display chosen Areas as well as chosen Towns, even if they are unrelated. But i don't know what statement to use? And at this stage none seem to work anyway :( . If i use AND it will need to have both Area AND Town for the record to show wont it? Which will not allow me to show different unrelated areas and towns. If i have OR it wont work either will it? Or is it because it is set to show all records if none are selected? I get confused with this sort of thing and don't know what to do, can someone help?

Also at the moment, with any areas or towns picked and the filter applied, it returns no records :confused: Any idea's? I have the report grouped by town and then area, would this affect it?

Here's the code:-
Code:
Private Sub Form_Load()
     DoCmd.OpenReport "rptCustomerDetails&LastPurchase", acViewPreview
End Sub

Private Sub cmdApplyFilter_Click()
Dim varItem As Variant
    Dim strArea As String
    Dim strTown As String
    Dim strFilter As String
' Build criteria string from lstArea listbox
    For Each varItem In Me.lstArea.ItemsSelected
        strArea = strArea & ",'" & Me.lstArea.ItemData(varItem) _
        & "'"
    Next varItem
    If Len(strArea) = 0 Then
        strArea = "Like '*'"
    Else
        strArea = Right(strArea, Len(strArea) - 1)
        strArea = "IN(" & strArea & ")"
    End If
' Build criteria string from lstTown listbox
    For Each varItem In Me.lstTown.ItemsSelected
        strTown = strTown & ",'" & Me.lstTown.ItemData(varItem) _
        & "'"
    Next varItem
    If Len(strTown) = 0 Then
        strTown = "Like '*'"
    Else
        strTown = Right(strTown, Len(strTown) - 1)
        strTown = "IN(" & strTown & ")"
    End If
' Build filter string
    strFilter = "[Area] " & strArea & _
                " AND [Town_City] " & strTown
' Apply the filter and switch it on
    With Reports![rptCustomerDetails&LastPurchase]
        .Filter = strFilter
        .FilterOn = True
    End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
     Reports![rptCustomerDetails&LastPurchase].FilterOn = False
End Sub

Edit: With the OR statement the filter returns all results, as it must be for example, Area OR everything. So it's the AND statement which is causing it to return no results, even when choosing an area which belongs to a town. Is this because the area's and towns are unrelated? But when choosing an area, and a town with the OR statement no records are returned. I'm confused :confused: :( I think the grouping doesn't affect it at all as i have tried without any grouping, but it's something to do with the relationship between area and town? Yes/no?
 
Last edited:
No there must be something wrong with my code. I've tried it with just one multi-select list on tblTown_Cities, with a simple report on JUST tblTown_Cities, and if i select any towns at all it shows no records :( Pllleeeeaassseeee can someone help?? Many thanks in advance.
 
Or is it something to do with the fields used in the query/report?

I have the Areas multi-select list sourced on tblAreas, which is

AreaID - PK, Autonumber
Area - Text

and the Town list on tblTown_Cities, as follows

Town_CityID - PK, Autonumber
Town_City - Text
CountyID - Number, but not used in the report or query.

I remember when i was trying stuff out it came up with an error at one point, unrelated and just cos i was messing with the code, but it showed the string as using numbers, so it is obviously using the AreaID and Town_CityID fields but thats normal right? And how it should be?

I'm getting quite confused :confused: Can nobody help? I appreciate everything you all do in this forum and it's a great place, but it seems everyone is busy this time? :(

Many thanks again.
 
I want to be able to choose any area's or towns and have it bring up the records that contain either so i guess i need to change code to do that but i don't know how. Or perhaps someone has some quite different code i can use? I don't know, i just tried to follow a tutorial as stated earlier :confused:

I'm going to move this to the report forum if thats alright, i think it might be better suited and i might get more replies - http://www.access-programmers.co.uk/forums/showthread.php?t=102818
 
Last edited:

Users who are viewing this thread

Back
Top Bottom