We wish to sort a large number of publications by category, then subcategory
by the user selecting a category from cboCategory and then a subcategory on
cboSubcategory on main form (fmAddNew).

The user then records title, date, author etc. in textboxes on a subform.
The subform is based on tblPublications, with master, child links between the
main form and subform are SubcategoryID. The relationships are tblCategory
to tblSubcategory (one to many) and then tblSubcategory to tblPublications
(one to many).

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

The AfterUpdate includes:
Private Sub ComboCategory_AfterUpdate()

' if Category is updated then erase current values
' for Subcategory and requery combo box
' to show Subcategories of selected Category
Me!ComboSubcategory = Null
Me!ComboSubcategory.Requery

For comboSubcategory
RowSource:
SELECT tblSubcategories.SubcategoryID, tblSubcategories.Subcategory FROM
tblSubcategories WHERE tblSubcategories.CategoryID=Form!comboCategory ORDER
BY tblSubcategories.Subcategory;

AfterUpdate code:
Private Sub ComboSubcategory_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SubcategoryID] = " & Str(Nz(Me![ComboSubcategory], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

We cannot get SubcategoryID to record into tblPublications each time a new
publication is entered into the database.

We would like the user to be able search the database on another form
(fmSearch) through the same combos (plus a third combo which provides a list
of publications sorted through category then subcategory) and allow the user
to select their preferred publication title from a drop-down list. Once the
publication is chosen, then all details pertaining to that publication are
displayed on fmSearch.

However, this is not possible. We presume it is because of the problem with
recording SubcategoryID onto tblPublications. but of course we may be wrong.

We are using Access2003.
any advice appreciated.
SJW

As cboSubCategory is an unbound control the LinkMasterFields property of the
subform control should be the name of the control, i.e. cboSubCategory. The
LinkChildFields property should stay as SubCategoryID. When you insert a new
record in the subform the SubCategoryID field will automatically be given the
value of the combo box on the Parent form.

For the search form you'd have three unbound combo boxes, the last two
correlated by references to the next higher control in the usual way and
requeried in the next higher control's AfterUpdate event procedure. The
form's RecordSource could by default be SELECT * FROM tblPublications ORDER
BY Title; and it could include controls bound to whatever fields you want to
show on the form for each publication.

In the AfterUpdate event procedure of the final combo box, cboPublication
say whose value would be the PublicationID you can change the form's
RecordSource property and requery the form with:

Dim strSQL As String

strSQL = "SELECT * FROM tblPublications " & _
"WHERE PublicationID = " & Me.cboPublication

Me.Requery

You might also want to include a Show All button on the form with the
following code in its Click event procedure:

Dim strSQL As String

strSQL = "SELECT * FROM tblPublications " & _
"ORDER BY Title"

Me.cboCategory = Null
Me.cboSubCategory = Null
Me.cboPublication = Null

Me.Requery

BTW you might be interested in the demo of various ways of using correlated
combo boxes at:


http://community.netscape.com/n/pfx/...g=ws-msdevapps


It uses a hierarchy of the administrative areas in my location, but the
principles are the same.

Ken Sheridan
Stafford, England

"SJW" wrote:

> We wish to sort a large number of publications by category, then subcategory
> by the user selecting a category from cboCategory and then a subcategory on
> cboSubcategory on main form (fmAddNew).
>
> The user then records title, date, author etc. in textboxes on a subform.
> The subform is based on tblPublications, with master, child links between the
> main form and subform are SubcategoryID. The relationships are tblCategory
> to tblSubcategory (one to many) and then tblSubcategory to tblPublications
> (one to many).
>
> The rowsource for cboCategory is
> SELECT tblCategories.CategoryID, tblCategories.Category FROM tblCategories;
>
> The AfterUpdate includes:
> Private Sub ComboCategory_AfterUpdate()
>
> ' if Category is updated then erase current values
> ' for Subcategory and requery combo box
> ' to show Subcategories of selected Category
> Me!ComboSubcategory = Null
> Me!ComboSubcategory.Requery
>
> For comboSubcategory
> RowSource:
> SELECT tblSubcategories.SubcategoryID, tblSubcategories.Subcategory FROM
> tblSubcategories WHERE tblSubcategories.CategoryID=Form!comboCategory ORDER
> BY tblSubcategories.Subcategory;
>
> AfterUpdate code:
> Private Sub ComboSubcategory_AfterUpdate()
> ' Find the record that matches the control.
> Dim rs As Object
>
> Set rs = Me.Recordset.Clone
> rs.FindFirst "[SubcategoryID] = " & Str(Nz(Me![ComboSubcategory], 0))
> If Not rs.EOF Then Me.Bookmark = rs.Bookmark
> End Sub
>
> We cannot get SubcategoryID to record into tblPublications each time a new
> publication is entered into the database.
>
> We would like the user to be able search the database on another form
> (fmSearch) through the same combos (plus a third combo which provides a list
> of publications sorted through category then subcategory) and allow the user
> to select their preferred publication title from a drop-down list. Once the
> publication is chosen, then all details pertaining to that publication are
> displayed on fmSearch.
>
> However, this is not possible. We presume it is because of the problem with
> recording SubcategoryID onto tblPublications. but of course we may be wrong.
>
> We are using Access2003.
> any advice appreciated.
> SJW
>

