Open a form to search for a record using a command button which prompts input box

[zztuf]

I have a main form called 'Main'
I have a table called 'Instruments' (one of those rows is called TAG, contains strings)
and I have a form to display all the records of Instruments table.

I'm trying to do the following:

Have a button on Main form.
When click on it, display an input box to search for instruments TAG.
and opens the form Instrumentsform with all records of Instruments that have in their TAG what I had inputted.

I search on the internet, but I found nothing that actually works.

[BHo15]

You could use something like this for your code

Dim strTag as String[INDENT]strTag = InputBox("Which Tag would you like to search for?"
[/INDENT]

Then you could apply a filter to your form using strTag (see https://msdn.microsoft.com/en-us/lib.../ff194672.aspx) for a start.

Hope it goes well.

[zztuf]

Dim strTag As String
    strTag = InputBox("TAG Name?")
        Me.Filter = strTag
        Me.FilterOn = True
    DoCmd.OpenForm "InstrumentsForm", acNormal, , "TAG = '" & strTag & "'"

Now I just have to find out, when I type only a part of the tag, I have no results. I have to type de hole TAG exactly.

But other than that, It works like a charm.
A big thank you !!!

[BHo15]

Glad it partially worked for you.

Here are a couple of thoughts...

1) If your button is not trying to filter the existing form, but instead trying to filter a form that you are going to open, then you don't need

  Me.Filter = strTag
  Me.FilterOn = True

The rest of it would handle that process fine.

2) If you want to filter on a partial result, you could use something like this for your Open Form statement...

 DoCmd.OpenForm "InstrumentsForm", acNormal, , "TAG Like '" & strTag & "*'"

I will say though, that you probably don't want to do that unless the form is a continuous form or a datasheet.

[zmbd]

As you have discovered, the problem with this approach is that the user must have some knowledge of the "tag's" value(s).

It would be a better approach to use something like:
home > topics > microsoft access / vba > insights > example filtering on a form

and a related method:
home > topics > microsoft access / vba > insights > cascading combo/list boxes

even a simple combobox with a rowsource based on the current data set would be easier for the enduser.


Next, it sounds as if your database isn't normalized:
home > topics > microsoft access / vba > insights > database normalization and table structures

Normalization is your friend, here's a recent example of the pitfalls a non-normalized database can encounter and one possible fix: home > topics > microsoft access / vba > questions > moving between records in a form is slow in certain combo boxes>Post8