Ab-normalization at Experts Exchange - part 18

Question today: How to flag "records" filtered in a Access form?

The problem:
Base on what the user had filtered, I would like to update "updateStatus" field in the tbl_TrainingSched.

Because "employeGroup" field is not a field from tbl_TrainingSched, the update can't be done.
That can work, if I use "employeId", but I have more than one employeId filtered in the form.


The crappy "solution":

Me.Filter = strFilter
Me.FilterOn = True

'Build the employeeID list
Dim strID
With Me.RecordsetClone
Do Until .EOF
If InStr(strID, !employeId) = 0 Then
strID = strID & "," & !employeId
End If
.MoveNext
Loop
strID = Mid(strID, 2)
End With
'identification of the records selected by the user
strUpdate = "update tbl_TrainingSched set [updateStatus]= True where employeId In (" & strID & ")"
CurrentDb.Execute strUpdate

The flaw is the limited length of the query string. When too long the query will be truncated and the query will fail.

The real solution would be to use the query the form is based on (stripped to return just the employeId) and add the Me.Filter as the WHERE clause. Next use an update query like:
update tbl_TrainingSched set [updateStatus]= True where employeId In (SELECT employeId from qryForm WHERE " & Me.Filter & ")"

This "expert" shows off with 3 Microsoft MVP awards, doesn't Microsoft check for the "quality"?
Guess you know the answer

A fail safe solution and faster, as the crappy VBA loop to build a string will take some time....


Nic;o)
Still proud to be suspended from this "quality site" where wannabee experts rule and "quality" is just another word from the dictionary.
(The question can be found at: http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24762354.html)