VBA Coding - Skipping Coding

[hey yer barred]

Hi All!

I have a very basic bit of coding that wont do what its meant to do!

When the user clicks on the 'Run' button, I want a text box (called txtAction) to update with some writting so the user know the database is doing soemthing!

This is the coding

Me.txtAction = "Now running report..."

Why does the VBA ignore this as it does not update the text box!

 

[VicRauch]

Please show us all the code behind the "Run" button. When we can see all the code, we might be able to actually help.

 

[hey yer barred]

Ok..this is under a GoTo function, just incase you need to know that bit

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMaketblCashDaily")
qdf.Parameters("Start Date") = Me.txtStart
qdf.Parameters("End Date") = Me.txtEnd
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Me.txtAction = "Now opening Excel and copying data.."
Set xl = New Excel.Application
Set xlwb = xl.Workbooks.Open(DIRECTORY & "TotalCashCC.xls")
Set xls = xl.Worksheets("Sheet1")
xls.Range("A2").CopyFromRecordset rs
xl.Sheets("Total Cash").Select
Me.txtAction = "Refreshing pivot table.."
xlwb.ActiveSheet.PivotTables("PivotTable1").RefreshTable
xlwb.ActiveSheet.PivotTables("PivotTable1").PivotFields("Department").CurrentPage = "CM Inbound"
xl.Sheets("Sheet1").Select
xl.ActiveWindow.SelectedSheets.Visible = False
Me.txtAction = "Nothing.."
xl.Visible = True
MsgBox "Export complete", vbInformation, "MOJED"

The code in bold are the ones that dont work!

 

[SydneyGeek]

You probably have an issue with setting the focus back to the form. Try using the status bar instead. Place this code in a standard module...

Sub Status(pstrStatus As String)
    
    Dim lvarStatus As Variant
    
    If pstrStatus = "" Then
        lvarStatus = SysCmd(acSysCmdClearStatus)
    Else
        lvarStatus = SysCmd(acSysCmdSetStatus, pstrStatus)
    End If
    
End Sub

To use, replace this
Me.txtAction = "Now opening Excel and copying data.."
with this

Status("Now opening Excel and copying data..")

Denis

 

[VicRauch]

Right after each Me.txtAction =, put this line:
DoEvents
DoEvents will "Yield execution so that the operating system can process other events."

 

[Norie]

I don't know if it will make any difference but you normally don't need to select anything in Excel.

So this might not be needed.

xl.Sheets("Total Cash").Select

This could be setting focus to Excel, which might be what Denis is hinting at.

PS Why are you exporting to Excel anyway? Couldn't you use a crosstab query in Access?

PPS Why are you are setting the CurrentPage of the pivot table to CM Input?