Many thanks Ken.
I knew my problem was something simple and changing the MasterLink did the
trick.
You should notice that most of the code I have used is based on your County,
District, Parish example. I found it most useful.
I used only two hierarchical combos on the form for adding new publications
(fmAddNew). However, I have used three cbos on the Search form. Following
selection of Category, Subcategory and Publication the details of the slected
publication are displayed within a subform.
I have two remaining problems.
1. I would like the user to be able to add a new subcategory and/or category
to the combos on fmAddNew. The code I have used for NotOnList causes too many
problems and definitely incorrect.
2. The command buttons I have used on fmAddNew don't seem to work. The user
should be able to save a record, delete a record and add a new record on this
form. However, the buttons i have used from the toolkit just don't work.
Preferably after the user has entered details of a new publication, they
click the 'save record' button, then click the 'add new record' button which
should clear the form and provide blank boxes for the user to fill in details
of a new record.
There may be an easier way to do this, so I am open to suggestions.
thanks again
Steven

"Ken Sheridan" wrote:

> As cboSubCategory is an unbound control the LinkMasterFields property of the
> subform control should be the name of the control, i.e. cboSubCategory. The
> LinkChildFields property should stay as SubCategoryID. When you insert a new
> record in the subform the SubCategoryID field will automatically be given the
> value of the combo box on the Parent form.
>
> For the search form you'd have three unbound combo boxes, the last two
> correlated by references to the next higher control in the usual way and
> requeried in the next higher control's AfterUpdate event procedure. The
> form's RecordSource could by default be SELECT * FROM tblPublications ORDER
> BY Title; and it could include controls bound to whatever fields you want to
> show on the form for each publication.
>
> In the AfterUpdate event procedure of the final combo box, cboPublication
> say whose value would be the PublicationID you can change the form's
> RecordSource property and requery the form with:
>
> Dim strSQL As String
>
> strSQL = "SELECT * FROM tblPublications " & _
> "WHERE PublicationID = " & Me.cboPublication
>
> Me.Requery
>
> You might also want to include a Show All button on the form with the
> following code in its Click event procedure:
>
> Dim strSQL As String
>
> strSQL = "SELECT * FROM tblPublications " & _
> "ORDER BY Title"
>
> Me.cboCategory = Null
> Me.cboSubCategory = Null
> Me.cboPublication = Null
>
> Me.Requery
>
> BTW you might be interested in the demo of various ways of using correlated
> combo boxes at:
>
>
> http://community.netscape.com/n/pfx/...g=ws-msdevapps
>
>
> It uses a hierarchy of the administrative areas in my location, but the
> principles are the same.
>
> Ken Sheridan
> Stafford, England
>
> "SJW" wrote:
>
> > We wish to sort a large number of publications by category, then subcategory
> > by the user selecting a category from cboCategory and then a subcategory on
> > cboSubcategory on main form (fmAddNew).
> >
> > The user then records title, date, author etc. in textboxes on a subform.
> > The subform is based on tblPublications, with master, child links between the
> > main form and subform are SubcategoryID. The relationships are tblCategory
> > to tblSubcategory (one to many) and then tblSubcategory to tblPublications
> > (one to many).
> >
> > The rowsource for cboCategory is
> > SELECT tblCategories.CategoryID, tblCategories.Category FROM tblCategories;
> >
> > The AfterUpdate includes:
> > Private Sub ComboCategory_AfterUpdate()
> >
> > ' if Category is updated then erase current values
> > ' for Subcategory and requery combo box
> > ' to show Subcategories of selected Category
> > Me!ComboSubcategory = Null
> > Me!ComboSubcategory.Requery
> >
> > For comboSubcategory
> > RowSource:
> > SELECT tblSubcategories.SubcategoryID, tblSubcategories.Subcategory FROM
> > tblSubcategories WHERE tblSubcategories.CategoryID=Form!comboCategory ORDER
> > BY tblSubcategories.Subcategory;
> >
> > AfterUpdate code:
> > Private Sub ComboSubcategory_AfterUpdate()
> > ' Find the record that matches the control.
> > Dim rs As Object
> >
> > Set rs = Me.Recordset.Clone
> > rs.FindFirst "[SubcategoryID] = " & Str(Nz(Me![ComboSubcategory], 0))
> > If Not rs.EOF Then Me.Bookmark = rs.Bookmark
> > End Sub
> >
> > We cannot get SubcategoryID to record into tblPublications each time a new
> > publication is entered into the database.
> >
> > We would like the user to be able search the database on another form
> > (fmSearch) through the same combos (plus a third combo which provides a list
> > of publications sorted through category then subcategory) and allow the user
> > to select their preferred publication title from a drop-down list. Once the
> > publication is chosen, then all details pertaining to that publication are
> > displayed on fmSearch.
> >
> > However, this is not possible. We presume it is because of the problem with
> > recording SubcategoryID onto tblPublications. but of course we may be wrong.
> >
> > We are using Access2003.
> > any advice appreciated.
> > SJW
> >
>

