using combo boxes to filter records in a subform

[Guest]

I'm designing a vendor contact database and I'd like to have a form to search
for contacts based on certain criteria.

I was thinking of something set up as a form with 1 subform - the form would
have 3 combo boxes that determines which contacts would be displayed in the
subform - the first box would be selecting by vendor the second by category
(office staff or field staff) and the third by department.

How do I format the combo boxes to display in the subform the records
relevant to the selection? Should I have the user select all their criteria
first then activate a command to apply a filter?

My even simpler question is how do I format any combo box on a main form to
update records in a subform based on what is selected?

Answers to any of these questions would be much obliged. Thanks in advance.

 

[tina]

How do I format the combo boxes to display in the subform the records

relevant to the selection? Should I have the user select all their criteria
first then activate a command to apply a filter?

you don't really need to use a mainform/subform to "filter" the records,
unless you want to present the records in Datasheet view. to use one form
only:

in the Header section of the form, add the unbound combo boxes. i'll call
them cboVendor, cboCategory, and cboDepartment. in the form's Properties
box, click on the RecordSource property and then the ellipsis (...) button
at the right side, which will take you into the Query Builder. add the
Contacts table to the top section, and pull the fields you want to see into
the grid below. in the field that holds the vendor data, add the following
criteria, as

Forms!FormName!cboVendor Or Forms!FormName!cboVendor Is Null

in the field that holds the category data, add the following criteria, as

Forms!FormName!cboCategory Or Forms!FormName!cboCategory Is Null

do the same for the field that holds the department data.

back in the form Design view, add a command button to the Header section.
add code to the button's Click event procedure, as

Me.Requery

now when you open the form in Form view, all the Contacts records should
display. choose a selection in any combination of the combo boxes, from one
to all three, and click the command button to requery the form's
RecordSource. you may want to add another command button to "Display all
records" again. add code to the button's Click event procedure, as

Me!cboVendor = Null
Me!cboCategory = Null
Me!cboDepartment = Null
Me.Requery

