Duplicating a group of records but adding user input

[Robin S.]

**Eric and Salad - thank you both for the polite kick in the butt. I hope
I've done a better job of explaining myself below.

I am trying to produce a form to add products to a table (new products).

Tables:

tblCategoryDeta ils
CategoryID
SpecID
(This table contains template records for each product. Each product belongs
to a category - "Monitors" would be a product category. Each category has
multiple specifications (SpecID), each spec being a separate record)

tblCategoryProJ oin
CategoryID
ProductID
SpecID
Value
(This table contains specifications for each product. Each record is one
spec for one product. SpecID would refer to "monitor size" for instance. The
Value, which will be manually entered later, would be "17" for instance)

tblProducts
ProductNo
ProductID (autonumber)
(This table contains a list of products)

The PROBLEM: When a new product is created using a form, I need a group of
records from tblCategoryDeta ils to be duplicated to the tblCategoryProJ oin
table. The user will select the category and enter the ProductNo (a catalog
number). The issue is that I don't want to use the ProductNo. I want to use
the corresponding ProductID as different manufacturers sometimes use
identical product numbers.

Any thoughts on how to accomplish this? Obviously I'm fairly green as far as
Access goes. Any thoughts or just key words to point me in the correct
direction would be very valuable.

Regards,

Robin

[Salad]

Re: Duplicating a group of records but adding user input

Robin S. wrote:
[color=blue]
> **Eric and Salad - thank you both for the polite kick in the butt. I hope
> I've done a better job of explaining myself below.
>
> I am trying to produce a form to add products to a table (new products).
>
> Tables:
>
> tblCategoryDeta ils
> CategoryID
> SpecID
> (This table contains template records for each product. Each product belongs
> to a category - "Monitors" would be a product category. Each category has
> multiple specifications (SpecID), each spec being a separate record)
>
> tblCategoryProJ oin
> CategoryID
> ProductID
> SpecID
> Value
> (This table contains specifications for each product. Each record is one
> spec for one product. SpecID would refer to "monitor size" for instance. The
> Value, which will be manually entered later, would be "17" for instance)
>
> tblProducts
> ProductNo
> ProductID (autonumber)
> (This table contains a list of products)
>
> The PROBLEM: When a new product is created using a form, I need a group of
> records from tblCategoryDeta ils to be duplicated to the tblCategoryProJ oin
> table. The user will select the category and enter the ProductNo (a catalog
> number). The issue is that I don't want to use the ProductNo. I want to use
> the corresponding ProductID as different manufacturers sometimes use
> identical product numbers.
>
> Any thoughts on how to accomplish this? Obviously I'm fairly green as far as
> Access goes. Any thoughts or just key words to point me in the correct
> direction would be very valuable.
>
> Regards,
>
> Robin[/color]

Hi Robin: Maybe this will make sense, maybe not.

I would create a form that contains the fields from tblCategoryProJ oin.
The fields CategoryID and SpecID would be made invisible (if datasheet
then hidden). Let's call this form CatetoryProduct s.

I am assuming you first select a category, then a specid, then enter the
products and values.

Now create another form (let's pretend it's called MainForm). It has
not row source. It will contain 2 combo boxes and a subform.

Create 2 combo boxes; the first is called ComboCategories and contains a
list of all categories. The second is called ComboSpecs and contains a
list of all specids. You want to filter the specids to only display
records for the category selected.

Open up the property sheet for ComboSpecs, click on the datatab, then
the triple dot on the rowsource line to open the SQL. In the criteria
row under CategoryID enter
Forms!MainForm! ComboCategory
This will now display spec records that match the category selected.

In the AfterUpdate event (event tab of property sheet) for ComboCategory
enter the following
Me.ComboSpecs.R equery
This tells the form that every time a category is selected, filter the
spec list to only show those records for that spec.

From the toolbox, select the Subform object, select the form
CatetoryProduct s, and drag it onto MainForm. Select No SQL. Resize it
to suit your needs.

