form and subform, restrict delete and edit but allow additiions

[soul786]

hi,
pls help
i have tables, invoice and sales, both linked.
i created main form (invoice) with subform (sales). 1 invoice can have multiple sales items. forms are bound
i want that user enter data and if he is not satisfied with data either edit it or delete it (only the current, latest record)
but i dont want him to delete or edit previous data.
once he is satisfied with data he print it and after print, i want restriction on delete and edit of that data.
i know this is possible with temporary tables and unbound form but i want a more simple and better hassle free way
i just want restrictions on all data except which the user has just entered and he is deciding wether to delete or edit it , once he prints and move to next record, now he cant delete or edit that record from which he just moved.

[CJ_London]

I assume the subform is a continuous form or datasheet.

you could set the form to dataentry=true so the user can only see the record they are working on.

Alternatively you can use conditional formatting to disable controls in earlier records - could be based of a flag field that is set to true once printed for example

or a bit of vba code in the current event to set the form allowedits and allowdeletes properties to false - again based on a flag

It is not clear from your description what you mean by current, latest record. Current record is the one that has the focus which may or may not be the latest record. And do you mean invoice or sales?

You can also investigate the committrans method which may meet your needs

https://msdn.microsoft.com/en-us/lib.../ff835985.aspx

[soul786]

by latest record i mean record just entered by user, like a customer selected products but is bargaining and after bargaining decides to not buy, so the user has to delete or edit that record, any previous record is old even if 1 minuted old
and i m new to access, pls can u elaborate ur previous post
invoice contains sales
main form contains subform
i can do what i want in single entry form where there is no subform
but problem is with multiple entries in subform, i want whole main form along with subform to be deleted on click of command button on main form of allow edits only for latest, just entered record, not for previous old record even if old is 1 minute old.

[CJ_London]

pls can u elaborate ur previous post

you could set the form to dataentry=true so the user can only see the record they are working on. - google 'access dataentry'
Alternatively you can use conditional formatting to disable controls in earlier records - google access conditional formatting
or a bit of vba code in the current event to set the form allowedits and allowdeletes properties to false -google access vba conditional formatting

i want whole main form along with subform to be deleted on click of command button on main form of allow edits only for latest, just entered record, not for previous old record even if old is 1 minute old.

Appreciate you are new to access but you need to be clear on terminology - a form is a form, not a record. there is no such thing in databases as first/last/next/previous without some sense of order (e.g. sorted by invoice number)

as a minimum you have/need two tables

- invoice which contains things like invoice number, customer name, invoice date and a primary key
- invoice items which contains things like foreign key to invoice product key, item sold, item price, item quantity

plus perhaps a third or more to contain details about item names, types, prices, customer details - address, contact details etc

if you are using a bound form (google access bound forms) then when you leave a record it is automatically inserted/updated. An invoice with three items has 4 records

To try to clarify what you want - Do you want a user to be able to enter an invoice and a number of items and then either click a 'print' button or a 'cancel' button? If print is clicked the invoice record and its associated item records become locked and cannot be changed or added to? and if cancel is clicked you want to delete the invoice record and its associated item records?

[soul786]

yes that is what i want.
i dont want user to tamper with or delete any existing records except the one displayed on current window\screen, the record he has just entered.
invoice table linked to sales table with 1 to many relationship
1 customer/invoice can have multiple products/sales
any method or procedure whether sql or vba or other is welcome, but easiest one
thanks

[soul786]

yes that is what i want.
i dont want user to tamper with or delete any existing records except the one displayed on current window\screen, the record he has just entered.
invoice table linked to sales table with 1 to many relationship
1 customer/invoice can have multiple products/sales
any method or procedure whether sql or vba or other is welcome, but easiest one
thanks

[soul786]

can anyone please give me solution to this problem
i m also considering to do this with sql and vba using temporary tables then transfer to original tables, but i m confused as to what events to use to trigger inserting records, i mean i can use command click to transfer 1 record but how to transfer several as i enter them in subform and then when satisfied , click button to transfer to original table from temp table.
form is unbound

[soul786]

i figured out solution myself, hope it may help someone
temptables - itbl and stbl
original tables - invoice and sales
respectively

use main form open and close events

a.create temporary copy of original tables u want to put data in
b.delete tables after form is closed

use main form load and unload events

c.set recordsource for main and sub forms
d.use command button to copy data from temp to original tables
e.use command button to delete data from temp tables after step d and requery forms


1.put this code in main form open event

this will create empty copy of tables in which u want to ultimately put the code

DoCmd.SetWarnings False
DoCmd.RunSQL "select * into itbl from invoice where id = null" 'create temp copy of original table
DoCmd.RunSQL "select * into stbl from sales where sid = null"

2.put this code in close event of main form

this will delete tables created in step 1

DoCmd.SetWarnings False
DoCmd.RunSQL "drop table itbl,stbl" 'delete tables

3.put this code in main form load event

this will set recordsource of both forms, 1st is main and 2nd is sub form

Me.RecordSource = "itbl" 'set recordsource for main form
Me.stbl.Form.RecordSource = "stbl" 'set recordsource for sub form

4.put this code in unload event of main form

this will remove the recordsource of both forms

Me.RecordSource = "" 'set recordsource to null or remove it
Me.stbl.Form.RecordSource = ""

5.put this code in click event of save button

this will copy all data from temporary to original tables

DoCmd.RunSQL "insert into invoice select * from itbl" 'copy data from temptable to original table
DoCmd.RunSQL "insert into sales select * from stbl"

6.put this code in new record or cancel button

this will delete all data from temporary tables and refresh form, in short it will clear all data from forms and tables

DoCmd.RunSQL "delete from itbl" 'delete data from temptable
DoCmd.RunSQL "delete from stbl"
Me.Requery 'refresh form
Me.stbl.Form.Requery 'refresh subform

7.put this code in subform before insert event

id is pk in main form, and fk in subform
this will put id value from main form (pk) into subform id field (fk)

Me.sid = Nz(DMax("sid", "stbl"), 0) + 1 'this will get, then increment and insert value from and in temptable
Me.id = Me.Parent.id

8.put this code in before insert event of main form

Me.id = Nz(DMax("id", "invoice"), 0) + 1
Me.stbl.Form.sid = Nz(DMax("sid", "sales"), 0) + 1 'this will get value of last record from original table and put it in subform and then in temptable