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 said:
rgrantz said:
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 sheet).
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)