Creation of an Input Form

  • Thread starter Thread starter James
  • Start date Start date
J

James

Hello I have the following tables and I would like to have
an input form.

tblMain

RespondatID (Autonumber)
Gender (Text)
Qustion (Text)
Answer (Text)

tblGender

GenderID (AutoNumber)
Gender (Text) (Male and Female Entered as data)

tblQuestion

QuestionID (Autonumber)
Question (Text) (Q01,Q02... Q25 entered as data)

tblAnswer

AnswerID (Autonumber)
Answer (Text) (True and False entered as data)

How do I get an Input form so that I can have something
like the follwoing:

Are you male or Female (cbobox for the values of Male and
Female) selected value goes in tblMain as a record.

Q01 (CboBox with values of True or false) when selected
the Question Number goes in the relevant field in tblMain
(Question) and the answer given (true or False) goes in
the (Answer) field in tblMain.

How do I do this?

Many Thanks

James
 
-----Original Message-----
Hello I have the following tables and I would like to have
an input form.

tblMain

RespondatID (Autonumber)
Gender (Text)
Qustion (Text)
Answer (Text)

tblGender

GenderID (AutoNumber)
Gender (Text) (Male and Female Entered as data)

tblQuestion

QuestionID (Autonumber)
Question (Text) (Q01,Q02... Q25 entered as data)

tblAnswer

AnswerID (Autonumber)
Answer (Text) (True and False entered as data)

How do I get an Input form so that I can have something
like the follwoing:

Are you male or Female (cbobox for the values of Male and
Female) selected value goes in tblMain as a record.

Q01 (CboBox with values of True or false) when selected
the Question Number goes in the relevant field in tblMain
(Question) and the answer given (true or False) goes in
the (Answer) field in tblMain.

How do I do this?
Click to expand...


Hi, James.

Your current design has some problems that are easy to
fix. First, it's good that you've tried to normalize your
tables. Your foreign keys in tblMain, though, must match
in type to their "lookup" tables, i.e., tblMain->Gender,
Question, and Answer should all be numeric types to match
the AutoNumber primary keys of the lookup tables.

Also, try to name your tables based on the "thing" they
represent. If you find it difficult to do so, it could be
a clue that your table does not represent attributes of a
single thing, and therefore, not sufficiently normalized.
In your case, tblMain represents two things, Respondants
and Responses, which have a one-to-many relationship.

While there's no harm in keeping it, tblAnswer is also not
really required. You can always calculate a text value
from a Boolean in a form or report control, such as: =IIf
([Answer]=True, "True","False"). You could also work
around Gender if you wish.

I suggest the following table designs:

tblRespondants
==============
RespondantID AutoNumber
Gender Number (Foreign Key to tblGender)

tblResponses
============
ResponseID AutoNumber
RespondantID Number (Foreign Key to tblRespondants)
QuestionID Number (Foreign Key to tblQuestions)
Answer Yes/No

Keep tblGender and tblQuestion as you've defined them.

In the Relationships window, define the following
relationships:

One Side Many Side
================= =============
tblRespondants tblResponses
tblGender tblRespondants
tblQuestion tblResponses

For data entry, create a main form based on tblRespondants
and a continuous subform based on tblResponses, linked by
the RespondantID (i.e., set the subform control's
LinkMasterFields and LinkChildFields properties to
tblRespondants' primary key and tblResponses' foreign key,
respectively).

Use the combo box wizard to create combo boxes for all
fields for which you will store a foreign key and display
a value in the form, i.e., tblRespondants.Gender,
tblResponses.RespondantID, tblResponses.QuestionID,
tblResponses.Answer. Answer could alternatively be an
option group.

To turn the wizard on, display the Toolbox from form
design view, and toggle the Wizard button (a wand and
stars). For the combo boxes, tell the wizard to look up
its values from the appropriate lookup table, hide the
primary key, and store the selection in the appropriate
foreign key field. Access will store the numeric key, but
display the text of the second table field.

