Question Transfer row data and Delete row from table after data entered in a certain field

[BPBP]

Transfer row from 1 table to another

Hi,

Not sure where i should post this, but this is what i'm trying to do,

0) I have a form which is based on tbl1. On the form, there is this textbox txtA. The form can be saved by clicking a button, without keying in txtA.

1) However if txtA is keyed with legit data( date type which is => current date) in it when the form is saved,

2) it would auto populate txtB and txtC. txtB is dmax + 1 from a dlookup of a field in another table tbl2. and followed by appending the year(2 digits) to the left of txtB and store this appended data into txtC.

3) Next it would copy this entire record from tbl1 and dump it into a new record on tbl2.

4) the entire record which was previously copied from tbl1 will then be deleted.

If txtA does not have data in part 1, part 2-4 wouldnt occur.




Questions - I've already done up to part 1 now but i'm not too sure how to continue hence i would like some suggestions if its possible to do this or better ways to do this?

Q1 - the check for legit data in txtA would be in the before update event of the form?

Q2 - This should occur also in the beforeupdate event of the form right after part 1?

Q3 - No idea how and when to do this, should this step be carried out in the afterupdate event of the form? and is there a row copy function?

Q4 - No idea how and when to do this part too. is there some vba for this "cut" function? Which event do i use to perform this step?

Questions numbers here corresponds to the parts above.

 

[jzwp22]

Why do you even need tbl1? Why store data that isn't legitimate? Base your form on tbl2 and use the before insert event of the form to check the value in txtA; if it is invalid, clear the textbox control and prompt the user for legitimate data.

 

[BPBP]

Why do you even need tbl1? Why store data that isn't legitimate? Base your form on tbl2 and use the before insert event of the form to check the value in txtA; if it is invalid, clear the textbox control and prompt the user for legitimate data.

Thanks for the reply.

because tbl2 has a primary key field which is an autoincrement number. this number must run in sequence and only be generated when there is a valid value in txtA.

However there are times when txtA is blank, which means the record cannot be saved if the key is not entered. but i want to have a temporary record of the other data of the form when txtA is blank. Until a user inputs value into that txtA field for that record, the other data has to be captured, that is why i put them into tbl1.

or someone has better suggestions please.

 

[jzwp22]

You can still use 1 table and 1 form and generate your own sequential number using the domain aggregate function DMAX() and adding 1 when you have valid data. You can still keep the data for those records that did not have valid data and adjust your queries, reports etc. to ignore those records without the sequential number.