Steven:

As regards adding new records via the combo boxes it depends on whether the
text you enter in the combo box is the only data which needs to be entered,
other than an autonumber ID, or whether there are other columns in the table
which need to be entered. In the first case you can do it all via the combo
box, in the latter case you normally need to open a form to enter the other
data, but possibly not in your case for reasons given below.

I'd imagine a new Category needs only the one value entered, i.e. the
category name, CategoryID being an autonumber. In the case of a new
SubCategory you also need to enter the ID of the category of which the
sub-category is a part, but as you'll have already selected this in the first
combo box then you don't need to do the usual thing and open a form based on
the tblSubCategories table.

So the code for the category combo box's NotInList event procedure would be
along these lines:

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

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

strSQL = "INSERT INTO tblCategories(Category) 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

The code for the sub-categories combo box would be similar, but would extend
the SQL string so that values are inserted into both the SubCategory and
CategoryID columns, and would also confim that a category has first been
selected:

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

Set ctrl = Me.ActiveControl
If IsNull(Me.cboCategory) Then
strMessage = "Please select a category first."
Msgbox strMessage, vbExclamation, "Invalid Operation"
Response = acDataErrContinue
ctrl.Undo
Else
strMessage = "Add new sub-category of " & _
Me.cboCategory,Column(1) & " to list?"

strSQL = "INSERT INTO tblSubCategories(SubCategory,CategoryID) "
& _
"VALUES(""" & NewData & ""," & Me.cboCategory & ")"

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
End If

As for the command buttons, frmAddNew is, as I understand it, an unbound
form with a bound subform within it. Consequently, there can be no record to
save or delete in the parent from, only in the subform. You could include
buttons in the subform's header or footer to save or delete rows from the
tblPublications table if the subform is in continuous form view, or in its
detail section if its in single form view. If its in datasheet view then you
can use the built in menu commands or toolbar buttons. If you wanted to
delete rows from the categories or sub-categories tables via the Parent from,
then you'd need button's on the parent form which execute SQL DELETE
statements to delete the row for the category or sub-category selected in the
combo boxes, followed by requerying the combo boxes. If, when you delete a
category you want all its sub-categories automatically deleted then you
should enforce cascade deletes in the relationship between the categories and
sub-categories tables. Otherwise, assuming referential integrity is
enforced, as it should be, you would not be able to delete a category until
all of its sub-categories had been deleted.

To clear the Parent form just set the combo boxes to Null and requery
cboSubCategory:

Me.cboCategory = Null
Me.cboSubCategory = Null
Me.cboSubcategory.Requery

This time it can be done from a button on the Parent form. The subform
should then automatically show no publication records until a category and
sub-category is selected in the Parent form's combo boxes.

Finally one point I meant to mention in my first reply (apologies if I've
already said this in the past), but you seem to have a field for the author
in the tblPublications table, so it would not handle multiple authors for a
publication (which is common in scientific literature of course). Also if
you don't have an Authors table which it can reference there is a strong risk
of inconsistent data creeping it; I have personal experience of this, having
encountered three versions of my own name as author in a table of technical
sources. The relationship between publications and authors is potentially
many-to-many, i.e. each author might have written one or more publications,
and each publication might have one or more authors. So you'd need a
PublicationAuthors table to model this, with PublicationID and AuthorID
foreign keys columns referencing the primary keys of the publications and
authors tables. The effect of this on your form would be that you'd need
either a subform within the publications subform, if its in single form view,
or two correlated subforms within the Parent form if the publications subform
is in continuous form or datasheet view. Either way the subform would be
based on the PublicationAuthors table.

Ken Sheridan
Stafford, England

"SJW" wrote:

> Many thanks Ken.
> I knew my problem was something simple and changing the MasterLink did the
> trick.
> You should notice that most of the code I have used is based on your County,
> District, Parish example. I found it most useful.
> I used only two hierarchical combos on the form for adding new publications
> (fmAddNew). However, I have used three cbos on the Search form. Following
> selection of Category, Subcategory and Publication the details of the slected
> publication are displayed within a subform.
> I have two remaining problems.
> 1. I would like the user to be able to add a new subcategory and/or category
> to the combos on fmAddNew. The code I have used for NotOnList causes too many
> problems and definitely incorrect.
> 2. The command buttons I have used on fmAddNew don't seem to work. The user
> should be able to save a record, delete a record and add a new record on this
> form. However, the buttons i have used from the toolkit just don't work.
> Preferably after the user has entered details of a new publication, they
> click the 'save record' button, then click the 'add new record' button which
> should clear the form and provide blank boxes for the user to fill in details
> of a new record.
> There may be an easier way to do this, so I am open to suggestions.
> thanks again
> Steven
>
> "Ken Sheridan" wrote:
>
> > As cboSubCategory is an unbound control the LinkMasterFields property of the
> > subform control should be the name of the control, i.e. cboSubCategory. The
> > LinkChildFields property should stay as SubCategoryID. When you insert a new
> > record in the subform the SubCategoryID field will automatically be given the
> > value of the combo box on the Parent form.
> >
> > For the search form you'd have three unbound combo boxes, the last two
> > correlated by references to the next higher control in the usual way and
> > requeried in the next higher control's AfterUpdate event procedure. The
> > form's RecordSource could by default be SELECT * FROM tblPublications ORDER
> > BY Title; and it could include controls bound to whatever fields you want to
> > show on the form for each publication.
> >
> > In the AfterUpdate event procedure of the final combo box, cboPublication
> > say whose value would be the PublicationID you can change the form's
> > RecordSource property and requery the form with:
> >
> > Dim strSQL As String
> >
> > strSQL = "SELECT * FROM tblPublications " & _
> > "WHERE PublicationID = " & Me.cboPublication
> >
> > Me.Requery
> >
> > You might also want to include a Show All button on the form with the
> > following code in its Click event procedure:
> >
> > Dim strSQL As String
> >
> > strSQL = "SELECT * FROM tblPublications " & _
> > "ORDER BY Title"
> >
> > Me.cboCategory = Null
> > Me.cboSubCategory = Null
> > Me.cboPublication = Null
> >
> > Me.Requery
> >
> > BTW you might be interested in the demo of various ways of using correlated
> > combo boxes at:
> >
> >
> > http://community.netscape.com/n/pfx/...g=ws-msdevapps
> >
> >
> > It uses a hierarchy of the administrative areas in my location, but the
> > principles are the same.
> >
> > Ken Sheridan
> > Stafford, England
> >
> > "SJW" wrote:
> >
> > > We wish to sort a large number of publications by category, then subcategory
> > > by the user selecting a category from cboCategory and then a subcategory on
> > > cboSubcategory on main form (fmAddNew).
> > >
> > > The user then records title, date, author etc. in textboxes on a subform.
> > > The subform is based on tblPublications, with master, child links between the
> > > main form and subform are SubcategoryID. The relationships are tblCategory
> > > to tblSubcategory (one to many) and then tblSubcategory to tblPublications
> > > (one to many).
> > >
> > > The rowsource for cboCategory is
> > > SELECT tblCategories.CategoryID, tblCategories.Category FROM tblCategories;
> > >
> > > The AfterUpdate includes:
> > > Private Sub ComboCategory_AfterUpdate()
> > >
> > > ' if Category is updated then erase current values
> > > ' for Subcategory and requery combo box
> > > ' to show Subcategories of selected Category
> > > Me!ComboSubcategory = Null
> > > Me!ComboSubcategory.Requery
> > >
> > > For comboSubcategory
> > > RowSource:
> > > SELECT tblSubcategories.SubcategoryID, tblSubcategories.Subcategory FROM
> > > tblSubcategories WHERE tblSubcategories.CategoryID=Form!comboCategory ORDER
> > > BY tblSubcategories.Subcategory;
> > >
> > > AfterUpdate code:
> > > Private Sub ComboSubcategory_AfterUpdate()
> > > ' Find the record that matches the control.
> > > Dim rs As Object
> > >
> > > Set rs = Me.Recordset.Clone
> > > rs.FindFirst "[SubcategoryID] = " & Str(Nz(Me![ComboSubcategory], 0))
> > > If Not rs.EOF Then Me.Bookmark = rs.Bookmark
> > > End Sub
> > >
> > > We cannot get SubcategoryID to record into tblPublications each time a new
> > > publication is entered into the database.
> > >
> > > We would like the user to be able search the database on another form
> > > (fmSearch) through the same combos (plus a third combo which provides a list
> > > of publications sorted through category then subcategory) and allow the user
> > > to select their preferred publication title from a drop-down list. Once the
> > > publication is chosen, then all details pertaining to that publication are
> > > displayed on fmSearch.
> > >
> > > However, this is not possible. We presume it is because of the problem with
> > > recording SubcategoryID onto tblPublications. but of course we may be wrong.
> > >
> > > We are using Access2003.
> > > any advice appreciated.
> > > SJW
> > >
> >

Dear Ken
Thanks again.
No, you hadn't mentioned the problems with authors' names, and I hadn't
thought about it. I have a text box for authors, so users could spell the
same author's name incorrectly on multiple occasions. I try and set-up the
author solution as you suggest below.
I'll continue to work through all the code you have provided over the next
couple of days.
Do you continue to check older threads that are back a few pages? I would
like to retain this communication with you, as i have almost completed this
database, but sure there could be more problems.
thanks
steven

"Ken Sheridan" wrote:

