How to show results in a subform datasheet based on keyboard input from user

K

kory.tytus

I'm trying to build a form to show the results base on input by the
user. I was trying to do this in a form/subform format. I can link
form and subform based on three links but this does not yield the
proper results and does not give me enough search/query/filter
parameters.

I can build forms and subforms...with successful links.

Example: I need to be able to enter Last Name, First Name, Location,
Occupation, or Zip code and have the subform display results based on
what is entered. I need it operate on all parameters entered, whether
it is one or ten.
 
S

strive4peace

Hi Kory,

rather than using a main/subform, consider unbound consider
using combo/textboxes in the form header or footer. Assign
this to the AfterUpdate event of each one...

=SetFormFilter()

then put this code behind the form

'------------------
Private Function SetFormFilter()

dim mFilter as string
mFilter = ""

If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" &
me.controlname_for_text & "'"
end if

If not IsNull(me.date_controlname ) Then
if len(mFilter) > 0 then mFilter = mFilter & " AND "
mfilter = mfilter & "[DateFieldname]= #" &
me.controlname_for_date & "#"
end if

If not IsNull(me.numeric_controlname ) Then
if len(mFilter) > 0 then mFilter = mFilter & " AND "
mfilter = mfilter & "[NumericFieldname]= " &
me.controlname_for_number
end if

if len(mfilter) > 0 then
me.filter = mfilter
me.FilterOn = true
else
me.FilterOn = false
end if

me.requery

End Function
'------------------

me.controlname_for_number refers to the NAME property of a
control on the form you are behind.

Me. represents the form -- kinda like "me" for me is not
"me" for you ;)

delimiters are used for data that is not a number
quote marks ' or " for text
number signs # for dates

mfilter is a string that is being built for each condition
-- but if nothing is specified in the filter control
(IsNull), then that addition to the filter string is skipped.

finally, when the filter string is done, it is applied to
your form.

That means that as you flip through records, ONLY records
matching that filter will show

Then, put another command button on the form

Name --> btnShowAll
OnClick --> [Event Procedure]

'------------------
me.filteron = false
me.requery
'------------------

a requery of the form will also reset the record pointer
back to the first record for that recordset.

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com
 
K

kory.tytus

Crystal,
First, thanks for the reply. Secondly, you'e given me too much
credit. My SQL/VBA abilities are limited. That said, I'm a quick
study so don't abandon me yet.

Here is what I've done; I've created a form using an empty table
with the same structure of table I'm trying to retrieve the data. I've
selected and unbound the fields in the form. Here is where I know I
made some amateurish mistakes... On AfterUpdate, I selected [event
procedure]. From there I copied and paste the code you provided. I
know I've honked-up the names of the fields...but this easy to fix.

I also have questions on the following;
where exactly does "=SetFormFilter()" go? I tried it as the control
source and in the AfterUpdate field, both yielded errors. I finally
added it to code directly beneath "Private Sub L_NAME_AfterUpdate()".
It gave me an error but accepting it.

The first two field names are L NAME and RANK. Once I can get
these two to work, I will add the other fields. The form name is UPMR
UPDATE and the table source is Table 3 Deployment Tracker.

Ultimately my intentions were to query records that meet the
criteria. Once visible, the user would select the record(s) to update
and subsquently make corrections, which would be stored in the table
mentioned above.

I've built quite a few complex data bases in the past but all were
macro and query driven.

If I'm beyond help...please advise and I will attempt another
approach (no hard feelings...smile). That said, I plan to go out and
purchase an ACCESS book today to brush up on my SQL/VBA. Thanks in
advance.

Kory
 
S

strive4peace

Hi Kory

change

Private Sub L_NAME_AfterUpdate()

to

Private Function SetFormFilter()

then, compile the code

---------- Compile ----------

Whenever you write or paste code, your should ALWAYS compile
it before you attempt to run it.

from the menu: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

---- Assign SetFormFilter to filter controls ----

on the AfterUpdate event of each of your filter controls, L
NAME and RANK -->

=SetFormFilter()

this way, whenever you add or remove a filter, the records
will change to show what matches.

Drop me an email and I will send you the first 3 chapters of
a book I am writing on VBA.

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top