You can now display product records for categories and specs. But you
need to filter them. In the AfterUpdate event for both ComboCategory
and ComboSpecs enter the following
Forms!MainForm! CatetoryProduct s.Form.Filter = _
"[CategoryID] = " & Me.CombCategory & " And " & _
"[SpecID] = " & Me.ComboSpecs
Forms!MainForm! CatetoryProduct s.Form.Filter = True

Now everytime a new category is selected, only products for that
category are presented. If a specid is selected from the combo then
only products for the Specid of that category is presented.

If you open up the form CategoryProduct s, you need to add one more thing
the the form's BeforeUpdate event. Enter
If me.NewRecord Then
Me.CategoryID = Forms!MainForm! ComboCategory
Me.SpecID = Forms!Mainform! ComboSpec
Endif
Now if the record is a new record, the associated CategoryID and SpecID
are added to the record.

Does this sound like what you want to accomplish?

[Robin S.]

Re: Duplicating a group of records but adding user input


"Salad" <oil@vinegar.co m> wrote in message
news:ur1ic.5891 $eZ5.2147@newsr ead1.news.pas.e arthlink.net...[color=blue]
>
> Hi Robin: Maybe this will make sense, maybe not.
>
> I would create a form that contains the fields from tblCategoryProJ oin.
> The fields CategoryID and SpecID would be made invisible (if datasheet
> then hidden). Let's call this form CatetoryProduct s.
>
> I am assuming you first select a category, then a specid, then enter the
> products and values.
>
> Now create another form (let's pretend it's called MainForm). It has
> not row source. It will contain 2 combo boxes and a subform.
>
> Create 2 combo boxes; the first is called ComboCategories and contains a
> list of all categories. The second is called ComboSpecs and contains a
> list of all specids. You want to filter the specids to only display
> records for the category selected.
>
> Open up the property sheet for ComboSpecs, click on the datatab, then
> the triple dot on the rowsource line to open the SQL. In the criteria
> row under CategoryID enter
> Forms!MainForm! ComboCategory
> This will now display spec records that match the category selected.
>
> In the AfterUpdate event (event tab of property sheet) for ComboCategory
> enter the following
> Me.ComboSpecs.R equery
> This tells the form that every time a category is selected, filter the
> spec list to only show those records for that spec.
>
> From the toolbox, select the Subform object, select the form
> CatetoryProduct s, and drag it onto MainForm. Select No SQL. Resize it
> to suit your needs.
>
> You can now display product records for categories and specs. But you
> need to filter them. In the AfterUpdate event for both ComboCategory
> and ComboSpecs enter the following
> Forms!MainForm! CatetoryProduct s.Form.Filter = _
> "[CategoryID] = " & Me.CombCategory & " And " & _
> "[SpecID] = " & Me.ComboSpecs
> Forms!MainForm! CatetoryProduct s.Form.Filter = True
>
> Now everytime a new category is selected, only products for that
> category are presented. If a specid is selected from the combo then
> only products for the Specid of that category is presented.
>
> If you open up the form CategoryProduct s, you need to add one more thing
> the the form's BeforeUpdate event. Enter
> If me.NewRecord Then
> Me.CategoryID = Forms!MainForm! ComboCategory
> Me.SpecID = Forms!Mainform! ComboSpec
> Endif
> Now if the record is a new record, the associated CategoryID and SpecID
> are added to the record.
>
> Does this sound like what you want to accomplish?
>[/color]

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 tblCategoryDeta ils 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 tblCategoryDeta ils to
tblCategoryProJ oin. 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 tblCategoryProJ oin:

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 tblCategoryProJ oin. 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

[Salad]

Re: Duplicating a group of records but adding user input

