?
Solved

Unpopulating a combo boc

Posted on 2011-10-21
18
Medium Priority
?
178 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:Frank Freese
  • 9
  • 8
18 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 37007770
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
 

Author Comment

by:Frank Freese
ID: 37007857
let's see if I got this straight:
SELECT StoreID From tblStore WHERE StoreID NOT IN (Select StoreId From tblPreliminaryBonus)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37007873
That looks good ...
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:Frank Freese
ID: 37007880
let me try it now
0
 
LVL 18

Expert Comment

by:nap0leon
ID: 37007888
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
 

Author Comment

by:Frank Freese
ID: 37007940
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37007984
I'll give you a hand with it a little later this afternoon ... not at a computer right now.
0
 

Author Comment

by:Frank Freese
ID: 37008015
no problem....thank you very much
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37008364
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37008436
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
 

Author Comment

by:Frank Freese
ID: 37008573
off to meeting - - will look at it over the weekend - thanks
0
 

Author Comment

by:Frank Freese
ID: 37012343
mbizup:
neither queries worked. would you like to see the db?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37012373
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
 

Author Comment

by:Frank Freese
ID: 37012531
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
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 37012974
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
 

Author Comment

by:Frank Freese
ID: 37014658
if anything one missed it's me...the ole man. looks good and also solved my other problem. points well earned
0
 

Author Closing Comment

by:Frank Freese
ID: 37014659
thank you
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37014671
Glad that worked out.

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

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question