Your form will then display the Respondants' name and
gender, and all of the questions and answers associated
with him or her.

HTH
Kevin Sprinkel
 
Hi...

I have followed everything you have said to the letter the
thing is it keps having problems here there and
everywhere...

Like the cboboxes I have created they display nothing...

the iif statment seems to be doing nothing....

I just cant seem to get it to work th damn thing...

Would you like to have a look at what I have so far and
them give me exactly the things you wish me to do? Would
this help?

Many Thanks

James
-----Original Message-----
-----Original Message-----
Hello I have the following tables and I would like to have
an input form.

tblMain

RespondatID (Autonumber)
Gender (Text)
Qustion (Text)
Answer (Text)

tblGender

GenderID (AutoNumber)
Gender (Text) (Male and Female Entered as data)

tblQuestion

QuestionID (Autonumber)
Question (Text) (Q01,Q02... Q25 entered as data)

tblAnswer

AnswerID (Autonumber)
Answer (Text) (True and False entered as data)

How do I get an Input form so that I can have something
like the follwoing:

Are you male or Female (cbobox for the values of Male and
Female) selected value goes in tblMain as a record.

Q01 (CboBox with values of True or false) when selected
the Question Number goes in the relevant field in tblMain
(Question) and the answer given (true or False) goes in
the (Answer) field in tblMain.

How do I do this?
Click to expand...


Hi, James.

Your current design has some problems that are easy to
fix. First, it's good that you've tried to normalize your
tables. Your foreign keys in tblMain, though, must match
in type to their "lookup" tables, i.e., tblMain->Gender,
Question, and Answer should all be numeric types to match
the AutoNumber primary keys of the lookup tables.

Also, try to name your tables based on the "thing" they
represent. If you find it difficult to do so, it could be
a clue that your table does not represent attributes of a
single thing, and therefore, not sufficiently normalized.
In your case, tblMain represents two things, Respondants
and Responses, which have a one-to-many relationship.

While there's no harm in keeping it, tblAnswer is also not
really required. You can always calculate a text value
from a Boolean in a form or report control, such as: =IIf
([Answer]=True, "True","False"). You could also work
around Gender if you wish.

I suggest the following table designs:

tblRespondants
==============
RespondantID AutoNumber
Gender Number (Foreign Key to tblGender)

tblResponses
============
ResponseID AutoNumber
RespondantID Number (Foreign Key to tblRespondants)
QuestionID Number (Foreign Key to tblQuestions)
Answer Yes/No

Keep tblGender and tblQuestion as you've defined them.

In the Relationships window, define the following
relationships:

One Side Many Side
================= =============
tblRespondants tblResponses
tblGender tblRespondants
tblQuestion tblResponses

For data entry, create a main form based on tblRespondants
and a continuous subform based on tblResponses, linked by
the RespondantID (i.e., set the subform control's
LinkMasterFields and LinkChildFields properties to
tblRespondants' primary key and tblResponses' foreign key,
respectively).

Use the combo box wizard to create combo boxes for all
fields for which you will store a foreign key and display
a value in the form, i.e., tblRespondants.Gender,
tblResponses.RespondantID, tblResponses.QuestionID,
tblResponses.Answer. Answer could alternatively be an
option group.

To turn the wizard on, display the Toolbox from form
design view, and toggle the Wizard button (a wand and
stars). For the combo boxes, tell the wizard to look up
its values from the appropriate lookup table, hide the
primary key, and store the selection in the appropriate
foreign key field. Access will store the numeric key, but
display the text of the second table field.

Your form will then display the Respondants' name and
gender, and all of the questions and answers associated
with him or her.

HTH
Kevin Sprinkel

.
Click to expand...
 
