Link to home
Start Free TrialLog in
Avatar of Frank Freese
Frank FreeseFlag for United States of America

asked on

Unpopulating a combo boc

Experts,
My form has a combo box labeled StoreID from my table tblStore.
My form's Record Source is tblPreliminaryBonus
tblPreliminaryBonus has a field (number) StoreID
This tblPreliminaryBonus is only a temporary table and all the records are planned to be deleted later.
Here's my objective. Everytime a record is written in tblPrelimiaryBonus I would like to repopulate my combo box to reflect only those stores where a Preliminary Bonus record does not have a corresponding StoreID in the Store table.

In other words, if I had 5 stores and one store has a Prelimiary Bonus record, I'd like to be able to show the four stores that have yet to have their own record in my combo box
Avatar of mbizup
mbizup
Flag of Kazakhstan image

The query would be something like:

SELECT Store FROM tblStore
WHERE StoreID NOT IN (SELECT  StoreID FROM tblPreliminaryBonus)

You could use that as a rowsource fro your combo, and requery it in the afterupdate event of your form.
Avatar of Frank Freese

ASKER

let's see if I got this straight:
SELECT StoreID From tblStore WHERE StoreID NOT IN (Select StoreId From tblPreliminaryBonus)
That looks good ...
let me try it now
Avatar of nap0leon
nap0leon

If anyone other than you will ever look at or update your code, for legibility, you ought to keep the capitalization that mbizup used (SELECT, FROM, NOT IN, WHERE, etc.)
Ok...(I think) I made a posting mistake but here's the sql
SELECT StoreNumberID FROM tblStore
WHERE ((([StoreNumberID]) Not In
(Select StoreId From tblPreliminaryBonus)));

Open in new window

Can you help me one step further. I'd like to incorpoarte the above code into this (an expert provide me this and it works fine - not my handy work)
SELECT DISTINCT tblStore.StoreNumberID, tblStore.StoreNumber,
 "# " & [StoreNumber] & " " & [StoreAddressOne] AS Store, (Select [tblEmployee.EmployeeFirstName]&" "&[tblEmployee.EmployeeLastName]
 AS DMName  FROM tblEmployee INNER JOIN tblDesignatedEmployee
 ON tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID
 Where tblDesignatedEmployee.StoreID = tblStore.StoreNumberID And tblDesignatedEmployee.EmployeeTypeID=1) AS DistrictManager,
 (Select [tblEmployee.EmployeeFirstName]&" "&[tblEmployee.EmployeeLastName]
 as SMName  FROM tblEmployee INNER JOIN tblDesignatedEmployee
  ON tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID
 Where tblDesignatedEmployee.StoreID = tblStore.StoreNumberID And tblDesignatedEmployee.EmployeeTypeID=2) AS StoreManager, (Select [tblEmployee.EmployeeFirstName]&" "& [tblEmployee.EmployeeLastName] 
AS AMName  FROM tblEmployee INNER JOIN tblDesignatedEmployee ON  tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID Where tblDesignatedEmployee.StoreID = tblStore.StoreNumberID And tblDesignatedEmployee.EmployeeTypeID=3) AS AssistantManager, 
(Select [tblEmployee.EmployeeFirstName]&" "&[tblEmployee.EmployeeLastName]
 AS ASMName  FROM tblEmployee INNER JOIN tblDesignatedEmployee  ON tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID Where tblDesignatedEmployee.StoreID = tblStore.StoreNumberID And tblDesignatedEmployee.EmployeeTypeID=5) AS AreaSupportManager
FROM tblStore;

Open in new window

I'll give you a hand with it a little later this afternoon ... not at a computer right now.
no problem....thank you very much
Thats one heck of a query!  You should be able to simply tack the WHERE clause on at the end like this:

SELECT DISTINCT tblStore.StoreNumberID, 
tblStore.StoreNumber,
 "# " & [StoreNumber] & " " & [StoreAddressOne] AS Store, 
