I refer to the wonderful article called Q262099 – ACC2000 Filter-by-Form Example by by Getz, Litwin, and Reddick (Sybex),’ Copyright 1994 – 1997. All rights reserved, written for the table tblclients. It works excellent when the ClientD is a unique 5 letters word. However my ClientID is an unique auto number, not a Text, and I cannot use this fine function.Can anybody help me to modify the function,?
=========
Option Compare Database ‘Use database order for string comparisons
Option Explicit
‘ Adapted from Microsoft Access 2 Developer’s Handbook
‘ by Getz, Litwin, and Reddick (Sybex)
‘ Copyright 1994 – 1997. All rights reserved.
‘ REQUIRES A REFERENCE TO Microsoft DAO 3.6.
Const adhcSeparator = “;”
Const adhcAssignment = “=”
Function adhDeleteItem(ByVal varInfo As Variant, ByVal varItemName As Variant) As Variant
‘ Delete a specific item name and its value.
‘
‘ Return the new info string, with the requested
‘ item and its value removed.
‘
‘ Adapted from Microsoft Access 2 Developer’s Handbook
‘ by Getz, Litwin, and Reddick (Sybex)
‘ Copyright 1994 – 1997. All rights reserved.
‘
‘ In:
‘ varInfo: string of items, delimited with adhcSeparator
‘ varItemName: name of item to delete from varInfo
‘
‘ Out:
‘ Return value: varInfo, with the item and its value deleted.
‘
‘ Example:
‘ If varInfo is “x=5;y=7;z=12;”, calling
‘ adhDeleteItem(varInfo, “y”)
‘ will return “x=5;z=12;”
Dim lngEndPos As Long
Dim strLeftPart As String
Dim lngPos As Long
Dim intRet As Integer
On Error GoTo adhDeleteItem_Err
‘ Look for the tag that you’ve asked to delete.
lngPos = adhFindItemPos(varInfo, varItemName)
If lngPos > 0 Then
‘ Find the end of the requested tag value. This’ll be
‘ 0 if there’s no more items after this one.
lngEndPos = InStr(lngPos + 1, varInfo, adhcSeparator)
‘ Gather up the part of the tag string to the left of the
‘ requested tag. This can’t fail, since you wouldn’t be
‘ here if lngPos wasn’t greater than 0.
strLeftPart = Left$(varInfo, lngPos – 1)
‘ If there’s stuff to the right of the requested item, tack it
‘ onto the end of the info string.
If lngEndPos > 0 Then
varInfo = strLeftPart & Mid$(varInfo, lngEndPos + 1)
End If
End If
adhDeleteItem = varInfo
adhDeleteItem_Exit:
Exit Function
adhDeleteItem_Err:
Select Case Err.Number
Case Else
adhErrorHandler Err.Number, Err.Description, “adhDeleteItem”
End Select
Resume adhDeleteItem_Exit
End Function
Sub adhErrorHandler(intErr As Integer, strError As String, strProc As String)
‘ Adapted from Microsoft Access 2 Developer’s Handbook
‘ by Getz, Litwin, and Reddick (Sybex)
‘ Copyright 1994 – 1997. All rights reserved.
MsgBox “Error: ” & strError & ” (” & intErr & “)”, _
vbInformation, strProc
End Sub
Private Function adhFindItemPos(varInfo As Variant, varItemName As Variant) As Long
‘ Adapted from Microsoft Access 2 Developer’s Handbook
‘ by Getz, Litwin, and Reddick (Sybex)
‘ Copyright 1994 – 1997. All rights reserved.
Dim lngPos As Long
Dim intRet As Integer
On Error GoTo adhFindItemPos_Err
lngPos = 0
‘ Don’t even bother if the info string or the item name is null
‘ Use a little trick here to trap both the Null case and the
‘ zero-length string case: if Len(yourString & vbNullString) = 0, then
‘ is either Null or a ZLS.
If Len(varInfo & vbNullString) > 0 And Len(varItemName & vbNullString) > 0 Then
‘ Stick a adhcSeparator on the front, and then look for
‘ “;varItemName=”
‘ If it’s there, it’ll find it on the first pass. No loops!
‘ This code must be fast, since it gets called A LOT!
lngPos = InStr(adhcSeparator & varInfo, adhcSeparator & varItemName & adhcAssignment)
End If
adhFindItemPos = lngPos
adhFindItemPos_Exit:
Exit Function
adhFindItemPos_Err:
Select Case Err.Number
Case Else
adhErrorHandler Err.Number, Err.Description, “adhFindItemPos”
End Select
Resume adhFindItemPos_Exit
End Function
Function adhGetItem(ByVal varInfo As Variant, ByVal varItemName As Variant) As Variant
‘ Retrieve a specific item value.
‘ This function will either return the requested
‘ value or Null if the item name wasn’t found.
‘ Adapted from Microsoft Access 2 Developer’s Handbook
‘ by Getz, Litwin, and Reddick (Sybex)
‘ Copyright 1994 – 1997. All rights reserved.
‘
‘ In:
‘ varInfo: string of items, delimited with adhcSeparator
‘ varItemName: name of item to retrieve from varInfo
‘
‘ Out:
‘ Return value: the value associated with varItemName.
‘
‘ Example:
‘ If varInfo is “x=5;y=7;z=12;”, calling
‘ adhGetItem(varInfo, “y”)
‘ will return 7
‘
Dim lngPos As Long
Dim lngEndPos As Long
Dim varResult As Variant
Dim intRet As Integer
On Error GoTo adhGetItem_Err
varResult = Null
lngPos = adhFindItemPos(varInfo, varItemName)
‘ If the item was found, keep a’goin’.
If lngPos > 0 Then
‘ Move lngPos to the start of the item value, and
‘ lngEndPos to the next adhcSeparator, if there is one.
lngPos = lngPos + Len(varItemName) + Len(adhcAssignment)
lngEndPos = InStr(lngPos, varInfo, adhcSeparator)
‘ Interpret a zero-length property as Null
If lngEndPos = lngPos Then
varResult = Null
Else
‘ If there wasn’t a adhcSeparator, just use the rest
‘ of the info string. Otherwise, take the part between
‘ lngPos and lngEndPos.
If lngEndPos = 0 Then
varResult = Mid$(varInfo, lngPos)
Else
varResult = Mid$(varInfo, lngPos, lngEndPos – lngPos)
End If
End If
End If
adhGetItem = varResult
adhGetItem_Exit:
Exit Function
adhGetItem_Err:
Select Case Err.Number
Case Else
adhErrorHandler Err.Number, Err.Description, “adhGetItem”
End Select
Resume adhGetItem_Exit
End Function
Function adhPutItem(ByVal varInfo As Variant, ByVal varItemName As Variant, ByVal varItemValue As Variant) As Variant
‘ Append the value
‘
‘ [varItemName]=[varItemValue];
‘
‘ onto the varInfo value passed in. If the
‘ item name already exists, it is deleted first and then the new
‘ value is appended to the end.
‘ Adapted from Microsoft Access 2 Developer’s Handbook
‘ by Getz, Litwin, and Reddick (Sybex)
‘ Copyright 1994 – 1997. All rights reserved.
‘
‘ In:
‘ varInfo: string of items, delimited with adhcSeparator
‘ varItemName: name of item to place into varInfo
‘ varItemValue: value of item
‘ Out:
‘ Return value: modified value of varInfo.
‘
‘ Example:
‘ If varInfo is “x=5;y=7;z=12;”, calling
‘ adhPutItem(varInfo, “q”, “15”)
‘ will return “x=5;y=7;z=12;q=15;”
‘
On Error GoTo adhPutItem_Err
Dim intRet As Integer
‘ If there’s already a value in the info string for the item
‘ you’re trying to replace, just REMOVE it.
varInfo = adhDeleteItem(varInfo, varItemName)
‘ By passing in a null or ZLS for the strItemValue, you effectively
‘ delete the tag.
If Len(varItemValue & vbNullString) > 0 Then
varInfo = varInfo & varItemName & adhcAssignment & varItemValue & adhcSeparator
End If
adhPutItem = varInfo
adhPutItem_Exit:
Exit Function
adhPutItem_Err:
Select Case Err.Number
Case Else
adhErrorHandler Err.Number, Err.Description, “adhPutItem”
End Select
Resume adhPutItem_Exit
End Function
Function adhCtlTagDeleteItem(ctl As Control, ByVal varItemName As Variant)
‘ Delete a specific tag name and its value from the
‘ requested control’s .Tag property.
‘
‘ Adapted from Microsoft Access 2 Developer’s Handbook
‘ by Getz, Litwin, and Reddick (Sybex)
‘ Copyright 1994 – 1997. All rights reserved.
‘
‘ In:
‘ ctl: reference to a control
‘ varItemName: name of item to delete
‘ Out:
‘ Return Value: the control’s Tag property, with the item deleted.
‘ See adhDeleteItem for details.
ctl.Tag = adhDeleteItem(ctl.Tag, varItemName)
adhCtlTagDeleteItem = ctl.Tag
End Function
Function adhCtlTagGetItem(ctl As Control, ByVal varItemName As Variant) As Variant
‘ Retrieve a specific tag name from the requested control’s
‘ .Tag property. This function will either return the requested
‘ value or Null if the tag name wasn’t found.
‘ Adapted from Microsoft Access 2 Developer’s Handbook
‘ by Getz, Litwin, and Reddick (Sybex)
‘ Copyright 1994 – 1997. All rights reserved.
‘
adhCtlTagGetItem = adhGetItem(ctl.Tag, varItemName)
End Function
Function adhCtlTagPutItem(ctl As Control, ByVal varItemName As Variant, ByVal varItemValue As Variant) As Integer
‘ Append the value
‘
‘ [varItemName]=[varItemValue]
‘
‘ onto the .Tag property for the requested control.
‘ See adhPutItem() for more information.
‘ Adapted from Microsoft Access 2 Developer’s Handbook
‘ by Getz, Litwin, and Reddick (Sybex)
‘ Copyright 1994 – 1997. All rights reserved.
Const adhcErrTagTooLong = 2176
Dim varOldTag As Variant
On Error GoTo CtlTagPutItemErr
‘ Assign the new tag value and then return True.
varOldTag = ctl.Tag
ctl.Tag = adhPutItem(varOldTag, varItemName, varItemValue)
adhCtlTagPutItem = True
ctlTagPutItemExit:
Exit Function
CtlTagPutItemErr:
If Err.Number = adhcErrTagTooLong Then
‘ Make sure ctl.Tag hasn’t changed. Then return False.
ctl.Tag = varOldTag
Else
adhErrorHandler Err.Description, Err.Number, “adhCtlTagPutItem”
End If
adhCtlTagPutItem = False
Resume ctlTagPutItemExit
End Function
Option Compare Database ‘Use database order for string comparisons
Option Explicit
‘ REQUIRES A REFERENCE TO Microsoft DAO 3.6.
Const QUOTE = “”””
‘ This string is the text that gets appended
‘ to the chosen form name, once it’s become a
‘ QBF form. It’s completely arbitrary, and can be
‘ anything you like.
Public Const conQBFSuffix = “_QBF”
Private Function BuildSQLString( _
ByVal strFieldName As String, _
ByVal varFieldValue As Variant, _
ByVal intFieldType As Integer)
‘ Build string that can be used as part of an
‘ SQL WHERE clause. This function looks at
‘ the field type for the specified table field,
‘ and constructs the expression accordingly.
Dim strTemp As String
On Error GoTo HandleErrors
If Left$(strFieldName, 1) “[” Then
strTemp = “[” & strFieldName & “]”
End If
‘ If the first part of the value indicates that it’s
‘ to be left as is, leave it alone. Otherwise,
‘ munge the value as necessary.
If IsOperator(varFieldValue) Then
strTemp = strTemp & ” ” & varFieldValue
Else
‘ One could use the BuildCriteria method here,
‘ but it’s not as flexible as I’d like to
‘ be. So, this code does all the work manually.
Select Case intFieldType
Case dbBoolean
‘ Convert to TRUE/FALSE
strTemp = strTemp & ” = ” & CInt(varFieldValue)
Case dbText, dbMemo
‘ Assume we’re looking for anything that STARTS with the text we got.
‘ This is probably a LOT slower. If you want direct matches
‘ instead, use the commented-out line.
‘ strTemp = strTemp & ” = ” & QUOTE & varFieldValue & QUOTE
strTemp = strTemp & ” LIKE ” & QUOTE & varFieldValue & “*” & QUOTE
Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle, dbDouble
‘ Convert to straight numeric representation.
strTemp = strTemp & ” = ” & varFieldValue
Case dbDate
‘ Convert to #date# format.
strTemp = strTemp & ” = ” & “#” & varFieldValue & “#”
Case Else
‘ This function really can’t handle any of the other data types. You can
‘ add more types, if you care to handle them.
strTemp = vbNullString
End Select
End If
BuildSQLString = strTemp
ExitHere:
Exit Function
HandleErrors:
MsgBox “Error: ” & Err.Description & ” (” & Err.Number & “)”, vbExclamation, “BuildSQLString”
strTemp = vbNullString
Resume ExitHere
End Function
Private Function BuildWHEREClause(frm As Form) As String
‘ Build the full WHERE clause based on fields
‘ on the passed-in form. This function attempts
‘ to look at all controls that have the correct
‘ settings in the Tag properties.
Dim strLocalSQL As String
Dim strTemp As String
Dim varDataType As Integer
Dim varControlSource As Variant
Dim ctl As Control
Const conAND As String = ” AND ”
For Each ctl In frm.Controls
‘ Get the original control source.
varControlSource = adhCtlTagGetItem(ctl, “qbfField”)
If Not IsNull(varControlSource) Then
‘ If the value of the control isn’t null…
If Not IsNull(ctl) Then
‘ then get the value.
varDataType = adhCtlTagGetItem(ctl, “qbfType”)
If Not IsNull(varDataType) Then
strTemp = “(” & BuildSQLString(varControlSource, ctl, varDataType) & “)”
strLocalSQL = strLocalSQL & conAND & strTemp
End If
End If
End If
Next ctl
‘ Trim off the leading ” AND ”
If Len(strLocalSQL) > 0 Then
BuildWHEREClause = “(” & Mid$(strLocalSQL, Len(conAND) + 1) & “)”
End If
End Function
Public Function DoQBF(ByVal strFormName As String, _
Optional blnCloseIt As Boolean = True) As String
‘ Load the specified form as a QBF form. If
‘ the form is still loaded when control returns
‘ to this function, then it will attempt to
‘ build an SQL WHERE clause describing the
‘ values in the fields. DoQBF() will return
‘ either that SQL string or an empty string,
‘ depending on what the user chose to do and
‘ whether or not any fields were filled in.
‘ In:
‘ strFormName: Name of the form to load
‘ blnCloseIt: Close the form, if the user didn’t?
‘ Out:
‘ Return Value: The calculated SQL string.
Dim strSQL As String
DoCmd.OpenForm strFormName, WindowMode:=acDialog
‘ You won’t get here until user hides or closes the form.
‘ If the user closed the form, there’s nothing
‘ to be done. Otherwise, build up the SQL WHERE
‘ clause. Once you’re done, if the caller requested
‘ the QBF form to be closed, close it now.
If IsFormLoaded(strFormName) Then
strSQL = BuildWHEREClause(Forms(strFormName))
If blnCloseIt Then
DoCmd.Close acForm, strFormName
End If
End If
DoQBF = strSQL
End Function
Public Function QBFDoClose()
‘ This is a function so it can be called easily
‘ from the Properties window directly.
‘ Close the current form.
On Error Resume Next
DoCmd.Close
End Function
Public Function QBFDoHide(frm As Form)
‘ This is a function so it can be called easily
‘ from the Properties window directly.
Dim strSQL As String
Dim strParent As String
‘Get the name of the Parent form
strParent = adhGetItem(frm.Tag, “Parent”) & vbNullString
‘Create the appropriate WHERE clause based on the fields with data in them.
strSQL = DoQBF(frm.Name, False)
If Len(strParent) > 0 Then
‘Open the Parent form filtered with the Where clause generated above
DoCmd.OpenForm FormName:=strParent, View:=acNormal, WhereCondition:=strSQL
End If
‘Make this QBF form invisible.
frm.Visible = False
End Function
Private Function IsFormLoaded(strName As String) As Boolean
‘ Return a logical value indicating whether a
‘ given formname is loaded or not.
‘ You could use the IsLoaded property of a member
‘ of the AllForms collection to get this information, but
‘ that method raises an error if you ask about a
‘ for that doesn’t exist. The obscure SysCmd function
‘ does not.
On Error Resume Next
IsFormLoaded = (SysCmd(acSysCmdGetObjectState, acForm, strName) 0)
End Function
Private Function IsOperator(varValue As Variant) As Boolean
‘ Return a logical value indicating whether a
‘ value passed in is an operator or not.
‘ This is NOT infallible, and may need correcting.
Dim strTemp As String
strTemp = Trim$(UCase(varValue))
IsOperator = False
‘ Check first character for , or =
If InStr(1, “=”, Left$(strTemp, 1)) > 0 Then
IsOperator = True
‘ Check for IN (x,y,z)
ElseIf ((Left$(strTemp, 4) = “IN (“) And (Right$(strTemp, 1) = “)”)) Then
IsOperator = True
‘ Check for BETWEEN … AND …
ElseIf ((Left$(strTemp, 8) = “BETWEEN “) And (InStr(1, strTemp, ” AND “) > 0)) Then
IsOperator = True
‘ Check for NOT xxx
ElseIf (Left$(strTemp, 4) = “NOT “) Then
IsOperator = True
‘ Check for LIKE xxx
ElseIf (Left$(strTemp, 5) = “LIKE “) Then
IsOperator = True
End If
End Function
