Cascading Combo boxes

[xoxavier]

I am creating a form with bound combo boxes. The Plant filters to the
corresponding Process which filters to the corresponding Equipment.

Plant-Process-Equipment

I have all the combos boxes working. The issue I am having is when I
change the value of the Plant field in a new record, all the values
that disagree with the new value, are deleted in all of the saved
records. How do I keep this from happening? I am assuming it has to do
with the way the requery macro is set up. A simple English answer
would be of great relief...

 

[Jeff Boyce]

It would be quite unusual for a combobox's requery to delete table records.

How have you determined that "all of the saved records" have been affected?

For your combobox(es), what are the queries for each (the SQL statements,
please).

What code are you running in the AfterUpdate event of each?

How are these comboboxes "bound" to your underlying dataset?

What are you using for an underlying dataset? (if a query, please post the
SQL statement ... if not, why not?!)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Marshall Barton]

I am creating a form with bound combo boxes. The Plant filters to the
corresponding Process which filters to the corresponding Equipment.

Plant-Process-Equipment

I have all the combos boxes working. The issue I am having is when I
change the value of the Plant field in a new record, all the values
that disagree with the new value, are deleted in all of the saved
records. How do I keep this from happening? I am assuming it has to do
with the way the requery macro is set up. A simple English answer
would be of great relief...

Nothing is deleted, the values in the table are not
disturbed. If you have the needed Requeries in the form's
Current event, the correct values will be displayed when you
move to a different record.

The values displayed on other rows in a continuous or
datasheet view are empty because there is only one RowSource
for all rows and the BoundColumn is not the first visible
column. In this situation the combo box on the other rows
can not translate the bound value to the displayed value.

Since the issue is only one of appearance, you are not
required to do anything. OTOH, the apparently blank values
can be disconcerting. Unfortunately the workaround is quite
messy, requiring that you change the form's record source
query to include the display fields from their respective
tables. Then you also need to put a text box bound to the
display field on top of each of the dependent combo boxes.

If you want to pursue the workaround and need help with it,
then I will need details about the form's record source and
each of the combo box's row source, bound column and column
widths.

 

[xoxavier]

Nothing is deleted, the values in the table are not
disturbed.  If you have the needed Requeries in the form's
Current event, the correct values will be displayed when you
move to a different record.

The values displayed on other rows in a continuous or
datasheet view are empty because there is only one RowSource
for all rows and the BoundColumn is not the first visible
column.  In this situation thecombobox on the other rows
can not translate the bound value to the displayed value.

Since the issue is only one of appearance, you are not
required to do anything.  OTOH, the apparently blank values
can be disconcerting.  Unfortunately the workaround is quite
messy, requiring that you change the form's record source
query to include the display fields from their respective
tables.  Then you also need to put a text box bound to the
display field on top of each of the dependentcomboboxes.

If you want to pursue the workaround and need help with it,
then I will need details about the form's record source and
each of thecombobox's row source, bound column and column
widths.

Wow! That makes sense...Thank you for that, it is encouraging. There
is not a lot of blog out there about this.
Let's do it your way. I have read similar explanations with less
clarity. Here is what I have:

cboPlant
Row Source: tblPlant
Column Count:2
Column Widths: 0";1"
After Update: Requery cboProcess
Bound Column:1

cboProcess
Row Source: qry--

SELECT [tblPlant/Process].[Process ID], [tblPlant/Process].Process
FROM [tblPlant/Process]
WHERE ((([tblPlant/Process].[Plant ID])=[Forms]![frmPlantProcess]!
[Plant]))
ORDER BY [tblPlant/Process].[Plant ID] DESC;

Column Count:2
Coulumn Widths:0";1"
After Update: Requery cboEquipment
Bound Column:1

cboEquipment
Row Source: qry--

SELECT [tblProcess/Equipment].[Equipment ID], [tblProcess/
Equipment].Equipment
FROM [tblProcess/Equipment]
WHERE ((([tblProcess/Equipment].[Process ID])=[Forms]!
[frmPlantProcess]![Process]))
ORDER BY [tblProcess/Equipment].[Process ID];

