Frank Freese
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
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
ASKER
let's see if I got this straight:
SELECT StoreID From tblStore WHERE StoreID NOT IN (Select StoreId From tblPreliminaryBonus)
SELECT StoreID From tblStore WHERE StoreID NOT IN (Select StoreId From tblPreliminaryBonus)
That looks good ...
ASKER
let me try it now
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.)
ASKER
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)));
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;
I'll give you a hand with it a little later this afternoon ... not at a computer right now.
ASKER
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);
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 :) ):
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);
ASKER
off to meeting - - will look at it over the weekend - thanks
ASKER
mbizup:
neither queries worked. would you like to see the db?
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.
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
if anything one missed it's me...the ole man. looks good and also solved my other problem. points well earned
ASKER
thank you
Glad that worked out.
I'm no 'spring chicken' either :-)
I'm no 'spring chicken' either :-)
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.