hi nothing seems to be working at all... :-(

Would you like me to send you a zipped up copy of the
small MDB File so you can see whats going on and then just
let me know what you wish me to do step by step and I will
gladly do it...

The problems are whne I put my subform in... I have tried
to create the combo boxes and also the option groups... I
cant get them to work so I tried to get them as a combo
box and that didnt work... please could you assist
further?

Many Thanks

James
-----Original Message-----
-----Original Message-----
Hello I have the following tables and I would like to have
an input form.

tblMain

RespondatID (Autonumber)
Gender (Text)
Qustion (Text)
Answer (Text)

tblGender

GenderID (AutoNumber)
Gender (Text) (Male and Female Entered as data)

tblQuestion

QuestionID (Autonumber)
Question (Text) (Q01,Q02... Q25 entered as data)

tblAnswer

AnswerID (Autonumber)
Answer (Text) (True and False entered as data)

How do I get an Input form so that I can have something
like the follwoing:

Are you male or Female (cbobox for the values of Male and
Female) selected value goes in tblMain as a record.

Q01 (CboBox with values of True or false) when selected
the Question Number goes in the relevant field in tblMain
(Question) and the answer given (true or False) goes in
the (Answer) field in tblMain.

How do I do this?
Click to expand...


Hi, James.

Your current design has some problems that are easy to
fix. First, it's good that you've tried to normalize your
tables. Your foreign keys in tblMain, though, must match
in type to their "lookup" tables, i.e., tblMain->Gender,
Question, and Answer should all be numeric types to match
the AutoNumber primary keys of the lookup tables.

Also, try to name your tables based on the "thing" they
represent. If you find it difficult to do so, it could be
a clue that your table does not represent attributes of a
single thing, and therefore, not sufficiently normalized.
In your case, tblMain represents two things, Respondants
and Responses, which have a one-to-many relationship.

While there's no harm in keeping it, tblAnswer is also not
really required. You can always calculate a text value
from a Boolean in a form or report control, such as: =IIf
([Answer]=True, "True","False"). You could also work
around Gender if you wish.

I suggest the following table designs:

tblRespondants
==============
RespondantID AutoNumber
Gender Number (Foreign Key to tblGender)

tblResponses
============
ResponseID AutoNumber
RespondantID Number (Foreign Key to tblRespondants)
QuestionID Number (Foreign Key to tblQuestions)
Answer Yes/No

Keep tblGender and tblQuestion as you've defined them.

In the Relationships window, define the following
relationships:

One Side Many Side
================= =============
tblRespondants tblResponses
tblGender tblRespondants
tblQuestion tblResponses

For data entry, create a main form based on tblRespondants
and a continuous subform based on tblResponses, linked by
the RespondantID (i.e., set the subform control's
LinkMasterFields and LinkChildFields properties to
tblRespondants' primary key and tblResponses' foreign key,
respectively).

Use the combo box wizard to create combo boxes for all
fields for which you will store a foreign key and display
a value in the form, i.e., tblRespondants.Gender,
tblResponses.RespondantID, tblResponses.QuestionID,
tblResponses.Answer. Answer could alternatively be an
option group.

To turn the wizard on, display the Toolbox from form
design view, and toggle the Wizard button (a wand and
stars). For the combo boxes, tell the wizard to look up
its values from the appropriate lookup table, hide the
primary key, and store the selection in the appropriate
foreign key field. Access will store the numeric key, but
display the text of the second table field.

Your form will then display the Respondants' name and
gender, and all of the questions and answers associated
with him or her.

HTH
Kevin Sprinkel

.
Click to expand...
 
-----Original Message-----
hi nothing seems to be working at all... :-(

Would you like me to send you a zipped up copy of the
small MDB File so you can see whats going on and then just
let me know what you wish me to do step by step and I will
gladly do it...

The problems are whne I put my subform in... I have tried
to create the combo boxes and also the option groups... I
cant get them to work so I tried to get them as a combo
box and that didnt work... please could you assist
further?

Many Thanks

James
Click to expand...

Hi, James. Rereading my post, I think I may have misled
you on the sequence, and/or not given you enough
information on using the Combo Box Wizard. But I think
you should first check all tables and verify each fields'
Type matches my earlier post.

Assuming they do, create your main form, based on
Respondants, with the following controls:

RespondantID Textbox
Gender ComboBox

The combo box wizard will ask you if you want to look up
your values from another table. Accept this default, then
specify the tblGender table. Select both the GenderID and
Gender fields. On the next screen, accept the default
of "Hide Key Column (Recommended)". Then tell Access to
store the selection in your Gender field (NOT remember it
for later), and give the combo box an appropriate label,
presumably "Gender".

The wizard sets the following properties, which you can
view by right-clicking on the combo box and choosing
Properties:

ControlSource Gender
Row Source SQL statement selecting the 2 Gender fields
Row Source Type Table/Query
Bound Column 1
LimitToList 1
ColumnWidths 0";x"

I encourage you to read your documentation for a detailed
description of these properties. In a nutshell, the Row
Source displays the data from the SQL statement, subject
to the ColumnWidths property. The latter is a list of the
displayed width of all columns, delimited by semi-colons.
If the width is 0", it doesn't display, so in your case
the combo box should display two values, "Male"
and "Female", and not the numeric key field. Experiment
with these properties to see how they behave.

The Bound Column is the column which is stored in the
field specified in the Control Source property.

Save the main form. Before testing it, be sure you've
added the two records to the Gender table.

Now on loading your form in a new record, Access should
assign a new RespondantID, and you should be able to pick
either Male or Female from the combo box. If this is
successful, close the form and look at tblRespondants
table. You'll see that what is stored in the Gender field
is the numeric foreign key to your tblGender choice,
because the key field is the first column, and you'd
specified that as the Bound Column.

I hope this is more helpful. Post your result, and if
successful, we'll walk through creating and inserting the
subform. It will be comparatively easy having done the
main form.

Best regards.

Kevin Sprinkel
 
Hello there everything worked like a treat...

I have it now looking as it should...

I now need it to do the Answer and Question field....

How do I achieve this?

Many Thanks

James
 
-----Original Message-----
Hello there everything worked like a treat...
I have it now looking as it should...
I now need it to do the Answer and Question field....
Click to expand...
How do I achieve this?
Many Thanks

James
Click to expand...

OK.

How you proceed depends on how your questionnaires are
administered. If all of your respondants will be asked
the same questions, I recommend first doing an Append
query into the tblResponses table, inserting the
RespondantID and the QuestionID. Access will auto-assign
the ResponseID. In SQL,

INSERT INTO tblResponses ( RespondantID, QuestionID )
SELECT tblRespondants.RespondantID, tblQuestions.QuestionID
FROM tblQuestions, tblRespondants;

Now you need a form to display the questions and responses
for each respondant. Because I assume you'd like to
display the text of each question rather than its ID#,
we'll base the form on a simple query that includes the
text of each question. In Query design view, show the
tblResponses and tblQuestions tables. If you entered the
Relationships I described, they will be linked by the
QuestionID. If there is no link showing, drag one of them
to the other to establish it. Select the RespondantID and
ResponseID from tblResponses, the text Question field from
tblQuestions, then the Answer field from tblResponses.
Execute the query; it should show each question for each
respondant. For example, for three respondants entered
and three questions per respondant, it will display nine
records. Save the query.

Now, with the Form Wizard create a *tabular* form based on
this query. Select only the Question and Answer fields.
If you display this form, it should show all nine records
in a continuous form.

One other decision now. You can use the Answer field such
that selecting the checkbox indicates an answer of True,
and not selecting it corresponds to an answer of False.
If this is not satisfying to you, I suggest replacing the
checkbox control on the form with an option group that
displays buttons for True and False, and storing the
selected value to the Answer field. Since an option group
by default will assign values of 1, 2, etc., you will need
to change them to -1 (True) and 0 (False). If you need
help with this, let me know.

Once this form is saved, open the original form in Design
view. Do not maximize the window, and move it to the side
so that you see the Database window. Select the newly
created answer form and drag it to the design view of the
respondants form to insert it. Check the subform's
properties to ensure that Link Master Fields and Link
Child Fields have been assigned. If they have not, click
the ellipsis button to the right of the field to open the
dialog; then be sure they are linked on the RespondantID
of tblRespondants and tblResponses, respectively.

That's it.

If you intend to administer different questions for
different respondants and wish to pick and choose which
ones to add to a given respondant, base your subform on
the tblResponses table directly, and place a combo box
that looks up the question from tblQuestions and stores
the ID in the QuestionID field, plus the answer checkbox
or alternative option group. If you hide the key field in
your combo box (like you did for the Gender control), the
text of the question will be displayed, while only its ID
will be stored.

Good luck.

HTH
Kevin Sprinkel
 
Hi...

Right everything you have said is working... I thank you
for that...

All's I need help with now are these Option Groups I have
never done them before. Please could you assist with this?

Many Thanks

James
 
Hi...
Right everything you have said is working... I thank you
for that...

All's I need help with now are these Option Groups I have
never done them before. Please could you assist with this?
Click to expand...

Open the form in Design View. Be sure the Toolbox toolbar
is displayed (View, Toolbox if not). Toggle the wizard on
if necessary (the 2nd button of the Toolbox, a wand and
five stars). Select the option group icon (a rectangle
with "XYZ" at the top) and draw the outline of the group
on your form. The wizard should then launch. Type the
label names for each option button; in this case "True"
and "False". Click Next. Choose a default if you wish,
or select "No, I don't want a default."

The next screen asks what value should be assigned to the
bound field when each of these selections is chosen. It
defaults to 1 and 2. We want to change these, since your
underlying control is a boolean (Yes/No), it needs a value
of -1 for True and 0 for False, so change these default
values accordingly in the dialog box, and proceed to the
next screen. Store the value in the Answer field. In the
next screen, choose your type of control and display style
(all are functionally equivalent), and in the next screen
give it an appropriate caption.

What you've done is created a group control that lets the
user select one of the values, and it stores the assigned
value (-1 for True, 0 for False) in the bound field (the
Control Source). If you had more than one choice, such as
a, b, c, d, e, for example, we would have made Answer a
numeric field, and could have accepted the default values
of 1,2,3,4,5. But since there are only two answers, we
save a little storage and memory by using a boolean.

Since you want to display this control on a continuous
subform, you will probably not want the answers stacked,
but rather side-by-side. Simply drag one along side the
other, and align with Format, Align, Top. You can delete
the caption if you wish.

Hope your project is successful.

Kevin Sprinkel
 
Many Thanks for that...

When I am inputting a record everything is good...

Its adding another record into the database which is
fine...

But when the user comes to input the data they have to
keep putting in there gender... is there a way where by
the Gender keeps its self inthe relevant filed until the
user gets to Q25 and then it asked them for there gender?

Also I have a frmQry where by I select some options like
the Question number the gender and the answer and then I
get a percentage of what it says in the database matching
the criteria specified...

How would I do this?

I curerrently have the SQL to calculate the Percentage and
everything... the thing I need now is how I get the values
in the combo boxes.. So how I get the Gender in the Gender
cboBox and the Answer in the Answer cboBox and the
Question in the Question cboBox and then everything will
work...

Many Thanks for you assistance on all this...

James
 
But when the user comes to input the data they have to
keep putting in there gender... is there a way where by
the Gender keeps its self inthe relevant filed until the
user gets to Q25 and then it asked them for there gender?
Click to expand...

I don't understand what you mean by "keeps its self in the
relevant field". The gender should only be input once for
each respondant. The gender control should be bound the
first column of the combo box.

If you want the user to enter their gender after the
questions, redo the Tab order (View/Tab Order).
Also I have a frmQry where by I select some options like
the Question number the gender and the answer and then I
get a percentage of what it says in the database matching
the criteria specified...

How would I do this?
Click to expand...

Place unbound combo box controls on your form, and refer
to their value in your query. To do the cb in the wizard,
choose "Remember this value for later" rather than storing
it in a field.

In the SQL, use a WHERE clause to specify the selection
criteria input into the combo boxes. For example, for a
combo box named cboGender, which, your WHERE clause would
be something like:

WHERE [GenderID] = Forms!yourunboundformname!cboGender

Remember the value in cboGender is a numeric value
representing the GenderID, not "Male" or "Female" which is
*displayed* in the cb.

I'm not an SQL expert; I primarily use the Query design
view, so you'll have to post a new question if you have
much more complex SQL questions.

HTH
Kevin Sprinkel
 
Hi in the frmQry I have the cbobox for the Answer is what
I ment... I would like the Answer (True or False) to be in
the cobo box for selection... How do I do this now I have
the option boxes?

Also as for the relevant field...

I have created the input for as you have requested...

Its a continuous form where by you constantly see the
RespondantID, Gender,Question and Answer...

So each time you fill in the form once you have to fill it
in again... so for example if I were to fill in the form
for teh first time I would have the following...

RespondantID Gender Question Answer

1 Male Q01 True
2 Male Q02 False

Although the above 2 are the same user they have toput
there gender in twice... This is inpractical for myself
and the users as you can probably see so I would like it
where by you add the Gender once and then you can change
the question and the answers where necissary....

Does this help you out a little more?

Many Thanks

James
-----Original Message-----
But when the user comes to input the data they have to
keep putting in there gender... is there a way where by
the Gender keeps its self inthe relevant filed until the
user gets to Q25 and then it asked them for there gender?
Click to expand...

I don't understand what you mean by "keeps its self in the
relevant field". The gender should only be input once for
each respondant. The gender control should be bound the
first column of the combo box.

If you want the user to enter their gender after the
questions, redo the Tab order (View/Tab Order).
Also I have a frmQry where by I select some options like
the Question number the gender and the answer and then I
get a percentage of what it says in the database matching
the criteria specified...

How would I do this?
Click to expand...

Place unbound combo box controls on your form, and refer
to their value in your query. To do the cb in the wizard,
choose "Remember this value for later" rather than storing
it in a field.

In the SQL, use a WHERE clause to specify the selection
criteria input into the combo boxes. For example, for a
combo box named cboGender, which, your WHERE clause would
be something like:

WHERE [GenderID] = Forms!yourunboundformname!cboGender

Remember the value in cboGender is a numeric value
representing the GenderID, not "Male" or "Female" which is
*displayed* in the cb.

I'm not an SQL expert; I primarily use the Query design
view, so you'll have to post a new question if you have
much more complex SQL questions.

HTH
Kevin Sprinkel
.
Click to expand...
 
OK, regarding your True/False combo box. If you're going
to use the value of your Answer field as search criteria,
remember it has the values of -1 or 0, it being a Yes/No
type field. That means that your combo box must have
these values. I think the easiest solution is to add a
small table with two records:

tblAnswer
AnswerID Number (Integer) NOT AutoNumber
Answer Text

Records

AnswerID Answer
-1 True
0 False

Once done, use the combo box wizard to add a cb to frmqry
as previously described, saying "Remember this value for
later".

Its a continuous form where by you constantly see the
RespondantID, Gender,Question and Answer...

So each time you fill in the form once you have to fill it
in again... so for example if I were to fill in the form
for teh first time I would have the following...

RespondantID Gender Question Answer

1 Male Q01 True
2 Male Q02 False
Click to expand...

Please reread my previous post about setting up the form.
Your main form should have only the RespondantID and
Gender fields. Into this form you will insert a
previously created continuous form of the questions and
answers. By doing so, it becomes a subform of the
original form. Set the Link properties of the subform
control as previously described.

HTH
Kevin Sprinkel
 
Right so I dont need the Gender Field in the Subform???
and I have the Gender Field on teh main form rather than
the sub form?

Ah right now I get it....

Thats if its correct...

Many Thanks for all your assistance. Its greatly
appreciated

James
 
Ok I have done that... How do I now Add a New record when
I need to becuase what happening now is I click on Male
and it pulls up all the records in the database with a
Gender of Male and the same for Female...

How do I get it to add a new record?

Many Thanks

James
 
James,

I am somewhat confused. My understanding is that you have
one data input form, which consists of a main form based
on tblRespondants, which includes controls for the
RespondantID and the GenderID, and an embedded continuous
subform consisting of the Questions and Answers. This
should be linked to the main form by setting the Link
Master Fields and Link Child Fields properties of the
subform control to the RespondantID of tblRespondants and
tblResponses, respectively. Therefore, the form should
display ONLY those responses associated with that
respondant.

I also understand that you've created a form by which you
can enter query criteria, presuming it is intended to work
something like this--enter Male in the Gender control,
e.g., and click on a command button that executes a query,
in whose definition the Criteria row for Gender refers to
the control, i.e., =Forms!frmqry!yourcomboboxname.

Please that my understanding is correct or not. So I have
a better idea of how to help you; for the form not
working, please post:

- the Control Source property of each form control
- the Record Source property of the form
- the Record Source property of the subform (if applicable)
- the Link Master Fields and Link Child Fields properties
of the subform control (if applicable)

Re: adding a new record--if your form is set up
correctly, you can begin a new record in several ways:
click the New Record button on the bottom of the window (a
right arrow and asterisk), or navigate with Tab, PgDn,
etc. past the last existing record. This is not
meaningful, however, for your query criteria form, as this
should be an unbound form, i.e., with no Record Source.

Good luck. I will check back today and on the weekend.

HTH
Kevin Sprinkel
 
Ok thanks for that I now have the input form working...

As for the FrmQuery Yes your understanding is correct on
that but it requires all 3 fields to be filled in as far
as I am aware.

Does this help?

Many Thanks

James
 
Good, so you've got it working...congratulations. Re:
frmQuery, yes, because the Criteria row of your query
definition gets its values from the form, if you leave one
blank, it will use Null as the criteria.

If you're interested in giving users the option of
entering any combination of criteria, including leaving
one or more blank, you can build an SQL string through the
AfterUpdate event of each combo box. My usual approach is
to store the result in a control on the form before
calling OpenQuery or OpenReport. For example, I call the
following procedure from each AfterUpdate event on a
report criteria form:


' GENERAL SUBS & FUNCTIONS

Private Sub WriteFilterString()
Dim intindex As Integer
Dim ctl As Control
Dim ctlValFld1 As Control, ctlValFld2 As Control

On Error Resume Next

'Reinitialize filter string control
Me!txtFilterString = ""

' Loop through all form controls; if there's data, add
to filter string
For Each ctl In Me.Controls

Select Case ctl.ControlType

Case acComboBox
' If it's not Null and not blank
If (Nz(ctl.Value) <> 0 And Nz(ctl.Value) <> "")
Then
' It must have a value
' Code here assumes that the name of the control
' is a three letter prefix followed by the field
' name it represents.
Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.Name,
Len(ctl.Name) - 3)) _
& "]=" & ctl.Value & " AND "
End If

Case acOptionGroup
Select Case ctl.Name
Case "optCost"
' Range of costs
Set ctlValFld1 = Me!txtCostRange1
Set ctlValFld2 = Me!txtCostRange2
Case "optYear"
' Range of years
Set ctlValFld1 = Me!txtYearRange1
Set ctlValFld2 = Me!txtYearRange2
End Select

' ValidOptionSelection verifies that the entered
' data makes sense--if a range is selected, it
' ensures that both controls have a value, and
' that control2>control1
If ValidOptionSelection(ctl, ctlValFld1,
ctlValFld2) Then
Call WriteOptionGroupString(ctl, ctlValFld1,
ctlValFld2)
Else
' MsgBox "Data for " & ctl.Name & " is not valid."
End If

End Select

Next ctl

' Strip " AND " from the end of filter
Me!txtFilterString = Left(Me!txtFilterString, Len(Me!
txtFilterString) - 5)

End Sub
 
Back
Top