Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 404
  • Last Modified:

saving data in a temporary table and write in respective table after confirmation

Hi experts,
I have a form and a datasheet subform I want to be able to enter all the details on the main form which contains the date customer etc… and the subform with the products, quantity and price, I would like to write it in the respective tables after clicking on a “Confirm button”. The Document Number should be progressive. How can I do that? An example would be really appreciated.
Jocelyn
0
Jocelyn_r64
Asked:
Jocelyn_r64
  • 10
  • 7
1 Solution
 
peter57rCommented:
Hi Jocelyn_r64,

If you are saying that you want to wait until all data entry is complete before you save anything then you have two choices.
Use unbound forms for all data entry and then write the records to the tables at the end, using VBA & SQL.
Bind the forms to tables used purely for data entry.  Then at the end use append queries to add the records to the main tables.

Pete
0
 
jjafferrCommented:
Hi Jocelyn

You will need 2 Tables for your requirement to work properly,
Tables:
tblDetails to enter the Details on frmDetails, which should have a unique ID, I will use an autonumber Detail_ID,
so there will be 1 record per Detail, thus 1 Detail_ID.
tblProducts to enter the rest of the data, which can be more than 1 records, but should a field called Detail_ID (long number). you make a relation between tblDetails and tblProducts using the Detail_ID.

Form:
Make the Form FrmDetails, with the Record Source as tblDetails.
Make the Form FrmProducts, with the Record Source as tblProducts, make it to datasheet, and you can hide Detail_ID column.
The Child/Master relation should be Detail_ID for both.

How to make it work:
when you enter the Details in FrmDetails, a Detail_ID will be generated, thus will create the link/relation to FrmProducts.

I don't recomend having a Temp table to do this, as it is straight forward, but if you wish for the TtblDetails and TtblProducts, then:
Make the Record Source for the Forms, these 2 new Ttbls, then once the data are entered, have a command button which will run 2 append queries,
1- from TtblDetails to tblDetails,
2- from TtblProducts to tblProducts,
then run 2 Delete queries:
1- Delete all the records in TtblDetails,
2- Delete all the records in TtblProducts,

But since you will use the Temp tables, then Detail_ID cannot be set as autonumber, as autonumber will revert to 1 once you "comapct and repair", so we will have to use
New_Detail_ID=DMAX("[Detail_ID]","tblDetails") + 1

please let me know if you nee further help.

jaffer
0
 
Jocelyn_r64Author Commented:
Hello Jaffer,
i have been able to do part of the first bit but i am stuck with the details... :-(
i have uploaded the file, do you think you could possibly check and explain my mistake?
this is the URL

http://file.webalice.it 
username j.rayapoulle
password cps
thanks
Jocelyn
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jjafferrCommented:
and where am I suppose to click to download the file?

jaffer
0
 
jjafferrCommented:
got it
0
 
Jocelyn_r64Author Commented:
Oops
check the box and then click "Scarica" top left
thanks
Jocelyn
0
 
jjafferrCommented:
Jocelyn

Are going the normal way, OR do you still want to use the Temp Table?
Can I know why you want to use the Temp Table? if after you enter the details you are going to add them to the right Tables any way?

jaffer
0
 
Jocelyn_r64Author Commented:
your way... then i will learn
:-)
0
 
jjafferrCommented:
Hi Jocelyn

Please test drive this mdb
http://s9.yousendit.com/d.aspx?id=3LIBA4P51JWAB1NLTYUVQOZOPV

I have done it the right way, without the Temp Table.

1.
You have done an [Event Procedure] "On Change" for Ved_Qty in Tbl_VendDet Subform,
I recommend changing it to "On AfterChange".

"On Change" means, while you are in the Control Ved_Qty, every time you enter a digit (with any press of the keyboard), the [Event Procedure] will run, thus if you have 100.5 then you are making it do the calculations 4 times, which is putting extra work on your PC with NO benefit.

2.
Since your Form gets its data from different Tables, it is best to use a query to link the Tables, instead of using a Lookup, which can be noticed (it is slow).


