asked on

Access Combo Re-Query

Access - need combo box to re-query . The form frm_MAIN_TechIDs has a combo box Combo46. (continuation of prior questions)
It shows a list of all the TechIDs that have a PM associated.
This form is where I associate PMs with the TechIDs.
However,
When I add a NEW TechID it does not show up in this list - I assume because it does not have a PM associated with it yet.
Is there a way to get this combo list to show TechIDs that do not have a PM associated yet?
The Combo already has an Embedded Macro associated with 'After Update'
I can't get the combo to show the NEW TechIDs that do not have a PM associated.
PM_Schedules.accdb

Paul Cook-Giles

Yes; you'd need to make two changes:

1) change the RowSource from a query that returns only matches between the tbl_TechIDS and tbl_TechPMLink tables to a query that returns all rows from tbl_TechIDS and any matches in the tbl_TechPMLink table. You can do that by double-clicking the link between the two tables and choosing the second option.

2) add code to refresh the data in the dropdown when you click inside the combo box: In the GotFocus event, add

Me.Combo46.RowSource = Me.Combo46.RowSource

Let us know how that works! :)

ssblue

ASKER

It does show the NEW TechID but the associated PMs are getting all mixed up in the subforms?

Paul Cook-Giles

I'm not sure exactly what 'the associated PMs are getting all mixed up in the subforms' means... I don't see any RecordSource properties on the subforms that are dependant on Combo46. Changing the RecordSource for an unbound field shouldn't affect any field (or subform) that isn't tied to the unbound field.

Can you tell me a bit more about what's happening?

Paul Cook-Giles

Wait... did you change the RowSource for Combo46, or for the frm_MAIN_TechID form?

ssblue

ASKER

I changed the RowSource for Combo46. When I click on the combo and select the NEW TechID the subforms are showing PMs associated with the last selection and I have to select a PM and then close and reopen before it will clear the PMs not associated.
example: if I open the form it show a TechID and the associated PMs. If I then click the down arrow to find my NEW TechID and select it the PMs that were associated with the previous TechID appears to be associated with my NEW TechID. I then have to select a PM and then close the form and then reopen before it shows the correct association.

ASKER CERTIFIED SOLUTION

Paul Cook-Giles

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Tsioumpris

You just need to change the RowSource of the combo46 to a Left Join

SELECT tbl_TechIDS.EQ_ID, tbl_TechIDS.TechID, tbl_TechIDS.AssetName
FROM tbl_TechIDS LEFT JOIN tbl_TechPMLink ON tbl_TechIDS.EQ_ID = tbl_TechPMLink.EQ_ID_FK
ORDER BY tbl_TechIDS.TechID;

Open in new window

PatHartman

The problem is caused because continuous forms and DS forms are simply multiple instances of a single form. That means that there is only a single set of properties and the RowSource of a combo is a property of the combo. I've attached a sample database that shows this problem using cities and states and offers a solution. The solution ONLY works in a continuous form because it relies on stacked controls. I believe that there might be a different solution that works on DS forms.
FixCascadingCombos.zip

ssblue

ASKER

Thanks for the info and suggestions. The Left Join doesn't work in this case. I don't fully understand why but based on the information and suggestions given I was able to create a form to create the initial link which works with existing forms, so everything is working as expected. Probably not the best way to do things but it's working and I'm learning.
Thanks again to everyone.

Paul Cook-Giles

Excellent-- glad we could be of assistance. :)