> Steven:
>
> As regards adding new records via the combo boxes it depends on whether the
> text you enter in the combo box is the only data which needs to be entered,
> other than an autonumber ID, or whether there are other columns in the table
> which need to be entered. In the first case you can do it all via the combo
> box, in the latter case you normally need to open a form to enter the other
> data, but possibly not in your case for reasons given below.
>
> I'd imagine a new Category needs only the one value entered, i.e. the
> category name, CategoryID being an autonumber. In the case of a new
> SubCategory you also need to enter the ID of the category of which the
> sub-category is a part, but as you'll have already selected this in the first
> combo box then you don't need to do the usual thing and open a form based on
> the tblSubCategories table.
>
> So the code for the category combo box's NotInList event procedure would be
> along these lines:
>
> Dim cmd As ADODB.Command
> Dim ctrl As Control
> Dim strSQL As String, strMessage As String
>
> Set ctrl = Me.ActiveControl
> strMessage = "Add new category to list?"
>
> strSQL = "INSERT INTO tblCategories(Category) 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
>
> The code for the sub-categories combo box would be similar, but would extend
> the SQL string so that values are inserted into both the SubCategory and
> CategoryID columns, and would also confim that a category has first been
> selected:
>
> Dim cmd As ADODB.Command
> Dim ctrl As Control
> Dim strSQL As String, strMessage As String
>
> Set ctrl = Me.ActiveControl
> If IsNull(Me.cboCategory) Then
> strMessage = "Please select a category first."
> Msgbox strMessage, vbExclamation, "Invalid Operation"
> Response = acDataErrContinue
> ctrl.Undo
> Else
> strMessage = "Add new sub-category of " & _
> Me.cboCategory,Column(1) & " to list?"
>
> strSQL = "INSERT INTO tblSubCategories(SubCategory,CategoryID) "
> & _
> "VALUES(""" & NewData & ""," & Me.cboCategory & ")"
>
> 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
> End If
>
> As for the command buttons, frmAddNew is, as I understand it, an unbound
> form with a bound subform within it. Consequently, there can be no record to
> save or delete in the parent from, only in the subform. You could include
> buttons in the subform's header or footer to save or delete rows from the
> tblPublications table if the subform is in continuous form view, or in its
> detail section if its in single form view. If its in datasheet view then you
> can use the built in menu commands or toolbar buttons. If you wanted to
> delete rows from the categories or sub-categories tables via the Parent from,
> then you'd need button's on the parent form which execute SQL DELETE
> statements to delete the row for the category or sub-category selected in the
> combo boxes, followed by requerying the combo boxes. If, when you delete a
> category you want all its sub-categories automatically deleted then you
> should enforce cascade deletes in the relationship between the categories and
> sub-categories tables. Otherwise, assuming referential integrity is
> enforced, as it should be, you would not be able to delete a category until
> all of its sub-categories had been deleted.
>
> To clear the Parent form just set the combo boxes to Null and requery
> cboSubCategory:
>
> Me.cboCategory = Null
> Me.cboSubCategory = Null
> Me.cboSubcategory.Requery
>
> This time it can be done from a button on the Parent form. The subform
> should then automatically show no publication records until a category and
> sub-category is selected in the Parent form's combo boxes.
>
> Finally one point I meant to mention in my first reply (apologies if I've
> already said this in the past), but you seem to have a field for the author
> in the tblPublications table, so it would not handle multiple authors for a
> publication (which is common in scientific literature of course). Also if
> you don't have an Authors table which it can reference there is a strong risk
> of inconsistent data creeping it; I have personal experience of this, having
> encountered three versions of my own name as author in a table of technical
> sources. The relationship between publications and authors is potentially
> many-to-many, i.e. each author might have written one or more publications,
> and each publication might have one or more authors. So you'd need a
> PublicationAuthors table to model this, with PublicationID and AuthorID
> foreign keys columns referencing the primary keys of the publications and
> authors tables. The effect of this on your form would be that you'd need
> either a subform within the publications subform, if its in single form view,
> or two correlated subforms within the Parent form if the publications subform
> is in continuous form or datasheet view. Either way the subform would be
> based on the PublicationAuthors table.
>
> Ken Sheridan
> Stafford, England
>
> "SJW" wrote:
>
> > Many thanks Ken.
> > I knew my problem was something simple and changing the MasterLink did the
> > trick.
> > You should notice that most of the code I have used is based on your County,
> > District, Parish example. I found it most useful.
> > I used only two hierarchical combos on the form for adding new publications
> > (fmAddNew). However, I have used three cbos on the Search form. Following
> > selection of Category, Subcategory and Publication the details of the slected
> > publication are displayed within a subform.
> > I have two remaining problems.
> > 1. I would like the user to be able to add a new subcategory and/or category
> > to the combos on fmAddNew. The code I have used for NotOnList causes too many
> > problems and definitely incorrect.
> > 2. The command buttons I have used on fmAddNew don't seem to work. The user
> > should be able to save a record, delete a record and add a new record on this
> > form. However, the buttons i have used from the toolkit just don't work.
> > Preferably after the user has entered details of a new publication, they
> > click the 'save record' button, then click the 'add new record' button which
> > should clear the form and provide blank boxes for the user to fill in details
> > of a new record.
> > There may be an easier way to do this, so I am open to suggestions.
> > thanks again
> > Steven
> >
> > "Ken Sheridan" wrote:
> >
> > > As cboSubCategory is an unbound control the LinkMasterFields property of the
> > > subform control should be the name of the control, i.e. cboSubCategory. The
> > > LinkChildFields property should stay as SubCategoryID. When you insert a new
> > > record in the subform the SubCategoryID field will automatically be given the
> > > value of the combo box on the Parent form.
> > >
> > > For the search form you'd have three unbound combo boxes, the last two
> > > correlated by references to the next higher control in the usual way and
> > > requeried in the next higher control's AfterUpdate event procedure. The
> > > form's RecordSource could by default be SELECT * FROM tblPublications ORDER
> > > BY Title; and it could include controls bound to whatever fields you want to
> > > show on the form for each publication.
> > >
> > > In the AfterUpdate event procedure of the final combo box, cboPublication
> > > say whose value would be the PublicationID you can change the form's
> > > RecordSource property and requery the form with:
> > >
> > > Dim strSQL As String
> > >
> > > strSQL = "SELECT * FROM tblPublications " & _
> > > "WHERE PublicationID = " & Me.cboPublication
> > >
> > > Me.Requery
> > >
> > > You might also want to include a Show All button on the form with the
> > > following code in its Click event procedure:
> > >
> > > Dim strSQL As String
> > >
> > > strSQL = "SELECT * FROM tblPublications " & _
> > > "ORDER BY Title"
> > >
> > > Me.cboCategory = Null
> > > Me.cboSubCategory = Null
> > > Me.cboPublication = Null
> > >
> > > Me.Requery
> > >
> > > BTW you might be interested in the demo of various ways of using correlated
> > > combo boxes at:
> > >
> > >
> > > http://community.netscape.com/n/pfx/...g=ws-msdevapps
> > >
> > >
> > > It uses a hierarchy of the administrative areas in my location, but the
> > > principles are the same.
> > >
> > > Ken Sheridan
> > > Stafford, England
> > >
> > > "SJW" wrote:
> > >
> > > > We wish to sort a large number of publications by category, then subcategory
> > > > by the user selecting a category from cboCategory and then a subcategory on
> > > > cboSubcategory on main form (fmAddNew).
> > > >
> > > > The user then records title, date, author etc. in textboxes on a subform.
> > > > The subform is based on tblPublications, with master, child links between the
> > > > main form and subform are SubcategoryID. The relationships are tblCategory
> > > > to tblSubcategory (one to many) and then tblSubcategory to tblPublications
> > > > (one to many).
> > > >
> > > > The rowsource for cboCategory is
> > > > SELECT tblCategories.CategoryID, tblCategories.Category FROM tblCategories;
> > > >
> > > > The AfterUpdate includes:
> > > > Private Sub ComboCategory_AfterUpdate()
> > > >
> > > > ' if Category is updated then erase current values
> > > > ' for Subcategory and requery combo box
> > > > ' to show Subcategories of selected Category
> > > > Me!ComboSubcategory = Null
> > > > Me!ComboSubcategory.Requery
> > > >
> > > > For comboSubcategory
> > > > RowSource:
> > > > SELECT tblSubcategories.SubcategoryID, tblSubcategories.Subcategory FROM
> > > > tblSubcategories WHERE tblSubcategories.CategoryID=Form!comboCategory ORDER
> > > > BY tblSubcategories.Subcategory;
> > > >
> > > > AfterUpdate code:
> > > > Private Sub ComboSubcategory_AfterUpdate()
> > > > ' Find the record that matches the control.
> > > > Dim rs As Object
> > > >
> > > > Set rs = Me.Recordset.Clone
> > > > rs.FindFirst "[SubcategoryID] = " & Str(Nz(Me![ComboSubcategory], 0))
> > > > If Not rs.EOF Then Me.Bookmark = rs.Bookmark
> > > > End Sub
> > > >
> > > > We cannot get SubcategoryID to record into tblPublications each time a new
> > > > publication is entered into the database.
> > > >
> > > > We would like the user to be able search the database on another form
> > > > (fmSearch) through the same combos (plus a third combo which provides a list
> > > > of publications sorted through category then subcategory) and allow the user
> > > > to select their preferred publication title from a drop-down list. Once the
> > > > publication is chosen, then all details pertaining to that publication are
> > > > displayed on fmSearch.
> > > >
> > > > However, this is not possible. We presume it is because of the problem with
> > > > recording SubcategoryID onto tblPublications. but of course we may be wrong.
> > > >
> > > > We are using Access2003.
> > > > any advice appreciated.
> > > > SJW
> > > >
> > >
>

