Requery not working (again)

[togo]

So I have one combo box at top of form that controls job number



for each job there may be multiple reports, a combo box below that is used to select report, then a button is pushed to open that report for editing.

I have other databases where this works fine. The report combo box contains the following SQL in the row source:

SELECT DISTINCT EXAMINATIONREPORT.ReportNo FROM EXAMINATIONREPORT WHERE examinationreport.jobnumber = jobnumber;

In the first combo box there is the following code:

DBEngine.Idle dbRefreshCache
Form_MPI!ReportController.Requery
Me.ReportController.RowSource = Me.ReportController.RowSource
Me.ReportController.Requery
Me.Requery
Me!ReportController.Requery
Set MPIreports = Forms!MPI!ReportController
MPIreports.Requery
Me.Refresh
Me.Repaint

So as you can see, up against the wall on this one. It just WILL NOT requery itself, since each job number has different numbers of reports or no reports at all, this combo box will change each time the job changes. The form is called MPI, but in its history it may have been renamed at one point, don't remember. I don't get why this method of selecting reports works on other forms but not this one. Thanks for any help.

[rpeare]

That's a whole lot of refreshing. Is your combo box pulling information that changes over time and you're trying to get the most recent information, or is it based on a fairly static list of items?

If you're trying to retrieve a most recent list of items you'd likey need to do a

me.refresh

BEFORE you requeried the combo box

Is that the code that existed before you attempted anything because you shouldn't need all that. ANd where does that code run, is it in the AFTER UPDATE or ON EXIT event of another field and you're asking it to update the secondary combo box (reportcontroller)?

[pbaldy]

This SQL:

SELECT DISTINCT EXAMINATIONREPORT.ReportNo FROM EXAMINATIONREPORT WHERE examinationreport.jobnumber = jobnumber;

is basically referring to itself, so I suspect you're getting all the records? To use Requery, you'd need to refer to a form or someplace for the value to filter to, like:

SELECT DISTINCT EXAMINATIONREPORT.ReportNo FROM EXAMINATIONREPORT WHERE examinationreport.jobnumber = Forms!FormName.jobnumber;

[istari88]

I think this is good
DBEngine.Idle dbRefreshCache 'event
Form_MPI!ReportController.Requery 'requery 1st commbobox


Not Needed...
Me.ReportController.RowSource = Me.ReportController.RowSource
Me.ReportController.Requery
Me.Requery
Me!ReportController.Requery
Set MPIreports = Forms!MPI!ReportController
MPIreports.Requery
Me.Refresh
Me.Repaint

Then Reference the 1st comboBox name in the record source of the 2nd.
SELECT DISTINCT EXAMINATIONREPORT.ReportNo FROM EXAMINATIONREPORT WHERE examinationreport.jobnumber = Forms!MPI.1stComboBoxName;

good luck
Phil

[togo]

Hi been very busy with related projects sorry for late response. It looks like someone deleted the source table that the combo box referred to. This might explain why every other combo box update situation works but this one didn't. If the problem occurs again I'll resurrect the thread.