Loosing form Values after event procedure (1 Viewer)

[w1fini]

Hi,

I have a Access 2007 form with 2 Subforms. On the main form I have a button that executes an event procedure. In this procedure I open an excel application + workbook and write some form values into the excel.

The code opens the excel and the workbook and even writes the values but when I want to execute the button an other time it seems as if my form values have vansihed. Although the values are still in the form, VB cant get hold of the variables like recordsets etc.

What can that be?I suspect it has anything to do with how I open Excel:

    Set xlWB = GetObject(workBookName)
    If IsNull(xlApp) Then
    'open new
        Set xlApp = CreateObject("Excel.Application")
        xlApp.Visible = True
        Set xlWB = xlApp.Workbooks.Open(workBookName, , False)
        xlWB.Worksheets(workSheetName).Activate
    Else
    'Activate the existing
        xlWB.Application.Visible = True
        xlWB.Parent.Windows(1).Visible = True
        xlWB.Activate
        xlWB.Worksheets(workSheetName).Activate
        xlWB.Worksheets(workSheetName).Range("A4").Value = "It works!!"
        Set xlApp = Nothing
    End If

I would appreciate very much if anyone can point me into a direction. Thank you very much in advance.


UPDATE: I dont know if this helps: If I click with the mouse into several controls in all the subforms The VBA code can access the values again.

regards
Nic

 

[DKO]

What code are you using to transfer the values to excel?

It sounds like a problem of where the focus is. If your form/subform has the focus, it works - if it doesn't have the focus, it doesn't work....correct?

So maybe just return the focus to your subform before transferring the values to excel.

Or maybe it's a problem with how you're referencing the controls on your form...

 

[w1fini]

Thank you very much for the tip. Now its setting the focus back into the form. Seems to work. As I still struggle with an inconstant state of the database as soon as an VB Error occurs I can't tell if the lose of data is related to this cause.

Hope I get it stable to give more feedback.

 

[HiTechCoach]

It would really help to see your c ode that actually places the values into the spreadsheet!

 

[w1fini]

Ok,

here comes the complete "Spaghetti Code". I recon it might have something to do with trying to access variables via Me.Form...
Maybe because I activate Excel the Me object gets changed or something.


Thus if I try to execute the action again the recordset from the subsubform is empty.

As soon as I navigate to the next record within my form I can execute the action gain.

Private Sub UpdateExcel_Click()

On Error GoTo Error_Handler

'Excel Variables
Dim InstallerNames As String
Dim JobDate As Date
Dim JobTime As Date
Dim CustomerName As String
Dim CustomerAddress  As String
Dim CustomerSuburb  As String
Dim Consultant  As String
Dim SqrMtr As String
Dim PricePerMeter  As String
Dim QuoteNr  As String


Dim whereCondition As String

'Catch the Data

Set rsJobs = Me!QuoteForm.Form!JobSubform.Form.Recordset
Set rsQuote = Me!QuoteForm.Form.Recordset

'Get Customer Details

CustomerName = Me.CustomerName
CustomerAddress = Me.CustomerAddress
CustomerSuburb = Me.CustomerSuburb

'Get Quote Details
Consultant = Me!QuoteForm.Controls("SalesPersonID")
SqrMtr = Me!QuoteForm.Controls("SqrMeter")
PricePerMeter = Me!QuoteForm.Controls("PricePerMeter")
QuoteNr = Me!QuoteForm.Controls("QuoteNr")
JobDate = Me!QuoteForm.Controls("DateOfInstall")
JobTime = Me!QuoteForm.Controls("TimeOfInstall")

whereCondition = Consultant
'Get Personal Details
If Not rsJobs.EOF Then
    rsJobs.MoveFirst
    Do While Not rsJobs.EOF
        If whereCondition = "" Or IsNull(whereCondition) Then
            whereCondition = rsJobs("PersonellNr")
        Else
            whereCondition = whereCondition & ", " & rsJobs("PersonellNr")
    End If
    rsJobs.MoveNext
    Loop
Else
    MsgBox ("No Installers assigned! Operation aborted.")
    Resume Exit_This_Sub
End If

'get the names
getPersonellNames whereCondition, InstallerNames, Consultant