(SELECT [tblEmployee.EmployeeFirstName]&" "&[tblEmployee.EmployeeLastName]
 AS DMName  FROM tblEmployee INNER JOIN tblDesignatedEmployee
 ON tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID
 WHERE tblDesignatedEmployee.StoreID = tblStore.StoreNumberID AND tblDesignatedEmployee.EmployeeTypeID=1) AS DistrictManager,
 (SELECT [tblEmployee.EmployeeFirstName]&" "&[tblEmployee.EmployeeLastName]
 as SMName  FROM tblEmployee INNER JOIN tblDesignatedEmployee
  ON tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID
 WHERE tblDesignatedEmployee.StoreID = tblStore.StoreNumberID AND tblDesignatedEmployee.EmployeeTypeID=2) AS StoreManager,
 (SELECT [tblEmployee.EmployeeFirstName]&" "& [tblEmployee.EmployeeLastName] 
AS AMName  FROM tblEmployee INNER JOIN tblDesignatedEmployee ON  tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID WHERE tblDesignatedEmployee.StoreID = tblStore.StoreNumberID AND tblDesignatedEmployee.EmployeeTypeID=3) AS AssistantManager, 
(SELECT [tblEmployee.EmployeeFirstName]&" "&[tblEmployee.EmployeeLastName]
 AS ASMName  FROM tblEmployee INNER JOIN tblDesignatedEmployee  ON tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID WHERE tblDesignatedEmployee.StoreID = tblStore.StoreNumberID AND tblDesignatedEmployee.EmployeeTypeID=5) AS AreaSupportManager
FROM tblStore
WHERE tblStore.StoreNumberID NOT IN
(SELECT StoreId FROM tblPreliminaryBonus); 

Open in new window

Also give this a try -

I don't think you need all of the prefixes and brackets - they are only needed to distinguish fields of the same name appearing in different tables, in join statements, and brackets when names contain spaces or special characters.  This is a bit more readable (it would be nice if it works, too :)  ):


 SELECT DISTINCT StoreNumberID, StoreNumber,
 "# " & StoreNumber & " " & StoreAddressOne AS Store, 
(SELECT EmployeeFirstName &" "& EmployeeLastName
 AS DMName  FROM tblEmployee INNER JOIN tblDesignatedEmployee
 ON tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID
 WHERE StoreID = StoreNumberID AND EmployeeTypeID=1) AS DistrictManager,
 (SELECT EmployeeFirstName &" "& EmployeeLastName
 as SMName  FROM tblEmployee INNER JOIN tblDesignatedEmployee
  ON tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID
 WHERE StoreID = StoreNumberID AND EmployeeTypeID=2) AS StoreManager,
 (SELECT EmployeeFirstName &" "& EmployeeLastName 
AS AMName  FROM tblEmployee INNER JOIN tblDesignatedEmployee ON  tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID WHERE StoreID = StoreNumberID AND EmployeeTypeID=3) AS AssistantManager, 
(SELECT EmployeeFirstName &" "& EmployeeLastName
 AS ASMName  FROM tblEmployee INNER JOIN tblDesignatedEmployee  ON tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID WHERE StoreID = StoreNumberID AND EmployeeTypeID=5) AS AreaSupportManager
FROM tblStore
WHERE StoreNumberID NOT IN
(SELECT StoreId FROM tblPreliminaryBonus); 

Open in new window


off to meeting - - will look at it over the weekend - thanks
mbizup:
neither queries worked. would you like to see the db?
That might be the best thing - the query I initially posted definitely works.  It's just a matter of getting the criteria right in yours.
ok...here's the attachment
The db opens to the form in question
For the month Select September
For the year select 2011
For the Store select #2
This query is in the control StoreID
I've added some code additional to the query in the control StoreID and a follow-up question will be posted OUTSIDE of my objective here since I'm having a problem in my cmdSave routine in getting Nulls just before saving.
Again, I would like to repopulate the combox box with only those stores that have not yet been saved for the preliminary bonus.
thanks for the weekend time - much appreciated
StoreBonus.mdb
ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
if anything one missed it's me...the ole man. looks good and also solved my other problem. points well earned
thank you
Glad that worked out.

I'm no 'spring chicken' either :-)