Unbound textbox to filter a different form

[Kingwi11y]

Hi

I have a database collecting patient information - its all based on one form called [TRAUMA DATABASE] which uses the table [TRAUMA DATABASE] (Access 2003)
To make it simple enough for surgeons to use, i created a intro form with big buttons called [Intro] - these are seperate forms in the same DB (not subform).
One says "Name search" next to an unbound textbox called "Name_Search"
In the afterupdate VBA i have the following code:

Private Sub Name_Search_AfterUpdate()
Dim stfilter As String
stfilter = stfilter & "([Name] Like ""*" & Me.Name_Search & "*"")"
Forms![TRAUMA DATABASE].Filter = stfilter
Forms![TRAUMA DATABASE].FilterOn = True
End Sub

^^ This is meant to take the value in the textbox and filter the [TRAUMA DATABASE] forms by partial match to the name field in [Name]. [Name] is used in [TRAUMA DATABASE] but not [INTRO]
I get the error message: "Run-time error '2450': Microsoft Office Access can't find the form 'TRAUMA DATABASE' referred to in a macro expression or Visual Basic Code"
The debug takes me back to:
Forms![TRAUMA DATABASE].Filter = stfilter

I tried searching from the [TRAUMA DATABASE] form with a textbox with the following VBA:

Private Sub Name_Search_AfterUpdate()
Dim stfilter As String
stfilter = stfilter & "([Name] Like ""*" & Me.Name_Search & "*"")"
Me.Form.Filter = stfilter
Me.Form.FilterOn = True
End Sub

This works fine so i guess i must just have the wrong syntax at
Forms![TRAUMA DATABASE].Filter = stfilter

I've tried to solve it myself, ive read hours and hours of forums but now im admitting defeat - please can i have some help
Do i have to make one form a subform of the other?
I know i could use queries etc but that seems to be overcomplicating something that should be very simple...

 

[CyberLynx]

Try.....

Private Sub Name_Search_AfterUpdate()
   Dim stfilter As String
   stfilter = stfilter & "([Name] Like '*" & Me.Name_Search & "*')"
   Forms("TRAUMA DATABASE").Form.Filter = stfilter
   Forms("TRAUMA DATABASE").Form.FilterOn = True
End Sub

or work directly with the RecordSource:

Private Sub Name_Search_AfterUpdate()
   Dim StrgSQL As String
   StrgSQL = "SELECT * FROM [TRAUMA DATABASE] WHERE [Name] LIKE '*" & _
                  Me.Name_Search & "*');"
   Forms("TRAUMA DATABASE").RecordSouce = StrgSQL
End Sub

.

 

[Kingwi11y]

Hi

Thanks for your reply, i have tried both these suggestions and i get exactly the same error message - that the form TRAUMA DATABASE cannot be found.

"Run-time error '2450': Microsoft Office Access can't find the form 'TRAUMA DATABASE' referred to in a macro expression or Visual Basic Code"

I do have a form and a source table both by that name - i even checked database properties to make sure i wasnt losing the plot... yes i do definitely have form by that name. Since all 3 attempts at doing VBA code for this have given the same message - maybe my form is known as something else to access?

Thanks in advance, Will

 

[CyberLynx]

The Error code you have provided (2450) can be initiated for several reasons. Te Full Error description is:

Microsoft Access can't find the form '|' referred to in a macro expression or Visual Basic code. The form you referenced may be closed or may not exist in this database.* Microsoft Access may have encountered a compile error in a Visual Basic module for the form.

The '|' shown above would be where the Form Name would be placed.

You need to make sure that none of the conditions explained within the Error Description exist.

- Does the Form actually exist within the database trying to reference it.
- Is the Form you are referencing actually open.

- Does the Form you are referencing contain any Compile Errors. Open the TRAUMA DATABASE Form, enter the VBA IDE to access the Forms' code module. Make sure Option Explicit is located at the top of the Form's Code Module right under the Option Compare Database statement (I'm a very strong believer in properly Declaring Variables rather than letting Access do it. Now, from the IDE Menu select Debug and then Compile yourDBname. Do you get any errors. If so, fix them.

Other than that.....I would need to see this DB.

.

 

[Kingwi11y]

You're brilliant thankyou - the form needed to be open...
I knew it would be something like that - been banging my head against it all day everytime i had a spare moment then read through one of the help bits and read about filter needing the form to be open - tried it, it works, came on here to say thankyou for your help and i find you've already given me the answer yesterday lol
anyway yes thanks - it was as simple as that

 

[CyberLynx]

Sometimes when you have been working particularly hard on a problem, it's best to just stop. Shut it down and sleep on it.

When you wake up and attack the problem again, you'll pick it off in seconds.

I'm glad things worked out for you. Good luck with your project.

.

 

[j500sl]

cyberlynx thanx your comment were helpfull

 

[kyuball]

I know this is quite some time after these posts, but I just wanted to thank everyone who contributed to this thread as it has helped this complete beginner put a really cool and necessary component to a database I am working on! I just wanted everyone to know that the contributions here are really valuable!