Saving current record

[Dkline]

What I'm trying to do is have a form that provides input data to an Excel
spreadsheet and gets back some calculated values and saves those calculated
values in the current record.

Everything is fine up to the point of actually saving the calculated data.
Only one table is being used as both the source of the parameters passed to
Excel and to store the calculated results.

Code is borrowed from the various conversations in this newsgroup. I've
tried the Me.Refresh, Me.Dirty = False, RunCommand acCmdSaveRecord

Run it, close the form, look at the record - the calculated values are not
there.

How can I do this?

Code is:
Option Compare Database
Option Explicit


Private Sub cmdExcel_Click()
On Error GoTo Err_cmdExcel_Click

Dim objXL As Excel.Application
Dim objWB As Excel.Workbook
Dim objWS As Excel.Worksheet
Dim objRange As Excel.Range

On Error Resume Next
Set objXL = GetObject(, Excel.Application)
Err.Clear
If objXL Is Nothing Then
Set objXL = CreateObject("Excel.Application")
End If
Set objWB = objXL.Workbooks.Open("C:\Illustrations\VL-A08b.xls")
Set objWS = objWB.Worksheets("Input")
objWS.Activate

'objXL.Visible = True

'joint
objWS.Range("B2") = Me!xlJoint.Value
'gender1
objWS.Range("B3") = Me!xlGender1.Value
'age1
objWS.Range("B4") = Me!xlAge1.Value
'class1
objWS.Range("B5").Value = Me!xlClass1.Value
'gender2
objWS.Range("B7") = Me!xlGender2.Value
'age2
objWS.Range("B8") = Me!xlAge2.Value
'class2
objWS.Range("B9").Value = Me!xlClass2.Value

objXL.Run "SetFaceToMec"

'These values are put onto the form to then be saved in the current record
Me!xlCalc_FaceAmount.Value = objWS.Range("F22").Value
Me!xlCalc_MEC.Value = objWS.Range("I17").Value
Me!xlCalc_GAP.Value = objWS.Range("I18").Value
Me!xlCalc_GSP.Value = objWS.Range("I19").Value
Me.Refresh


Set objRange = Nothing
Set objWS = Nothing
objWB.Saved = True
Set objWB = Nothing
objXL.Workbooks.Close
Set objXL = Nothing

Exit_cmdExcel_Click:
Exit Sub

Err_cmdExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExcel_Click

End Sub

 

[Byron]

You did not say what version of Access you are using, but,
try placing the following code just below where you put
the data into the fields on the form. (Watch the wraping
of the line)

DoCmd.DoMenuItem acFormBar, acRecordsMenu,
acSaveRecord, , acMenuVer70

HTH

Byron

 

[John Vinson]

What I'm trying to do is have a form that provides input data to an Excel
spreadsheet and gets back some calculated values and saves those calculated
values in the current record

To force a save you can use either

DoCmd.RunCommand acCmdSaveRecord

or

If Me.Dirty = True Then Me.Dirty = False

 

[Dkline]

Thank you both for your help.

It occured to me about 3 AM what was the real problem. I had left the
"calculated" fields on the form unbound as is sometimes needed under
different circumstances - like using a combolist box for record selection.
After I bound the controls on the form to their field - Voila!

Guess I had a senior moment yesterday.