By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,155 Members | 2,518 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,155 IT Pros & Developers. It's quick & easy.

Use InputBox to Open Form/Locate and open Record

P: 20
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:

Expand|Select|Wrap|Line Numbers
  1.  Private Sub Command3_Click()
  2.     Dim Search As String, Message As String
  3.     Search = "Find Program"
  4.     Message = InputBox(Prompt, "Search")
  5.     DoCmd.OpenForm "Programs", acNormal, , "Left(Program,10) = 'Message'"
  6. End Sub
Where Programs is a Form and Program is a field in the form.
Dec 29 '06 #1
Share this Question
Share on Google+
10 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Dec 29 '06 #2

P: 20
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.
Dec 29 '06 #3

nico5038
Expert 2.5K+
P: 3,072
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.txtFilter)) > 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)
Dec 29 '06 #4

P: 20
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.txtFilter)) > 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]"?
Dec 30 '06 #5

nico5038
Expert 2.5K+
P: 3,072
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)
Dec 30 '06 #6

P: 20
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.
Dec 30 '06 #7

nico5038
Expert 2.5K+
P: 3,072
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)
Dec 30 '06 #8

NeoPa
Expert Mod 15k+
P: 31,616
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command3_Click()
  2.     Dim Search As String, Message As String
  3.     Search = "Find Program"
  4.     Message = InputBox(Prompt, "Search")
  5.     DoCmd.OpenForm "Programs", acNormal, , "Left(Program,10) = 'Message'"
  6. End Sub
should have read something like :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command3_Click()
  2.     Dim Search As String, Message As String
  3.  
  4.     Search = "Find Program"
  5.     Message = InputBox(Prompt:=Search)
  6.     DoCmd.OpenForm "Programs", acNormal, , "[Program] Like '" & Message & "*'"
  7. End Sub
Dec 30 '06 #9

P: 20
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command3_Click()
  2.     Dim Search As String, Message As String
  3.  
  4.     Search = "Find Program"
  5.     Message = InputBox(Prompt:=Search)
  6.     DoCmd.OpenForm "Programs", acNormal, , "[Program] Like '" & Message & "*'"
  7. 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 =).
Dec 31 '06 #10

NeoPa
Expert Mod 15k+
P: 31,616
;)
It was a good starting point to move forward from.
Dec 31 '06 #11

Post your reply

Sign in to post your reply or Sign up for a free account.