Moved Program to Access 2007 PC and it will not work

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
 
B

Beetle

Access 2007 will disable code if the source file of the db is not a "trusted"
location. Go to the Office button in the upper left, go to Access Options,
then to the Trust Center and add your source file to the list of trusted
locations. This may solve your problem.

HTH

Gary. said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top