jaffer
0
 
jjafferrCommented:
Hi Jocelyn

How did it go?

jaffer
0
 
Jocelyn_r64Author Commented:
Hi Jaffer,
i am sorry for the delay... well part of it is good but it seems that when ever you select a client from the combo box automatically it writes it into the table without clicking on the confirm.
what i was expecting is key in the:-
date
choose the client from the Cmb box
move to the Data sheet
insert the product code, qty and price then if i click on the button then they go in the respective tables.. i also would like to use the ID of the tbl_vendita as the Invoice Number.
this is why i thought that a temporary table would help me to avoid increasing that Unique ID

i'm sorry for being a bit dumb... i have learn access from bits and pieces... examples from here and there...

:-(

Thanks a lot anyway..
Jocelyn
0
 
jjafferrCommented:
Hi Jocelyn

1. > it seems that when ever you select a client from the combo box automatically it writes it
Yes, that is how bound controls work, not only on the Client, but with the rest of the controls too, so any changes you do, they get saved automatically, which is an advantage in most of the projects.

2. On this site, please don't accept the comment as an answer unless it replies to your question to your satisfaction.

3. When I asked:
> Are going the normal way, OR do you still want to use the Temp Table?
your replied
> your way... then i will learn
So I did it my way, the correct way, for you to learn.

4. I am out of town now, and once back on Sat or Sun, I will send you the revised mdb which will use the Temp Tables.

5. > i also would like to use the ID of the tbl_vendita as the Invoice Number
So you don't want VenID, you want to change the field name to Invoice Number, then to allow you to enter this number, which should be the child/master relation between the Main and the SubForm, right?


jaffer
0
 
Jocelyn_r64Author Commented:
HI Jaffer,
1. I am so sorry for these issues may be I was not clear enough but I wanted this (I would like to write it in the respective tables after clicking on a “Confirm button”.)

2. I honestly did not know, as you did the job I thought it would be fair to give you the points though.

3. Yes, as I am a quite new in MS access, I was and I am sure that your approach is better than mine

4. That's cool... thanks

5. Well what I want to expecting to achieve is
      let the ID "autonumber" I want it to be the Doc Number (for future use) and there should not be anyone missing.
      Insert the Date
      Insert the client name or code
      Insert the product code, quantity and price in the subform
      After completing an order I should click on a button to paste these data's into their respective tables, if cancel the Order while inserting
                the data's the ID should not be increased.

I suppose this is feasible...

Kind regards and thanks for your help
Jocelyn
0
 
jjafferrCommented:
Hi Jocelyn

Now you have 2 Forms:
Entery Form:
TTbl_VendDet
It depends on 2 Temporary Tables.
These 2 Tables are always empty, except when you enter new data.

Edit / Delete / view the old Records:
Tbl_VendDet
It depends on the original Tables.

and here is the download link:
http://s41.yousendit.com/d.aspx?id=3Q8ALWTHDZGKA0W0VSB2PMWNHF

once you are done with your testing and you want to start the Autonumber from 1, then please Compact the mdb to reset the autonumber to 1.

I wish you success with your project.

jaffer
0
 
jjafferrCommented:
Hi Jocelyn

1. www.yousendit.com (where the last file is uploaded to) will hold the file for 7 days only, after that it will be deleted form their surver,
3 days have passed already, so you have 3-4 days left.
Please download the file ASAP otherwise it be gone.

2. Since you accepted an answer for your question, and based on the new rules of EE, this question will be closed and you won't be able to post further comments for additional help.


So, please let me know if you need further help ASAP :o)

jaffer
0
 
Jocelyn_r64Author Commented:
Hi Jaffer,
sorry for the delay...
i have just downloaded it... will check it out during the day and will revert to you...
thanks a lot
kind regards
Jocelyn
0
 
Jocelyn_r64Author Commented:
Hi Jaffer,
thanks a lot for your valuable help, now it's working and i still have some more stuff to do...

kind regards
Jocelyn
0
 
jjafferrCommented:
you are welcome :o)
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 10
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now