subform refresh after combo update for "<ALL>"

[MickM]

Hi All,

This is driving me nuts!!

I have a form with a combo box that populates a subform with data equal to the combo box value. I have added "<ALL>" to the combo box to display all records if selected. The after update code changes the queryDefs. The problem I have is that when "<ALL>" is selected, no records show. But the query has updated. If I close the main form and reopen it all the records show, but if I select a new value nothing happpens. But the actual query has updated. I have tried many combinations of refresh, repaint...etc.
Anyway, here is the code, any help appreciated.


Private Sub cboFeedMtrl_AfterUpdate()

Dim qryAllMat As QueryDef
Dim sql As String
Set dbsCurrent = CurrentDb
Set qryAllMat = dbsCurrent.QueryDefs("qryViewData")

If Me![cboFeedMtrl].Value = "<ALL>" Then
qryAllMat.sql = "SELECT * FROM tblMachineSelection"
Me![frmViewData].Requery


Else
qryAllMat.sql = "SELECT * FROM tblMachineSelection where [feed Material] = [Forms]![frmView]![cboFeedMtrl]"
Me![frmViewData].Requery


End If


End Sub

 

[WayneRyan]

MickM,

Do a "qryAllMat.Close" before requerying.

Wayne

 

[MickM]

Thanks Wayne, but still doing the same.
If I open the main form and select any value except <ALL> it works and filters for that value. If I select <ALL>, then no records show.If I then close the main form and reopen it, All the records show. But if I then select a record other than <ALL> nothing happens and All records still show.

After each update I can open the subform on its own and it shows records that match the combo box including <ALL> records. It is like a refresh problem on the subform.

Thanks again

 

[MickM]

Still no luck. Maybe there is a better way.
What I am trying to do is have a form with 4 comboboxes which filter the subform on the afterupdate function for 4 of the fields. Therefore, the user can select any combination of values (each box to have an <ALL> option) and the results will display on the afterupdate when any box is updated. If <ALL> is selected in the 4 boxes then ALL records will show.
Thanks again

 

[WayneRyan]

Mick,

Can you post a sample?

Remove unwanted stuff,
Tools --> Database Utilities --> Compact/Repair,
Then ZIP & attach.

Wayne

 

[MickM]

Thanks Wayne,

Here is a sample. Note I have only set up one combo box so far.

Thanks again

 

[boblarson]

I think you were trying to complicate things too much. Here's the db attached with changes I made and now it works.

 

[MickM]

Brilliant Bob!!

This is the best forum ever. saved me from a straight jacket.
Now go and buy yourself a beer, cheers

MickM

 

[Janjang]

oh great, this is what im looking for !! cheers

 

[Janjang]

i try and follow everything from sample that wayne gave. but it not working for mine, subform not update.
i hope any expert will help me to find out what is my mistake. attached my test program.

i want to update the subform list by selecting Date from form,

table use - tblDailyDetails_Deliver
Query - QDailyDetails_Deliver
From - FormCalendarTest
Subform - subDailyDetails_Deliver