By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 1,151 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

Duplicating a group of records but adding user input

P: n/a
**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:

tblCategoryDetails
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)

tblCategoryProJoin
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 tblCategoryDetails to be duplicated to the tblCategoryProJoin
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
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Robin S. wrote:
**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:

tblCategoryDetails
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)

tblCategoryProJoin
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 tblCategoryDetails to be duplicated to the tblCategoryProJoin
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


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

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

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.Requery
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
CatetoryProducts, 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!CatetoryProducts.Form.Filter = _
"[CategoryID] = " & Me.CombCategory & " And " & _
"[SpecID] = " & Me.ComboSpecs
Forms!MainForm!CatetoryProducts.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 CategoryProducts, 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?


Nov 12 '05 #2

P: n/a

"Salad" <oi*@vinegar.com> wrote in message
news:ur*****************@newsread1.news.pas.earthl ink.net...

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

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

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.Requery
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
CatetoryProducts, 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!CatetoryProducts.Form.Filter = _
"[CategoryID] = " & Me.CombCategory & " And " & _
"[SpecID] = " & Me.ComboSpecs
Forms!MainForm!CatetoryProducts.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 CategoryProducts, 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?


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
Nov 12 '05 #3

P: n/a
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.

Nov 12 '05 #4

P: n/a

"Salad" <oi*@vinegar.com> wrote in message
news:Xq*****************@newsread1.news.pas.earthl ink.net...

This should at least give you some ideas to try. How you now present
the records once added is up to you.


Salad,

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

Set rst = CurrentDb.Openrecordset("tblProducts", 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
Nov 12 '05 #5

P: n/a
Robin S. wrote:
"Salad" <oi*@vinegar.com> wrote in message
news:Xq*****************@newsread1.news.pas.earthl ink.net...
This should at least give you some ideas to try. How you now present
the records once added is up to you.

Salad,

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

Set rst = CurrentDb.Openrecordset("tblProducts", 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

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.

Nov 12 '05 #6

P: n/a

"Salad" <oi*@vinegar.com> wrote in message
news:VO*****************@newsread1.news.pas.earthl ink.net...
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.


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
Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.