Searching fields in Access

[Eeebs]

Hi there

Is there a way to search through specified fields in a Table?

I have a table which has a few records inside with the following fields:
[ID, Software, Fix]

I have a Form setup which needs to act as a search form. You enter the search criteria into one field and then you specify via a dropdown box, which field in the table you want to search through. You then press the search button to bring up the results based on which field you chose to search through (and the criteria you entered).

So say you want to search for "Microsoft Word" in the field "Software", how would you go about doing this?

 

[pbaldy]

You would need to build SQL in code and then use it as desired. This type of thing:

strSQL = "SELECT Blah FROM TableName WHERE " & Me.FieldComboName & " = '" & Me.SearchValue & "'"

It would get trickier if the potential fields were of different data types, as numbers/text/dates are treated differently.

 

[Eeebs]

You would need to build SQL in code and then use it as desired. This type of thing:

strSQL = "SELECT Blah FROM TableName WHERE " & Me.FieldComboName & " = '" & Me.SearchValue & "'"

It would get trickier if the potential fields were of different data types, as numbers/text/dates are treated differently.

Thanks for the help. I've tried using the code below but it asks for Me.Combo7 and Me.Text0 which are the names for the combo box and text field respectively. Is there sometign wrong with the code?

SELECT Content.[Article ID], Content.Category, Content.Problem, Content.Cause, Content.Fix
FROM Content
WHERE Me.Combo7  = Me.Text0;

 

[pbaldy]

As I said, you would need to build a string in code and then use it for something. You can't do it in a query alone (not the field selection anyway).

 

[Eeebs]

As I said, you would need to build a string in code and then use it for something. You can't do it in a query alone (not the field selection anyway).

Ah right, so how do I go about using the string nce ive coded it?

 

[DCrake]

If your form is bould to an underlying table or query you could use this method.

On your OnClick() event of your button place the following code

DoCmd.ShowAllRecords        ' [COLOR="Green"]clears any previous filters[/COLOR]
Dovents                            [COLOR="green"]' waits until the above is complete[/COLOR]

'Reset the filter to the form

Me.Filter = "[" & Me.ComboBox & "]='" & Me.TextBox & "'"
Me.FilterOn = True

Remember to change the controls names to match your own.

Also the list of fields you supply in your combo box may not be evident to the user which data items they relate to. You may need to create a table of field of field names with Actual field names and foreign names such as reqdat and Required Date. Then refer to the actual name and not the offered name in you syntax.


David

 

[pbaldy]

I was vague because I don't know what you're trying to accomplish. You can set the filter as David pointed out, you can set the record source of a form or subform to the SQL string, you can use it to open a recordset, etc.

 

[Eeebs]

Thanks for the information DCrake

I have the following code for the button im clicking

Private Sub SearchButton_Click()
    On Error GoTo Err_SearchButton_Click
    Dim stDocName As String
    DoCmd.ShowAllRecords        ' clears any previous filters
    DoEvents                     ' waits until the above is complete
    Me.Filter = "[" & Me.Combo7 & "]='" & Me.Text0 & "'"
    Me.FilterOn = True
    stDocName = "MacroOpenResult" 'Opens a result form which has a query showing records.
    DoCmd.RunMacro stDocName
    
Exit_SearchButton_Click:
    Exit Sub
Err_SearchButton_Click:
    MsgBox Err.Description
    Resume Exit_SearchButton_Click
    
End Sub

However what it does is show the next form with the query displaying all the results, rather than the filtered ones. Is there something that may need to be added to the subform query on the results form.
Just for information the Search form contains the button and the Results form contains the subformquery.
Any help is greatly appreciated

 

[DCrake]

What is your macro doing?

 

[Eeebs]

What is your macro doing?

It just opens a form. It is set on the "on click" part of the Event tab.

 

[DCrake]

This filtering syntax if for the form that is currently open. If you want to open a form with a filter linked to the calling form then you can use the following

On the On Load of the form to be opened

Me.Filter = "[FilterField] =" & Forms("YourCallingForm")("TheFormField")
Me.FilterOn = True

David

 

[Eeebs]

This filtering syntax if for the form that is currently open. If you want to open a form with a filter linked to the calling form then you can use the following

On the On Load of the form to be opened

Me.Filter = "[FilterField] =" & Forms("YourCallingForm")("TheFormField")
Me.FilterOn = True

David

I have the following code inside the OnLoad on the form to display the filtered query results.

Me.Filter = "[Combo7] =" & Forms("SearchForm")("Text0")
Me.FilterOn = True

Just to clear up what exists on the both forms:

Form name: SearchForm

Text0 = The text field in which the criteria to search for, is entered.
Combo7 = The combo box that selects a field to filter through
SearchButton = A button that when clicked, takes you to the ResultForm.

Form name: ResultForm

IDSearch subform = A subform that shows a query with the filtered results.

However it still shows all of the records in the subform query on the ResultForm. Im not sure what im doing wrong.