Steven:

I would be notified of any new post you make in this thread.

Ken Sheridan
Stafford, England

"Ken Sheridan" wrote:

> Steven:
>
> I would be notified of any new post you make in this thread.
>
> Ken Sheridan
> Stafford, England
>

OK.
Everything is beginning to work well. However, the problem is that the form
for adding new publications (fmAddNew) is acting like a form for searching
existing publications. After selecting Add New Publication from Switchboard,
and being directed to fmAddNew, the user then selects a category and
subcategory on the main form. But within the subform all details of one of
the existing publications are displayed which falls under the selected
Category/Subcategory combination.
I have a command button at the top of the subform "Add New Publication". the
user needs to click this button in order to clear the subform and continue
entering data for a new publication into the database.
This is cumbersome, so is there a way of keeping the subform empty in
fmAddNew after selecting the Category and Subcategory for a new entry?
Apologies if you had already answered this question, I am not sure.
Also, your advice re. Show All button on fmSearch. Does clicking this button
provide all the publications stored in the database regardless ie. all
publication titles are listed on the third cbo (cboPublications)?
thanks
Steven

If you only want to use the subform for adding new records then set the
RecordSource of the subform to a query which returns no rows, e.g.

SELECT *
FROM tblPublications
WHERE PublicationID = 0;

