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
Frank FreeseAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
Hi -

One of us might have missed something when copy/pasting the full query - but tacking the following criteria on to the end of your query seems to work for me:

WHERE StoreNumberID NOT IN
(SELECT StoreId FROM tblPreliminaryBonus);  

There is a record in tblPreliminaryBonus for Store #2 (recorded as StoreID 4).  With the criteria added, that store no longer shows up in the drop-down list.  Is that the intent?
StoreBonus.mdb
0
 
mbizupCommented:
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.
0
 
Frank FreeseAuthor Commented:
let's see if I got this straight:
SELECT StoreID From tblStore WHERE StoreID NOT IN (Select StoreId From tblPreliminaryBonus)
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
mbizupCommented:
That looks good ...
0
 
Frank FreeseAuthor Commented:
let me try it now
0
 
nap0leonCommented:
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.)
0
 
Frank FreeseAuthor Commented:
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

0
 
mbizupCommented:
I'll give you a hand with it a little later this afternoon ... not at a computer right now.
0
 
Frank FreeseAuthor Commented:
no problem....thank you very much
0
 
mbizupCommented:
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

0
 
mbizupCommented:
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


0
 
Frank FreeseAuthor Commented:
off to meeting - - will look at it over the weekend - thanks
0
 
Frank FreeseAuthor Commented:
mbizup:
neither queries worked. would you like to see the db?
0
 
mbizupCommented:
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.
0
 
Frank FreeseAuthor Commented:
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
0
 
Frank FreeseAuthor Commented:
if anything one missed it's me...the ole man. looks good and also solved my other problem. points well earned
0
 
Frank FreeseAuthor Commented:
thank you
0
 
mbizupCommented:
Glad that worked out.

I'm no 'spring chicken' either :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.