Cascading Combo boxes

[xoxavier]

The query is not updatable on its own. the form text boxes are bound
to fields named the same as tblDowntime however, they have a "T." in
fornt i.e. "T.Plant"...You're doing great Marsh, I'm learning a lot
from this and your coaching me through it well...

Bleep, bleep, bleep...

Well, there's no telling when Jet decides it won't be able
to update a table's values.  I've seen queries with 7 Joins
that were updatable while another with only 3 joins was not.

Question:  Is it feasible to change the LEFT JOIN to INNER
JOIN?  That would mean that all current and future records
must have all fourcombobox filled in (not something I
would want to do).  

Another thing to try is to include the PK field from every
table.

While you're at it, let's change the names of the fields
that are bound to the four text boxes.  I don't know what
you did to make the query run so double check this against
what you are now using:

SELECT T.*,
        tblPlant.[Plant ID] As [XPlant ID], tblPlant.Plant As
XPlant,
        PP.[Process ID] As [XProcess ID], PP.Process As XProcess,
        E.[Equipment ID] As [XEquipment ID], E.Equipment As
XEquipment,
        ERR.[Error ID] As [XError ID], ERR.[Error Code] As [XError
Code]
FROM (((tblDowntime AS T
        LEFT JOIN tblPlant
             ON T.Plant = tblPlant.[Plant ID])
        LEFT JOIN [tblPlant/Process] AS PP
             ON T.Process = PP.[Process ID])
        LEFT JOIN [tblProcess/Equipment] AS E
             ON T.Equipment = E.[Equipment ID])
        LEFT JOIN [tblEquip/Error] AS ERR
             ON T.[Error Code] = ERR.[Error ID]