The subform will be blank when the form opens. When you add a new record in
the subform, as soon as it is saved, the subform will go blank again ready
fro another new record to be added.

Another possible approach would be to show all the records for the selected
category/subcategory in the subform, as you have it now, but move the subform
to a new record. To do this add the following code to the sub-category combo
box's AfterUpdate event procedure:

Me.sfrPublications.SetFocus
DoCmd.GoToRecord Record:=acNewRec

where sfrPublications is the name of the subform control, i.e. the control
on the parent form which houses the subform.

The 'Show All' button doesn't affect the navigational combo boxes at all.
Its just an extra bit of functionality you might want to consider, and sets
the underlying recordset of the search form so it includes all rows from the
table rather than just the 'searched for' record, with the first one in Title
order as the current record. This would simply mean you could browse through
the records with the built in navigation buttons etc if you wished. If you
don't want this functionality, but simply be able to view the 'searched for'
record then you don't need the button.

Ken Sheridan
Stafford, England

"SJW" wrote:

>
>
> "Ken Sheridan" wrote:
>
> > Steven:
> >
> > I would be notified of any new post you make in this thread.
> >
> > Ken Sheridan
> > Stafford, England
> >
>
> OK.
> Everything is beginning to work well. However, the problem is that the form
> for adding new publications (fmAddNew) is acting like a form for searching
> existing publications. After selecting Add New Publication from Switchboard,
> and being directed to fmAddNew, the user then selects a category and
> subcategory on the main form. But within the subform all details of one of
> the existing publications are displayed which falls under the selected
> Category/Subcategory combination.
> I have a command button at the top of the subform "Add New Publication". the
> user needs to click this button in order to clear the subform and continue
> entering data for a new publication into the database.
> This is cumbersome, so is there a way of keeping the subform empty in
> fmAddNew after selecting the Category and Subcategory for a new entry?
> Apologies if you had already answered this question, I am not sure.
> Also, your advice re. Show All button on fmSearch. Does clicking this button
> provide all the publications stored in the database regardless ie. all
> publication titles are listed on the third cbo (cboPublications)?
> thanks
> Steven

Thanks Ken, that solved the problem.
Some code you provided earlier in this thread is causing problems. You
provided code to enter into cboSubcategory NotInList ie.

strMessage = "Add new sub-category of " & _
Me.cboCategory,Column(1) & " to list?"

strSQL = "INSERT INTO tblSubCategories(SubCategory,CategoryID) "
& _
"VALUES(""" & NewData & ""," & Me.cboCategory & ")"

This code is displaying in red, and when it runs it returns a Compile Error:
Expected: End of Statement and directs cursor to the coma after cboCategory
above.

Grateful your advice.
thanks
sTeven

"Ken Sheridan" wrote:

> If you only want to use the subform for adding new records then set the
> RecordSource of the subform to a query which returns no rows, e.g.
>
> SELECT *
> FROM tblPublications
> WHERE PublicationID = 0;
>
> The subform will be blank when the form opens. When you add a new record in
> the subform, as soon as it is saved, the subform will go blank again ready
> fro another new record to be added.
>
> Another possible approach would be to show all the records for the selected
> category/subcategory in the subform, as you have it now, but move the subform
> to a new record. To do this add the following code to the sub-category combo
> box's AfterUpdate event procedure:
>
> Me.sfrPublications.SetFocus
> DoCmd.GoToRecord Record:=acNewRec
>
> where sfrPublications is the name of the subform control, i.e. the control
> on the parent form which houses the subform.
>
> The 'Show All' button doesn't affect the navigational combo boxes at all.
> Its just an extra bit of functionality you might want to consider, and sets
> the underlying recordset of the search form so it includes all rows from the
> table rather than just the 'searched for' record, with the first one in Title
> order as the current record. This would simply mean you could browse through
> the records with the built in navigation buttons etc if you wished. If you
> don't want this functionality, but simply be able to view the 'searched for'
> record then you don't need the button.
>
> Ken Sheridan
> Stafford, England
>
> "SJW" wrote:
>
> >
> >
> > "Ken Sheridan" wrote:
> >
> > > Steven:
> > >
> > > I would be notified of any new post you make in this thread.
> > >
> > > Ken Sheridan
> > > Stafford, England
> > >
> >
> > OK.
> > Everything is beginning to work well. However, the problem is that the form
> > for adding new publications (fmAddNew) is acting like a form for searching
> > existing publications. After selecting Add New Publication from Switchboard,
> > and being directed to fmAddNew, the user then selects a category and
> > subcategory on the main form. But within the subform all details of one of
> > the existing publications are displayed which falls under the selected
> > Category/Subcategory combination.
> > I have a command button at the top of the subform "Add New Publication". the
> > user needs to click this button in order to clear the subform and continue
> > entering data for a new publication into the database.
> > This is cumbersome, so is there a way of keeping the subform empty in
> > fmAddNew after selecting the Category and Subcategory for a new entry?
> > Apologies if you had already answered this question, I am not sure.
> > Also, your advice re. Show All button on fmSearch. Does clicking this button
> > provide all the publications stored in the database regardless ie. all
> > publication titles are listed on the third cbo (cboPublications)?
> > thanks
> > Steven
>

