Forms filtering question

[Buckmac]

Hi, I'm a bit of a beginner at creating complex forms in Access. I've dabbled a bit over the years, and now need to create a fairly comples (for me) db.

I've built all the tables and their relationships and they appear to be good. I've populated with a limited amount of dummy data for testing purposes and am in the process of building my forms.

I have run into a bit of a brick wall. I have a form with several command buttons on. These will open 'second level' forms with further information on them. The whole thing is concerned with managing projects - and one of the key fields is called ProjID and is the key for a table called ProjectNames.

In the event for the button I have the following:

Private Sub FilterChildForm21()

If Me.NewRecord Then
Forms![Funding].DataEntry = True

Else
Forms![Funding].Filter = "[ProjID] = " & Me.[ProjID]
Forms![Funding].FilterOn = True
End If

The form I want to open is called Funding. I am trying to set the filter to be ProjID = ProjID where the second ProjID comes from the main table ProjectNames which is part of the query behind the form. I can add this value into a text box and it displays correctly! However, when I get to the line

Forms![Funding].Filter = "[ProjID] = " & Me.[ProjID]

I get the following error:

Microsoft Access cannot find the field "|" referred to in your expression.

Any ideas what the (no doubt) stupid mistake that I am making is???

Thanks in advance, regards, Chris B

End Sub

 

[CJBIRKIN]

Hi

Having concocted a quick test using the forms wizard 2 things strike me.

1: the sub form must be open before filtering.

If ChildFormIsOpen() Then
        CloseChildForm
    Else
        OpenChildForm <--------- HERE!
        FilterChildForm
    End If

Yours would be

If Me.NewRecord Then
Forms![Funding].DataEntry = True

Else
DoCmd.OpenForm "Funding"
Forms![Funding].Filter = "[ProjID] = " & Me.[ProjID]
Forms![Funding].FilterOn = True
End If

2) the code

 Forms![TBL-BEDS].Filter = "[UNIT-ID-FK] = " & Me![UNIT-ID]

uses an ! after me rather than the dot that you use Me.[ProjID]


You should check all your spellings as well.

Hope that helps

Chris