Form conversion from 2003 to 2007 problem 1

[Penny001]

I converted a 2003 database to 2007 and all works well except for one form – which worked perfectly in 2003. This form opens to only a gray screen. This form is the only one that opens with a filter – and I feel sure this is the problem. I did not create this database and I'm not sure how to fix it.

PROPERTIES:
RECORD SOURCE = EMPLOYEEWITHTOOLS
FILTER: EMPL_ID = Forms!CheckInOutTools!InpEmpID

ON CURRENT:
Private Sub Form_Current()
Me.InpEmpID = Me.EMPL_ID
DoCmd.Maximize
End Sub

ON GOT FOCUS:
Private Sub Form_GotFocus()
Me.Requery
End Sub



UNBOUND FIELD: NAME = INPEMPID
ROW SOURCE: SELECT DISTINCT AllEmployeesAndTools.EMPL_ID FROM AllEmployeesAndTools;

AFTER UPDATE:
Private Sub InpEmpID_AfterUpdate()
txtUserUpdated = False
DoCmd.ApplyFilter , "EMPL_ID = Forms!CheckInOutTools!InpEmpID"
End Sub


We are going to update all databases this weekend to 2007 and I don't know how to make this one work. Any help is appreciated.

 

[MajP]

I would not use the docmd.applyfilter. Seems there are some similar posts to yours. May be a bug or you are applying the filter on top an existing filter returning no records. Use the filter property of the form instead.

And to be extra safe I add a few steps.

dim strFilter as string
strFilter = "EMPL_ID = " & Forms!CheckInOutTools!InpEmpID
'Verify your your filter string
debug.print "strFilter: " & strFilter
'turn off the filter (maybe overkill)
me.filterOn = false
'set the new filter
me.filter = strFilter
'Double check to be sure
debug.print "Filter " & me.filter
'turn filter back on
me.filteron = true

 

[Penny001]

Thanks MajP for responding....those "similar posts" are mine and I'm desperate for some help.

As suggested I am not using the "docmd.applyfilter". I added your steps:
Private Sub InpEmpID_AfterUpdate()
txtUserUpdated = False
'DoCmd.ApplyFilter , "EMPL_ID = Forms!CheckInOutTools!InpEmpID"
Dim strfilter As String
strfilter = "EMPL_ID = " & Forms!CheckInOutTools!InpEmpID
'Verify your filter string
Debug.Print "strFilter: " & strfilter
'turn off the filter
Me.FilterOn = False
'set the new filter
Me.Filter = strfilter
'Double check to be sure
Debug.Print "Filter " & Me.Filter
'turn filter back on
Me.FilterOn = True

End Sub

When testing the form I got "Runtime error '3464' Data Type mismatch in criteria expression"

There should not be a data mismatch because all my fields are text??

 

[MajP]

if empl_ID is text the original should have never worked. You need to put text in single quotes in a sql string

Empl_ID = 'AB1234'

So this line would have to be
strfilter = "EMPL_ID = '" & Forms!CheckInOutTools!InpEmpI & "'"

I assume the error is at this line
Me.Filter = strfilter

 

[Penny001]

I'll try this....

When I ran Debug on the error it pointed to "me.filteron = true"

This is strange - it really did work for a number of years....

 

[MajP]

I am sorry, me.filteron is where I would expect the error.

if you empl_ID is a text field then when you turn the filter on
a filter like
"EMPL_ID = 123"
will give a data type error
"EMPL_ID = '123'"
will work.

If it is a numeric field then
"EMPL_ID = 123"
will work and
"EMPL_ID = '123'"
will give a data type error.

 

[MajP]

Uncomment the correct strFilter

Private Sub InpEmpID_AfterUpdate()
  txtUserUpdated = False
  Dim strfilter As String
  ' if empl_id is a text field then
  strfilter = "EMPL_ID = '" & Forms!CheckInOutTools!InpEmpID & "'"
  ' If it is numeric
  'strfilter = "EMPL_ID = " & Forms!CheckInOutTools!InpEmpID
  'Verify your filter string
  Debug.Print "strFilter: " & strfilter
  'turn off the filter
  Me.FilterOn = False
  'set the new filter
  Me.Filter = strfilter
  'Double check to be sure
  Debug.Print "Filter " & Me.Filter
  'turn filter back on
 Me.FilterOn = True
  
End Sub

 

[Penny001]

THANK YOU MAJP!!!

It's working great....I can't thank you enough for responding to me.

YOU'VE GOT A STAR!!