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

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

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

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

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

jaffer

got it

Oops
check the box and then click "Scarica" top left
thanks
Jocelyn

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

your way... then i will learn
:-)

Hi Jocelyn

How did it go?

jaffer

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

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

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

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

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

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

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

you are welcome :o)