Column Count:2
Coulumn Widths:0";1"
After Update: Requery cboErrorcode
Bound Column:1

cboErrorcode
Row Source:qry--

SELECT [tblEquip/Error].[Error ID], [tblEquip/Error].[Error Code]
FROM [tblEquip/Error]
WHERE ((([tblEquip/Error].[Equipment ID])=[Forms]![frmPlantProcess]!
[cboEquipment]))
ORDER BY [tblEquip/Error].[Equipment ID] DESC;

Column Count:2
Coulumn Widths:0";1"
Bound Column:1

Thank you so much for the time you are taking to help me. It will be a
life saver to get this to work. I will create the text boxes I need to
cover the combo boxes while I wait for your response. Thanks again!!!!!

 

[Marshall Barton]

Nothing is deleted, the values in the table are not
disturbed.  If you have the needed Requeries in the form's
Current event, the correct values will be displayed when you
move to a different record.

The values displayed on other rows in a continuous or
datasheet view are empty because there is only one RowSource
for all rows and the BoundColumn is not the first visible
column.  In this situation the combobox on the other rows
can not translate the bound value to the displayed value.

Since the issue is only one of appearance, you are not
required to do anything.  OTOH, the apparently blank values
can be disconcerting.  Unfortunately the workaround is quite
messy, requiring that you change the form's record source
query to include the display fields from their respective
tables.  Then you also need to put a text box bound to the
display field on top of each of the dependent comboboxes.

If you want to pursue the workaround and need help with it,
then I will need details about the form's record source and
each of thecombobox's row source, bound column and column
widths.

Wow! That makes sense...Thank you for that, it is encouraging. There
is not a lot of blog out there about this.
Let's do it your way. I have read similar explanations with less
clarity. Here is what I have:

cboPlant
Row Source: tblPlant
Column Count:2
Column Widths: 0";1"
After Update: Requery cboProcess
Bound Column:1

cboProcess
Row Source: qry--

SELECT [tblPlant/Process].[Process ID], [tblPlant/Process].Process
FROM [tblPlant/Process]
WHERE ((([tblPlant/Process].[Plant ID])=[Forms]![frmPlantProcess]!
[Plant]))
ORDER BY [tblPlant/Process].[Plant ID] DESC;

Column Count:2
Coulumn Widths:0";1"
After Update: Requery cboEquipment
Bound Column:1

cboEquipment
Row Source: qry--

SELECT [tblProcess/Equipment].[Equipment ID], [tblProcess/
Equipment].Equipment
FROM [tblProcess/Equipment]
WHERE ((([tblProcess/Equipment].[Process ID])=[Forms]!
[frmPlantProcess]![Process]))
ORDER BY [tblProcess/Equipment].[Process ID];

Column Count:2
Coulumn Widths:0";1"
After Update: Requery cboErrorcode
Bound Column:1

cboErrorcode
Row Source:qry--

SELECT [tblEquip/Error].[Error ID], [tblEquip/Error].[Error Code]
FROM [tblEquip/Error]
WHERE ((([tblEquip/Error].[Equipment ID])=[Forms]![frmPlantProcess]!
[cboEquipment]))
ORDER BY [tblEquip/Error].[Equipment ID] DESC;

Column Count:2
Coulumn Widths:0";1"
Bound Column:1

You forgot to post the form's record source query. Lacking
that, I'll just use placeholder names for the names I don't
know.

The first thing you need to do is modify the form's record
source query so it looks something like:

SELECT T.*,
tblPlant.plantnamefield,
PP.Process,
E.Equipment,
ERR.[Error Code]
FROM (((sometable As T
LEFT JOIN tblPlant
ON T.plantFKfield = tblPlant.plantPKfield)
LEFT JOIN [tblPlant/Process] As PP
ON T.ProcessFKfield = PP.[Process ID])
LEFT JOIN [tblProcess/Equipment] As E
ON T.equipmentFKfield = E.[Equipment ID])
LEFT JOIN [tblEquip/Error] As ERR
ON T.equipmenterrorFKfield = ERR.[Error ID]

Run the query by itself to make sure it runs, returns all
the correct values and that all but the last four fields are
updatable.

