Full Version: Filtering a form with VBA...
UtterAccess Forums > MicrosoftŪ Access > Access Forms
adaytay
Morning all! It's that time again when I need some help trying to figure something out. I'll apologise in advance as this might be a bit of a long post.
I'm busy writing a Sales system that our regional account managers will use to keep tabs on their contacts in the area, it displays the last contact and is searchable and filterable so they can specify just certain areas.
Ok... right, here's the thing. The form is question is driven by a query, and this query uses an UDF to identify the "region", based on the postcode - I have another table containing a list of postcodes, and a junction table listing each postcode and what region it relates to.
Now back to what I want to acheive - At present, the Account Manager can open the form and view their accounts. They can filter by contact type (eg we deal with bodyshops, taxi companies, solicitors, etc etc). They can also filter by contact "status", eg "existing" or "pending".
I would like to be able to additionally filter by Region. However, because (I think) the query is fed partially by a UDF, I keep getting the error "2001: You cancelled the Previous Operation".
How can I filter the query so that I can filter by RegionID? (It's a number between 1 and 12).
Here's some code:
This is what I'm using behind the filter button on the form:
CODE
Private Sub imgFilterRegion_Off_Click()
    Dim strFilter As String
    DoCmd.OpenForm "frmAccFilter_ByRegion", acNormal, , , , acDialog
    
    '
    
    strFilter = ParseArgString("Filter", strArgs)
    
    Me.Filter = "RegionID = " & CLng(strFilter)
    Me.FilterOn = True
'    Me.Requery
    Me.imgFilterRegion_On.Visible = True
    Me.imgFilterRegion_Off.Visible = False
    
End Sub

(It crashes on Me.FilterOn = True)
Here's the UDF behind the function:
CODE
Public Function GetRegion(Postcode As String) As Long
    'This function reads in the postcode from the field and returns the string number
On Error GoTo errFunction
    Dim strPCode As String
    
    'Work out where the space is...
    
    strPCode = Trim(Left(Postcode, InStr(1, Postcode, " ") - 1))
    GetRegion = tLookup("Region", "tblPostcodes", "PostCode='" & strPCode & "'")
exitFunction:
    Exit Function
errFunction:
    GetRegion = 0
    Resume exitFunction
    
End Function

...and here's the SQL behind the query.
----
SELECT tblContacts.ContactID, tblContacts.AccountManager, tblAccountMgrs.AccountManagerLogin, tblContactType.ContactTypeDescription, tblContacts.CompanyName, tblContacts.Town, tblContacts.Postcode, tmax("ApptDate","tblAppointments","ContactID=" & tblContacts!ContactID) AS LastContact, tblContactStatus.ContactStatus, "Region " & [RegionID] AS Region, tLookup("Region","tblPostcodes","PostCode='" & Trim(Left([Postcode],InStr(1,[Postcode]," ")-1)) & "'") AS RegionID
FROM (tblAccountMgrs RIGHT JOIN (tblContacts LEFT JOIN tblContactStatus ON tblContacts.ContactStatus=tblContactStatus.ContactStatusID) ON tblAccountMgrs.AccountManagerID=tblContacts.AccountManager) INNER JOIN tblContactType ON tblContacts.ContactType=tblContactType.ContactTypeID
WHERE (((tblAccountMgrs.AccountManagerLogin)=forms!frmContactsByAccMgr!cboStaffName));
---
Any ideas? It's probably something really silly... but just can't think what it is!
Cheers,
Ad
LPurvis
Hi
I'm familiar with the scenario - and it isn't necessarily consistent IMO. (I've known the occasional corruption of Memo fields mean that a UDF calc field fails in a filter where otherwise it wouldn't have.)
Though the UDF isn't necessarily the cause at hand in your case - I've known it be, and it's a likely suspect.
In theory, when filtering, Jet will only request the relevant rows. However if you change the source query then it has no choice ;-)
The other advantage of that is that you can query on the original function call - rather than upon the calc field it returns in the saved query.
Opresume the requirement that your form does the updating means you've avoided using Joins (and taking a DISTINCT result) or something similar?
You could use subqueries still to limit records without ending up at a read only result set.
(See this is why searching is more fun done read only - and a separate form displays the results ;-)
Might not be today - but if you post what you have, I'll get a look at it.
adaytay
Hi Leigh... no probs thanks I'll post something up.
d
adaytay
hi Leigh,
Well I had a huge post all ready to hit submit to and then lost it somehow... so here we go again.
I've upped a copy of the DB to my webspace and protected it as there's some sensitive information on there.
I've sent you a PM...
Download this file using the user and pass as per ur PM...
(NB if anyone else fancies a crack at this drop me a PM and I'll provide you with a username and password so you can download it lol)
Open frmContactsByAccMgr, and choose my name from the list...and try to filter (ie click on the green filter button) by anything *other* than region first. Then try to filter by region in the same way - see it falls over?
Any ideas? Thanks for taking the time... sad.gif
Ad
LPurvis
Only just back and had a chance to look at this now.

First thought was confirmed - you were getting the code error raised because some rows returned errors (e.g. there's no Postcode entered - hence a lookup failed).
You're then attempting to filter on a column which has some errors in rows (just like the afore mentioned corrupted Memo rows). This fails.
You can easily prevent *those* errors using a Nz to wrap those.
e.g. tLookup("Region","tblPostcodes","PostCode='" & Trim(Left(Nz([Postcode],""),InStr(1,Nz([Postcode]," ")," ")-1)) & "'")

However that still leaves form filtering problems.
(Even when not using filtering - and altering the recordsource).

However I also mentioned earlier on that just including the required table in the query source tables would generally be better than attempting a lookup in code (and has the benefit of being filterable).
(It's why I asked about updateability ;-)

Anyway - try the following SQL as your query instead.
Should be fine.

CODE
SELECT tblContacts.ContactID, tblContacts.AccountManager, tblAccountMgrs.AccountManagerLogin, tblContactType.ContactTypeDescription, tblContacts.CompanyName, tblContacts.Town, tblContacts.Postcode, tmax("ApptDate","tblAppointments","ContactID=" & tblContacts!ContactID) AS LastContact, tblContactStatus.ContactStatus, "Region " & tblPostcodes.Region AS Region, tblPostcodes.Region AS RegionID
FROM
(
(tblAccountMgrs RIGHT JOIN
   (tblContacts LEFT JOIN
     tblContactStatus
    ON tblContacts.ContactStatus = tblContactStatus.ContactStatusID)
  ON tblAccountMgrs.AccountManagerID = tblContacts.AccountManager)
INNER JOIN
tblContactType
ON tblContacts.ContactType = tblContactType.ContactTypeID
)
  LEFT JOIN tblPostcodes ON tblPostcodes.PostCode= Trim(Left(Nz(tblContacts.[Postcode],""),InStr(1,Nz(tblContacts.[Postcode]," ")," ")-1))
WHERE (((tblAccountMgrs.AccountManagerLogin)=[forms]![frmContactsByAccMgr]![cboStaffName]));
adaytay
Hi Leigh - awesome, thank you sad.gif
Just one problem (I'm sorry) - it runs the query perfectly however it then comes up with an error "Invalid procedure call".
This is just on displaying the query results - nothing else.
Any ideas? Really appreciate your assistance on this one sad.gif
Ad
adaytay
Seperate issue this time, so I've created a new thread here, please don't reply to this thread.
Thanks,
Ad
LPurvis
No probs - continued there.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.