filter "on load"

[boblarson]

The Area field is not in the forms record source table. is this the problem?

Well that would be part of it. How do you know what records are for what area? Is there anything anywhere which would let you know that?

 

[mmmk]

The form is based off the Events table, The Events table is the main table in my DB which has tables, suchas Area, related to it. The Event table holds all the records related to one event and uniquely identifies with an event_ID. Only the Area_ID is kept in the Events table, which then references the Area table for the name. I hope this answers your question.

 

[boblarson]

The form is based off the Events table, The Events table is the main table in my DB which has tables, suchas Area, related to it. The Event table holds all the records related to one event and uniquely identifies with an event_ID. Only the Area_ID is kept in the Events table, which then references the Area table for the name. I hope this answers your question.

So, in order for you to use the code you have posted as trying out, you would need to include the Area table in the query with the link on Area_ID to the Events table (I'm guessing that is the table the form is based on currently) and then you can use the name for the criteria. But if you have Area_ID in the form's criteria, you could use the code but modified to use Area_ID instead. But I hate to see this "hard-coding" of things where the example database I gave you, could do it all dynamically without having to hard code anything in the vba. For example, what if you add a department? In my example you would just add a table entry. In yours you would need to go modify code.

 

[mmmk]

Thanks Bob! I am going to work on another version of my DB and set it up using your advice. For now, how would I modify my code to get this filter to work? He is what I have:

Private Sub Form_Load()
Dim StrFilter As String
StrStatus = "Ops"
StrFilter = "[Area] <> '" & StrStatus & "'"
Me.Filter = StrFilter
FilterOn = True

End Sub

Thanks!

 

[boblarson]

For now, how would I modify my code to get this filter to work? He is what I have:

Private Sub Form_Load()
Dim StrFilter As String
StrStatus = "Ops"
StrFilter = "[Area.Area] <> '" & StrStatus & "'"
Me.Filter = StrFilter
FilterOn = True

End Sub

I really can't tell you how you would modify that to work. I would really need to see the database itself in order to give you a definitive answer. I can say that using [Area.Area] is definitely WRONG and should just be [Area] <> ...etc. But since I can't see your form or it's record source I can't tell you if that will work or not.