Assuming all that goes well, then bind the plantname text
box to the plantnamefield, process text box to the Process
field, equipment text box to the Equipment field and the
error code text box to [Error Code]

Set the TabStop property to No for all these text boxes.
Then create a GotFocus event procedure for each text box to
reset the focus to the corresponding combo box. For
example, the plant text box's GotFocus event procedure would
be:
Me.cboPlant.SetFocus

Finally, size and position each text box over the top of the
text portion of its corresponding text box. (Use the Format
- Bring to Front menu item if needed.)

 

[xoxavier]

Wow! That makes sense...Thank you for that, it is encouraging. There
is not a lot of blog out there about this.
Let's do it your way. I have read similar explanations with less
clarity. Here is what I have:

cboPlant
Row Source: tblPlant
Column Count:2
Column Widths: 0";1"
After Update: Requery cboProcess
Bound Column:1

cboProcess
Row Source: qry--

SELECT [tblPlant/Process].[Process ID], [tblPlant/Process].Process
FROM [tblPlant/Process]
WHERE ((([tblPlant/Process].[Plant ID])=[Forms]![frmPlantProcess]!
[Plant]))
ORDER BY [tblPlant/Process].[Plant ID] DESC;

Column Count:2
Coulumn Widths:0";1"
After Update: Requery cboEquipment
Bound Column:1

cboEquipment
Row Source: qry--

SELECT [tblProcess/Equipment].[Equipment ID], [tblProcess/
Equipment].Equipment
FROM [tblProcess/Equipment]
WHERE ((([tblProcess/Equipment].[Process ID])=[Forms]!
[frmPlantProcess]![Process]))
ORDER BY [tblProcess/Equipment].[Process ID];

Column Count:2
Coulumn Widths:0";1"
After Update: Requery cboErrorcode
Bound Column:1

cboErrorcode
Row Source:qry--

SELECT [tblEquip/Error].[Error ID], [tblEquip/Error].[Error Code]
FROM [tblEquip/Error]
WHERE ((([tblEquip/Error].[Equipment ID])=[Forms]![frmPlantProcess]!
[cboEquipment]))
ORDER BY [tblEquip/Error].[Equipment ID] DESC;

Column Count:2
Coulumn Widths:0";1"
Bound Column:1

You forgot to post the form's record source query. Lacking
that, I'll just use placeholder names for the names I don't
know.  

The first thing you need to do is modify the form's record
source query so it looks something like:

SELECT T.*,
                                        tblPlant.plantnamefield,
                                        PP.Process,
                                        E.Equipment,
                                        ERR.[Error Code]
FROM (((sometable As T
        LEFT JOIN tblPlant
                ON T.plantFKfield = tblPlant.plantPKfield)
        LEFT JOIN [tblPlant/Process] As PP
                ON T.ProcessFKfield = PP.[Process ID])
        LEFT JOIN [tblProcess/Equipment] As E
                ON T.equipmentFKfield = E.[Equipment ID])
        LEFT JOIN [tblEquip/Error] As ERR
                ON T.equipmenterrorFKfield = ERR.[Error ID]

Run the query by itself to make sure it runs, returns all
the correct values and that all but the last four fields are
updatable.

Assuming all that goes well, then bind the plantname text
box to the plantnamefield, process text box to the Process
field, equipment text box to the Equipment field and the
error code text box to [Error Code]

Set the TabStop property to No for all these text boxes.
Then create a GotFocus event procedure for each text box to
reset the focus to the correspondingcombobox.  For
example, the plant text box's GotFocus event procedure would
be:
        Me.cboPlant.SetFocus

Finally, size and position each text box over the top of the
text portion of its corresponding text box.  (Use the Format
- Bring to Front menu item if needed.)

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

- Show quoted text -

Thank you for the prompt response. I am trying to transcrice the
query. Can you explain
"plantFKfield = tblPlant.plantPKfield" ? What do you mean by FK and PK
Field? Is that Primary and Function Field?

 

[xoxavier]

Wow! That makes sense...Thank you for that, it is encouraging. There
is not a lot of blog out there about this.
Let's do it your way. I have read similar explanations with less
clarity. Here is what I have:

cboPlant
Row Source: tblPlant
Column Count:2
Column Widths: 0";1"
After Update: Requery cboProcess
Bound Column:1

cboProcess
Row Source: qry--

SELECT [tblPlant/Process].[Process ID], [tblPlant/Process].Process
FROM [tblPlant/Process]
WHERE ((([tblPlant/Process].[Plant ID])=[Forms]![frmPlantProcess]!
[Plant]))
ORDER BY [tblPlant/Process].[Plant ID] DESC;

Column Count:2
Coulumn Widths:0";1"
After Update: Requery cboEquipment
Bound Column:1

cboEquipment
Row Source: qry--

SELECT [tblProcess/Equipment].[Equipment ID], [tblProcess/
Equipment].Equipment
FROM [tblProcess/Equipment]
WHERE ((([tblProcess/Equipment].[Process ID])=[Forms]!
[frmPlantProcess]![Process]))
ORDER BY [tblProcess/Equipment].[Process ID];

Column Count:2
Coulumn Widths:0";1"
After Update: Requery cboErrorcode
Bound Column:1

cboErrorcode
Row Source:qry--

SELECT [tblEquip/Error].[Error ID], [tblEquip/Error].[Error Code]
FROM [tblEquip/Error]
WHERE ((([tblEquip/Error].[Equipment ID])=[Forms]![frmPlantProcess]!
[cboEquipment]))
ORDER BY [tblEquip/Error].[Equipment ID] DESC;

Column Count:2
Coulumn Widths:0";1"
Bound Column:1

You forgot to post the form's record source query. Lacking
that, I'll just use placeholder names for the names I don't
know.  

The first thing you need to do is modify the form's record
source query so it looks something like:

SELECT T.*,
                                        tblPlant.plantnamefield,
                                        PP.Process,
                                        E.Equipment,
                                        ERR.[Error Code]
FROM (((sometable As T
        LEFT JOIN tblPlant
                ON T.plantFKfield = tblPlant.plantPKfield)
        LEFT JOIN [tblPlant/Process] As PP
                ON T.ProcessFKfield = PP.[Process ID])
        LEFT JOIN [tblProcess/Equipment] As E
                ON T.equipmentFKfield = E.[Equipment ID])
        LEFT JOIN [tblEquip/Error] As ERR
                ON T.equipmenterrorFKfield = ERR.[Error ID]

Run the query by itself to make sure it runs, returns all
the correct values and that all but the last four fields are
updatable.

Assuming all that goes well, then bind the plantname text
box to the plantnamefield, process text box to the Process
field, equipment text box to the Equipment field and the
error code text box to [Error Code]

Set the TabStop property to No for all these text boxes.
Then create a GotFocus event procedure for each text box to
reset the focus to the correspondingcombobox.  For
example, the plant text box's GotFocus event procedure would
be:
        Me.cboPlant.SetFocus

Finally, size and position each text box over the top of the
text portion of its corresponding text box.  (Use the Format
- Bring to Front menu item if needed.)

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

- Show quoted text -

Here is what I have equated your query to:

