How to requery a listbox that is using a row source (1 Viewer)

[Lkwdmntr]

I am filtering a form based on the value of a textbox and when I click the next button it queries the main form and I'm trying to query the list box as well, but it is not working. There is no control source on the box, but there is a row source. Any suggestions. I tried to just query the list box but nothing happens.

I also get an error that reads "Field 'DOW' is based on an expression and cannot be edited. Once I click ok, it does the editing and changes the DOW . It looks like it is requiring the main form, but not the list box.

Here is the code for the function that is performed when I hit the next button or the back button.

Function mfNavigateMealDays(strDirection As String) As Boolean
On Error GoTo Err_PROC
'strDirection is either Next or back

    Dim intDOW As Integer, strCriteria As String
    intDOW = Me.txtDOW

    Select Case strDirection
        Case "Next"
           If intDOW = 1 Then
                Me.cmdBack.Enabled = True
            End If
           strCriteria = "[DOW] = " & (intDOW + 1)
           Me.Filter = strCriteria
           Me.FilterOn = True
            If intDOW + 1 = 7 Then
                Me.cmdNext.Enabled = False
                Me.CompleteWeekbtn.Enabled = True
            Else
                Me.cmdNext.Enabled = True
                Me.txtDOW = intDOW + 1
                Me.Requery
                Me.lstWeeklyMeals.Requery
            End If
          
        Case "Back"
            If intDOW = 2 Then
                Me.cmdBack.Enabled = False
            Else
                Me.cmdBack.Enabled = True
                Me.txtDOW = Me.txtDOW - 1
                Me.Requery
                Me.lstWeeklyMeals.Requery
            End If
            
            strCriteria = "[DOW] = " & (intDOW - 1)
            Me.cmdNext.Enabled = True
            Me.Filter = strCriteria
            Me.FilterOn = True
        
        Case Else
    End Select    
    
Exit_PROC:
    Exit Function

Err_PROC:
    MsgBox Error$
    Resume Exit_PROC
End Function

 

[theDBguy]

Have you stepped through your code? You're only requerying the listbox on certain conditions, looks like. Have you verified you're meeting those conditions? Just curious... What is the Row Source of your Listbox?

 

[Lkwdmntr]

Have you stepped through your code? You're only requerying the listbox on certain conditions, looks like. Have you verified you're meeting those conditions? Just curious... What is the Row Source of your Listbox?

Yes, I have and it seems to be hitting an error on "Me.Filter = strCriteria". The row source is a query. The SQL for the query is

SELECT tblActionsPerWeeklyPlan.ActionsPerWeeklyPlanID, tblActions.Action AS Meal, Format([StartTime],"h:nn AM/PM") AS [Time], tblChallenges.ChallengeDate AS [Meal Date]
FROM (tblActionCategories INNER JOIN tblActions ON tblActionCategories.ActionCategoryKey = tblActions.ActionCategoryFK) INNER JOIN (tblActionsPerWeeklyPlan INNER JOIN tblChallenges ON tblActionsPerWeeklyPlan.ActionsPerWeeklyPlanID = tblChallenges.ActionsPerWeeklyPlanIDFK) ON tblActions.ActionID = tblActionsPerWeeklyPlan.ActionIDFK
WHERE (((tblChallenges.ChallengeDate)=[Forms]![frmMealPlans]![Meal Date]) AND ((tblActions.ActionCategoryFK)="Meals") AND ((tblActionsPerWeeklyPlan.WeeklyPlanIDFK)=[Forms]![frmSelectWeektoCreateMealPlan]![txtWeeklyPlanID]) AND ((tblActionsPerWeeklyPlan.ActionIncluded)=True))
ORDER BY tblActions.Index;

Not sure if that's what you asked for, but there it is. This is boggling because the date changes like the main form is being required.

 

[theDBguy]

...it seems to be hitting an error on "Me.Filter = strCriteria".

Did you do a Debug.Print strCriteria to verify if it is a valid WhereCondition clause?

 

[Lkwdmntr]

Did you do a Debug.Print strCriteria to verify if it is a valid WhereCondition clause?

I checked and the calculations are going through. strCriteria is getting the proper values.

 

[theDBguy]

I checked and the calculations are going through. strCriteria is getting the proper values.

I guess the next step is to share your db, so we can help you troubleshoot it.

 

[Lkwdmntr]

I'm not sure I can get the size down. What is the limit? Right now it's at 3,843 KB zipped.

 

[theDBguy]

I'm not sure I can get the size down. What is the limit? Right now it's at 3,843 KB zipped.

You only need to include all the objects necessary to duplicate the problem.