If that is upadable, don't forget to change the four text
box's bound field to the new names before testing the form.
(I don't think you need text boxes bound to the PK fields,
but that would be another thing to try out of desperation.

I copied what you have posted and changed the JOINs to INNER JOIN.
That actually works out better for me becasue all four fields do need
to be filled. The query runs but is still not updatable

 

[xoxavier]

The query is not updatable on its own. the form text boxes are bound
to fields named the same as tblDowntime however, they have a "T." in
fornt i.e. "T.Plant"...You're doing great Marsh, I'm learning a lot
from this and your coaching me through it well...

Bleep, bleep, bleep...

Well, there's no telling when Jet decides it won't be able
to update a table's values.  I've seen queries with 7 Joins
that were updatable while another with only 3 joins was not.

Question:  Is it feasible to change the LEFT JOIN to INNER
JOIN?  That would mean that all current and future records
must have all fourcombobox filled in (not something I
would want to do).  

Another thing to try is to include the PK field from every
table.

While you're at it, let's change the names of the fields
that are bound to the four text boxes.  I don't know what
you did to make the query run so double check this against
what you are now using:

SELECT T.*,
        tblPlant.[Plant ID] As [XPlant ID], tblPlant.Plant As
XPlant,
        PP.[Process ID] As [XProcess ID], PP.Process As XProcess,
        E.[Equipment ID] As [XEquipment ID], E.Equipment As
XEquipment,
        ERR.[Error ID] As [XError ID], ERR.[Error Code] As [XError
Code]
FROM (((tblDowntime AS T
        LEFT JOIN tblPlant
             ON T.Plant = tblPlant.[Plant ID])
        LEFT JOIN [tblPlant/Process] AS PP
             ON T.Process = PP.[Process ID])
        LEFT JOIN [tblProcess/Equipment] AS E
             ON T.Equipment = E.[Equipment ID])
        LEFT JOIN [tblEquip/Error] AS ERR
             ON T.[Error Code] = ERR.[Error ID]

If that is upadable, don't forget to change the four text
box's bound field to the new names before testing the form.
(I don't think you need text boxes bound to the PK fields,
but that would be another thing to try out of desperation.

I got it! I had set the Plant field as the PK. I changed it and tah
dah! Everythin else seems to be working....WOW I am impressed. You
have taken me past a huge hurdle that I can pass. Thank you so much!

 

[Marshall Barton]

The query is not updatable on its own. the form text boxes are bound
to fields named the same as tblDowntime however, they have a "T." in
fornt i.e. "T.Plant"...You're doing great Marsh, I'm learning a lot
from this and your coaching me through it well...

Bleep, bleep, bleep...

Well, there's no telling when Jet decides it won't be able
to update a table's values.  I've seen queries with 7 Joins
that were updatable while another with only 3 joins was not.

Question:  Is it feasible to change the LEFT JOIN to INNER
JOIN?  That would mean that all current and future records
must have all fourcombobox filled in (not something I
would want to do).  

Another thing to try is to include the PK field from every
table.

While you're at it, let's change the names of the fields
that are bound to the four text boxes.  I don't know what
you did to make the query run so double check this against
what you are now using:

SELECT T.*,
        tblPlant.[Plant ID] As [XPlant ID], tblPlant.Plant As
XPlant,
        PP.[Process ID] As [XProcess ID], PP.Process As XProcess,
        E.[Equipment ID] As [XEquipment ID], E.Equipment As
XEquipment,
        ERR.[Error ID] As [XError ID], ERR.[Error Code] As [XError
Code]
FROM (((tblDowntime AS T
        LEFT JOIN tblPlant
             ON T.Plant = tblPlant.[Plant ID])
        LEFT JOIN [tblPlant/Process] AS PP
             ON T.Process = PP.[Process ID])
        LEFT JOIN [tblProcess/Equipment] AS E
             ON T.Equipment = E.[Equipment ID])
        LEFT JOIN [tblEquip/Error] AS ERR
             ON T.[Error Code] = ERR.[Error ID]

If that is upadable, don't forget to change the four text
box's bound field to the new names before testing the form.
(I don't think you need text boxes bound to the PK fields,
but that would be another thing to try out of desperation.

I copied what you have posted and changed the JOINs to INNER JOIN.
That actually works out better for me becasue all four fields do need
to be filled. The query runs but is still not updatable

I am running out of ideas, here.

BTW, does the form display all the values you wanted when we
started this quest? If not, then we will have to change the
query (or dream up something altogether different), making
this query only of academic interest.

If the form is cool, then I only have a couple of other
shots in the dark to make the query updatable.

First, try removing the T.* and replacing it with a specific
list of the fields that are needed for the form (even if its
all the fields). This might affect the way SQL processes
the query plan.

Last, because this will be about as fast as a three legged
pig in mud, try using:

SELECT T.[Entry ID], Y.[Date], T.[Year], T.Week, T.Shift,
T.Plant, T.Process, T.Equipment, T.[Error Code],
T.Details, T.Minutes of Downtime], T.[Work Order #]
      DLookup("Plant", "tblPlant", "[Plant ID]=" &
T.Plant) As XPlant,
DLookup("Process", "tblPlant/Process", "[Process
ID]=" & T.Process) As XProcess,
DLookup("Equipment", "tblEquip/Error", "[Equipment
ID]=" & T.Equipment) As XEquipment,
DLookup("[Error Code]", "tblEquip/Error", "[Error
ID]=" & T.[Error Code] As [XError Code]

FROM tblDowntime AS T

 

[Marshall Barton]

The query is not updatable on its own. the form text boxes are bound
to fields named the same as tblDowntime however, they have a "T." in
fornt i.e. "T.Plant"...You're doing great Marsh, I'm learning a lot
from this and your coaching me through it well...

Bleep, bleep, bleep...

Well, there's no telling when Jet decides it won't be able
to update a table's values.  I've seen queries with 7 Joins
that were updatable while another with only 3 joins was not.

Question:  Is it feasible to change the LEFT JOIN to INNER
JOIN?  That would mean that all current and future records
must have all fourcombobox filled in (not something I
would want to do).  

Another thing to try is to include the PK field from every
table.

While you're at it, let's change the names of the fields
that are bound to the four text boxes.  I don't know what
you did to make the query run so double check this against
what you are now using:

SELECT T.*,
        tblPlant.[Plant ID] As [XPlant ID], tblPlant.Plant As
XPlant,
        PP.[Process ID] As [XProcess ID], PP.Process As XProcess,
        E.[Equipment ID] As [XEquipment ID], E.Equipment As
XEquipment,
        ERR.[Error ID] As [XError ID], ERR.[Error Code] As [XError
Code]
FROM (((tblDowntime AS T
        LEFT JOIN tblPlant
             ON T.Plant = tblPlant.[Plant ID])
        LEFT JOIN [tblPlant/Process] AS PP
             ON T.Process = PP.[Process ID])
        LEFT JOIN [tblProcess/Equipment] AS E
             ON T.Equipment = E.[Equipment ID])
        LEFT JOIN [tblEquip/Error] AS ERR
             ON T.[Error Code] = ERR.[Error ID]

If that is upadable, don't forget to change the four text
box's bound field to the new names before testing the form.
(I don't think you need text boxes bound to the PK fields,
but that would be another thing to try out of desperation.

I got it! I had set the Plant field as the PK. I changed it and tah
dah! Everythin else seems to be working....WOW I am impressed. You
have taken me past a huge hurdle that I can pass. Thank you so much!

Wow! Great to hear that.

Tracking that down was a nice bit of detective work. I
doubt that I would have thought of the table having the
wrong field as its primary key.

 

[xoxavier]

(e-mail address removed) wrote:
The query is not updatable on its own. the form text boxes are bound
to fields named the same as tblDowntime however, they have a "T." in
fornt i.e. "T.Plant"...You're doing great Marsh, I'm learning a lot
from this and your coaching me through it well...
Bleep, bleep, bleep...
Well, there's no telling when Jet decides it won't be able
to update a table's values.  I've seen queries with 7 Joins
that were updatable while another with only 3 joins was not.
Question:  Is it feasible to change the LEFT JOIN to INNER
JOIN?  That would mean that all current and future records
must have all fourcombobox filled in (not something I
would want to do).  
Another thing to try is to include the PK field from every
table.
While you're at it, let's change the names of the fields
that are bound to the four text boxes.  I don't know what
you did to make the query run so double check this against
what you are now using:
SELECT T.*,
        tblPlant.[Plant ID] As [XPlant ID], tblPlant.Plant As
XPlant,
        PP.[Process ID] As [XProcess ID], PP.Process As XProcess,
        E.[Equipment ID] As [XEquipment ID], E.Equipment As
XEquipment,
        ERR.[Error ID] As [XError ID], ERR.[Error Code] As [XError
Code]
FROM (((tblDowntime AS T
        LEFT JOIN tblPlant
             ON T.Plant = tblPlant.[Plant ID])
        LEFT JOIN [tblPlant/Process] AS PP
             ON T.Process = PP.[Process ID])
        LEFT JOIN [tblProcess/Equipment] AS E
             ON T.Equipment = E.[Equipment ID])
        LEFT JOIN [tblEquip/Error] AS ERR
             ON T.[Error Code] = ERR.[Error ID]
If that is upadable, don't forget to change the four text
box's bound field to the new names before testing the form.
(I don't think you need text boxes bound to the PK fields,
but that would be another thing to try out of desperation.

I got it! I had set the Plant field as the PK. I changed it and tah
dah! Everythin else seems to be working....WOW I am impressed. You
have taken me past a huge hurdle that I can pass. Thank you so much!

Wow!  Great to hear that.

Tracking that down was a nice bit of detective work.  I
doubt that I would have thought of the table having the
wrong field as its primary key.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

When I do a query by form to this table, it displays all the PK ID#s,
not the text FK that carries real meaning. how can I display the Fk
based of the PKs that result from the query?

 

[Marshall Barton]

When I do a query by form to this table, it displays all the PK ID#s,
not the text FK that carries real meaning. how can I display the Fk
based of the PKs that result from the query?

I'm not sure because I never use it, but I think that's what
query by form is supposed to do. Because the text boxes
that display the string values redirect the focus, you can
not even use them to tell query by form what to do.

Let's back up and explain what you want to accomplish and I
might be able to come up with an alternative approach. I
would also need to see the latest version of the record
source query.

If you feel that query by form is your best UI design, then
I suggest that you post a new thread with more background
information. Maybe someone more familiar with query by form
will have some ideas.

 

[xoxavier]

I'm not sure because I never use it, but I think that's what
query by form is supposed to do.  Because the text boxes
that display the string values redirect the focus, you can
not even use them to tell query by form what to do.

Let's back up and explain what you want to accomplish and I
might be able to come up with an alternative approach.  I
would also need to see the latest version of the record
source query.

If you feel that query by form is your best UI design, then
I suggest that you post a new thread with more background
information.  Maybe someone more familiar with query by form
will have some ideas.

I would like to be able to sort the data table that is created by the
form in any of a number of ways. For example, by Year, Week, Shift,
Plant, Process, Equipment, or Error code, in any combination. A query
by form will display these results but since I have these fields
connected numerically by ID#s, the results are all #s and not the text
that I have bound to them. Eventually, I want to be able to chart
these results as well but that will come in time. I am willing to use
another application to make this feasible, i.e., excell if there is a
way to transfer the table that we created in our first form's query.
Any thoughts on the best approach?

 

[Marshall Barton]

I would like to be able to sort the data table that is created by the
form in any of a number of ways. For example, by Year, Week, Shift,
Plant, Process, Equipment, or Error code, in any combination. A query
by form will display these results but since I have these fields
connected numerically by ID#s, the results are all #s and not the text
that I have bound to them. Eventually, I want to be able to chart
these results as well but that will come in time. I am willing to use
another application to make this feasible, i.e., excell if there is a
way to transfer the table that we created in our first form's query.
Any thoughts on the best approach?

Best is highly subjective.

Since we've included the text values in the record source
query, it's easy to sort the query. The big problem you
have is deciding how you want users to specify the fields to
sort.

You can usually use the Records - Advanced Filter/Sort menu
item to sort a continuous form, but IMO that is a really
crude UI. Since I normally hide all of the menus and tool
bars, I use buttons and code in the form to allow users to
specify how to sort the records.

If your form's header section has labels for each column,
then you could use those to allow users to select a field to
sort by. If you want users to be able to select multiple
fields, then the MouseUp event allows you to distinguish a
click and a click with the control or shift key. If the
control key in down when a user clicks a label, you can add
the field to the order by list. If not, then you can replace
the list with the clicked label's field.

Post back with your thoughts on where you want to go with
this or your preferred alternative UI design.