Robin S. wrote:
Salad,
Thank you for your extensive response. Unfortunately, I don't think I made
myself as clear as I could have.
All the records in tblCategoryDetails will be entered manually before any
products are added. One category would have multiple records in this table.
A sample of this table is as follows:
CategoryID SpecID
Monitor Diagonal size
Monitor Dot pitch
Monitor Max resolution
Mouse Optical or conventional
Mouse Number of buttons
Mouse Wheel (yes/no)
When a user wants to add a monitor to tblProducts, the form must duplicate
all the records with CategoryID as "Monitors" from tblCategoryDetails to
tblCategoryProJoin. Also, the ProductID must be inserted into each of the
records.
In the above example, it would have duplicated the first three records
(monitor). The ProductID obviously relates the record to the product it is
describing. The Value field (which is manually entered later on another
form) would be the catalog value of the spec the record refers to.
For tblCategoryProJoin:
CategoryID ProductID SpecID Value
Monitor 755DF Diagonal Size 17
Monitor 755DF Dot pitch .24
Monitor 755DF Max resolution 1280x1024
NOTE: All ID's have been substituted with their "real" information
counterpart to make this more obvious. The Value field is shown with data
for clarification, but the end result of this procedure would not add this
data - it will be added later on another form.
A significant issue for this process is the fact that the ProductID (which
is an autonumber created in tblProducts) rather than the product's catalog
number must be used in tblCategoryProJoin. How do I insert this step before
all the records are duplicated?
Again, thanks for the help. I've been working this problem over in my mind
for months now. I do not yet have the experience to figure it out myself.
Regards,
Robin
Ok. I understand you have a form for entering data into
tblCategoryDetails. And this is the structure for the table.
CategoryID
SpecID
In your example, we have the following records
CategoryID SpecID
Monitor Diagonal size
Monitor Dot pitch
Monitor Max resolution
Mouse Optical or conventional
Mouse Number of buttons
Mouse Wheel (yes/no)
Step 1 is understood. I am hazy on the rest.
Then you want to enter a generate a tblCategoryProJoin record(s) group
that contains all catspec records for the category specified?
I'm a bit hazy on your product table. When does a product ID get
entered? 755DF is a product code. If I understand you correctly, 755DF
could be a Sony code for a monitor and a Epson printer code, so you want
to create a record of 755DF that is related to the code for the Sony and
another for the Epson.
Let's see if this gets us closer.
You have a form called MainForm.
In this form I would create a combo box to list your categories. Call
it ComboCat.
I would create another field to enter the product code field. It's name
will be ProductNo.
I would add another field to enter store the ProductID (autonumber) from
the table tblProducts. It's name will be ProductID.
I would then have a command button called CommandAdd to add as many
records as as there are specs of the category/spec.
Regarding tblProducts....if I understand you correctly. You would add 1
(one) record into the Product table based on the value of the value in
ProductNo. You would not check for duplicates since duplicate codes
could exist elsewhere for other categories.
This is a pretty funky design but I oftentimes don't question things
because I don't know the entire story and I have done things that people
spend more time wondering why I want to do something instead of
answering the problem and letting me live with my own reasons for funkiness.
OK. Now that we have a combo box to list categories, a field to enter a
product code/number, and a command button that when pressed is goint to
create 1 to may records in tblCategoryProJoin. It will also create 1
record in the table tblProducts.
<Soapbox On>In my first app I ever designed in Access I used "tbl" in
front of all table names, and "qry" in front of all queries, "frm" in
forms and "rpt" in reports. I kicked myself in the ass for a long time
afterwords. When I had a lot of items, I found it a PITA to find
things. If you aren't deeply involved in the app, my currect preference
is to call things without tbl, qry, frm, rpt in front of everything.
When you are sorting the database window by name, mod date, create date,
etc and looking for things...and if you have a lot of objects, those
extra 3 characters are nothing more than a nuisance. I ALWAYS use the
int, lng, str, etc for variables in code...but never for object naming
of the objects in the database window. <Soapbox off>...to each their own.
OK. Create an append query, I'll call it Query1, with the following SQL
statement. You will see that I am appending records to the join table
from tblCategoryDetails. Do you notice how I created a column using the
field ProductID (visible = no) to update the productid? Only catspec
records will be added to the table based on the category selected.
INSERT INTO tblCategoryProJoin ( CategoryID, SpecID, ProductID )
SELECT tblCategoryDetails.CategoryID, tblCategoryDetails.SpecID,
[Forms]![MainForm]![ProductID] AS ProductID
FROM tblCategoryDetails
WHERE tblCategoryDetails.CategoryID=[Forms]![MainForm]![ComboCat];
Now we simply add some code to the OnClick event of CommandAdd
Private Sub CommandAdd_OnClick
If IsNull(Me.ComboCat) Then
msgbox "Please select a category"
elseif IsNull(Me.ProductNo) then
msgbox "Please enter a product code"
else
'we passed validation. 1st, add a product record.
'productno is the field to hold the code. We want
'to store the productid from the products table in the
'table. So first create a product record.
Dim rst As DAO.Recordset
set rst = Currentdb.Openrecordset("tblProducts",_
dbopensnapshot)
rst.AddNew
rst!ProductNo = Me.ProductNo
'now store the ProductID (autonum) of recordset
'to invisible field ProductID on the form.
Me.ProductID = rst!ProductID
'now commit changes
rst.update
rst.Close
set rst = Nothing
'now run the append query Query1
Docmd.setwarnings false
Currentdb.Execute "Query1", dbFailOnError
Docmd.setwarnings True
msgbox "The new product category has been created"
Endif
End Sub
This should at least give you some ideas to try. How you now present
the records once added is up to you.