Need to Add New Record with a Button on a Form 1

[grippy]

Hello, I am using access forms to add products to my DB. Generally to add a new product you hit the >* button and it takes you to the next record with blank fields to fill out. Many of my products use repeated information. Is there a way I can be on a current product that has all the fileds filled out and then just change a few things like product code and price then click a button to make it add a new one with all of that info rather than just updating the current product that your on? Thanks for the help this will save me sooo much time.

 

[PHV]

In the Click event procedure of your "Add" button, set the DefaultValue property of the desired controls to the current value:
Me![name of control to duplicate].DefaultValue = Me![name of control to duplicate].Value

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[HitechUser]

grippy,

This example copies the existing record on a form and creates a new record with exactly the same information. If the underlying table has an Autonumber then this field takes the next available number. To use the code paste the code into the On Click event.

Private Sub cmdInsert_Click()
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend
End Sub

Just another idea.

I hope this helps..

Good Luck...

 

[grippy]

thanks a bunch that worked great HitechUser

 

[grippy]

Ok here is my next question. On my form that adds products there is also a subform on it which has a table to add colors that is has available. This code does not duplicate the subform, only the main form. Any way for it to duplicate both with the click of only 1 button as a new record? Thanks for the help.

 

[HitechUser]

grippy,

What you are asking can be done fairly simply. One solution is to create an Append query (simple to test that way). Set this query to copy the records (key value) from the previous records. Just pass the Append query the value. Do this in you "Click Event" subroutine.

Makes sense?

Good Luck...

 

[grippy]

I am fairly new to access so you might have to explain more towards a novice. I dont know what an Append Query is or what it does. If you could explain in a bit more detail that would be greatly appreciated (step by step process) Thank you.

 

[HitechUser]

I would suggest using the help text and searching the Microsoft web site. There are several step by step examples available. Here are some to get you started.

http://office.microsoft.com/en-us/assistance/HP051880831033.aspx

These have some screen captures..

http://its.augustana.edu/documentation/access97/accessqueries.pdf

http://www.meredith.edu/techserv/helpdocs/access_97-2000_queries_1.htm

I hope this helps.

Good Luck...

 

[grippy]

Hello, ive read through this info am im not quite sure that I need a Append Query for this or at least i just cant connect the pieces together on how it would integrate into everything. If its not too much trouble is there anyway I could send my access file to you and have you take a look at it so i can see how you set it all up? Thanks a bunch.

 

[HitechUser]

grippy,

I supose that is possible. Can you be more specific on what you need in your example.

Do you have an Internet share? For example...

http://briefcase.yahoo.com/

If you do and would like to place your code (your risk) we may be able to take a look at it.

Let me know..

 

[grippy]

Hello, ok I have uploaded a much smaller version of my DB that only has the Tables and enough records for you to deal with to keep it simple.

http://69.36.185.63/testdb.mdb

when you first open the DB goto the dbo_tblProduct Form. From here you should be on the first record which happens to have 3 colors listed for it in the subform. I need to be able to change the product code 20-050 to say 20-125, then change the attributes below from 20-050B to 20-125B for blue, 20-125R for red and so on. Once I change all the info that I need to for the product I am trying to add then I need to click duplicate button on the form and have it create a new record with all that was on the previous record along with my changes. And i need to make sure the previous record remains untouched. Let me know if you have any other questions once you look at it. Thank you so much for your help.

 

[HitechUser]

grippy,

In your original post your request was to "copy" the data that existed on the screen. Now you are saying (I believe) you want to change the Productcode. Wasn't the original request that the user see the info and then change it? This "next step" is a little bit more involed due to the setup of your database.

I had a quick look at you data layout for dbo_tblProduct and dbo_tblProductAttributes.

You will need to create an Append query to select from dbo_tblProductAttributes where mainProductId is equal to the dbo_tblProduct.ID of the original (the one you are copying) productCode and insert them into dbo_tblProductAttributes. Then use a the select portion of the query to display them in the subform.

The user will need to update the information (as stated in your first post).

I hope this points you in the right direction.

Good Luck...

 

[grippy]

Ok in my original post I said:

Many of my products use repeated information. Is there a way I can be on a current product that has all the fileds filled out and then just change a few things like product code and price then click a button to make it add a new one with all of that info rather than just updating the current product that your on?
---------

Lets say I have a product which has the following info:

Record: 1
Product Code: 2222
Description: Super Cool Exhaust System
Make: Chevy
Model: Silverado
Category: Exhausts
Company: Borla
Price: $499

Now if I am currently on this record and Im trying to add all of the other Borla exhausts, rather than going to a blank field I want to goto record 1 and change the following info only:

Product Code 2223
Make: Dodge
Model: Ram
Price: $599

then I want to click a button which creates this:

Record: 2
Product Code: 2223
Description: Super Cool Exhaust System
Make: Dodge
Model: Ram
Category: Exhausts
Company: Borla
Price: $599

This way I dont have to retype all the info that remains constant such as descriptions and categories and manufactures. So no I should have 2 records, 1 that never changed and a new one which used part of the data from the first one and the other part from what I typed in. Please let me know if this clears it all up. Thank you for your time.

 

[HitechUser]

I thought you have that now!.

Your database has a "one to many" relationship between Products and Attributes. You above example is misleading, because it reflects only one original and one duplicate (values changed of course) Your table shows product codes with many product attribute records. Soooooooooooo that being said, are the many being copied from the original? You can either do an append query or you will have to do this using VBA code.

Also your only unique identifier is the id, since you can have the same product code in the product table more than once.

If may be beyond the scope of this forum exchange. If someone wants to "write the code" for you then maybe you will be able to solve your request.

I am just trying give you pointers to help you solve your request.

I hope this helps.

 

[grippy]

Very sorry i forgot to add the attributes in the example. For the above example to make sense with the attributes use these as the two records:

Record: 1
Product Code: 2222
Description: Super Cool Exhaust System
Make: Chevy
Model: Silverado
Category: Exhausts
Company: Borla
Price: $499
Attribute Prod Code: 2222B Attrbitue Name: Blue
Attribute Prod Code: 2222R Attrbitue Name: Red

Then changed info:

Product Code 2223
Make: Dodge
Model: Ram
Price: $599
Attribute Prod Code: 2223Y Attrbitue Name: Yellow
Attribute Prod Code: 2223P Attrbitue Name: Purple
Attribute Prod Code: 2223O Attrbitue Name: Orange

then the new record 2

Record: 2
Product Code: 2223
Description: Super Cool Exhaust System
Make: Dodge
Model: Ram
Category: Exhausts
Company: Borla
Price: $599
Attribute Prod Code: 2223Y Attrbitue Name: Yellow
Attribute Prod Code: 2223P Attrbitue Name: Purple
Attribute Prod Code: 2223O Attrbitue Name: Orange