Hierarchical cbos to be used in search

[SJW]

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]

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