if you want to display the Contacts records in Datasheet view, then create a
form for Contacts with the RecordSource set up as i described above. make
sure the DefaultView property is set to Datasheet. create another form, and
add the combobox controls as described above, except put them in the Detail
section (you don't need a Header section). add the Contacts form to the
Detail section, as a subform. add the command button(s) to the main form as
described above - EXCEPT, change the Requery code to

Me!SubformControlName.Form.Requery

make sure you use the name of the subform control, which may be different
from the name of the Contacts form in the database window.

(btw, in Access, the term "formatting" generally refers to how an object (or
data) looks, not to anything it "does".)

My even simpler question is how do I format any combo box on a main form to
update records in a subform based on what is selected?

*updating* records is a whole different kettle of fish. can you provide more
detail on exactly what you want to do?

hth

 

[Guest]

Thanks tina, I did what you suggested but I think I'm missing some pieces:

I created the Query via the forms properties box as you suggested adding all
the fields i wanted from the contacts tbl (ioncluding the builder, category
and department fields) to the query and added the code as witten by you to
the criteria of each of the 3 fields, adjusting where needed the names of the
form and the combo boxes.

However when I switch to form view after following all the steps (the form
properties, the combo boxes and the command buttons) nothing is displayed in
the details section and the combo boxes arent assigned any values to select
from.

Do I need to put the fields I want displayed in the details section of the
form?
Is there anything I need to do to the combo boxes so they display the values
i want to sort the displayed records by?
Am I just missing some link between the SQL statement we createrd and the
forms details?
Sorry to be so bothersome but this is beyond what I've been taught so I'm a
little lost as to how the records are being displayed on the form with what
weve done so far.

Thanks for you last response tho it definitly cleared a few things up and
and taught me a few things, any more input from you would be great.

Lost Ross

 

[tina]

Do I need to put the fields I want displayed in the details section of the

form?

yes. open the form in design view. on the menu bar, click View | Field List.
highlight all the fields in the list, then drag them onto the Detail section
of the form and drop them.

Is there anything I need to do to the combo boxes so they display the values
i want to sort the displayed records by?

i don't know what you mean by "sort". we've talking about *filtering* the
form's recordset. the *user* will choose values from the combo boxes'
droplists according to what records they want to see, and then click the
command button to requery the form's RecordSource. are you asking how to set
up the "droplist" of a combo box control?

hth

 

[Guest]

Now I have things set up as I'd like them - I'm currently making all the
adjustments you instructed me to on a tabular autoform (so i can display more
than 1 record at a time). I have all the records displaying when I initially
switch to form view from design view but as soon as i try to requery with the
command button after selecting criteria in the combo box I get the following
error: " Runtime Error 3420 - Object invalid or no longer set"

Right now to try and keep things simple i've only added 1 combo box to sort
by builder. In the query built through the forms record source I added all
the fields I wanted displayed (Is this correct?) and under the criteria for
Builder(Instead of vendor - im in the construction industry) I entered

Forms!FormName!cboBuilder Or Forms!FormName!cboBuilder Is Null

After doing this and saving it the form's record source reads as follows:

SELECT tContacts.LastName, tContacts.FirstName, tContacts.Direct,
tContacts.Ext, tContacts.Mobile, tContacts.[Mike#], tContacts.[Fax#],
tContacts.[Pager#], tContacts.[E-mail], tContacts.Builder,
tContacts.Category, tContacts.Department, tContacts.[Job Title],
tContacts.Details FROM tContacts WHERE
(((tContacts.Builder)=Forms!Form2!cboBuilder Or Forms!Form2!cboBuilder Is
Null));

Am I making an obvious mistake?

Thanks for your patience and efforts tina

Ross

 

[tina]

i don't see any obvious errors. i'm sure there's something that needs to be
"tweaked", but i can't think of any specific questions i can ask that will
lead us to the problem. i'm willing to take a look at the db if you want to
send me a copy. if you decide to take me up on the offer, make a copy of
your db and remove any proprietary data from the copy. then change the
file's extension from .mdb to .bak (do NOT open it after you make that
change!), and zip it to under 1 MB if necessary. you can get my email
address at http://home.att.net/~california.db/tips.html#aTip11
follow the instructions to decode the address, and also change the 2 to a 1.

hth

Now I have things set up as I'd like them - I'm currently making all the
adjustments you instructed me to on a tabular autoform (so i can display more
than 1 record at a time). I have all the records displaying when I initially
switch to form view from design view but as soon as i try to requery with the
command button after selecting criteria in the combo box I get the following
error: " Runtime Error 3420 - Object invalid or no longer set"

Right now to try and keep things simple i've only added 1 combo box to sort
by builder. In the query built through the forms record source I added all
the fields I wanted displayed (Is this correct?) and under the criteria for
Builder(Instead of vendor - im in the construction industry) I entered

Forms!FormName!cboBuilder Or Forms!FormName!cboBuilder Is Null

After doing this and saving it the form's record source reads as follows:

SELECT tContacts.LastName, tContacts.FirstName, tContacts.Direct,
tContacts.Ext, tContacts.Mobile, tContacts.[Mike#], tContacts.[Fax#],
tContacts.[Pager#], tContacts.[E-mail], tContacts.Builder,
tContacts.Category, tContacts.Department, tContacts.[Job Title],
tContacts.Details FROM tContacts WHERE
(((tContacts.Builder)=Forms!Form2!cboBuilder Or Forms!Form2!cboBuilder Is
Null));

Am I making an obvious mistake?

Thanks for your patience and efforts tina

Ross

yes. open the form in design view. on the menu bar, click View | Field List.
highlight all the fields in the list, then drag them onto the Detail section
of the form and drop them.


i don't know what you mean by "sort". we've talking about *filtering* the
form's recordset. the *user* will choose values from the combo boxes'
droplists according to what records they want to see, and then click the
command button to requery the form's RecordSource. are you asking how to set
up the "droplist" of a combo box control?

hth


adding
all of
the displayed
in I'm
a see
into criteria,
as from
one

 

[Guest]

Thanks a ton tina I think I will take you up on that.

i don't see any obvious errors. i'm sure there's something that needs to be
"tweaked", but i can't think of any specific questions i can ask that will
lead us to the problem. i'm willing to take a look at the db if you want to
send me a copy. if you decide to take me up on the offer, make a copy of
your db and remove any proprietary data from the copy. then change the
file's extension from .mdb to .bak (do NOT open it after you make that
change!), and zip it to under 1 MB if necessary. you can get my email
address at http://home.att.net/~california.db/tips.html#aTip11
follow the instructions to decode the address, and also change the 2 to a 1.

hth

Now I have things set up as I'd like them - I'm currently making all the
adjustments you instructed me to on a tabular autoform (so i can display more
than 1 record at a time). I have all the records displaying when I initially
switch to form view from design view but as soon as i try to requery with the
command button after selecting criteria in the combo box I get the following
error: " Runtime Error 3420 - Object invalid or no longer set"

Right now to try and keep things simple i've only added 1 combo box to sort
by builder. In the query built through the forms record source I added all
the fields I wanted displayed (Is this correct?) and under the criteria for
Builder(Instead of vendor - im in the construction industry) I entered

Forms!FormName!cboBuilder Or Forms!FormName!cboBuilder Is Null

After doing this and saving it the form's record source reads as follows:

SELECT tContacts.LastName, tContacts.FirstName, tContacts.Direct,
tContacts.Ext, tContacts.Mobile, tContacts.[Mike#], tContacts.[Fax#],
tContacts.[Pager#], tContacts.[E-mail], tContacts.Builder,
tContacts.Category, tContacts.Department, tContacts.[Job Title],
tContacts.Details FROM tContacts WHERE
(((tContacts.Builder)=Forms!Form2!cboBuilder Or Forms!Form2!cboBuilder Is
Null));

Am I making an obvious mistake?

Thanks for your patience and efforts tina

Ross

Do I need to put the fields I want displayed in the details section of the
form?

yes. open the form in design view. on the menu bar, click View | Field List.
highlight all the fields in the list, then drag them onto the Detail section
of the form and drop them.

Is there anything I need to do to the combo boxes so they display the
values
i want to sort the displayed records by?

i don't know what you mean by "sort". we've talking about *filtering* the
form's recordset. the *user* will choose values from the combo boxes'
droplists according to what records they want to see, and then click the
command button to requery the form's RecordSource. are you asking how to set
up the "droplist" of a combo box control?

hth


Thanks tina, I did what you suggested but I think I'm missing some pieces:

I created the Query via the forms properties box as you suggested adding
all
the fields i wanted from the contacts tbl (ioncluding the builder,
category
and department fields) to the query and added the code as witten by you to
the criteria of each of the 3 fields, adjusting where needed the names of
the
form and the combo boxes.

However when I switch to form view after following all the steps (the form
properties, the combo boxes and the command buttons) nothing is displayed
in
the details section and the combo boxes arent assigned any values to
select
from.

Do I need to put the fields I want displayed in the details section of the
form?
Is there anything I need to do to the combo boxes so they display the
values
i want to sort the displayed records by?
Am I just missing some link between the SQL statement we createrd and the
forms details?
Sorry to be so bothersome but this is beyond what I've been taught so I'm
a
little lost as to how the records are being displayed on the form with
what
weve done so far.

Thanks for you last response tho it definitly cleared a few things up and
and taught me a few things, any more input from you would be great.

Lost Ross

:

to use one form only:

in the Header section of the form, add the unbound combo boxes. i'll
call
them cboVendor, cboCategory, and cboDepartment. in the form's Properties
box, click on the RecordSource property and then the ellipsis (...)
button
at the right side, which will take you into the Query Builder. add the
Contacts table to the top section, and pull the fields you want to see
into
the grid below. in the field that holds the vendor data, add the
following
criteria, as

Forms!FormName!cboVendor Or Forms!FormName!cboVendor Is Null

in the field that holds the category data, add the following criteria,
as

Forms!FormName!cboCategory Or Forms!FormName!cboCategory Is Null

do the same for the field that holds the department data.

back in the form Design view, add a command button to the Header
section.
add code to the button's Click event procedure, as

Me.Requery

now when you open the form in Form view, all the Contacts records should
display. choose a selection in any combination of the combo boxes, from
one
to all three, and click the command button to requery the form's
RecordSource. you may want to add another command button to "Display all
records" again. add code to the button's Click event procedure, as

Me!cboVendor = Null
Me!cboCategory = Null
Me!cboDepartment = Null
Me.Requery

 

[tina]

okay, i looked at your db; i'll answer you here rather than email, so others
may benefit.

the problem is in the combo box control properties' settings on "form2". you
added cboBuilder and set the RowSource to tBuilders, but didn't change any
other properties from the default values. tBuilders is a multi-field table,
so unless the primary key field is the very first field in the table (and it
is not, in this case), you can't get by with NOT changing the combo box
properties on the form. even if the primary key had been the first field in
tBuilders, i would still set up the combo box to show the names of the
builders, rather than the primary key - so you really can't get away without
tweaking the combo box settings.

change the combo box control settings as follows:

RowSource: SELECT BuilderID, Builder FROM tBuilders ORDER BY Builder;
<the Select statement goes all on one line in the property.>
ColumnCount: 2
ColumnWidths: 0";2"
<now the value of cboBuilders is the primary key field, as it should be, but
the user sees only the builders' names, again as it should be.>
ListWidth: 2.25"

also suggest you add the following code to cboBuilder's NotInList event
procedure, as

Me!cboBuilder.Undo
Me!cboBuilder.Dropdown

just makes things a little easier for your user.

also, just some general notes on your design:
- you turned off Name AutoCorrect in the database - very good.
- you related the "supporting" tables to the main data table correctly -
very good again.
- in each table, recommend you set the table's SubdatasheetName property to
[None]. see http://allenbrowne.com/bug-09.html and scroll down to Tables:
SubdatasheetName, for more info.
- in each table, recommend you set the AllowZeroLength property of each Text
and Memo field to No. at the same link as above, scroll to Fields: Allow
Zero Length, for more info.
- you used the # sign in several fieldnames in your tables; recommend you
don't use anything except alpha characters, underscores, and numeric
characters (numerics only if you must, and not at the beginning of the
fieldname) in the name of ANYTHING that *you* name, in the database - so i
suggest you also change the name of your query "qContacts (B&C)", to
something like "qContacts_BandC".
- your tContacts does not have a primary key; you need to set one right
away. none of the fields or combinations of fields in the table make a good
candidate for pk, so recommend you add an Autonumber field (ContactID) to do
the job.
- you did a good job of normalizing your data in some respects: first and
last names in separate fields; different parts of address (street, city,
province) in different fields. all very good.
- however, you're breaking one of the rules of normalization by putting data
(phone types, such as "direct", "mobile", "fax", "pager") into fieldnames,
in tContacts. recommend that you move the Contacts' phone numbers into a
separate table, as

tblContactPhones
PhoneID (pk, Autonumber)
ContactID (foreign key from tblContacts)
PhoneTypeID (foreign key from tblPhoneTypes)
PhoneNumber
Notes

tblPhoneTypes
PhoneTypeID (pk)
TypeName (direct, mobile, fax, pager, etc)

email and radio number fields can be left in tContacts.
you did the same thing with the phone numbers in tBuilders. here i might let
it slide, (though it still breaks normalization rules) if no builder will
ever have more than one MainLine and FaxNumber (i really hate to say "never"
when talking about any business process...), and if most builders will have
both.

hth

Now I have things set up as I'd like them - I'm currently making all the
adjustments you instructed me to on a tabular autoform (so i can display more
than 1 record at a time). I have all the records displaying when I initially
switch to form view from design view but as soon as i try to requery with the
command button after selecting criteria in the combo box I get the followi ng
error: " Runtime Error 3420 - Object invalid or no longer set"

Right now to try and keep things simple i've only added 1 combo box to sort
by builder. In the query built through the forms record source I added all
the fields I wanted displayed (Is this correct?) and under the criteria for
Builder(Instead of vendor - im in the construction industry) I entered

Forms!FormName!cboBuilder Or Forms!FormName!cboBuilder Is Null

After doing this and saving it the form's record source reads as follows:

SELECT tContacts.LastName, tContacts.FirstName, tContacts.Direct,
tContacts.Ext, tContacts.Mobile, tContacts.[Mike#], tContacts.[Fax#],
tContacts.[Pager#], tContacts.[E-mail], tContacts.Builder,
tContacts.Category, tContacts.Department, tContacts.[Job Title],
tContacts.Details FROM tContacts WHERE
(((tContacts.Builder)=Forms!Form2!cboBuilder Or Forms!Form2!cboBuilder Is
Null));

Am I making an obvious mistake?

Thanks for your patience and efforts tina

Ross

yes. open the form in design view. on the menu bar, click View | Field List.
highlight all the fields in the list, then drag them onto the Detail section
of the form and drop them.


i don't know what you mean by "sort". we've talking about *filtering* the
form's recordset. the *user* will choose values from the combo boxes'
droplists according to what records they want to see, and then click the
command button to requery the form's RecordSource. are you asking how to set
up the "droplist" of a combo box control?

hth


adding
all of
the displayed
in I'm
a see
into criteria,
as from
one

 

[Guest]

Thanks again for all that tina, I've made all the adjustments you suggested
yet I'm still fuzzy on how I will display all the data for each contact once
I move the phone numbers into a separate table. I assume I would use a query
to combine the information from the different tables such as the contacts
personal info, the builder's, category's and dept's name (as opposed to ID),
and the phone numbers then from there, base my forms off the query.

First of all is this correct? And second what happens in the event of a
contactID showing up more than once in tblContactPhones as a result of more
than 1 phone number being assigned to that person (such as direct phone# and
cell#)?

How will the query dispaly multiple phone#'s for the same contact if I only
want 1 record per contact?

ty4yt

Ross

 

[tina]

use a mainform/subform setup to display multiple child (phone) records for
one parent (contact) record. if you want to see the parent records in a
continuous or datasheet format, yet still see the related child records, you
have to get a little creative. one way is to use two subforms on one main
form. the first subform is bound to the parent table. add an unbound
textbox, which i'll call txtPK, to the main form, and set its' Visible
property to False. set its' ControlSource to
=[NameOfSubformControl].[Form]![NameOfPKFieldInParentTable]
the second subform is bound to the child table. in that subform control's
LinkChildFields property, enter the name of the *foreign* key field in the
child table that links back to the parent table. in the LinkMasterFields
property, enter the name of the unbound textbox on the mainform, as
[txtPK]

now the child subform is indirectly linked to the parent subform, via the
unbound textbox control on the main form. as you move from record to record
in the parent subform, the related child records will show in the child
subform. you can still use unbound combo box controls to search the parent
records; just put the controls on the main form. in the parent subform's
RecordSource SQL statement, change the criteria to refer to the main form,
as

[Forms]![MainFormName]![cboWhatever] Or [Forms]![MainFormName]![cboWhatever]
Is Null

hth

 

[Guest]

Tina....I hope you see this message...I followed your instructions above to
add 3 combo boxes to a header of a form with the details below. Now, when I
add one combo box...it works great...as soon as I add another combo box
nothing works...no data is returned. Why when i add more than 1 combo box
does it not bring back results? Any ideas? Or if you need more from me.
Thanks much!

use a mainform/subform setup to display multiple child (phone) records for
one parent (contact) record. if you want to see the parent records in a
continuous or datasheet format, yet still see the related child records, you
have to get a little creative. one way is to use two subforms on one main
form. the first subform is bound to the parent table. add an unbound
textbox, which i'll call txtPK, to the main form, and set its' Visible
property to False. set its' ControlSource to
=[NameOfSubformControl].[Form]![NameOfPKFieldInParentTable]
the second subform is bound to the child table. in that subform control's
LinkChildFields property, enter the name of the *foreign* key field in the
child table that links back to the parent table. in the LinkMasterFields
property, enter the name of the unbound textbox on the mainform, as
[txtPK]

now the child subform is indirectly linked to the parent subform, via the
unbound textbox control on the main form. as you move from record to record
in the parent subform, the related child records will show in the child
subform. you can still use unbound combo box controls to search the parent
records; just put the controls on the main form. in the parent subform's
RecordSource SQL statement, change the criteria to refer to the main form,
as

[Forms]![MainFormName]![cboWhatever] Or [Forms]![MainFormName]![cboWhatever]
Is Null

hth

Thanks again for all that tina, I've made all the adjustments you suggested
yet I'm still fuzzy on how I will display all the data for each contact once
I move the phone numbers into a separate table. I assume I would use a query
to combine the information from the different tables such as the contacts
personal info, the builder's, category's and dept's name (as opposed to ID),
and the phone numbers then from there, base my forms off the query.

First of all is this correct? And second what happens in the event of a
contactID showing up more than once in tblContactPhones as a result of more
than 1 phone number being assigned to that person (such as direct phone# and
cell#)?

How will the query dispaly multiple phone#'s for the same contact if I only
want 1 record per contact?

ty4yt

Ross