Re: Combo Box text display problem based on AfterUpdate...

The S, E, M, and Y are table aliases... shorthand references rather than
writing the entire table name each time.

You need to change the table structure for tblPick. The Store, Manager, and
Employee fields all need to be Number (long integer) data types, not text.
You need to make these changes so that the query will work.

OK, the query would be something like this:

SELECT S.strStoreName, M.strManagerName, E.strEmployeeName
FROM ((tblPick AS Y
INNER JOIN tblStore AS S
ON Y.Store.=S.lngStoreID )
INNER JOIN tblManager AS M
ON Y.Manager = M.lngManagerID)
INNER JOIN
tblEmployee AS E ON Y.Employee=E.lngEmployeeID;

--

Ken Snell
<MS ACCESS MVP>



"Joe Leon" <JoeLeon@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:36B20E6B-C6FB-41E6-9659-BBE4523101F9@xxxxxxxxxxxxxxxx
I'm confused. Here is what I have to now:
The three tables: tblStore, tblManager, tblEmployee that are used to
extract
the data.
The tblPick is what is getting filled with the data from the combo boxes.

tblStore:
Primary Key: lngStoreID AutoNumber
strStoreName Text

tblManager:
Primary Key: lngManagerID AutoNumber
lngStoreID Number
strManagerName Text

tblEmployee:
Primary Key: lngEmployeeID AutoNumber
lngManagerID Number
strEmployeeName Text

tblPick
Primary Key: lngPickID AutoNumber
Store Text
Manager Text
Employee Text

Looking at the way you instruct how the query should be written, I have
the
following, but, I'm confused with the Y, S, and M.

SELECT Store, Manager, Employee
FROM ((tblPick AS Y
INNER JOIN tblStore AS S
ON Y.Store.=S.StoreID)
INNER JOIN tblManager AS M
ON Y.Manager = M.ManagerID)
INNER JOIN
tblEmployee AS E ON Y.Employee=E.EmployeeID;


Ken Snell (MVP)" wrote:

The query would be something like this:

SELECT S.StoreName, M.ManagerName, E.EmployeeName
FROM ((YourTableName AS Y INNER JOIN StoreTable AS S
ON Y.Store.=S.StoreID) INNER JOIN ManagerTable AS M
ON Y.Manager = M.ManagerID) INNER JOIN
EmployeeTable AS E ON Y.Employee=E.EmployeeID;

--

Ken Snell
<MS ACCESS MVP>



"Joe Leon" <JoeLeon@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1A373EDA-BF47-4A77-B648-140BA6FA71AE@xxxxxxxxxxxxxxxx
Thanks...

But I'm having a problem coming up with the query.

So far, from the form I created, the table is getting filled with the
following 4 records:

tblPickID | Store | Manager | Employee
1 | 1 | 1 | 2
2 | 2 | 4 | 9
3 | 3 | 6 | 18
4 | 4 | 8 | 16

And yes you're right all the values match as you say. But I'm having
problems coming up with the query. Thanks... Joe...



"Ken Snell (MVP)" wrote:

You should be storing the ID, not the name. The bound column of the
combo
box is the column whose value is actually the value of the combo box.
In
a
relational database, you store the related field value, and then you
use
a
query to get the actual name when you want it for another form or
report.

This means that you can update a store name in the store table, and
not
need
to update it in all the other records where you'd stored a name
instead
of
the StoreID.

So your setup is working correctly.
--

Ken Snell
<MS ACCESS MVP>




"Joe Leon" <JoeLeon@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A8562628-7B32-4835-B2E9-7095BAD53C08@xxxxxxxxxxxxxxxx
Here's a follow on question.... Now that I have this working I
tried
placing
the StoreName, ManagerName, and EmployeeName in a table after the
combo
box
selction. But what I am storing is not the text but the ID. What am
I
doing
wrong?
Thanks...Joe...

"Ken Snell (MVP)" wrote:

OK - second clue should say the first combo box not the second...
but
still
lets me appear to be clairvoyant < g >
--

Ken Snell
<MS ACCESS MVP>