SELECT T.*,
tblPlant.Plant,
PP.Process,
E.Equipment,
ERR.[Error Code]
FROM (((tblDowntime AS T
LEFT JOIN tblPlant
ON T.Plant = tblPlant.[Plant])
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];

When I run the query, I get "Type mismatch in JOIN expression." I'm so
close!!! What did I miss?

 

[Marshall Barton]

Can you explain
"plantFKfield = tblPlant.plantPKfield" ? What do you mean by FK and PK
Field? Is that Primary and Function Field?

PK is a common abbreviation for Primary Key and FK for
Foreign Key (the index that links to the one side table).

Your form's table is on the many side of a one to many
relationship with the other tables. E.g. tblPlant has one
record for a plant, but the forms table can have many
records for a plant. You are using cboPlant to fill in the
FK field in the form, but I don't know the name of the field
the combo box bound to.

 

[xoxavier]

SELECT T.*,
tblPlant.Plant,
PP.Process,
E.Equipment,
ERR.[Error Code]
FROM (((tblDowntime AS T
LEFT JOIN tblPlant
ON T.Plant = tblPlant.[Plant])
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];

This is what I've equated your response to....When I run it I get
"Type mismatch in JOIN expression"

 

[xoxavier]

SELECT T.*,
                                         tblPlant.Plant,
                                         PP.Process,
                                         E.Equipment,
                                         ERR.[Error Code]
FROM (((tblDowntime AS T
        LEFT JOIN tblPlant
                ON T.Plant = tblPlant.[Plant])
        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];

This is what I've equated your response to....When I run it I get
"Type mismatch in JOIN expression"

Where you have "sometable", what table am I looking for? Is it th
table myu form is bound to or do I create a separate table?

 

[Marshall Barton]

SELECT T.*,
                                         tblPlant.Plant,
                                         PP.Process,
                                         E.Equipment,
                                         ERR.[Error Code]
FROM (((tblDowntime AS T
        LEFT JOIN tblPlant
                ON T.Plant = tblPlant.[Plant])
        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];

This is what I've equated your response to....When I run it I get
"Type mismatch in JOIN expression"

Where you have "sometable", what table am I looking for? Is it th
table my form is bound to or do I create a separate table?

"sometable" is the table used by the form. Apparently, that
is tblDowntime.

Double check that the names of the fields in tblDowntime are
really named Plant, Process, Equipment and [Error Code]

I find those names to be a little misleading because the
fields all contain ID type values (presumably a number or
autonumber).

 

[Marshall Barton]

SELECT T.*,
tblPlant.Plant,
PP.Process,
E.Equipment,
ERR.[Error Code]
FROM (((tblDowntime AS T
LEFT JOIN tblPlant
ON T.Plant = tblPlant.[Plant])
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];

This is what I've equated your response to....When I run it I get
"Type mismatch in JOIN expression"

That implies the Plant, etc. fields are text fields and the
ID fields are numbers or vice versa.

You are not using the evil Lookup field feature in your
table, are you? If you are, set the fields' Lookup control
to text box so you see the real values.

If all this is getting confusing, please post details about
each of those four fields in all five tables.

 

[xoxavier]

SELECT T.*,
                                        tblPlant.Plant,
                                        PP.Process,
                                        E.Equipment,
                                        ERR.[Error Code]
FROM (((tblDowntime AS T
       LEFT JOIN tblPlant
               ON T.Plant = tblPlant.[Plant])
       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];

This is what I've equated your response to....When I run it I get
"Type mismatch in JOIN expression"

That implies the Plant, etc. fields are text fields and the
ID fields are numbers or vice versa.

You are not using the evil Lookup field feature in your
table, are you?  If you are, set the fields' Lookup control
to text box so you see the real values.

If all this is getting confusing, please post details about
each of those four fields in all five tables.

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

- Show quoted text -

Yes, I am using every bit of lookup galore! ouch! Here is my tangled
web as it sits:

tblPlant
PK Plant ID AutoNumber
FK Plant Text

tblPlant/Process
PK Process ID
FK Plant ID
Lookup Combo Box
tblPlant
Bound 1
Count 2
Widths 0,1
Process

tblProcess/Equipment
PK Equipment ID
FK Process ID
Lookup Combo Box
tblPlant/Process
Bound 1
Count 3
Widths 0,0,1
Equipment

tblEquip/Error
PK Error ID
FK Equipment ID
Lookup Combo Box
tblPlant/Process
Bound 1
Count 3
Widths 0,0,1
Error Code

tblDowntime
PK Entry ID AutoNumber
Date txt
Year txt
Week txt
Shift txt
Plant txt
Process txt
Equipment txt
Error Code txt
Details txt
Minutes of Downtime txt
Work Order # txt

 

[xoxavier]

I got it! I got the query to run. When I am in the form however, I am
unable to use my comboboxes, the message I get is that "recordset is
not updatable" I am also unable to creat new records.

 

[Marshall Barton]

SELECT T.*,
                                        tblPlant.Plant,
                                        PP.Process,
                                        E.Equipment,
                                        ERR.[Error Code]
FROM (((tblDowntime AS T
       LEFT JOIN tblPlant
               ON T.Plant = tblPlant.[Plant])
       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];

This is what I've equated your response to....When I run it I get
"Type mismatch in JOIN expression"

That implies the Plant, etc. fields are text fields and the
ID fields are numbers or vice versa.

You are not using the evil Lookup field feature in your
table, are you?  If you are, set the fields' Lookup control
to text box so you see the real values.

If all this is getting confusing, please post details about
each of those four fields in all five tables.

Yes, I am using every bit of lookup galore! ouch! Here is my tangled
web as it sits:

tblPlant
PK Plant ID AutoNumber
FK Plant Text

tblPlant/Process
PK Process ID
FK Plant ID
Lookup Combo Box
tblPlant
Bound 1
Count 2
Widths 0,1
Process

tblProcess/Equipment
PK Equipment ID
FK Process ID
Lookup Combo Box
tblPlant/Process
Bound 1
Count 3
Widths 0,0,1
Equipment

tblEquip/Error
PK Error ID
FK Equipment ID
Lookup Combo Box
tblPlant/Process
Bound 1
Count 3
Widths 0,0,1
Error Code

tblDowntime
PK Entry ID AutoNumber
Date txt
Year txt
Week txt
Shift txt
Plant txt
Process txt
Equipment txt
Error Code txt
Details txt
Minutes of Downtime txt
Work Order # txt

Do yourself a favor and leave the combo boxes for your forms
where they provide a benefit to users. For you to think
clearly about your design, change all those field's lookup
controls from combo box to text box. Then, what you see
will be what you get. The way it is now, you don't know
what you are dealing with.

The table/fields information is well presented, but, except
for the Plant table, you forgot a very important piece. The
datatype of the PK and FK fields is essential because it
affects how some queries are written, how the records can be
sorted and even if they can work at all.

Assuming all of your ID fields are AutoNumber or Long
Integers, let's try using:

SELECT T.*,
tblPlant.Plant,
PP.Process,
E.Equipment,
ERR.[Error 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]

Based on the information you posted, I only change the On
clause in the JOIN to the Plant table. If you still get a
type mismatch error, then clean up the tables as I suggested
above and include each field's Data Type, and for Number
types, the Field Size. Don't just look at a table's sheet
view and guess at what a field's Data Type might be.

 

[John W. Vinson]

I got it! I got the query to run. When I am in the form however, I am
unable to use my comboboxes, the message I get is that "recordset is
not updatable" I am also unable to creat new records.

Xavier, you clearly know what you're talking about but we're not there.

Please read the above message from the viewpoint of someone who knows nothing
more about your application. We're not there; you're not the only person
posting messages here!

Please post a better description, or at least don't change the subject line
and thereby start a new discussion.

John W. Vinson [MVP]

 

[Marshall Barton]

I got it! I got the query to run. When I am in the form however, I am
unable to use my comboboxes, the message I get is that "recordset is
not updatable" I am also unable to creat new records.

Well, I did tell you to run the query all by itself and
check to make sure it is updatable. If it is, then we know
there is something funny with the form or combo boxes. If
the query is not updatable, then we don't have to waste time
on the form.

Also, check the field names in tblDowntime. If the fields
we added for the text boxes have the same name as fields in
the table, we'll have to alias the field names that we
added.

 

[xoxavier]

Xavier, you clearly know what you're talking about but we're not there.

Please read the above message from the viewpoint of someone who knows nothing
more about your application. We're not there; you're not the only person
posting messages here!

Please post a better description, or at least don't change the subject line
and thereby start a new discussion.

             John W. Vinson [MVP]

Understood, forgive me, I'm mew posting. I kept getting an error
message when I tried to respond previously which lead me to change the
subject in order to post. It's my faux pas!

 

[xoxavier]

Well, I did tell you to run the query all by itself and
check to make sure it is updatable.  If it is, then we know
there is something funny with the form orcomboboxes.  If
the query is not updatable, then we don't have to waste time
on the form.

Also, check the field names in tblDowntime.  If the fields
we added for the text boxes have the same name as fields in
the table, we'll have to alias the field names that we
added.

Thanks Marsh!
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...

 

[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 four combo box 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.