Command button to transfer data to an identically structured table

[DeanRowe]

Hi,

I’m using Access 2000.

I use access to process orders for my company. I have two identical forms, one which I use to quote a customer a price, and another to actually process the order - one is called “QuoteForm” and the other “OrderForm”.

Both forms are completely identical; they both draw information from the same tables and queries, however the data is stored in two separate tables – “QuoteTable” and “OrderTable” - they also have identical structures.

The reason I do this is so we do not confuse which orders have been placed and paid for, and which have only been quoted for...

Currently I am taking the time to fill out the “QuoteForm”, then if the customer decides to make a purchase I have to spend further time filling out the “OrderForm” – effectively doing the same thing twice!

I would like to place a button on the “QuoteForm” which can automatically load up the “OrderForm”, start a new record and transfer this information across – cutting my data entry time in half!

Has anyone come across anything like this before?

I was also considering “a little red button” to place on the “QuoteForm” – so if the order is placed I can press the button and it self-destructs/erases itself! Although I think I would need a little confirmation screen to pop up asking me if I was sure I wanted to do this – so any ideas on this would also be very appreciated, but is not essential as I also thought little red buttons were foolish as a child.

Thank you for taking the time to read this. Any help would be greatly appreciated.

 

[RuralGuy]

See if this link helps.

 

[DeanRowe]

Hi Rural Guy,

Thanks for replying. I've looked through the link and I think it may be a little over my head, and I'm not sure whether this method would suit what I require...

"Archiving is best reserved for cases where you won't ever need the old data, or there are overriding considerations e.g. hundreds of thousands of records, with new ones being added constantly. The archive table will probably be in a separate database."

In my case I will require the "old" data - it will be used to print a delivery note, a receipt, there's lots of pricing data that is used by various queries to calculate totals etc. I know it is only an example but the code on this example is for transfering the data to another database located on the hard drive, I would require the data moving from one table to another within the same database - and my knowledge is very limited. However I may have a play around with a backup copy to see if anything happens.

Thank you for your help, I appreciate you taking the time. Any suggestions of another method would be welcomed - after reading that link I was wondering whether an append query could be used somehow to add the data to the "Orders" table - then somehow program the command button to open up the "OrdersForm" to show the last entry - this way the data would be appended to the orders form and i could see the information on the "OrdersForm" (when the customer pays for the goods - all the order information such as the product and its price is the same, however I need to add further details such as the transaction code and the staff member who puts the order through).

 

[RuralGuy]

My personal preference is to simply have a field in one table for Quote/Order and set it accordingly. No moving of records required. Allen's code can easily be changed to move the record to a table in the current db. The important information is it shows how to add a record from one table to another table and then delete the source record after the successful append.

 

[DeanRowe]

Hi Rural Guy,

Thanks for your help - after your first post I started looking into performing an append query. On my quote form I've managed to make a command button that appends the current record on the form from the "quote" table into the "orders" table - and I hadn't even thought about append queries before you posted that first post so thank you very much indeed!

There's still quite a bit I need to do though... the record that is appended from the "quote" table to the "orders" table still remains in the "quote" table - I'm not to sure how to get it to delete itself afterwards.

Also - do you know how to vb script a command to= close existing form, and open a new form at the last entry?

If I can get these two problems fixed then the whole objective will have been achieved! At the moment I've still saved myself a whole lot of data entry time! Thanks for your help rural guy, any ideas on these remaining problems?

 

[RuralGuy]

Allen's link shows how to delete the "moved" record after a successful move. You can use a WhereCondition argument of the OpenForm command to open the other form on the record you want.

 

[DeanRowe]

Hi Rural Guy,

I've gone about it a little differently than Allen did I think - I used the Access wizard to make the button and link it to the append query, so i didn't do any coding myself. I've gone into the event procedure for the click of the button and here is what it shows: (the append query is labelled "Quote Query" and the button itself is "Command124")


Private Sub Command124_Click()
On Error GoTo Err_Command124_Click

Dim stDocName As String

stDocName = "Quote Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command124_Click:
Exit Sub

Err_Command124_Click:
MsgBox Err.Description
Resume Exit_Command124_Click

End Sub

As I said before Allens coding was a little over my head - would you mind giving me a few pointers please? such as where in the sequence I should enter the DELETE coding and where I should enter the OPENFORM coding, I presume I should enter a CLOSEFORM code before the OPENFORM code to close down the "QuoteForm". I will start researching the code itself but if you wouldn't mind letting me know where it should go then that would be a tremendous help. Thanks alot Rural Guy.

 

[RuralGuy]

How about posting the SQL view of the "Quote Query" so we can see it?

 

[missinglinq]

Why not simply have a checkbox to indicate that the order has been placed/paid for?

 

[KeithG]

Why not just make the PK from the quotes table a FK in the orders table? This way you can associate a qoute to an order.

 

[DeanRowe]

Hi guys,

firstly a very big thank you for all your help and advice. I took on board what you were saying and the db now runs like a dream. i added an "Quote/Orders" field to the "Orders" table and use queries to seperate the two, and forms to run from these queries. So once again thank you for all your help.

 

[RuralGuy]

Good solution. Thanks for posting back.

 

[DeanRowe]

No problem, thank you very much for your help, I really have appreciated it.

If it would not be too much to ask, would you mind taking a look over a post I have just made in the forms forum please to see if you might know what the best solution would be.

Thanks again for all your help RuralGuy

 

[GaryPanic]

I use the .add function

I have quotes and I have order

I have quotes that may or may not be taken up (insurance)
so I have a client with a policy
this policy may need to have items added removed to it by endorsement

so i have a history table (order)
and what I do is have a button to add a record into my order table(not using qry as its not that simple)- on the click of this button it creates a invoice/transaction number stores this and the date/time when i pushed the button it copies my current record into the history/order table i run my accounts off this table. - but also on the clicking of this button i have it check all the fields to ensure (validation) that there is info in where I need it to do bucket loads of reporting- simple things like inception date (which may be dioffferent from now date or date of invoicing - ie emails giniving instructions over the weekend etc
diary functions, tax functions, etc all on the click of one button rather than forcing validation at each point , cos sometmes i may not know what tax to use or what currency i am using - or who the handlier is or even who the end accoutn is going to be

i rate the .add function highly and many thanks to whoever showed it to me .

regards

 

[DeanRowe]

Hi Gary,

Would you mind taking a look at the post I currently have here - http://www.access-programmers.co.uk/forums/showthread.php?t=129934

It looks like what you are saying would suit, except i couldnt add it from one form to another. It must be taken from another form.

Let me explain further, I need to take information from Form1 and copy it to Form2. however the command button must be on Form2, so by pressing a command button on form2 I am taking/copying info from Form1.

Any help would be appreciated.

 

[boblarson]

Let me explain further, I need to take information from Form1 and copy it to Form2. however the command button must be on Form2, so by pressing a command button on form2 I am taking/copying info from Form1.

Nothing difficult about this as long as both forms are open. You can copy from one field on form 1 to a field in form 2 by using:

Me.MyTextBoxNameHere = Forms!YourForm1NameHere.YourTextBoxNameHere

 

[GaryPanic]

bobs the man on this ..
I have from table to table
the forms have no real meaning for me
its tables with data in that I move from quote to history
what I will do is copy this and post it up
Its at home and i am at work - I throw it up in a couple of hours

 

[DeanRowe]

ITS ALIVE!!!! Bob the code worked, your a legend, you've just cut my data entry time by around 500%!!!

Thank you ever so much, I really really really appreciate it!!

Yippee!!!

 

[boblarson]

Glad to hear you got it to work the way you wanted.