Steven:

Oops! Typo on my part, it should be a full stop, not a comma:

strMessage = "Add new sub-category of " & _
Me.cboCategory.Column(1) & " to list?"

I think it may also be that your newsreader has broken another line over two
lines; this is a common gotcha here which you need to look out for. I've
split the line into shorter snippets below, using the underscore continuation
character, so it should be OK:

strSQL = "INSERT INTO tblSubCategories" & _
"(SubCategory,CategoryID) " & _
"VALUES(""" & NewData & ""," & _
Me.cboCategory & ")"

Ken Sheridan
Stafford, England

"SJW" wrote:

> Thanks Ken, that solved the problem.
> Some code you provided earlier in this thread is causing problems. You
> provided code to enter into cboSubcategory NotInList ie.
>
> strMessage = "Add new sub-category of " & _
> Me.cboCategory,Column(1) & " to list?"
>
> strSQL = "INSERT INTO tblSubCategories(SubCategory,CategoryID) "
> & _
> "VALUES(""" & NewData & ""," & Me.cboCategory & ")"
>
> This code is displaying in red, and when it runs it returns a Compile Error:
> Expected: End of Statement and directs cursor to the coma after cboCategory
> above.
>
> Grateful your advice.
> thanks
> sTeven
>
> "Ken Sheridan" wrote:
>
> > If you only want to use the subform for adding new records then set the
> > RecordSource of the subform to a query which returns no rows, e.g.
> >
> > SELECT *
> > FROM tblPublications
> > WHERE PublicationID = 0;
> >
> > The subform will be blank when the form opens. When you add a new record in
> > the subform, as soon as it is saved, the subform will go blank again ready
> > fro another new record to be added.
> >
> > Another possible approach would be to show all the records for the selected
> > category/subcategory in the subform, as you have it now, but move the subform
> > to a new record. To do this add the following code to the sub-category combo
> > box's AfterUpdate event procedure:
> >
> > Me.sfrPublications.SetFocus
> > DoCmd.GoToRecord Record:=acNewRec
> >
> > where sfrPublications is the name of the subform control, i.e. the control
> > on the parent form which houses the subform.
> >
> > The 'Show All' button doesn't affect the navigational combo boxes at all.
> > Its just an extra bit of functionality you might want to consider, and sets
> > the underlying recordset of the search form so it includes all rows from the
> > table rather than just the 'searched for' record, with the first one in Title
> > order as the current record. This would simply mean you could browse through
> > the records with the built in navigation buttons etc if you wished. If you
> > don't want this functionality, but simply be able to view the 'searched for'
> > record then you don't need the button.
> >
> > Ken Sheridan
> > Stafford, England
> >
> > "SJW" wrote:
> >
> > >
> > >
> > > "Ken Sheridan" wrote:
> > >
> > > > Steven:
> > > >
> > > > I would be notified of any new post you make in this thread.
> > > >
> > > > Ken Sheridan
> > > > Stafford, England
> > > >
> > >
> > > OK.
> > > Everything is beginning to work well. However, the problem is that the form
> > > for adding new publications (fmAddNew) is acting like a form for searching
> > > existing publications. After selecting Add New Publication from Switchboard,
> > > and being directed to fmAddNew, the user then selects a category and
> > > subcategory on the main form. But within the subform all details of one of
> > > the existing publications are displayed which falls under the selected
> > > Category/Subcategory combination.
> > > I have a command button at the top of the subform "Add New Publication". the
> > > user needs to click this button in order to clear the subform and continue
> > > entering data for a new publication into the database.
> > > This is cumbersome, so is there a way of keeping the subform empty in
> > > fmAddNew after selecting the Category and Subcategory for a new entry?
> > > Apologies if you had already answered this question, I am not sure.
> > > Also, your advice re. Show All button on fmSearch. Does clicking this button
> > > provide all the publications stored in the database regardless ie. all
> > > publication titles are listed on the third cbo (cboPublications)?
> > > thanks
> > > Steven
> >