How to pass a variable between procedures?

[G Lam]

Hi, I have a Part Number edit form, which is linked to the tblPart table
with two fileds- partnbr and PartDiscr. When the frmPartedit form is opened,
both fields have value passed from the click event that opens the form. The
user can then changes the decription and click the OK button.
What I want to do is to store the partnbr, old description and new
description to a tblParteditHis table. I use RunSql INSERT INTO statement
and store the partnbr and new description value into the tblparteditHis
without problem. I am having trouble to retain the old description value and
capture it the RunSQL statement in the Click_OK button event.
Appreciate your help in advance.
Gary

 

[Geoff]

Hi Gary

An interesting problem.

I've not tested this, but I think you should create a
module-wide variable at the top of the module
(ie in the general declarations) behind the
frmPartEdit form, eg:

Dim strOldDescription as String

Code the OnCurrent event (which fires when you
change records), to store the value currently in
the PartDescr textbox in the above variable, eg:

strOldDescription = Me.txtPartDescr

When the user clicks the OK button, open a
recordset on your History table and
add a new record using the appropriate data
as follows.

If you have a reference to the DAO object library
(on the vba editor Tools, References menu),
then something like:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("HistoryTableName")
rs.AddNew
rs.Fields("PartNbr") = Me.txtPartNbr
rs.Fields("PartDescr") = Me.txtPartDescr
rs.Fields("PartOldDesription") = strOldDescription
rs.Update

If Not rs Is Nothing Then rs.Close
Set rs = Nothing
Set db = Nothing

Good luck with your project!
Geoff