"Ken Snell (MVP)" <kthsneisllis9@xxxxxxxxxxxxxxxxxx> wrote in
message
news:%23XNC1CxoGHA.2400@xxxxxxxxxxxxxxxxxxxxxxx
First clue was your statement that the cboEmployee combo box is
showing
the lngMangaerID, which is the second column in the Row Source
query.

Second clue was assuming that you probably copied the cboManager
combo
box
to make the cboEmployee combo box, and the cboManager combo box
has
only
two columns in its RowSource query. Common error to forget to
change
the
settings when doing this -- I've done it many times.

--

Ken Snell
<MS ACCESS MVP>


"Joe Leon" <JoeLeon@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A1D682F9-AD61-4A84-8FEC-B3EF6B26F727@xxxxxxxxxxxxxxxx
Thanks! but how did you know???

"Ken Snell (MVP)" wrote:

Sounds like the column widths property is not properly set. It
should
be
this:

0";0";1"

Or use whatever width for the third colum that you want. Also
be
sure
that
the column count property is set to 3.

--

Ken Snell
<MS ACCESS MVP>

"Joe Leon" <JoeLeon@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E8F63AB3-8132-4AF9-89E0-D75C58341438@xxxxxxxxxxxxxxxx
I have three tables defined at follows:

tblStore:
Primary Key: lngStoreID AutoNumber
strStoreName Text

tblManager:
Primary Key: lngManagerID AutoNumber
lngStoreID Number
strManagerName Text

tblEmployee:
Primary Key: lngEmployeeID AutoNumber
lngManagerID Number
strEmployeeName Text

I have 3 combo boxes, cboStore, cboManager, and cboEmployee

The AfterUpdate for cboStore is as follows and and the store
picked
is
used
to filter the managers name that will display in cboManager

Private Sub cboStore_AfterUpdate()
Dim sManagerSource As String

sManagerSource = "SELECT [tblManager].[lngManagerID],
[tblManager].[lngStoreID], [tblManager].[strManagerName] " &
_
"FROM tblManager " & _
"WHERE [lngStoreID] = " &
Me.cboStore.Value
Me.cboManager.RowSource = sManagerSource
Me.cboManager.Requery
End Sub

The AfterUpdate for cboManager is as follows and the manager
name
picked
is
used to filter the employee name that will display in
cboEmployee

Private Sub cboManager_AfterUpdate()
Dim sEmployeeSource As String

sEmployeeSource = "SELECT
[tblEmployee].[lngEmployeeID],[tblEmployee].[lngManagerID],
[tblEmployee].[strEmployeeName] " & _
"FROM tblEmployee " & _
"WHERE [lngManagerID] = " &
Me.cboManager.Value
Me.cboEmployee.RowSource = sEmployeeSource
Me.cboEmployee.Requery
End Sub

The cboStore displays the strStoreName (Text), the cboManger
displays
the
strManagerName (Text) based on the store picked, but the
cboEmployee
displays
the lngMangaerID (Number) instead of the strEmployeeName
(Text)..
What's
wrong??? Thanks..Joe.

















.

Relevant Pages

  • Re: query not working
    ... not removing the control values thereby allowing a duplicate record to be ... "Ken Snell " wrote: ... that should cause your query to have a record. ... <MS ACCESS MVP> ...
    (microsoft.public.access.formscoding)
  • Re: Union All query truncating
    ... <MS ACCESS MVP> ... and it is still truncating on the query. ... "Ken Snell " wrote: ... that may be the source of the truncation. ...
    (microsoft.public.access.reports)
  • Re: Sort Multi Time Fields
    ... Ken Snell ... <MS ACCESS MVP> ... I do have the report sorting and grouping by ... Save the query. ...
    (microsoft.public.access.reports)
  • Re: query not working
    ... information in that I know was duplicate. ... opened the query with the form still open and it displayed a blank record. ... "Ken Snell " wrote: ... <MS ACCESS MVP> ...
    (microsoft.public.access.formscoding)
  • Re: Decimal places in a calculated Text Box
    ... property of the control (if the control is bound to the field and nothing ... Access MVP 2002-2005, 2007-2008 ... FROM ((((FlagAudits INNER JOIN (CauseCodes INNER JOIN CauseCodeFamilys ON ... "Ken Snell " wrote: ...
    (microsoft.public.access.reports)
Loading