Saving ONE TIME calculation to Table

[Rhodes7292]

Access 2003

I am trying to save a calculated field (Item No) from a form to the accosiated field within the Products table

I have a hidden text box (itemNo) which is bound to the relevant field and a second text box with the following formula

="TV-" & Left(CatShort.column(1),3) & "-" & Format([ProductID],"0000") as its source

What I am trying to do is to provide an item number for each item of stock based on the Category short name (Catshort) and the incremented item ID Number, with a TV prefix.

I am aware that its a bad itea to place calculated field into the table if the calculation is likely to change, however once my calculation is stored it has no need to change.

I have tried the beforeUpdate method within the ItemNo textbox

Private Sub ItemNo_BeforeUpdate(Cancel As Integer)
Me!ItemNo = Me!ItemCalc
End Sub

but nothing happens (in that the table is not updated)

can anyone help either sort this out, or provide a way of putting together an incremental item number based on the selected category with a "TV" prefix

Many thanks

 

[Isskint]

I would suggest putting your code in the after update event of the CatShort OR the ProdID controls. EG

Me!ItemNo ="TV-" & Left(CatShort.column(1),3) & "-" & Format([ProductID],"0000")

 

[gemma-the-husky]

Private Sub ItemNo_BeforeUpdate(Cancel As Integer)
Me!ItemNo = Me!ItemCalc
End Sub

you cannot set the itemno in its own update event

if the itemno is blank, you probably need to set it in the FORMS beforeupdate event.

personally, i think a structured field as you describe is not the best idea though. it is immediately non-normalised. far better to store the 3 bits that generate the field separately.

 

[nanscombe]

Are you just generating some sort of Item Number for human readability?

If so then, as Isskint said, you can use code to do it and as, gemma-the-husky said, you want to put it in the form_BeforeUpdate code.

I presume that CatShort, which appears to be a lookup of some kind, and ProductID as already saved in your table anyway.

As you probably don't want to do it every the time the record is changed what I would suggest is ..

Sub yourForm_BeforeUpdate(Cancel as Integer)
 if NZ(Me!ItemNo,"") = "" then Me!ItemNo ="TV-" & Left(CatShort.column(1),3) & "-" & Format([ProductID],"0000")
End Sub

 

[Rhodes7292]

thanks guys

Is there anyway I can force the field to update, as I have to close after every update for it to register in the table

 

[nanscombe]

Use the Command Button wizard to put a Save Record button on your Form?

Or press Ctrl + Enter?

 

[Dmak]

thanks guys

Is there anyway I can force the field to update, as I have to close after every update for it to register in the table

.Requery????