Automation of Copying records

[Ade F]

Right I know I have only just put up a post but I'm refining a DB and wish to gleam some knowledge in order to put those nice little touches to it.

I have the following form, some fields have been ommited for clarity.

Form Main
QuoteID
Date of Creation

Sub Form (Continuous)
QuoteID
Quantity
Item Description
Price

What I would like to do is have a button that takes entries from main and sub (including all continous form entries) and paste it into the next Record.

My premise is basically to copy and create an entire quotation that could then be modified (item descriptions etc). The reason being some customers wish for similar quotes with slightly different items.

I fully appreciate that this may be a little hard to do but it really is a nice touch. I have searched thorughout the forums for such solutions but none that provide solid evidence to solve this one.

Any other info you need is no probs ...please do ask.

Any ideas from the pro's?

Ade

 

[WayneRyan]

Ade,

You need two append queries; one for the main table and
one for the child.

If you are calculating your primary key:

Insert Into tblMain (Key1, F2, F3, F4)
Select Forms![YourMainForm]![YourNewKey], F2, F3, F4
From   tblMain
Where  [Key1] = Forms![YourMainForm]![MainFormKey]

Insert Into tblChild (Key1, F2, F3, F4)
Select Forms![YourMainForm]![YourNewKey], F2, F3, F4
From   tblChild
Where  [Key1] = Forms![YourMainForm]![MainFormKey]

You can make a "holder" on your main form for your
new calculated key, have the form open and develop
and test the queries using the Criteria section to
reference the "Source" key for the appends.

If your field is an AutoNumber, then you can't save
it on your form. Do the main table's append, then
retrieve the new autonumber and use it in your
second append query.

Whether you calculate your new key or use an autonumber
you will probably want to requery your main/child forms
to display the new info.

An alternative would be to use ADO/DAO. I like code.

Hope that gets you started.
Wayne

 

[Ade F]

Holy Contraceptives Batman

You have just blown me away, I think my brain was fried on line 5; is there an example db you can post in order for me to pick at?. From your response you are a pro.

In answer to the primary key yes I would like a new one generated when then information is copied into the new records.

Any further help would be greatly appreciated you have been great so far.

Ade,

You need two append queries; one for the main table and
one for the child.

If you are calculating your primary key:

Insert Into tblMain (Key1, F2, F3, F4)
Select Forms![YourMainForm]![YourNewKey], F2, F3, F4
From   tblMain
Where  [Key1] = Forms![YourMainForm]![MainFormKey]

Insert Into tblChild (Key1, F2, F3, F4)
Select Forms![YourMainForm]![YourNewKey], F2, F3, F4
From   tblChild
Where  [Key1] = Forms![YourMainForm]![MainFormKey]

You can make a "holder" on your main form for your
new calculated key, have the form open and develop
and test the queries using the Criteria section to
reference the "Source" key for the appends.

If your field is an AutoNumber, then you can't save
it on your form. Do the main table's append, then
retrieve the new autonumber and use it in your
second append query.

Whether you calculate your new key or use an autonumber
you will probably want to requery your main/child forms
to display the new info.

An alternative would be to use ADO/DAO. I like code.

Hope that gets you started.
Wayne

 

[WayneRyan]

Ade,

If you can post a sample db, then maybe one of us here can make it do
what you want.

Tools --> Database Utilities --> Compact/Repair
Then ZIP
Then attach to your post

Wayne

 

[Ade F]

Ok

I will see what I can do tonight. It will be the absolutely bare essentials though as this is a very cut down version of my DB that I made for work.

I'm basically lashing together another small database for my wife's business. Its very much in the development stages so this would be a great time for you to solve this problem.

Cheers Wayne

Regards

Ade

Ade,

If you can post a sample db, then maybe one of us here can make it do
what you want.

Tools --> Database Utilities --> Compact/Repair
Then ZIP
Then attach to your post

Wayne

 

[Ade F]

Heres the chopped DB

Just the bare essentials but I would like the details from the Invoice section to copy themselves into following record set with the use of a button.

I will explain more should you need it.

Cheers

Ade

I will see what I can do tonight. It will be the absolutely bare essentials though as this is a very cut down version of my DB that I made for work.

I'm basically lashing together another small database for my wife's business. Its very much in the development stages so this would be a great time for you to solve this problem.

Cheers Wayne

Regards

Ade

 

[Ade F]

Anyone care to have a shot?

Just wondered if there is anybody else out there with an idea for this one?. Wayne is probably on vacation . Or I have just asked an arse of a question.

Cheers

Ade