Hierarchical cbos to be used in search

[SJW]

I am trying to build a database that sorts hundreds of publications stored on
our network. First, the User needs to select the category and then the
sub-category of their desired publication. Therefore, I have three combos
which sort hierarchical data. ie. cboCategory to cboSubcategory to
cboPublication. These combos are unbound and work OK. However, once I try to
use these cbos in other ways, problems start.

I want to be able to use these three combos in two ways.
1. I want the user to be able to add new publications. By using these cbos.
the user chooses the category and sub-category. The user should then be able
to add the title of the new publication in the cboPublication. Following
that, they enter other details into fields eg. date, author, abstract etc.
including a hyperlink to the document (if a soft-copy is available on file).
2. On another form, I would like the user to be able to search for their
desired publication. Once the Category and Subcategory have been selected, a
list of publications is provided in the third cbo. The user selects a
publication from the list, and then all details of that publication are
provided eg. author, date etc. including a hyperlink to the document.

For the first part, I used the Form "AddNewPublication" and bound it to
tblPubdetails. But the three combos do not work once they are bound to a
field in this table.
I hope this explanation makes sense. I am using Access2003. Grateful any
advice.
thanks
SJW

 

[Jeanette Cunningham]

Hi SJW,
Yes this is how Access does things.
The 3 unbound combos can be used in a search form to allow users to find a
publication.

If you want to use combos to add a new publication, you need a separate
form, which it sounds that you already have.
To add a new publication, you need to think of all the tables that need an
entry in them.
If the user is going to add a new publication, they can't just add it to the
table of publications.
The wizard should be able to build you a main from with subform which will
allow users to add a new publication.
This form and subform will be separate from your search form where the user
looks for a publication.

Jeanette Cunningham

 

[SJW]

thanks for the response.
I added a subform to my main form for adding new publications. However, now
I cannot enter the title of a new publication into my cboPublication (which
is on the main form). An error message saying "The Text you entered was not
on the list. Select item from the list or enter text that matches one of the
listed items". But there are no publication details as yet ie. its blank.
Maybe some more details of what I have at present.

three combos - cboCategory, CboSubcategory and CboPublication on the main
form.

The RowSource for cboCategory is: SELECT tblCategories.CategoryID,
tblCategories.Category FROM tblCategories;

The afterUpdate code for cboCategory is

Private Sub ComboCategory_AfterUpdate()

' if Category is updated then erase current values
' for Subcategory and Publication and requery combo boxes
' to show Subcategories of selected Category and empty list
' of Publicatios (pending selection of a Subcategory)
Me!ComboSubcategory = Null
Me!ComboPublication = Null
Me!ComboSubcategory.Requery
Me!ComboPublication.Requery

For cboSubcategory
Row source is : SELECT tblSubcategories.SubcategoryID,
tblSubcategories.Subcategory FROM tblSubcategories WHERE
tblSubcategories.CategoryID=Form!comboCategory ORDER BY
tblSubcategories.Subcategory;

and AfterUpdate:

Private Sub ComboSubcategory_AfterUpdate()

' requery Publication combo box to
' show publications of selected Subcategory
Me!ComboPublication.Requery

End Sub

For cboPublication
RowSource is :
SELECT tblPublications.PublicationID, tblPublications.Publication FROM
tblPublications WHERE
(((tblPublications.SubcategoryID)=Form!ComboSubcategory)) ORDER BY
tblPublications.Publication;

No AfterUpdate entry.

the Subform has fields for sub-title, author, date, and abstract. based on
tblPubDetails.


Any further assistance appreciated.
thanks
SJW

 

[Jeanette Cunningham]

Hi,
Here is how I would do it.
Remove the cboPublication completely from the main form.
The main form for adding new publications lets you select the category and
sub-category for a new publication, and it will pass the value for
sub-category to the subform through the link master fields and link child
fields.
On the subform there needs to be a textbox to type in the name of the new
publication along with the other details of the new publication.

I assume you have tables like this:
a table for Category
a table for Subcategory
a table for Publication

If not please post details of your tables

Jeanette Cunningham

 

[Ken Sheridan]

