How can I search a form without using FilterbyForm?

M

Merlinar

I am trying to port a database from LotusApproach to Access. One of the
features of Lotus is that one can click find, is given a blank form (much
like filter by form) and can fill in any field with any criteria seperated by
commas (for multiple instances). Access seems to have a lot of problems
doing this. I am trying to find a way to do this programmatically and haven't
had much luck. Does anybody know what I'm talking about and have any ideas
about setting something like that up? Any help would be greatly appriciated.
 
J

John Vinson

I am trying to port a database from LotusApproach to Access.
Click to expand...

As I'm sure you're aware by now, these are VERY different programs
with very different structures and programming styles. Attempting to
use Access as if it were a flawed implementation of Approach will get
you into no end of trouble. Access can do everything that Approach
can... *but it does it differently*.
One of the
features of Lotus is that one can click find, is given a blank form (much
like filter by form) and can fill in any field with any criteria seperated by
commas (for multiple instances). Access seems to have a lot of problems
doing this. I am trying to find a way to do this programmatically and haven't
had much luck. Does anybody know what I'm talking about and have any ideas
about setting something like that up? Any help would be greatly appriciated.
Click to expand...

Is it OBLIGATORY that this be the exact user interface?

If so, then you can do it; you'ld use an unbound Form, and write VBA
code to massage the entered values into the SQL of a query, which you
would then use as the recordsource for a form or report. But this is
not a builtin Access methodology; you'll need to program it yourself!

Just to get started, you might have code like:

Dim strSQL As String
strSQL = "SELECT thisfield, thatfield, theotherfield FROM tablename" _
& " WHERE True"
If Not IsNull(Me!txtLastnameCrit) Then
strSQL = strSQL & " AND LastName IN(" & Me!txtLastnameCrit & ")"
End If
If Not IsNull(Me!txtFirstnameCrit Then
strSQL = strSQL & " AND FirstName IN(" & Me!txtFirstnameCrit & ")"
End If
<and so on>


John W. Vinson[MVP]
 
M

Merlinar

John, thank you for the reply.

I think your right, and I'm finding out that what I'm trying to do is not
going to be easy. Here is my problem or situation:

I have 4 tables. 1 table contains Corporate information (name, address,
contacts, etc). Table 2 contains the retail locations for each corporation.
Example: Charming Shoppes may contain 250 Fashion Bug locations. Each
location has the store name, address, phone, etc. My third table has vendor
information. There are over 13500 vendors listed in the table from all over
the country and more are added daily. The forth table contains WorkOrders
that are created for any store that calls in with a problem. The WorkOrder
contains all relevant information including some information from the
Corporate table, Location table, vendor table and a whole lot of new
information, (dates, times, Id's, etc.). There are over 100,000 WorkOrders
and it grows at least 70+ a day.

My biggest problem is finding information in these tables. I know Access
includes some tools, but they aren't the most user friendly. I've been trying
to create my own search tools, but as I'm not overly experianced with VBA,
I've run into snags that I have problems working around.

I'm trying to do something like filter by form, but for the sake of the
people who will be using the database and don't know the first thing about
access and queries etc., I'd like to have a button that says "Find Record".
Person clicks it and is given a blank form in which they can fill in any
information that they want to narrow their search. For fields that the user
will enter multiple criteria, have it seperated by commas. The person then
clicks "OK" and the underlying code would then take all the information
entered into the form, find the commas, turn them into "AND" and then build
the Where clause.

I actually have most of that done. The only problems I'm having are: a way
to find the commas and turn them into "AND" and the use of wildcards,
example: If one wants to find all records with a zipcode that start with 193.
Right now, whenever I type in anything that isn't exactly the same as it is
in the table, I get a blank record presumably because it can't find anything
that matches the criteria.

I don't know if this helps explain anything at all or if you can even offer
some ideas, but anything will help. Thank you very much.
 
J

John Vinson

I actually have most of that done. The only problems I'm having are: a way
to find the commas and turn them into "AND"
Click to expand...

You actually want either OR or In(value, value, value): AND logic
would require that the record has the field equal to X and also equal
to Y, in the same record in the same field. This CAN NEVER HAPPEN of
course, since a field can have only one value!

To use multiple values for a field you will indeed need some VBA code
to create the SQL for a query using the syntax

Fieldname IN ("value1", "value2", "value3")

in the WHERE clause of the SQL. This isn't trivial but it's not all
that hard.
and the use of wildcards,
example: If one wants to find all records with a zipcode that start with 193.
Right now, whenever I type in anything that isn't exactly the same as it is
in the table, I get a blank record presumably because it can't find anything
that matches the criteria.
Click to expand...

Wildcards are much easier: use a criterion of

LIKE [Forms]![searchform]![txtZip] & "*"

to use an (efficient, indexed) search for zip codes beginning with any
entered number of characters; you may also want

LIKE "*" & [Forms]![searchform]![textbox] & "*"

to find the entered string anywhere within the field, but be aware
that this won't use indexing and may be inefficient for tables of the
size you're describing.

John W. Vinson[MVP]
 
Top