'get the workbook
Dim xlWB As Excel.Workbook
Set xlWB = getWorkbook(JobDate, InstallerNames)


'Check if Values where changes and delete the old entries
If (dateOldDateValue > 0) Then
    If (dateOldTimeValue > 0) Then
        Call clearOldExcelData(xlWB, dateOldDateValue, dateOldTimeValue, InstallerNames)
    Else
        Call clearOldExcelData(xlWB, dateOldDateValue, JobTime, InstallerNames)
    End If
Else
    If (dateOldTimeValue > 0) Then
        Call clearOldExcelData(xlWB, JobDate, dateOldTimeValue, InstallerNames)
    End If
End If

'Zeile =abhaenging von Zeit
Dim rowModifier As Integer
rowModifier = getRowModifier(JobTime)
'Spalte=abhaenig von Datum
Dim colModifier As String
colModifier = getColModifier(JobDate)

'--------------------Start Insert Values-------------------------------------
'Time
xlWB.Application.ScreenUpdating = False

xlWB.Worksheets(InstallerNames).Range(colModifier & (9 * rowModifier + 3)).Value = JobTime
xlWB.Worksheets(InstallerNames).Range(colModifier & (9 * rowModifier + 4)).Value = CustomerName
xlWB.Worksheets(InstallerNames).Range(colModifier & (9 * rowModifier + 5)).Value = CustomerAddress
xlWB.Worksheets(InstallerNames).Range(colModifier & (9 * rowModifier + 6)).Value = CustomerSuburb
xlWB.Worksheets(InstallerNames).Range(colModifier & (9 * rowModifier + 7)).Value = Consultant
xlWB.Worksheets(InstallerNames).Range(colModifier & (9 * rowModifier + 8)).Value = SqrMtr
xlWB.Worksheets(InstallerNames).Range(colModifier & (9 * rowModifier + 9)).Value = PricePerMeter
xlWB.Worksheets(InstallerNames).Range(colModifier & (9 * rowModifier + 10)).Value = QuoteNr
xlWB.Application.ScreenUpdating = True

'---------------------Finished Put Values--------------------------------------

xlWB.Save

'reset old values    
dateOldDateValue = 0
dateOldTimeValue = 0
    

Exit_This_Sub:
        Exit Sub

Error_Handler:
    ' Display error information.
    Select Case Err.Number

        Case 0
            ' Do nothing; no error has occurred.
            MsgBox "Error number " & Err.Number & ": " & Err.Description
        Case 1004
            'Make a new Workbook
            xlApp.Workbooks.Add (workBookName)
            Resume Next
        Case Else
            MsgBox "Error number " & Err.Number & ": " & Err.Description
        ' Resume with statement following occurrence of error.
        Resume Exit_This_Sub
    End Select

    Err.Clear

End Sub

 

[w1fini]

Update.

I tried to set the focus as the very beginning of the sub

Application.Forms.CustomerForm.SetFocus
Application.Forms.CustomerForm.Form!QuoteForm.SetFocus
Application.Forms.CustomerForm.Form!QuoteForm.Form!JobSubform.SetFocus

But in the second run I can't get hold of the recordsets from forms and subforms. Funnily they are still displayed in access. Thus where are they gone that the VB can't get them?
Is there some microsoft hiding spot where the recordset disapears?

 

[LPurvis]

In what way can you not get "hold" of the form recordsets after this procedure has run?
What is the error, where does it occur?

 

[w1fini]

I execute the procedure that open an excel workbook to put data into the excel worksheet. If I click a second time an the button I get a lot of errormessages because the recordsets in the form and subforms are empty.

I just got an idea. Can it be that I somehow change the recordset or the pointer while I browser through them to get the values?

 

[w1fini]

Wow! Thanks for the sokratical help (Maieutik). That was the point.

I now clone the recordset and navigate through the clone. And viola I can call the procedure as often as I want.

Thank you guys!

 

[LPurvis]

Want a moral of the story?
Be descriptive in the question. Exact problem, exact error - exact position where it occurs.
So many post with "got an error". That really is almost meaningless.
I don't know about the devil, but the answer is in the details.

Cheers.

 

[w1fini]

I think my error description wasn't THAT bad. Its just the longer the look at an error the clearer it come and you can describe it.