Use InputBox to Open Form/Locate and open Record

[AccessHelp32]

Hi All,

I have a command button that opens an InputBox. I would like users to enter a string and have the InputBox open a Form and go to the record that matches the users string most closely. I'm using the following code with no luck:

 Private Sub Command3_Click()
    Dim Search As String, Message As String
    Search = "Find Program"
    Message = InputBox(Prompt, "Search")
    DoCmd.OpenForm "Programs", acNormal, , "Left(Program,10) = 'Message'"
End Sub

Where Programs is a Form and Program is a field in the form.

[nico5038]

I don't like an inputbox as it's resulting in nothing when I make a typo and I have to start typing the whole string again.
When you have a limited set of ID's it's better to create a combobox with the wizard that looks up a value on the form. (Third option when the wizards starts)
Then the entered value is also autoappended with existing values...

When it's a "LIKE" search you can use the filter option of the form to select the set of matching rows.

Idea ?

Nic;o)

[AccessHelp32]

I don't like an inputbox as it's resulting in nothing when I make a typo and I have to start typing the whole string again.
When you have a limited set of ID's it's better to create a combobox with the wizard that looks up a value on the form. (Third option when the wizards starts)
Then the entered value is also autoappended with existing values...

When it's a "LIKE" search you can use the filter option of the form to select the set of matching rows.

Idea ?

Nic;o)

I like the idea, but I'm concerned because I may not have a limited number of ID's. If all goes well, this will be a temporary fix until the new Computer System goes live. However, as with all new systems, I expect delays, problems etc. - in which case this will be more than a temporary fix indefinitely. Is there another option to get this to work.

[nico5038]

Try then an additional textbox on the form, e.g. named txtFilter and add a button [Search]
Code in the OnClick event of the button:

' test a string is found
IF len(nz(me.txtFi lter)) > 0 then
' set a filter on the form
me.filter = "[fieldname to search] like *" & me.txtfilter & "*"
me.filteron = true
else
' "remove" the filter by deactivation
me.filteron = false
endif

Getting the idea ?

Nic;o)

[AccessHelp32]

Try then an additional textbox on the form, e.g. named txtFilter and add a button [Search]
Code in the OnClick event of the button:

' test a string is found
IF len(nz(me.txtFi lter)) > 0 then
' set a filter on the form
me.filter = "[fieldname to search] like *" & me.txtfilter & "*"
me.filteron = true
else
' "remove" the filter by deactivation
me.filteron = false
endif

Getting the idea ?

Nic;o)

I think this is the solution! However, my [fieldname to search] is a textbox (bound to a table). When I use the code above, I get no results and I suspect it's because the form is fitering an empty textbox. Do I need to point the code to the table holding all the records of [fieldname to search]?, so the code searches the table for "Like [fieldname to search]" and loads the Form with the complete Record of "Like [fieldname to search]"?

[nico5038]

The form needs to be bound to the table (or a query) and the textfield you're filtering needs to be on the form and in the form's recordsource.

Nic;o)

[AccessHelp32]

The form needs to be bound to the table (or a query) and the textfield you're filtering needs to be on the form and in the form's recordsource.

Nic;o)

So I have the Form [Programs] bound to table [Program]. Both the Form and the Table contain 3 fields: Program ID, Program and Program Desc. I'm trying to filter the field Program using the txtFilter I just added to the Form. The control source of the Form [Program] field says Program. Does this sound right?

Once I'm given the idea, I can generally make things work, as you have successfully given me an idea before (Thanks, by the way!). But for some reason, I can't get this to work. The filter finds no records and jumps to create a new one.

[nico5038]

The field to use as a filter needs to be "unbound", so add a new field you don't "bind" to a table field !

Nic;o)

[NeoPa]

I don't want to interfere too much here but I wanted to point out a couple of basic miscodings in the first post.
I'm hoping that this might trigger a better understanding on your part and get you moving in the right direction.

Private Sub Command3_Click()
    Dim Search As String, Message As String
    Search = "Find Program"
    Message = InputBox(Prompt, "Search")
    DoCmd.OpenForm "Programs", acNormal, , "Left(Program,10) = 'Message'"
End Sub

should have read something like :

Private Sub Command3_Click()
    Dim Search As String, Message As String

    Search = "Find Program"
    Message = InputBox(Prompt:=Search)
    DoCmd.OpenForm "Programs", acNormal, , "[Program] Like '" & Message & "*'"
End Sub

[AccessHelp32]

I don't want to interfere too much here but I wanted to point out a couple of basic miscodings in the first post.
I'm hoping that this might trigger a better understanding on your part and get you moving in the right direction.

should have read something like :

Private Sub Command3_Click()
    Dim Search As String, Message As String

    Search = "Find Program"
    Message = InputBox(Prompt:=Search)
    DoCmd.OpenForm "Programs", acNormal, , "[Program] Like '" & Message & "*'"
End Sub

Nico, Neopa,

Thanks for the input, both of you. I just figured out both solutions! I plan to use both solutions in various parts of my DB just to gain experience with the code.

Neopa, I'm glad to see that my syntax wasn't too far off in my original post =).

[NeoPa]

;)
It was a good starting point to move forward from.