Re: access 2003

I have - at long last - replicated the behaviour you are experiencing and I
have found the cause of the problem.

The problem is caused by the Filter/FilterOn commands, which appear in the
Form_Open event and the ChooseJob_AfterUpdate event:

Private Sub Form_Open(Cancel As Integer)
' If you rem out the filter lines, the problem disappears:
' Me.Filter = "[wrkid]=0"
' Me.FilterOn = True
End Sub

Private Sub ChooseJob_AfterUpdate()
On Error Resume Next
' Rem out the FilterOn line:
' Me.FilterOn = False
Me.Requery
End Sub

Your Original Code:

When the form opens, the word "(Filtered)" appears after the Record
Navigation buttons at the bottom of the form. After the ChooseJob combo box
is used to select a job, the word "(Filtered)" correctly disappears.
However, the Detail Section does not wake up and display the records for the
selected customer and job number.

With the Filter Commands Remmed Out:

If you rem out the code lines relating to the Filter commands (as shown
above), then, "(Filtered)" does not appear after the Record Navigation
buttons when the form opens. In my simulation of your database, the form
opens without showing any records in the Detail Section (which made me
wonder why the Filter was needed). After a customer and job number are
selected, the Detail Section shows the appropriate records for the customer
and job number.

According to online help, your original code appears correct; namely,
(Me.FilterOn = False) should indeed remove the effect of the filter.
However, although the word "(Filtered)" disappears from after the Record
Navigation buttons, the Detail Section does not show the appropriate
records. I have not yet found a command that will make the Detail Section
wake up and display the records. It does appear there is something wrong
with Access.

I thought I'd report this to you now as it's getting late here.

If the filter is important to you (and you might like to consider whether it
is), then I would suggest you might have some better luck if you designed an
unbound main form containing the combo boxes and a bound subform (to replace
your Detailed Section). If I had time, I'd experiment a bit to see if that
approach might work. However, if you don't need the Filter, then your
problem is solved with your existing form.

This has been quite a deal!

It has made me re-evaluate my understanding of parameterized queries and how
they work at the user-interface and in VBA..I do now believe that your
following code is very suspicious:

Set qd = db.CreateQueryDef("", "SELECT wrkky_invno FROM
qryWorkOrders_uninvoiced WHERE wrkky_invno = true;")
qd![Forms!frmInvoiceSelect!ChooseCust] = Me!ChooseCust
qd![Forms!frmInvoiceSelect!ChooseJob] = Me!ChooseJob

The SELECT clause and WHERE clause do not contain the required parameters:

[Forms!frmInvoiceSelect!ChooseCust]
[Forms!frmInvoiceSelect!ChooseJob]

I think it would be easier, when running a query in VBA (as the above code
does), to use a simple name for the Parameter, rather than a criteria
expression that would only be needed at the user-interface. But that's
another story.......

Regards.
Geoff



.