Place Calc'd Form Field to New Record in Table?

[jumpyhu]

I've created a form which generates new inventory numbers for our stock based on a series of pulldowns and text fields. I'd like to allow the user the option of clicking thru a macro which will paste the value (field is called Text12) contained in the generated field into a specific cell in the inventory table (cell called Product ID)

I've tried a macro which opened the table and, in theory, used 'set value' to set the new record's product ID to that of Text 12, but the table opens and the cursor just sits in the Product ID cell, new record, blank.
Is there a better way to do this via macro or even VB Module? Last time I had a problem I posted here and the response was absolutely perfect

Thanks!

 

[S]

Using macro you can create a "micro" add_entry form containing only the field you need:
openform "microform"
set value productID_microform = forms![formname]![text12]
close form "microform"

Using code:
Dim db as database, rec as recordset
set db = currentdb()
set rec = db.openrecordset("TableName",dbopentable)
rec.lockedits false
rec.edit
rec.addnew
rec![productid]= forms![formname]![text12]
rec.update
rec.close
end sub

Bye, S.