S
SJW
I am trying to build a database that will sort a large number of publications
by category, then subcategory. To enter details of a publication, the user
selects a category from cboCategory and then a subcategory on cboSubcategory
on main form (fmAddNew). Then the user records title, date, author etc. in
textboxes on a subform. The subform is based on tblPublications, where all
fields for each publication are recorded. The 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
The problem is that I cannot get SubcategoryID to record into
tblPublications each time a new publication is entered into the database.
Subsequently, I would like to be able to search the database on another form
(fmSearch) through the same combos 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 because of the problem with recording
SubcategoryID onto tblPublications.
I hope this all makes sense. I am using Access2003.
any advice appreciated.
SJW
by category, then subcategory. To enter details of a publication, the user
selects a category from cboCategory and then a subcategory on cboSubcategory
on main form (fmAddNew). Then the user records title, date, author etc. in
textboxes on a subform. The subform is based on tblPublications, where all
fields for each publication are recorded. The 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
The problem is that I cannot get SubcategoryID to record into
tblPublications each time a new publication is entered into the database.
Subsequently, I would like to be able to search the database on another form
(fmSearch) through the same combos 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 because of the problem with recording
SubcategoryID onto tblPublications.
I hope this all makes sense. I am using Access2003.
any advice appreciated.
SJW