I need to put a calculated field in a table. I have a good reason.

[gobarr]

I am not educated well in SQL, but if it is the only solution to thi
problem please show me anyway. Here is what I'm trying to do.

I have Access 97. I have a scanner that scans barcodes from product
as we receive them into our warehouse. Most of our products are rente
so they are continuously coming back to us, so we need a way to trac
them. Well in order for our CSR's to locate and identify our product
we have prefixes that need to be concatenated to these raw seria
numbers. E.I. WC (wheelchair) is concatenated to 38563892 (seria
number) to get WC38563892. We do this because we print out our ow
serial numbers and place them on all the products so that we can trac
them and have much more inventory control. So instead of a barcod
that just has numbers 38563892 we have a barcode that has WC3856389
and it can be scanned and easily recognized in our database so that w
know that this particular item is back in our warehouse to be rente
again. The difficulty is when we buy new items we have to add th
correct prefix to the serial number and we do this by typing it all i
(very slow and prone to mistakes).

Well I developed a form that you can choose the corosponding prefi
from a list box and then scan the new products serial number next.
Then I made a calculated field that concatenates these two to make th
correct serial number that we will be printing out to be placed on th
item for tracking. All I want to do is store that calculated fiel
value into a table so that we can print from this table the correc
labels, which may be hundreds, to be placed on the products. Then th
CSR's will be able to locate and see the status on these newly bough
items. We buy and receive lots of new items all the time, and thi
would be a huge time saver. Please help! (Even if you know of
better way to do all this -- and trust me I know you do --- still le
me know how to do this please.) Thank you much!

 

[Allen Browne]

On the form where you add new items, use 3 controls:
1. A (hidden?) text box bound to the primary key field.

2. An unbound combo where the user selects the prefix code (such as WC).

3. An unbound text box where the barcode is read in.

Assuming these are called MyID, Prefix, and Serial respectively, use the
AfterUpdate event procedure of Prefix and Serial to assign the value to the
key field:

Private Sub txtPrefix_AfterUpdate()
If Not (IsNull(Me.Prefix) Or IsNull(Me.Serial) Then
Me.MyID = Me.Prefix & Me.Serial
End If
End Sub

Private Sub txtSerial_AfterUpdate()
Call txtPrefix_AfterUpdate
End Sub


Also, use the Current and Undo events of the form to clear the text boxes:
Private Sub Form_Current()
Me.Prefix = Null
Me.Serial = Null
End Sub