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 said:
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 said:
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...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps
It uses a hierarchy of the administrative areas in my location, but the
principles are the same.
Ken Sheridan
Stafford, England
:
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
