G
Gary.
I have a program that is running fine on a Access 2003 pc but will not
work on a Access 2007 pc
When I press the command button nothing happens
The only libabry change on the access 2007 pc it to Microsoft Access
12.0 Object libaray the 2003 pc is using Microsoft Access 12.0 Opject
library
Here is the code
Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you
can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one
onwards; _
End date only = all dates up to (and including
this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria
string to append to.
Dim strError As String
Dim FilterCount As Long
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates in a JET query string.
strError = """0ut of Range"""
' This area is used to set the W D and H to the proper record
'Set the Width setup field
If [txtEnterWidth] = 12 Then
[txtLookupWidth] = 12
ElseIf [txtEnterWidth] > 12 And [txtEnterWidth] <= 15 Then
[txtLookupWidth] = 15
ElseIf [txtEnterWidth] > 15 And [txtEnterWidth] <= 18 Then
[txtLookupWidth] = 18
ElseIf [txtEnterWidth] > 18 And [txtEnterWidth] <= 21 Then
[txtLookupWidth] = 21
ElseIf [txtEnterWidth] > 21 And [txtEnterWidth] <= 24 Then
[txtLookupWidth] = 24
ElseIf [txtEnterWidth] > 24 And [txtEnterWidth] <= 27 Then
[txtLookupWidth] = 27
ElseIf [txtEnterWidth] > 27 And [txtEnterWidth] <= 30 Then
[txtLookupWidth] = 30
ElseIf [txtEnterWidth] > 30 And [txtEnterWidth] <= 33 Then
[txtLookupWidth] = 33
ElseIf [txtEnterWidth] > 33 And [txtEnterWidth] <= 36 Then
[txtLookupWidth] = 36
Else: [txtLookupWidth] = txtEnterWidth
End If
'Set the Height Lookup Field
If [txtEnterHeight] = 24 Then
[txtLookupHeight] = 24
ElseIf [txtEnterHeight] > 24 And [txtEnterHeight] <= 27 Then
[txtLookupHeight] = 27
ElseIf [txtEnterHeight] > 27 And [txtEnterHeight] <= 30 Then
[txtLookupHeight] = 30
ElseIf [txtEnterHeight] > 30 And [txtEnterHeight] <= 34 Then
[txtLookupHeight] = 34
ElseIf [txtEnterHeight] > 34 And [txtEnterHeight] <= 36 Then
[txtLookupHeight] = 36
ElseIf [txtEnterHeight] > 72 And [txtEnterHeight] <= 75 Then
[txtLookupHeight] = 75
ElseIf [txtEnterHeight] = 72 Then
[txtLookupHeight] = 72
ElseIf [txtEnterHeight] > 75 And [txtEnterHeight] <= 78 Then
[txtLookupHeight] = 78
ElseIf [txtEnterHeight] > 78 And [txtEnterHeight] <= 81 Then
[txtLookupHeight] = 81
ElseIf [txtEnterHeight] > 81 And [txtEnterHeight] <= 84 Then
[txtLookupHeight] = 84
Else: [txtLookupHeight] = txtEnterHeight
End If
'Debug.Print txtLookupHeight
'Set the Depth Lookup Field
If [txtEnterDepth] = 12 Then
[txtLookupDepth] = 12
ElseIf [txtEnterDepth] > 12 And [txtEnterDepth] <= 24 Then
[txtLookupDepth] = 24
ElseIf [txtEnterDepth] > 24 And [txtEnterDepth] <= 30 Then
[txtLookupDepth] = 30
'Else: [txtLookupDepth] = txtEnterDepth
Else: [txtLookupDepth] = txtEnterDepth
End If
If [txtLookupDepth] < 12 Then
' ElseIf [txtLookupDepth] > 30 Then
' MsgBox "Unit Depth is out of range"
End If
'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.
'Another text field example. Use Like to find anywhere in the field.
' If Not IsNull(Me.txtFilterMainName) Then
' strWhere = strWhere & "([MainName] Like ""*" &
Me.txtFilterMainName & "*"") AND "
' End If
'Number field example. Do not add the extra quotes.
If Not IsNull(Me.cboCabinet) Then
strWhere = strWhere & "([ProductName] = """ & Me.cboCabinet &
""") AND "
End If
If Not IsNull(Me.cboMaterial) Then
strWhere = strWhere & "([ProductID] = " & Me.cboMaterial & ")
AND "
End If
'Number field example. Do not add the extra quotes.
If Not IsNull(Me.txtLookupWidth) Then
strWhere = strWhere & "([UnitWidth] = " & (Me.txtLookupWidth) &
") AND "
End If
'Number field example. Do not add the extra quotes.
If Not IsNull(Me.txtLookupHeight) Then
strWhere = strWhere & "([UnitHeight] = " & (Me.txtLookupHeight)
& ") AND "
End If
'Number field example. Do not add the extra quotes.
If Not IsNull(Me.txtLookupDepth) Then
strWhere = strWhere & "([UnitDepth] = " & (Me.txtLookupDepth) &
") AND "
' Else: MsgBox "Data is out of range"
' Cancel = True
End If
'If MsgBox "Data is out of range"
'Cancel = True
'End If
'Date field example. Use the format string to add the # delimiters
and get the right international format.
' If Not IsNull(Me.txtStartDate) Then
' strWhere = strWhere & "([EnteredOn] >= " & Format
(Me.txtStartDate, conJetDate) & ") AND "
' End If
'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove
the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line.
Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere
'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
'DoCmd.ApplyFilter
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "Your Data is out of range"
End If
'FilterCount = Me.RecordsetClone.ReordCount
If FilterCount > 1 Then
MsgBox "You have selected multi items"
End If
'Debug.Print FilterCount
End If
End Sub
work on a Access 2007 pc
When I press the command button nothing happens
The only libabry change on the access 2007 pc it to Microsoft Access
12.0 Object libaray the 2003 pc is using Microsoft Access 12.0 Opject
library
Here is the code
Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you
can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one
onwards; _
End date only = all dates up to (and including
this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria
string to append to.
Dim strError As String
Dim FilterCount As Long
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates in a JET query string.
strError = """0ut of Range"""
' This area is used to set the W D and H to the proper record
'Set the Width setup field
If [txtEnterWidth] = 12 Then
[txtLookupWidth] = 12
ElseIf [txtEnterWidth] > 12 And [txtEnterWidth] <= 15 Then
[txtLookupWidth] = 15
ElseIf [txtEnterWidth] > 15 And [txtEnterWidth] <= 18 Then
[txtLookupWidth] = 18
ElseIf [txtEnterWidth] > 18 And [txtEnterWidth] <= 21 Then
[txtLookupWidth] = 21
ElseIf [txtEnterWidth] > 21 And [txtEnterWidth] <= 24 Then
[txtLookupWidth] = 24
ElseIf [txtEnterWidth] > 24 And [txtEnterWidth] <= 27 Then
[txtLookupWidth] = 27
ElseIf [txtEnterWidth] > 27 And [txtEnterWidth] <= 30 Then
[txtLookupWidth] = 30
ElseIf [txtEnterWidth] > 30 And [txtEnterWidth] <= 33 Then
[txtLookupWidth] = 33
ElseIf [txtEnterWidth] > 33 And [txtEnterWidth] <= 36 Then
[txtLookupWidth] = 36
Else: [txtLookupWidth] = txtEnterWidth
End If
'Set the Height Lookup Field
If [txtEnterHeight] = 24 Then
[txtLookupHeight] = 24
ElseIf [txtEnterHeight] > 24 And [txtEnterHeight] <= 27 Then
[txtLookupHeight] = 27
ElseIf [txtEnterHeight] > 27 And [txtEnterHeight] <= 30 Then
[txtLookupHeight] = 30
ElseIf [txtEnterHeight] > 30 And [txtEnterHeight] <= 34 Then
[txtLookupHeight] = 34
ElseIf [txtEnterHeight] > 34 And [txtEnterHeight] <= 36 Then
[txtLookupHeight] = 36
ElseIf [txtEnterHeight] > 72 And [txtEnterHeight] <= 75 Then
[txtLookupHeight] = 75
ElseIf [txtEnterHeight] = 72 Then
[txtLookupHeight] = 72
ElseIf [txtEnterHeight] > 75 And [txtEnterHeight] <= 78 Then
[txtLookupHeight] = 78
ElseIf [txtEnterHeight] > 78 And [txtEnterHeight] <= 81 Then
[txtLookupHeight] = 81
ElseIf [txtEnterHeight] > 81 And [txtEnterHeight] <= 84 Then
[txtLookupHeight] = 84
Else: [txtLookupHeight] = txtEnterHeight
End If
'Debug.Print txtLookupHeight
'Set the Depth Lookup Field
If [txtEnterDepth] = 12 Then
[txtLookupDepth] = 12
ElseIf [txtEnterDepth] > 12 And [txtEnterDepth] <= 24 Then
[txtLookupDepth] = 24
ElseIf [txtEnterDepth] > 24 And [txtEnterDepth] <= 30 Then
[txtLookupDepth] = 30
'Else: [txtLookupDepth] = txtEnterDepth
Else: [txtLookupDepth] = txtEnterDepth
End If
If [txtLookupDepth] < 12 Then
' ElseIf [txtLookupDepth] > 30 Then
' MsgBox "Unit Depth is out of range"
End If
'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.
'Another text field example. Use Like to find anywhere in the field.
' If Not IsNull(Me.txtFilterMainName) Then
' strWhere = strWhere & "([MainName] Like ""*" &
Me.txtFilterMainName & "*"") AND "
' End If
'Number field example. Do not add the extra quotes.
If Not IsNull(Me.cboCabinet) Then
strWhere = strWhere & "([ProductName] = """ & Me.cboCabinet &
""") AND "
End If
If Not IsNull(Me.cboMaterial) Then
strWhere = strWhere & "([ProductID] = " & Me.cboMaterial & ")
AND "
End If
'Number field example. Do not add the extra quotes.
If Not IsNull(Me.txtLookupWidth) Then
strWhere = strWhere & "([UnitWidth] = " & (Me.txtLookupWidth) &
") AND "
End If
'Number field example. Do not add the extra quotes.
If Not IsNull(Me.txtLookupHeight) Then
strWhere = strWhere & "([UnitHeight] = " & (Me.txtLookupHeight)
& ") AND "
End If
'Number field example. Do not add the extra quotes.
If Not IsNull(Me.txtLookupDepth) Then
strWhere = strWhere & "([UnitDepth] = " & (Me.txtLookupDepth) &
") AND "
' Else: MsgBox "Data is out of range"
' Cancel = True
End If
'If MsgBox "Data is out of range"
'Cancel = True
'End If
'Date field example. Use the format string to add the # delimiters
and get the right international format.
' If Not IsNull(Me.txtStartDate) Then
' strWhere = strWhere & "([EnteredOn] >= " & Format
(Me.txtStartDate, conJetDate) & ") AND "
' End If
'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove
the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line.
Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere
'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
'DoCmd.ApplyFilter
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "Your Data is out of range"
End If
'FilterCount = Me.RecordsetClone.ReordCount
If FilterCount > 1 Then
MsgBox "You have selected multi items"
End If
'Debug.Print FilterCount
End If
End Sub