Permanent filter on form based on unbound text box on another form

[KyleB]

I'm not sure I can find anything quite like this elsewhere with the search function, following examples in other threads it's like my filter is being completely ignored, gives an error, or shows no records. Here's the situation: This database contains forecast information by month, so upon opening the switchboard there are 2 unbound text boxes. The first text box contains the current month+1, the second contains the current year. One of the control buttons is used to open a form based on a table. I tried using a form based on a query, but this does not allow manipulation of any records, it only shows the existing records.

Instead I thought I'd just grab the data off the table and filter it to show the same information, thereby bypassing the query, and allowing edits. However; I cannot get an automated query to apply to the records. When I open the form it shows all records, and I have to physically click on the Apply Filter button at the top of the screen. I've attempted a couple of different methods of automating the apply filter command via VBA, but I either get errors regarding "|" which it can't recognize, or the filter is ignored completely. Any suggestions? Currently the OnClick option for the form looks like this (this is just the most recent iteration though):

Private Sub Modify_Backfill_Click()
On Error GoTo Err_Modify_Backfill_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frm Modify Backfill Data"
    DoCmd.OpenForm stDocName, acFormDS, , "Month([Backfill Info].[Month]) = " & _
    "'Me.[Month of Forecast]'" & " And " & "Year([Backfill Info].[Month]) = " & _
    "'Me.[Year of Forecast]'"
    
Exit_Modify_Backfill:
    Exit Sub

Err_Modify_Backfill_Click:
    MsgBox Err.Description
    Resume Exit_Modify_Backfill
End Sub

I've also attempted to use the FilterOn option, or the
docmd.applyfilter, "Month([Backfill Info].[Month]) = " & "'Me.[Month of Forecast]'" & " And " & "Year([Backfill Info].[Month]) = " & "'Me.[Year of Forecast]'"
method, but none of them seem to work. I've tried several variations on the procedures to call the text boxes ie: [Forms]![frm Switchboard]![Month of Forecast] etc, but I've got a breakdown in the system somewhere.

All I want to do is open the form (which is in datasheet form) and show the records from the table that have a matching month, and year to what is on the switchboard unbound text boxes. The text boxes on the switchboard are [Month of Forecast] and [Year of Forecast] and the field in the table is [Month] (which actually contains data of this format (09/07/2004) which I format as mmmm\,yyyy). I want to allow modifications/additions/deletions in this form. At present I can add new entries, and then modify or delete them, but the existing data doesn't show up. I'm just not certain what it is that I'm doing wrong. The examples I can find with search all deal with combo boxes, so should work similarly I'd think, but I'm obviously doing something wrong with them.

Kyle

 

[irini]

The problem is where you have your quotes located. The way your quotes are placed, you're specifying the month 'Me.[Month of Forecast]' instead of the value in the field Me.[Month of Forecast]. It should look like this:

DoCmd.OpenForm stDocName, acFormDS, , "Month([Backfill Info].[Month]) = '" & _
Me.[Month of Forecast] & "' And " & "Year([Backfill Info].[Month]) = '" & _
Me.[Year of Forecast] & "'"