prompt user for a field

[maxmangion]

i have a Search button on a form which uses the "find what" dialog box of MS Access. By default Access adds the following line:
Screen.PreviousControl.SetFocus

Now instead of that line i am creating an input box which asks the user to type in the field name he wants to search in and then set the focus on that field.

e.g.

InputBox("Which field would you like to search" & vbcrlf & "Author" & vbcrlf & "email", "Searching")

Now in this manner i have to make the above statement:

if inputbox.... = "Author" then
Author.SetFocus
else
Email.SetFocus
end if

the above method is fine for a few fields, but if the if i would have about 20 fields it would be really tedious doing it in the above method.

is there a way that i can read the input from the input box and then set the userinput.setfocus?

thank you

 

[GrahamB]

Try

dim variablename as string

variablename = inputbox("Enter fieldname")

Form.Controls(variablename).SetFocus

 

[R6Flyer]

I would be more tempted to create an option group on your form containing the names of all of the fields you want to search by (there is a limit to 20 options in Access). The user could then click on the option referring to the field that they want to search on and you could include code on your command button along the lines of:

select case OptionGroupName
Case Is
Me.relevantfieldname.SetFocus
Case Is
...
End Select

Then continue with your existing code.

The danger of using Graham's solution is that the user must type the field name exactly the same as you have named it in the form. Failure to do so would result in an error message.

 

[GrahamB]

Yes but I gave him what he asked for

You can always use some error handling routine to check names...

Also could have a popup form that has a combo box on it containing all the names of the search fields and get around it that way