You could (but see below as to why you don't need to) open a form, bound to
the Publications table via the NotInList event procedure of the publications
combo box. Here's some code which does this for adding a new city to a
Cities table:

Private Sub cboCities_NotInList(NewData As String, Response As Integer)

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmCities", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmCities closed
DoCmd.Close acForm, "frmCities"
' ensure city has been added
If Not IsNull(DLookup("CityID", "Cities", "City = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Cities table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

Then in the Open event procedure of the frmCities form goes this code:

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
Me.City.DefaultValue = """" & Me.OpenArgs & """"
End If

End Sub

The city would correspond to your publication, so your bound publications
form would have a bound combo box to select the sub-category, in the same way
that my frmCities form has one to select the County in which the city is
located.

HOWEVER! As in your case you've presumably already selected a sub-category
in the second combo box, however, you can omit opening the bound publications
form completely and insert the row directly into the table. Here's code for
the NotInList event procedure, again for cities which does this:

Dim cmd As ADODB.Command
Dim ctrl As Control
Dim strSQL As String, strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add new city to list?"

strSQL = "INSERT INTO Cities(City) VALUES(""" & _
NewData & """)"

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
cmd.CommandText = strSQL
cmd.Execute
Response = acDataErrAdded
Else
Response = acDataErrContinue
ctrl.Undo
End If

Set cmd = Nothing

You'd have to expand theSQL statement of course to include the sub-category.
The SQL statement would be built like this:

strSQL = "INSERT INTO tblPublications(Publication, SubCategoryID)
VALUES(""" & _
NewData & """," & Me.ComboSubCategory & ")"

This assumes that the PublicationID column is an autonumber whose value will
be inserted automatically by the system when the new row is inserted by the
above SQL statement.

Ken Sheridan
Stafford, England

 

[SJW]

Thanks for your quick response. My tables are as you noted below.

I decided to go your route, and open the Subform. I added all the text boxes
necessary, and bound the subform to tblPublications. tblSubcategory is linked
to tblPublications as a one-to-many relationship. I used the SubcategoryID as
the link between masterfields and childfields.

Now I get a stuck and not sure what I describe next makes sense or not.

I cannot get the SubcategoryID to record in the subcategoryID field in
tblPublications. I want to be able to create another form that allows the
user to search for desired publications using the Category/Subcategory fields
to provide a list of publications. Therefore, I thought it would be necessary
for SubcategoryID to record on tblPublications in order to allow such a
search to occur.

Any further advice appreciated.
thanks
SJW

 

[Jeanette Cunningham]

Hi SJW,
when learning to do this, it is easier to have one form to show publications
by category and a separate form when you want to add new publications.

Here is how to do it to search for publications.
--Open your form in design view.
--Remove any textboxes and combos from the main form (not the subform).
--On the toolbox click on the combobox tool and with the mouse draw a
rectangle on the main form (not the subform).
--The wizard will open, choose the 3rd option Find a record on my form . . .
..
--Choose subcategory ID and subcategory and follow the prompts.
--You should now have a form where you can choose the subcategory and the
subform will show the publications for it.
--To be able to choose by Category, you need another combobox, this time
with the wizard choose option 1, I want the combo to lookup . . .
--Select CategoryID, Category and follow the prompts.
--Now you can add code to the after update event for the Category combo to
set the values to show in the subcategory combo.

Post back if you want info on how to do this step

Jeanette Cunningham

 

[SJW]

Jeanette
Thanks for your patience.
What you describe below is pretty close to what I already have done on
FmAddingNew. (however, on my combobox wizard there are only two options at
the first step either the cbo looks up existing values from a table or query
OR you type in the values required ie. there is no third option for cbo to
look up values from a form....)

I thought it best to try and get the form for entering new publication
details (FmAddingNew) right before moving onto the form for searching. Once I
have the first form correct, then I can adapt it for the Search form. I am
starting from scratch and want to be able to enter the details of all our
publications via. the 'Enter New Publication' form (ie FmAddingNew). Select
Category, then Subcategory, then enter all the details on the Subform. I have
the Subform based on tblPublications, and links master to child via
SubcategoryID.

However, the problem is the SubcategoryID is not recording onto the
tblPublications. If it was not appearing on this table, then how could the
user use FmSearch later on to find the relevant publications??

I might be approaching this task the wrong way around, but it seemed this
approach was the most logical.

thanks for your answers.
Steven

 

[SJW]

Dear Ken
thanks for your advice. I was using your cbo demonstration with County,
District and parish picked-up from an earlier thread. Also, thanks for your
explanation on the error msg I was getting on Category. As you can see from
this thread, I decided to use the Subform approach rather than trying to use
the third cbo in the hierarchy.
regards
SJW

 

[Jeanette Cunningham]

SJW,
most likely, its the way you have set up your tables that stops the form
wizard from showing the 3rd option that you need.
Let's check how you have set up your tables
I did this with the tables set up like this:

--tblCategories ...
--CategoryID >Primary Key
--Category
--Other fields for Category info

--tblSubcategories ...
--SubcategoryID >Primary Key
--Subcategory
--Other fields for Subcategory info

--tblPublications ...
--PublicationID >Primary Key
--Publication
--Other fields for Publication info

tblCategories related 1:many with tblSubcategories
tblSubcategories related 1:many with tblPublications

Set up your tables like I have.

Create the forms again using the wizard, (using the description in the
previous post). This will only take 5 mins.
Don't do any work on the form such as changing positions, colours, sizes etc
until you have it working to add info the way you want it.

Jeanette Cunningham

 

[SJW]

Yes, that is the way I have my table relationships set-up, but as mentioned
before, the wizard only delivers two options.

 

[Jeanette Cunningham]

Steven,
I will detail how I used the form wizard as I think it's the easiest way
for someone getting started with setting up forms.
the first wizard step, choose only 2 of the tables
--choose tblSubcategories, choose SubcategoryID and Subcategory
--choose tblPublications, choose PublicationID and Publication

See if this choice allows the 3rd wizard option

Jeanette Cunningham

 

[SJW]

Yes Jeanette, that did it. Using the wizard provided the third choice.
I have now included cboSubcategory and cboCategory on main form, and
SubfmPublications with master child links via SubcategoryID.
The form is working OK for entering new data. I have also used code for
NotInList field on these two cbos, which allows the user to enter new
category and subcategory information.

I was thinking that i would be able to adapt this AddNewForm to make a
SearchForm. By using the cboCategory and cboSubcategory on the main form of
SearchForm, the user would be able to filter through all the publications,
and produce a list at the end. However, now i'm stuck because I already have
code in the AfterUpdate field.
could you suggest the right code to use for listing the publications.

many thanks again
Steven

 

[Jeanette Cunningham]

Steven,
I'm not fully understanding the problem. Maybe your subform has its Default
View property set to Single Form?, if you change this property to datasheet
or continuous forms, it will show all the publications for a particular
subcategory.

Jeanette Cunningham

 

[SJW]

Sorry Jeanette, didn't explain myself clearly.
I have a main switchboard which directs the user to either add a new
publication or view a publication. I thought that because the fmAddNew was
working OK for adding a new publication, I could simply copy this form and
adapt it for fmSearch, to put on the View Publication page. However, it
doesn't work because the subform is the same on each form, and therefore the
subform cannot be altered on fmSearch without effecting fmAddNew.

What I wanted to do on fmSearch, was provide a third cbo linked to
cboSubcategory, which would list all the publications under the chosen
Category and Subcategory. The user could then select the desired title from
the list and view all details of the chosen publication.

I suppose it requires building fmSearch with another subform, rather than
simply copying fmAddNew. But if you could recommend a more effective way, it
would be appreciated.
many thanks
Steven

 

[Jeanette Cunningham]

Steven,
you can use the same subform on more than one form as long as only one of
the main forms is open at the same time.

Build a new search form with the 3rd combo, use the same subform. You may
need to put code on its BeforeInsert event, to cancel the insert to prevent
users adding new records, and set its allow edits to false to stop users
changing the data.

It's just as easy to make a copy of the subform you have now, change its
properties the way you need them and use that.


Jeanette Cunningham

 

[SJW]

Jeanette
This is becoming frustrating.
I copied the form and added a third cboPublication to the main form. These
three hierarchical combos work well, however, the publication details do not
appear in the subform once the publication is chosen in the third combo.

Also, before the FmAddNew was working OK, however, it is now not recording
the SubcategoryID to tblPublications and therefore, new publications cannot
be recalled.

Also, on the FmAddNew (for adding new publication details) I had added code
to NotInList on the two combos (cboCategory, cboSubcategory) to allow users
to add new Categories and Subcategories into these combos, but my code
doesn't work now. I used,

Private Sub ComboCategory_NotInList(NewData As String, Response As Integer)

Dim db As Database
Set db = CurrentDb

'Ask the user if they want to add to the list
If MsgBox("Do you want to add this entity to the list?", vbYesNo +
vbQuestion, "Add new value?") = vbYes Then

'The user clicked Yes - add the new value
db.Execute "INSERT INTO tblCategories (Category) VALUES (""" & NewData &
""")", dbFailOnError

'Tell Access you've added the new value
Response = acDataErrAdded

Else

'The user clicked No - discard the new value
Me.ComboCategory.Undo
'Tell Access you've discarded the new value
Response = acDataErrContinue

End If

db.Close
Set db = Nothing

End Sub
and similar code for cboSubcategory.

After selecting the category from cboCategory and trying to type in a new
subcategory into cboSubcategory, I get - runtime Error 3201, Cannot add or
change a record because a related record is required in tblCategories.
However, I thought I'd just chosen the Category from the cboCategory.

I hope all this makes sense, I have been trying for hours to try and work
out where I am going wrong.
Further advice much appreciated.
thanks
Steven

 

[Jeanette Cunningham]

Steven,
we are talking about the search form to show publications?
Have the recordsource for the main form set to a query which fetches the ID
for the publications table.
The subform has a recordsource which shows publications and their details.
The link master and child fields is the ID for the publications table.
All 3 combos are unbound.
The publications combo has 2 columns, 1st column is hidden and has the ID
for publications, the 2nd column has the publication name.
The subform has allow edits set to No.
The main form has allow edits set to Yes, data entry set to No, allow
additions set to No.

An easy way is to delete the 3rd combo for publications and create a new one
using the wizard.
With the wizard choose the 3rd option - find a record based on a value from
the combo.

That should get the search form working.

I will post back about the form for adding new publications.

Jeanette Cunningham

 

[Jeanette Cunningham]

Steven,
now we look at the frmaddnew that was previously working.
Here I will explain a little bit how the relationships between the tables
work.
tblCategories is at the top
tblSubcategories is downstream
tblPublications is downstream from both tblSubcategories and tblCategories

To add a subcategory, the database needs to know which category to add the
subcategory to.
So you can't just add a subcategory to tblSubcategories, you need to tell
Access which category to put the subcategory in.
Because tblSubcategories is upstream of tblPublications, whenever you want
to add a
new publication, you have to know the ID field for the upstream table. To
add a new publication, accesss
needs to know which subcategory to put the new publication
in.

Let's review frmAddNew and make any changes so it fits what I have below.
We will ignore the combo for Category and get the subcategory combo and add
new publications working first.
--Comment out all the code you added for both not in list events.
--frmAddNew has qrySubcategory for its recordsource.
--qrySubcategory has 2 fields, SubcategoryID and the name of the
subcategory.
--The subform on frmAddNew has tblPublications for its recordsource.
--The subform has a textbox for each field in tblPublications, make sure the
textbox for SubcategoryID is there on the subform.
--The after update event for cboSubcategory has code made by the wizard to
set the bookmark of the subform.

The user selects a subcategory and they can add a new publication, but if
the combo for subcategory is empty, they can't add a new publication.
We can add code to remind the user to choose a subcategory first.
(Note:cboSubcategory is the name of the combo for subcategory, you replace
it with the name of your combo)

Private Sub Form_BeforeInsert(Cancel As Integer)
If IsNull(Parent.cboSubcategory) Then
Cancel = True
MsgBox "You must choose a subcategory"
Else
End If

End Sub

There is a similar situation if a user wants to add a new subcategory -
Access needs to have the ID for any tables upstream of tblSubcategories.
In your code for the not in list event for cboSubcategory, you need to grab
the value for CategoryID from the combo for Category.
Your code must add both the value for CategoryID and the name for the new
subcategory to tblSubcategory in the not in list event.

See if this much will work as shown above and post back when you need to.

Jeanette Cunningham

 

[SJW]

Jeanette
Many thanks for your assistance.
I am working on this database in Afghanistan, and electricity and Internet
are not reliable. It will take me a couple of days to work through what you
have just sent.
So, please bear with me.
thanks again
Steven