ApplyFilter in mde

[Bob Darlington]

Can anyone tell me why:
DoCmd.ApplyFilter, "ElectID = " & vNewElectID
should work in an mdb, but fail when the file is converted to an mde. It
fails with a 'Enter Parameter Value: ElectID" message.

After playing around with it, I inserted:
Me.Requery
immediately before the ApplyFilter and it now works in the mde.

So while the problem is solved, I cannot see why should it have been
different in the mdb and mde????

Has anyone else experience similar problems?
--
Bob Darlington
Brisbane

[Allen Browne]

Hi Bob

As I'm sure you realize, there is no good reason for this line of code to
work in an MDB but not in an MDE. It therefore has to be either a
corruption, or a side-effect of something else that's going on.

Name AutoCorrect would be the first culprit. Make sure the boxes are
unchecked under:
Tools | Options | General | Name AutoCorrect
For a list of *some* of the problem caused by this misfeature, see:
http://www.allenbrowne.com.au/bug-03.html

After that, compact the database:
Tools | Database Utilities | Compact

Now close Access, and decompile by entering something like this at the
command prompt while Access is not running. It is all one line, and include
the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

Compact again. That should clear out the kinds of corruption that cause
trigger the behavior you describe.

If the record is dirty, Access has to save it before the filter can be
applied, and there are some rather odd side effects of the implicit save.
The code could also fail if the vNewElectID is null or a zero-length string,
so you might try coding like this:
If Me.Dirty Then
Me.Dirty = False
End If
Me.Filter = "ElectID = " & Nz(vNewElectID, 0)
Me.FilterOn = True

After that, you can try creating another MDE. Hopefully the problem is now
gone.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bob Darlington" <bo*@dpcmanAX.com.au> wrote in message
news:42**********************@news.optusnet.com.au ...

Can anyone tell me why:
DoCmd.ApplyFilter, "ElectID = " & vNewElectID
should work in an mdb, but fail when the file is converted to an mde. It
fails with a 'Enter Parameter Value: ElectID" message.

After playing around with it, I inserted:
Me.Requery
immediately before the ApplyFilter and it now works in the mde.

So while the problem is solved, I cannot see why should it have been
different in the mdb and mde????

Has anyone else experience similar problems?
--
Bob Darlington
Brisbane

[White Bilky]

"Bob Darlington" <bo*@dpcmanAX.com.au> wrote:

Can anyone tell me why:
DoCmd.ApplyFilter, "ElectID = " & vNewElectID
should work in an mdb, but fail when the file is converted to an mde. It
fails with a 'Enter Parameter Value: ElectID" message.

Is the variable correctly declared with 'option explicit' also set?

Keith.
www.keithwilby.com

[Bob Darlington]

Thanks Allen,
Auto correct was turned off, but it wasn't always. So I guess the solution
may be contained in the last sentence of your reference. Will need to bite
the bullet and just do it.

--
Bob Darlington
Brisbane
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:42***********************@per-qv1-newsreader-01.iinet.net.au...

Hi Bob

As I'm sure you realize, there is no good reason for this line of code to
work in an MDB but not in an MDE. It therefore has to be either a
corruption, or a side-effect of something else that's going on.

Name AutoCorrect would be the first culprit. Make sure the boxes are
unchecked under:
Tools | Options | General | Name AutoCorrect
For a list of *some* of the problem caused by this misfeature, see:
http://www.allenbrowne.com.au/bug-03.html

After that, compact the database:
Tools | Database Utilities | Compact

Now close Access, and decompile by entering something like this at the
command prompt while Access is not running. It is all one line, and
include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

Compact again. That should clear out the kinds of corruption that cause
trigger the behavior you describe.

If the record is dirty, Access has to save it before the filter can be
applied, and there are some rather odd side effects of the implicit save.
The code could also fail if the vNewElectID is null or a zero-length
string, so you might try coding like this:
If Me.Dirty Then
Me.Dirty = False
End If
Me.Filter = "ElectID = " & Nz(vNewElectID, 0)
Me.FilterOn = True

After that, you can try creating another MDE. Hopefully the problem is now
gone.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bob Darlington" <bo*@dpcmanAX.com.au> wrote in message
news:42**********************@news.optusnet.com.au ...

Can anyone tell me why:
DoCmd.ApplyFilter, "ElectID = " & vNewElectID
should work in an mdb, but fail when the file is converted to an mde. It
fails with a 'Enter Parameter Value: ElectID" message.

After playing around with it, I inserted:
Me.Requery
immediately before the ApplyFilter and it now works in the mde.

So while the problem is solved, I cannot see why should it have been
different in the mdb and mde????

Has anyone else experience similar problems?
--
Bob Darlington
Brisbane

[Bob Darlington]

Thanks Keith,
The answer is 'yes' to both your suggestions, but was worth double checking.

--
Bob Darlington
Brisbane
"White Bilky" <ke*********@AwayWithYerCrap.com> wrote in message
news:Xn***********************@10.15.188.42...

"Bob Darlington" <bo*@dpcmanAX.com.au> wrote:

Can anyone tell me why:
DoCmd.ApplyFilter, "ElectID = " & vNewElectID
should work in an mdb, but fail when the file is converted to an mde. It
fails with a 'Enter Parameter Value: ElectID" message.

Is the variable correctly declared with 'option explicit' also set?

Keith.
www.keithwilby.com