Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating excel worksheet from an Access Query | Links and Linking in Excel | |||
Can I use MS Query in Excel like an Append Query in Access | Excel Discussion (Misc queries) | |||
How to open Access recordset via Query in Excel VBA??? | Excel Programming | |||
Microsoft Query rejects "nz" function in Access Query | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |