Complete record

[rui_jc]

Greetings to all,

i have a database with a few tables, but i need a special one that stores initial date and final date for a particular ID, but like this...
The table has the folowing fields; ID,STAGE,INITIALDATE,FINALDATE

New record:
ID1 - STAGE - INITIAL DATE - FINALDATE( leave blank )

New record:
ID2 - STAGE - INITIAL DATE - FINALDATE( leave blank )

When ID1 is repeated it must detect the blank field ( FINALDATE) , fill with the now date ( to finalize the record ) and also add a new record ( ID1 - STAGE - INITIALDATE - FINALDATE ( leave blank again ).

It also can have other records of other IDs in between.

I think i need a VBA code to detect the last record for ID#, detect FINALDATE field, write the date on it, save, and add a new record again.

Can this be done?

Many thanks in advance

 

[dopedealer]

what is your Primary Key then??
chris

 

[rui_jc]

My key is the ID.

The Idea is to have a main Form that when the ID is typed, the subform does the job automaticaly with code while the main form stores other data.

 

[dopedealer]

You will need a continous subform linked via the master/child PK ID fields. Make the default value of the final date as =Date() and create a button on the sub form next to the field called change final date or something which once clicked will empty final date and allow the user to input the final date, so by default final date will be locked. your code for the button would be: On Click:
Finaldate.locked = false
Finaldate.value = ""

that is one way to do it, alternatively you can make a query and use dlookup to see if the record exists and if its empty fill in the final date as = Date() so something like:
On click of a button:
If Dlookup("[FinalDate]","[tblsometable]") = "" then
Me.Subformname.Finaldate.value = Date()
end if

All done in a hurry so i might not be understanding you correctly.
hth
Chris

 

[rui_jc]

Thanks very much for your help,

But what i need is that on a main form ( that records in table X ) has a subform ( invisible and working with table Y ).

Imagine that it's for cars and the ID is the licence plate.
In the main form the user types data like plugs, wires, etc.
The subform knows that this car ( ID ) arrived monday 12AM ( INITIALDATE ) for engine fix ( STAGE ).
Meanwile other cars were recorded...
Then, at the paint shop ( same database - other main form but with same subform ) the user will place in the X table other data like paint, etc.

The Subform must know that the car with ID that arrived 12AM ( no FINALDATE ) will start it's paint job ( STAGE ).

So the subform must fill now the FINALDATE for the engine fix record ( as the user starts inserting the paint data ) , add a new record with this same ID# but with the new INITIALDATE ( can be the same as the previous finaldate ) and fill the STAGE ( paint fix ). - The finaldate in blank until another Stage area is pass.

And i say it has to be done with code because it has to be all automatic.

 

[rui_jc]

I know a way to find the last entry for this ID.

I used this code in the subform ( that is linked with the ID with the main form like you said):

Private Sub carID_AfterUpdate()
DoCmd.FindRecord Me.carID, , , acDown, , acCurrent
End Sub

now, when the user presses the save button on the main form...
in the subform FINALDATE is filled...

Me.Form![Ytable]![FINALDATE] = now()
...

but now i only need the code for adding a new record next, fill the ID,stage and INITIALDATE again and save.

I cannot put the Addnewrecord to work !!!!

 

[dopedealer]

Sorry for the late reply, busy at work, the only way i can think of is using continuous forms with default values.
hth
Chris