Hierarchical cbos to be used in search

Jeanette
I have the three table relationships as you described and understand the
hierarchial nature of the relationship.
fmAddNew would not run when a querySubcategory was the recordsource. I had
been using tblSubcategories which delivers subcategoryID and subcategory as
the recordsource. The combos category and subcategory work OK on main form.
I made the subform based on tblPublications (however, when looking at
properties of this subform it states "source object Subform Publications" and
not tblPublications?? I have deleted and rebuilt this subform many times, but
it keeps stating "source object Subform Publications). The link between child
and master is SubcategoryID. I have the subcategory field on the subform.
What I don't understand is your final point:

--The after update event for cboSubcategory has code made by the wizard to
set the bookmark of the subform--

I tried to work out how to do this task but cannot. Maybe this is the link
that is causing the problem of not copying the SubcategoryID to
tblPublications when entering a new publication. I have never heard of
setting a bookmark

Also, your final comment:
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.

Would you be able to provide suitable code for grabbing CategoryID from the
cboCategory and including this into tblSubcategory.

Many thanks again.
I have also had a few problems with the advice you provided on fmSearch in
your other post, but I'll try and resolve fmAddNew first.

STeven


"Jeanette Cunningham" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
news Yes, that is the way I have my table relationships set-up, but as
mentioned
before, the wizard only delivers two options.


"Jeanette Cunningham" wrote:

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

Steven,
glad you are hanging in there. It will be much quicker to send you a working
database with frmAddNew in it than trying to write many lines of explanation
on how to do it. The working example only takes me 5 minutes to make.
You will be able to open the working example, see how it works and use it to
get yours working or use it as you wish.
I can provide the sample in Access version 2000. The sample will work in
versions of Access from 2000 and above.
If you are using Access 2000, 2002 or 2003 and if you are happy to get the
sample from me, contact me via email with an email address I can send the
sample to.
You can get my e mail address by removing the spaces from the following
jj cc 14 @ gmail .com


Note: I don't usually provide a sample, but it will save me a lot of time
and typing and you will have a very basic database with 3 tables and 2
working forms.

Jeanette Cunningham


"SJW" wrote in message
...
Jeanette
I have the three table relationships as you described and understand the
hierarchial nature of the relationship.
fmAddNew would not run when a querySubcategory was the recordsource. I had
been using tblSubcategories which delivers subcategoryID and subcategory
as
the recordsource. The combos category and subcategory work OK on main
form.
I made the subform based on tblPublications (however, when looking at
properties of this subform it states "source object Subform Publications"
and
not tblPublications?? I have deleted and rebuilt this subform many times,
but
it keeps stating "source object Subform Publications). The link between
child
and master is SubcategoryID. I have the subcategory field on the subform.
What I don't understand is your final point:

--The after update event for cboSubcategory has code made by the wizard to
set the bookmark of the subform--

I tried to work out how to do this task but cannot. Maybe this is the link
that is causing the problem of not copying the SubcategoryID to
tblPublications when entering a new publication. I have never heard of
setting a bookmark

Also, your final comment:
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.

Would you be able to provide suitable code for grabbing CategoryID from
the
cboCategory and including this into tblSubcategory.

Many thanks again.
I have also had a few problems with the advice you provided on fmSearch in
your other post, but I'll try and resolve fmAddNew first.

STeven


"Jeanette Cunningham" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
news Yes, that is the way I have my table relationships set-up, but
as
mentioned
before, the wizard only delivers two options.


"Jeanette Cunningham" wrote:

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