When does the Access query run at worksheet open?
 

Hello all,

I'm working on an excel file that will get information from a couple cells,
and put that into a msgbox at worksheet open. I have one problem tho, if I
put the code at the Worksheet_Open, it fires before the query is run, and the
msgbox has old info in it. I would like the msgbox to popup without running
a macro, so where do I put the code so it will fire after the query? Any
help is appreciated, and I hope I was clear enough.

Code I'm using:

Dim strTotal, strRes, strUnres As String

'Worksheets("Count").Visible = True
Sheets("Count").Select
Range("C2").Select
'strTotal = ActiveCell.Value
Range("C3").Select
strRes = ActiveCell.Value
Range("C4").Select
strUnres = ActiveCell.Value
'Worksheets("Count").Visible = False

MsgBox "Resolved Issues: " & strRes & Chr(13) _
& "Unresolved Issues: " & strUnres & Chr(13) _
& "Total Issues: " & strTotal, vbOKOnly, "Tallies"

When does the Access query run at worksheet open?
 

Okay, I cleaned the code up a bit. I also decided to disable the automatic
updating of the access query, since I'm going to be e-mailing this form to
others, and most don't have access to the database. Still curious about the
original question tho. I found a workaround, but curiousity is killing this
cat.


Private Sub Workbook_Activate()

Dim strTotal, strRes, strUnres As String

strTotal = Sheets("Count").Range("C2").Value
strRes = Sheets("Count").Range("C3").Value
strUnres = Sheets("Count").Range("C4").Value

MsgBox "Resolved Issues: " & strRes & " " & Chr(13) _
& "Unresolved Issues: " & strUnres & " " & Chr(13) _
& "Total Issues: " & strTotal, vbOKOnly, "Tallies"

End Sub

When does the Access query run at worksheet open?
 

On 8 Mar, 19:59, Wood Grafing
wrote:
Okay, I cleaned the code up a bit. I also decided to disable the automatic
updating of the access query, since I'm going to be e-mailing this form to
others, and most don't have access to the database. Still curious about the
original question tho. I found a workaround, but curiousity is killing this
cat.

Private Sub Workbook_Activate()

Dim strTotal, strRes, strUnres As String

strTotal = Sheets("Count").Range("C2").Value
strRes = Sheets("Count").Range("C3").Value
strUnres = Sheets("Count").Range("C4").Value

MsgBox "Resolved Issues: " & strRes & " " & Chr(13) _
& "Unresolved Issues: " & strUnres & " " & Chr(13) _
& "Total Issues: " & strTotal, vbOKOnly, "Tallies"

End Sub

You could explicitly refresh the query as part of the Workbook_Open.
Depending on where it is, use something like:

ThisWorkbook.Sheets("Sheet1").Range("A1").QueryTab le.Refresh
BackgroundQuery:=False

before your MsgBox line. If you don't want users without database
access to do this, you might need to add a Yes/No MsgBox before this.

The 'BackgroundQuery:=False' makes sure that the MsgBox won't appear
before the refresh is complete.

When does the Access query run at worksheet open?
 

On 8 Mar, 23:08, "JakeyC" wrote:
On 8 Mar, 19:59, Wood Grafing
wrote:





Okay, I cleaned the code up a bit. I also decided to disable the automatic
updating of the access query, since I'm going to be e-mailing this form to
others, and most don't have access to the database. Still curious about the
original question tho. I found a workaround, but curiousity is killing this
cat.

Private Sub Workbook_Activate()

Dim strTotal, strRes, strUnres As String

strTotal = Sheets("Count").Range("C2").Value
strRes = Sheets("Count").Range("C3").Value
strUnres = Sheets("Count").Range("C4").Value

MsgBox "Resolved Issues: " & strRes & " " & Chr(13) _
& "Unresolved Issues: " & strUnres & " " & Chr(13) _
& "Total Issues: " & strTotal, vbOKOnly, "Tallies"

End Sub

You could explicitly refresh the query as part of the Workbook_Open.
Depending on where it is, use something like:

ThisWorkbook.Sheets("Sheet1").Range("A1").QueryTab le.Refresh
BackgroundQuery:=False

before your MsgBox line. If you don't want users without database
access to do this, you might need to add a Yes/No MsgBox before this.

The 'BackgroundQuery:=False' makes sure that the MsgBox won't appear
before the refresh is complete.- Hide quoted text -

- Show quoted text -

....and I should say add that line before you assign values to
variables...

When does the Access query run at worksheet open?
 

Thank you, just what I needed to know.

"JakeyC" wrote:

On 8 Mar, 23:08, "JakeyC" wrote:
On 8 Mar, 19:59, Wood Grafing
wrote:





Okay, I cleaned the code up a bit. I also decided to disable the automatic
updating of the access query, since I'm going to be e-mailing this form to
others, and most don't have access to the database. Still curious about the
original question tho. I found a workaround, but curiousity is killing this
cat.

Private Sub Workbook_Activate()

Dim strTotal, strRes, strUnres As String

strTotal = Sheets("Count").Range("C2").Value
strRes = Sheets("Count").Range("C3").Value
strUnres = Sheets("Count").Range("C4").Value

MsgBox "Resolved Issues: " & strRes & " " & Chr(13) _
& "Unresolved Issues: " & strUnres & " " & Chr(13) _
& "Total Issues: " & strTotal, vbOKOnly, "Tallies"

End Sub

You could explicitly refresh the query as part of the Workbook_Open.
Depending on where it is, use something like:

ThisWorkbook.Sheets("Sheet1").Range("A1").QueryTab le.Refresh
BackgroundQuery:=False

before your MsgBox line. If you don't want users without database
access to do this, you might need to add a Yes/No MsgBox before this.

The 'BackgroundQuery:=False' makes sure that the MsgBox won't appear
before the refresh is complete.- Hide quoted text -

- Show quoted text -

....and I should say add that line before you assign values to
variables...