Advice on forms (1 Viewer)

[Scott488]

Hi All,

Still fairly new to Access but slowly learning.

I have a form with a built in search function and would like to place this in a navigation form. The search works ok on the form, but when I add it to the navigation form it doesn't.

The search is done by a macro on a command button:

[FirstName] Like "*" & [Forms]![SearchForm]![Text17] & "*" Or [SurName] Like "*" & [Forms]![SearchForm]![Text17] & "*" Or [Address] Like "*" & [Forms]![SearchForm]![Text17] & "*"

When I move it to the navigation form, I believe I have to change the references for the search but now matter how I try I can not get this to work.

Could someone please point me in the correct direction, been trying to solve this (probably simple) problems most of the day

 

[BigHappyDaddy]

So a question to clarify your intent:
When you say "...place this in a navigation form", are you trying to insert the original form (SearchForm) as a subform into the navigation form, or are you trying to replicate the search functionality on the navigation form?

 

[Scott488]

Hi BigHappyDaddy,

A simple drag it into the navigation form when in the layout view, does this make it a subform?

 

[BigHappyDaddy]

Ok, sounds like a subform. So I am assuming that this subform still includes the text box control (named Text17) and the command button. I am also assuming the results are based on applying your criteria as a filter.

Ok next, please explain what you mean by "can not get this to work". Incorrect results, no results, error message?

Is this search completed by applying a filter?

 

[Scott488]

The subform is exactly as it is prior to being moved into the navigation form.

It still includes the text box control (named Text17) and the command button, but when using these, the following message is displayed.

"The action or method is invalid because the form or report isn't bound to a table or query"

If I understand correctly, the macro needs to refer to this subform on the navigation form, but I have not been able to get this to work.

Along the lines of:

[FirstName] Like "*" & [Forms]![Navigation Form]![NavigationSubform]![SearchForm]![Text17] & "*"


Is there any way of doing this in code? A macro is limited to 255 characters so it may be best to write this in code to ensure that any increase in search parameters can be handled.

 

[BigHappyDaddy]

The error is being generated because you are trying to apply a filter to a form that doesn't have any data attached to it (it other words, the form is unbound). So either the error is being generated because you are trying to apply the filter to the parent form (navigation form) or the underlying data on the search form was removed. Ensure that the record source for the search form is still valid.

Second, if the search form is truly a child form of the navigation form and nothing has changed, then the filter references shouldn't have to change either.

 

[BigHappyDaddy]

Sorry, I just saw your line about doing this in code.

It is my preference to do all this in code instead of macros. There are many advantages of doing this in code over macros.

If you are able to post a copy of your database, I would be willing to take a look at it. Please remove any confidential / personal date prior to posting.

 

[Scott488]

I've attached a test database that I have been trying this with at home as the full database is at work.

Thanks for looking at this

 

[BigHappyDaddy]

See attached.

You were right and your reference was incorrect.

I did two things, converted the macro to code and simplified the reference. See code below:

Private Sub Search_Click()
   Dim strFilter As String
   
   strFilter = "FirstName Like '*" & Me!Text17 & "*' " & _
               "OR SurName Like '*" & Me!Text17 & "*' " & _
               "OR Address Like '*" & Me!Text17 & "*'"
   Me.Filter = strFilter
   
   Me.FilterOn = True
   
End Sub

The use of the reserved word Me is an implicit reference to the form containing the executing code. This code is part of the Search form. Even though the form is a subform of Navigation, the implicit reference allows this to work if either as a stand only form, or as a subform.

Take a look at it and let me know if you have any other questions.

 

[Scott488]

BigHappyDaddy,

You are a superstar, that is just what I was after. Thank you very much.

And thanks for posting the code, I'll copy this over to the database at work and try it there.

 

[BigHappyDaddy]

My pleasure

Sent from my SM-G900P using Tapatalk