Populate a form based on user input

[Drowning in VB code]

Hi,

I am sure this question had to have come up before but have found only
partial solutions.

Using a table of client information (LastName, FirstName, ClientNo, DOB.
Etc.), I would like to let a user view a record in the same form used for
entering their search. They may wish to search by only one of the field (DOB
or ClientNo) or alternately a combination of two or more fields (LastName,
FirstName). In other words, by entering some information on the form, the
rest of the data is populated. I would assume where two or more records
match, a filtered list of records would be produced. Because of the number
of records, combo boxes would be almost useless.
I was able to retrieve a one record based on a ClientID but for both
simplicity and elegance, it would be nice to have one button search to search
for data in all fields while ignoring blank ones. Although query by form
works, users will have both limited skills and access

Any and all help would be very greatly appreciated.

Thank you

 

[Albert D. Kallal]

While looking at the reocrd, use the access menu:

go:

Records->filter ->Filter by Form

The above will allow you to fill in any values in the form..and seach...


So, try the query by form.....

 

[Drowning in VB code]

Thank you Albert for your quick response.

Unfortunately, I do not want the users to acess the menu as this will only
lead to too many problems. It would be nice to be able to replicate the
filter by form and filter buttons from the menu to the form itself, but I
don't know if this can be done. A lot of databases have forms that let you
search and view from the same form. Access, unfortunately won't let me do
this easily.

I am looking forward to any and all other ideas.

 

[Albert D. Kallal]

Unfortunately, I do not want the users to acess the menu as this will only

lead to too many problems. It would be nice to be able to replicate the
filter by form and filter buttons from the menu to the form itself, but I
don't know if this can be done.

You can certainly build you own custom menu bar, and place those features in
that bar. In fact, I find they even work when using the runtime system (at
least they do in a2003). Here is some screen shots:

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

Note how in the 2nd screen shot, the filter by selection button is in the
menu bar (and, it not even in a sub-menu).

I would consider building a custom menu if you don't want users messing
around with other features. However, hiding the ms-access interface is much
a given in most of my applications. (and, it don't even take code to
accomplish that hiding of the access interface).

A lot of databases have forms that let you
search and view from the same form. Access, unfortunately won't let me do
this easily.

Gee, I a confused. You have filter by selection, you have the filter by
form, you even have "advanced" filter options (have you tried this option
brings up the query grid).

So, you state that a lot of database have form that let your search? Access
has a whole bunch of these features. Then you state that you don't want to
use them, and then you state that access will not let you do this? I must be
missing something here? In addition to the above many types of filtering
systems, you can also use the wizard to build a combo box on the form that
will search for you also.

If for some strange reason you don't want to use the 4 or 5 possible way of
looking for things, then you could roll your own search form. I talk about
this here:

http://www.members.shaw.ca/AlbertKallal/Search/index.html

The built in searching features are quite rich in features, but you can
certainly roll your own, it just not quite clear what you are looking for at
this point in time.

There is also a few more forms here that might give you some ideas:
http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

 

[Drowning in VB code]

Hi Albert,
Sorry, don't mean to diss Access - just don't know how to do it, that's all.
What I am striving for is a user-friendly form attached to a table that has
fields like Last Name, First name, Client No, DOB, Gender etc. There will be
about 1000+ active clients in this table so a combo box, while simple to use,
would be impractical with so many records. What I thought would be the
simplest (from the user's perspective) is to let them enter the information
they may have. In some instances they may know the unique Client No but on
many other occasions they may only be provided a first and last name from a
third party. After the info that they do have has been entered into each
appropriate text box such as the last and first name fields, a command button
would execute a filter based on this variable data entered. This in turn
would populate the rest of the form. This is what Query-By-Form does except
it is best to keep users out of the main menu. I did have a look at the
link you posted re menu buttons but it hooked me up with "..splitting a
database."

I trust this helps to explain what I am after, if not, please do let me know
and maybe I can call you to explain myself better or alternately I could
email you a sample of what the form looks like.

Thank you so much .
PS My son lives in Edmonton.

 

[Drowning in VB code]

Maybe to explain further...

I did manage to open your "Cool Select Example" dbs. Your first form
"Contacts" is exactly what I was hoping to achieve with the exception that
your existing "exit" button instead of closing the form would execute a query
to finish populating the form.

Your did provide another sample screen shot (no code) of a form that had
query fields in the header with what appears to be a datasheet subform. This
would be my second choice if the first were unavailable. Since the user will
always be querying on different fields, the header would get a busy look to
it. Most would be simple searches. On the other hand, an advanced search
like who was that "female" that has an "Admission date" sometime in late
November or December would add a lot of versatilty to the program.

 

[Albert D. Kallal]

Hi Albert,
Sorry, don't mean to diss Access - just don't know how to do it, that's
all.

Oh. gosh...not worried one bit abut you dishing on access. That is not a
problem!!!

The issue was that there is a ton of ways to search, but you seem to be
hinting
that there are none. It is really simple to say that you don't find the
built in
searching features very good, or user friendly. I don't find searching that
great at all either..and you can see I have a good number of search screen
shots in those examples as proof that I don't like the built in searching
either.

it hooked me up with "..splitting a

database."

opps...my falt. teh screen shot with the filter menu is here:

http://www.kallal.ca/ridestutorialp/drivers.htm

What I am striving for is a user-friendly form attached to a table that
has
fields like Last Name, First name, Client No, DOB, Gender etc. There will
be
about 1000+ active clients in this table so a combo box, while simple to
use,
would be impractical with so many records.

I could not agree more with the above. A combo box gets real weak real
fast, and futher they perform poorly.

I don't have seach form download of yet. However, here is how I make those
forms work:

If you are going to code a form to search, then you can try the following:

Build a unbound form. Place a text box near the top. Lets call the control
txtLastName. Turn just about everything off for this form (record selector,
navigation buttons etc). This form is NOT attached to a table, nor any data
(we call this a un-bound form).

In addition to the above, you an build nice continues form that displays the
columns of data in a nice list. you can then put this form as a sub-form
into the above form.

Then, in the txtLastName after update event you simply stuff the results
into that sub-form.

dim strSql as string

strSql = "select * from tblCustomer where LastName like '" & me.txtLastName
& "*'"

me.MySubFormname.Form.RecordSource = strSql.

That is how this works. However, you could EASLIY put in a few more text
boxes in the headder part of hte form.

eg:

dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

then go

strSql = "select * from tblCustoers where " & strWhere
me.MySubFormname.Form.RecordSource = strSql

The resulting search is then displayed in a nice continues form, and if you
get 8 matches..the user doe snot have to scroll through 8 records, but
simply clicks on the "glasses" icon for that one record.