search for part of a business and display all results on demand

[Guest_imported]

What i want to do is have a search function come up so that i can search for part/all of a business name, and have any results which match these come up in a subform from which i can select one and have all of the business details for the name placed into a ready designed form so that i can edit bits??

any ideas

p.s. i dont know vba but i can follow very specific instrucions, and i can send the form im on about if someone can help me

please help me

 

[PaulF]

Why not use a combobox in your mainform that provides a unique list of business names (if it is not too large). Use a query for the RowSource of the subform. In the query used for the subform, use as the form's ComboBox as the criteria for the field that displays the business name. ( = forms!frmName!ComboBoxName). Then refresh the form or requery the subform.

If there are too many names for the combobox you can use a textbox and for the criteria of the query use: Like * & forms!frmName!TextBoxName & *


PaulF

 

[Lightning]

Xena_Morph

Try this code. I run it from the On Click event of a command button.

Private Sub cmdFindName_Click()
'Search for a Specific name in the "YourField" field.
On Error GoTo Err_cmdFindName_Click
Dim strLen, i As Integer
Dim strTempName, strSearchName As String

strLen = Len(strSearchName)

DoCmd.GoToControl "YourField"
strSearchName = InputBox("Enter the name to search for.", "Look Up name&quot
DoCmd.FindRecord strSearchName, acAnywhere, , acSearchAll, , acCurrent, True
DoCmd.RunCommand acCmdFind

Exit_cmdFindName_Click:
Exit Sub

Err_cmdFindName_Click:
MsgBox Err.Description
Resume Exit_cmdFindName_Click

End Sub
Change the code in red to your field/control names.

What this code does is
- set the focus to the field you want to search
- Opens an Input box to type in the name you are searching for
- Opens the built-in Access Find function and passes what you have typed into the input box to that function.
- the function then searches the field for you.

Hope this helps.

Lightning

 

[Halifax]

Say your form is called: frmMyForm
Create a textbox called: txtFindThis (unbound)

I assume you've already created the subform and that it shows data from the underlying table/query correctly, you just want to filter it for whatever is typed in the textbox

Open in design view, the query upon which your subform is based. In the criteria of the field that has the company name, put: Like "*" & Forms!frmMyForm![txtFindThis] & "*"

Close and save the query

Now when a user types in art of a business name, the query will return all records that contain that substring in the Business name field.

You'll have to code an AfterUpdate event in the txtFindThis textbox to requery the subform so that it will update with the correct data.

Good luck