Re: requery vs. refresh vs. Remove Filter/Sort

From: rgrantz (r_grantz_at_hotmail.com)
Date: 11/17/04 

Date: Wed, 17 Nov 2004 14:42:32 -0800

Thanks for the response. Below is the info requested:

SQL of recordsource (although I use the query builder):

SELECT ProductionData.EnteredBy, EmpList.EmpName, ProductionData.DateRec,
ProductionData.DateEntered, ProductionData.ItemPartNum,
ProductionData.PSNum, ProductionData.CustNum, CustomerList.CustName,
ProductionData.Status, OrderStatusList.Status2, ProductionData.DateDes,
ProductionData.SqFoot, ProductionData.AutoDes, ProductionData.DesBy,
EmpList_1.EmpName, ProductionData.Notes, ItemData.ItemNum,
ItemData.DateProd, ItemData.Vendor, VendorList.VendorName, ItemData.Wall,
ItemData.Floor, ItemData.Bead, ItemData.MachineNum, ItemData.ShiftProd,
ItemData.OperatorNum, EmpList_2.EmpName, ItemData.PartnerNum,
EmpList_3.EmpName, ItemData.Verifier1Num, EmpList_4.EmpName,
ItemData.Verifier2Num, EmpList_5.EmpName, ItemData.[2ndVerifier1Num],
ItemData.[2ndVerifier2Num], ItemData.TurnaroundDays, ItemData.Notes,
[DateEntered]-[DateRec] AS EntryTurn, [DateDes]-[DateEntered] AS DesignTurn,
[DateProd]-[DateDes] AS ProdTurn, [DateProd]-[DateEntered] AS TotalTurn

FROM EmpList AS EmpList_4 RIGHT JOIN (EmpList AS EmpList_3 RIGHT JOIN
(CustomerList RIGHT JOIN (OrderStatusList RIGHT JOIN (VendorList RIGHT JOIN
(EmpList AS EmpList_5 RIGHT JOIN (EmpList AS EmpList_2 RIGHT JOIN (EmpList
AS EmpList_1 RIGHT JOIN (EmpList RIGHT JOIN (ItemData RIGHT JOIN
ProductionData ON ItemData.PSNum = ProductionData.PSNum) ON EmpList.EmpID =
ProductionData.EnteredBy) ON EmpList_1.EmpID = ProductionData.DesBy) ON
EmpList_2.EmpID = ItemData.OperatorNum) ON EmpList_5.EmpID =
ItemData.Verifier2Num) ON VendorList.VendorID = ItemData.Vendor) ON
OrderStatusList.ID = ProductionData.Status) ON CustomerList.CustID =
ProductionData.CustNum) ON EmpList_3.EmpID = ItemData.PartnerNum) ON
EmpList_4.EmpID = ItemData.Verifier1Num

WHERE (((ProductionData.EnteredBy) Like
[Forms]![frmMainReport]![JREnteredBy] & "*") AND
((ProductionData.CustNum)=[Forms]![frmMainReport]![JRCustNum]) AND
((ProductionData.DesBy) Like [Forms]![frmMainReport]![JRDesBy] & "*") AND
(([DateEntered]-[DateRec])>=[Forms]![frmMainReport]![JREntryTurn]) AND
(([DateDes]-[DateEntered])>=[Forms]![frmMainReport]![JRDesTurn]) AND
(([DateProd]-[DateDes])>=[Forms]![frmMainReport]![JRProdTurn]) AND
(([DateProd]-[DateEntered])>=[Forms]![frmMainReport]![JRTotalTurn])) OR
((([Forms]![frmMainReport]![JRCustNum]) Is Null));

As you may have guessed, the multiple EmpName and EmpList entries are using
one Employee List table to show names for employee numbers, or to show
specific department employees in the criteria drop-downs. The "Like
[formfield] & *" is to return all records in that field even when no value
is chosen (ie. no criteria applied).

Row Source of Customer Combobox:

SELECT [CustomerList].[CustID], [CustomerList].[CustName] FROM CustomerList;

Column Count: 2
Bound Column: 1
AfterUpdate: Docmd.Requery (with the understood Sub/End Sub wrap in the
module window)

Since I posted that last problem, I've run into several more. It seems my
query's just getting too complex, as I have 7 unbound controls with the
AfterUpdates set to DoCmd.Requery, and the criteria in the query pulling
from these 7 separate unbound controls. I'm getting extremely unpredictable
results that simply are not adding up. Is my placement of the Is Nulls and
the Or Isnulls in the query builder messed up? This form working correctly
seems to depend on the order in which I set the criteria in the form. You
can see the unbound controsl in the SQL, they are all the ones that express
criteria. I need to be able to allow for the calculated fields having nulls
(ie. DateProd. might be Null, so TotalTurn (turnaround time from receipt of
order to date produced) needs to be able to have nulls.

Why isn't this working? Am I just trying to do to much on one form? I can
put this DB somewhere for download if people want to see the weird behavior
of this form.

Thanks again Dirk, and everyone.

"Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
news:ekSMpQNzEHA.3836@TK2MSFTNGP12.phx.gbl...
> "rgrantz" <r_grantz@hotmail.com> wrote in message
> news:301drtF2r1coaU1@uni-berlin.de
> > Interesting and very frustrating problem:
> >
> > I have a form w/ unbound controls in the Header; the values of these
> > controls are the parameters of the query that produces the records in
> > the Details section. All of the parameters in the query are:
> >
> > Like [Forms]![FormName]![UnboundFormControlName] & "*"
> >
> > 2 of the unbound controls are comboboxes with a list source of
> > queries that are employee numbers from specific departments. I have
> > the OnChange event set to fire the Me.Requery procedure. The 3rd
> > combobox has a list source of a table (customer number and associated
> > name). I can't use the OnChange on this one, because I need it to
> > auto-expand as user types customer name.
> >
> > The problem:
> >
> > The 2 employee comboboxes work great. However, no matter what I do,
> > I can't get the records to refresh and show the new recordset using
> > the CUSTOMER combobox. Putting me.requery in the after update, or
> > even on a separate command button after customer list is chosen, will
> > not refresh the records. The only way to refresh them and show the
> > new recordset after the customer name is changed is to go the records
> > menu and choose "remove filter/sort." The other 2 comboboxes work
> > great; when I remove filter/sort, the records reflect the customer
> > name in the combobox (and the existing values in the other 2
> > comboboxes), and I can continue to change the recordset from there
> > using the OTHER 2 comboboxes, but once customer name is changed, I
> > need to choose Records-->Remove filter/Sort, which then refreshes to
> > show data for new customer name and the other 2 comboboxes.
> >
> > Can anyone explain this? I can email this DB to someone if you want
> > to see this. It seems EXTREMELY weird to me.
> >
> >
> > Thanks for any help or ideas. Using Access 2000 (updated) w/ Windows
> > 2000 Pro (updated)
>
> You shouldn't use the Change event of a combo box for this sort of
> thing, for the reason that you've discovered. The AfterUpdate event is
> what you should normally use, so I don't understand why it isn't working
> for you. Please post the SQL of the form's recordsource, as well as the
> code you have in the CUSTOMER combo box's AfterUpdate event and any
> other events associated with that combo box. Also post the rowsource of
> the combo box, its ColumnCount property, and its BoundColumn property
> (as displayed on the property ***).
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>