Form Filter

[Matt, Sheffield, UK]

Can anyone tell me how to set up a form tht filters tables.
I have a form for inputting data into a table, I want a virtual copy of the
input form and use this as a search form where users can type in criteria and
filter the records, for example one of the input fields is name, so in the
search form they can free type a name into the name field and click search,
the database will then filter all records with that name, but I also want it
to do multiple filters, so it might be name matches Smith, but type (another
field) is Academy. The database then gives me all the matches for Smith and
Academy, I know i can do this by switching the filters on, but I want a
separate search screen with a search button. I also want the results to come
up in another form, possibly a tabular style form, is this also possible.
Thanks to whoever can help me on this.

Matt

 

[Allen Browne]

Matt, there is a small sample database for Access 2000 and later here:
http://allenbrowne.com/unlinked/Search2000.zip

Although there is no accompanying explanation, if you understand a bit of
VBA code, you will be able to apply the techniques to your database.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Matt, Sheffield, UK]

Thanks Allen its worked a treat your a star

One final question, on my results section I need it to return some fields
from my table but its just showing their primary keys as they are actually
from another table that is linked ot the main one I am using, so for instance
the title of an item is showing up on the results ar '2' as its numbered 2 in
its own table, but i need it show the actual text explanation of what 2 is as
it is listed in the main table I am using, does this make sense if so any
ideas.

 

[Allen Browne]

Could you create a query that contains both tables (your main one, and the
one that has the lookup value for 2), and use that as the RecordSource for
the search form? That way you can display the text field rather than the 2.

Alternatively, you could use a combo to display this field. Set the Column
Width to 0 for the bound column, and display the next one. That's a less
attractive option, as a combo you can't change is not ideal. Certainly using
a combo for selecting the value for the filter is a good idea.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Matt, Sheffield, UK]

Hi again Allen you have been a great help again, coul dyou try one last one
for me, I have set up my searh form which displays records found from the
criteria searched up, it shows them in a list at the bottom like your example
does.

How could I have comman button tha would take me to one of those records
(ie open the main input form showing that record and all its fields) either
that or could i double click on one of the found records again and this take
me to the other form showing that record.

Thanks again

 

[Allen Browne]

Assuming a primary key field named ClientID (number type field), you would
just code the Click event of your button or DblClick of your text box to:

Dim strWhere As String
If Not IsNull(Me.ClientID) Then
strWhere = "[ClientID] = " & Me.ClientID
DoCmd.OpenForm "MyOtherForm", WhereCondition:= strWhere
End If

Note that this only works correctly if the other form is not already open.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message