AfterUpdate

[JohnLute]

I have 2 combo boxes:
[txtFacilityID]
SELECT tblFacilities.txtFacilityID, tblFacilities.FacilityName,
tblFacilities.City, tblFacilities.StateOrProvince FROM tblFacilities;

[LineID]
SELECT tblFacilitiesLineIDs.LineID, tblFacilitiesLineIDs.Description,
tblFacilitiesLineIDs.txtFacilityID, tblFacilities.FacilityName,
tblFacilities.City, tblFacilities.StateOrProvince FROM tblFacilities INNER
JOIN tblFacilitiesLineIDs ON tblFacilities.txtFacilityID =
tblFacilitiesLineIDs.txtFacilityID;

How can I code the AfterUpdate of [txtFacilityID] so that [LineID] will
display only the [LineID]'s related to the selected [txtFacilityID]?

Whew!

As always - Thanks for your help!

 

[Jeff Boyce]

Take a look at "Cascading Combo-Boxes" as a HELP or a search topic.

If you add a WHERE clause for the second combobox, you can limit the SELECT
to those
WHERE tblFacilities.txtFacilityID = Me![txtFacilityID]

Note: there may be some confusion (Access, you or me?!) on your naming
convention. When you say you have two combo boxes, but give their names as
[txtFacilityID] and [LineID], are you quite sure Access (and you) are
referring to the controls (i.e., the combo boxes), or to the underlying
fields with those names?

Another approach would be to name your combobox controls something like:
cboFacility, and
cboLine

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[JohnLute]

Thanks, Jeff. That seems simple enough. I typically use "cb" as a prefix for
all of my combo boxes but forwhatever reason I didn't follow that "rule" with
these. I revised [LineID] to this:
SELECT tblFacilitiesLineIDs.LineID, tblFacilitiesLineIDs.Description,
tblFacilitiesLineIDs.txtFacilityID, tblFacilities.FacilityName,
tblFacilities.City, tblFacilities.StateOrProvince
FROM tblFacilities INNER JOIN tblFacilitiesLineIDs ON
tblFacilities.txtFacilityID = tblFacilitiesLineIDs.txtFacilityID
WHERE (((tblFacilities.txtFacilityID)=[Me]![cbFacilityID]));

This returns an Enter Parameter Value box that asks for a "Me!cbFacilityID"
value.

???

I've looked through the Help files but as always found nothing.

--
www.Marzetti.com

Take a look at "Cascading Combo-Boxes" as a HELP or a search topic.

If you add a WHERE clause for the second combobox, you can limit the SELECT
to those
WHERE tblFacilities.txtFacilityID = Me![txtFacilityID]

Note: there may be some confusion (Access, you or me?!) on your naming
convention. When you say you have two combo boxes, but give their names as
[txtFacilityID] and [LineID], are you quite sure Access (and you) are
referring to the controls (i.e., the combo boxes), or to the underlying
fields with those names?

Another approach would be to name your combobox controls something like:
cboFacility, and
cboLine

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have 2 combo boxes:
[txtFacilityID]
SELECT tblFacilities.txtFacilityID, tblFacilities.FacilityName,
tblFacilities.City, tblFacilities.StateOrProvince FROM tblFacilities;

[LineID]
SELECT tblFacilitiesLineIDs.LineID, tblFacilitiesLineIDs.Description,
tblFacilitiesLineIDs.txtFacilityID, tblFacilities.FacilityName,
tblFacilities.City, tblFacilities.StateOrProvince FROM tblFacilities INNER
JOIN tblFacilitiesLineIDs ON tblFacilities.txtFacilityID =
tblFacilitiesLineIDs.txtFacilityID;

How can I code the AfterUpdate of [txtFacilityID] so that [LineID] will
display only the [LineID]'s related to the selected [txtFacilityID]?

Whew!

As always - Thanks for your help!

 

[Jeff Boyce]

You will get that parameter prompt if Access doesn't find the form/control
EXACTLY as you've spelled it. This also means that if the form is not open,
Access can't see the control (your "cbFacilityID").

Note ... you will need to requery the second combo box in the AfterUpdate
event of the first. The English translation is roughly: "take another look
at the source in combobox2"

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks, Jeff. That seems simple enough. I typically use "cb" as a prefix
for
all of my combo boxes but forwhatever reason I didn't follow that "rule"
with
these. I revised [LineID] to this:
SELECT tblFacilitiesLineIDs.LineID, tblFacilitiesLineIDs.Description,
tblFacilitiesLineIDs.txtFacilityID, tblFacilities.FacilityName,
tblFacilities.City, tblFacilities.StateOrProvince
FROM tblFacilities INNER JOIN tblFacilitiesLineIDs ON
tblFacilities.txtFacilityID = tblFacilitiesLineIDs.txtFacilityID
WHERE (((tblFacilities.txtFacilityID)=[Me]![cbFacilityID]));

This returns an Enter Parameter Value box that asks for a
"Me!cbFacilityID"
value.

???

I've looked through the Help files but as always found nothing.

--
www.Marzetti.com

Take a look at "Cascading Combo-Boxes" as a HELP or a search topic.

If you add a WHERE clause for the second combobox, you can limit the
SELECT
to those
WHERE tblFacilities.txtFacilityID = Me![txtFacilityID]

Note: there may be some confusion (Access, you or me?!) on your naming
convention. When you say you have two combo boxes, but give their names
as
[txtFacilityID] and [LineID], are you quite sure Access (and you) are
referring to the controls (i.e., the combo boxes), or to the underlying
fields with those names?

Another approach would be to name your combobox controls something like:
cboFacility, and
cboLine

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have 2 combo boxes:
[txtFacilityID]
SELECT tblFacilities.txtFacilityID, tblFacilities.FacilityName,
tblFacilities.City, tblFacilities.StateOrProvince FROM tblFacilities;

[LineID]
SELECT tblFacilitiesLineIDs.LineID, tblFacilitiesLineIDs.Description,
tblFacilitiesLineIDs.txtFacilityID, tblFacilities.FacilityName,
tblFacilities.City, tblFacilities.StateOrProvince FROM tblFacilities
INNER
JOIN tblFacilitiesLineIDs ON tblFacilities.txtFacilityID =
tblFacilitiesLineIDs.txtFacilityID;

How can I code the AfterUpdate of [txtFacilityID] so that [LineID] will
display only the [LineID]'s related to the selected [txtFacilityID]?

Whew!

As always - Thanks for your help!