Robin S. wrote:
[color=blue]
> 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 tblCategoryDeta ils 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 tblCategoryDeta ils to
> tblCategoryProJ oin. 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 tblCategoryProJ oin:
>
> 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 tblCategoryProJ oin. 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
>[/color]
Ok. I understand you have a form for entering data into
tblCategoryDeta ils. 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 tblCategoryProJ oin 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 tblCategoryProJ oin. 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 tblCategoryDeta ils. 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 tblCategoryProJ oin ( CategoryID, SpecID, ProductID )
SELECT tblCategoryDeta ils.CategoryID, tblCategoryDeta ils.SpecID,
[Forms]![MainForm]![ProductID] AS ProductID
FROM tblCategoryDeta ils
WHERE tblCategoryDeta ils.CategoryID=[Forms]![MainForm]![ComboCat];

Now we simply add some code to the OnClick event of CommandAdd
Private Sub CommandAdd_OnCl ick
If IsNull(Me.Combo Cat) Then
msgbox "Please select a category"
elseif IsNull(Me.Produ ctNo) 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.Openr ecordset("tblPr oducts",_
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.setwarnin gs false
Currentdb.Execu te "Query1", dbFailOnError
Docmd.setwarnin gs 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.

[Robin S.]

Re: Duplicating a group of records but adding user input


"Salad" <oil@vinegar.co m> wrote in message
news:Xqmic.7129 $eZ5.6861@newsr ead1.news.pas.e arthlink.net...[color=blue]
>
> This should at least give you some ideas to try. How you now present
> the records once added is up to you.[/color]

Salad,

Your solution seems to be exactly what I'm looking for. However, Access
doesn't like the following:

Set rst = CurrentDb.Openr ecordset("tblPr oducts", dbopensnapshot)
rst.AddNew

I get a run-time error 3251. I think you're not allowed to use
"dbopensnapshop " with "rst.AddNew ".

Any thoughts on how to work around this?

Thanks again for your advice.

Regards,

Robin

[Salad]

Re: Duplicating a group of records but adding user input

Robin S. wrote:
[color=blue]
> "Salad" <oil@vinegar.co m> wrote in message
> news:Xqmic.7129 $eZ5.6861@newsr ead1.news.pas.e arthlink.net...
>[color=green]
>>This should at least give you some ideas to try. How you now present
>>the records once added is up to you.[/color]
>
>
> Salad,
>
> Your solution seems to be exactly what I'm looking for. However, Access
> doesn't like the following:
>
> Set rst = CurrentDb.Openr ecordset("tblPr oducts", dbopensnapshot)
> rst.AddNew
>
> I get a run-time error 3251. I think you're not allowed to use
> "dbopensnapshop " with "rst.AddNew ".
>
> Any thoughts on how to work around this?
>
> Thanks again for your advice.
>
> Regards,
>
> Robin
>
>[/color]
You are right. It's dbopendynaset. Look at OpenRecordset in help.
ALso, this is DAO...in case you are set up for ADO. If so, enter
Dim rst As DAO.Recordset
or modify to work with ADO if that is your preference.

[Robin S.]

Re: Duplicating a group of records but adding user input


"Salad" <oil@vinegar.co m> wrote in message
news:VO9jc.1016 9$eZ5.870@newsr ead1.news.pas.e arthlink.net...[color=blue]
> Robin S. wrote:
>
> You are right. It's dbopendynaset. Look at OpenRecordset in help.
> ALso, this is DAO...in case you are set up for ADO. If so, enter
> Dim rst As DAO.Recordset
> or modify to work with ADO if that is your preference.
>[/color]

Salad,

After doing some fiddling, I have implemented your code.

I had to place an =Eval("") into the append query for the form object
references (combo box and field) as I was getting a 3601 error.

The reason for my funky design is this: Our company sells tens of thousands
of different products. I need to guide the user when entering product
specifications. This is the reason for the categories. The idea is to have
these categories configurable in the future (I'm trying to think ahead).

You're probably thinking that perhaps the company should hire a "real"
programmer. True, but we're very small and change doesn't come quickly. If
this system takes off, we'll certainly get someone to hammer out the dents
(or perform a complete rebuild). I'm obviously just learning (and very much
enjoying myself).

Thanks again for your help. Your example has taught me quite a bit. I'm just
starting "Beginning Access 2002 VBA" by Smith, Sussman, et al.

Regards,

Robin