473,327 Members | 2,012 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Duplicating a group of records but adding user input

**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
6 2474
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

"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
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

"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
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

"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: mellie | last post by:
Hi there, SQL 2000 database with ASP I've found many things "out there" regarding updating and deleting multiple records with checkboxes but I can't seem to find anything about adding them. ...
7
by: Drew | last post by:
I have a db table like the following, UID, int auto-increment RegNo Person Relation YearsKnown Now here is some sample data from this table,
1
by: pwys | last post by:
Hello to everyone WOndering if there anyone could help me with this. I have a Primary & a secondary table with the unique key (InvoiceNo) Waht i want is to make duplicate copy of a selected...
7
by: Paolo | last post by:
I know I should not be doing this, but I find it very useful. I have a database in Access which stores data for a small company. Sometimes we need to add similar information to different tables....
6
by: Robin S. | last post by:
**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...
11
by: EdB | last post by:
I'm still having trouble grasping what's going on here and how to resolve it. In VB6 I have a form that has several combo boxes with lots of items in each; it takes a long time to load the form...
6
by: syvman | last post by:
Hi all... I have a quick question, if anyone knows the answer. I've built a query which looks at a set of records in a table (tblFilename) and I've set it up so that it displays the following...
2
by: mavmavv | last post by:
I have a Form where I have created a duplicate record button, no problem... The subform is where my problem lies. The subform displays data matching the mainform's ID, these two values are...
7
by: Ron | last post by:
Hi All, Using Access2000, winXP. Table 1 = tblClients displayed on frmClients via qryClients. 2nd table = tblInvoices shown on frmInvoices via qryInvoices. 2nd table = tblDetails shown on...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.