Works when stepping thru only

[Guest]

The following code inserts a query, adds a new column that formats column C's
time as h:ss, moves that column to the right of column C then hides Column C.

When I step through the code it works perfect but when I take all the break
points off and run it, column Column C is hidden and the results of the
CreateTimeFormula procedure does not seem to run (or there is some other
problem. I am really stuck. Thank you.

Sub CreateQueryTables()

Dim strCnn As String, strCmdTxt As String
' Set up connection string.
strCnn = "ODBC;DBQ=H:\EducationPro\EducationPro-New.mdb;" & _
"Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS
Access;PageTimeout=15"

'If I password this then enable below
'strCnn = strCnn & ";Password=<pwd>;User ID=<userID>"
strCmdTxt = Empty

strCmdTxt = "SELECT DISTINCT [Unit] & [Tier] & [Room] & [Bed] AS House,
" & _
"tblStudentHistory.DOCNumber AS [DOC#], tblStudentHistory.Time,
[LastName] & ', ' " & _
"& [FirstName] AS NAME, 'MSC Education' AS DESTINATION FROM
tblStudentHistory " & _
"INNER JOIN tblStudents ON tblStudentHistory.DOCNumber =
tblStudents.DOCNumber " & _
"WHERE (((tblStudentHistory.Quarter) = 'Summer 2007')) ORDER BY
tblStudentHistory.Time, " & _
"[LastName] & ', ' & [FirstName];"


'Clear contents of columns A through E to input to refresh query info
Columns("A:G").Select
Selection.Delete


' Create the QueryTable on the ActiveSheet at the range stated.
With ActiveSheet.QueryTables.Add(Connection:=strCnn,
Destination:=Range("A3"))
If .QueryType = xlOLEDBQuery Then .CommandType = xlCmdDefault
.CommandText = strCmdTxt
.RefreshStyle = xlOverwriteCells
.HasAutoFormat = False
.RefreshOnFileOpen = False
.Refresh
End With

'Replaces unit names with initials
ReplaceUnit

'Fill column F with time and formula for 12 hour clock
CreateTimeFormula

'Hide original times column and display formatted data after moving the
column
Columns("C:C").Select
Worksheets("Sheet1").Columns("C").Hidden = True
End Sub

Sub CreateTimeFormula()
'
' CreateTimeFormula Macro
' Created to fill column F with time and formula for 12 hour clock
'
Columns("D").Select
Selection.Insert
Range("D3").Select
ActiveCell.FormulaR1C1 = "TIME"
Range("D4").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",MOD(RC[-1],0.5))"
Range("D4").Select
Selection.AutoFill Destination:=Range("D4856"), Type:=xlFillDefault
Worksheets("Sheet1").Range("D4856").NumberFormat = "h:mm"

Range("A4").Select
End Sub

 

[Tim Williams]

Check your query is not executing in the background.
If it is, it may not be completed in time for the rest of your code.

Try adding

..BackgroundQuery = False

Tim

 

[Guest]

Put this at the top of your code module:

Public Function HalfSecDly()
s = Timer + 0.5
Do While Timer < s
DoEvents
Loop
End Function

and then add this line:

HalfSecDly

after your call for:

CreateTimeFormula

Sometimes the End Sub executes before code outside
the main sub can execute. It shouldn't, but it does.
With the delay, it might allow your time format code
to execute.

The following code inserts a query, adds a new column that formats column C's
time as h:ss, moves that column to the right of column C then hides Column C.

When I step through the code it works perfect but when I take all the break
points off and run it, column Column C is hidden and the results of the
CreateTimeFormula procedure does not seem to run (or there is some other
problem. I am really stuck. Thank you.

Sub CreateQueryTables()

Dim strCnn As String, strCmdTxt As String
' Set up connection string.
strCnn = "ODBC;DBQ=H:\EducationPro\EducationPro-New.mdb;" & _
"Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS
Access;PageTimeout=15"

'If I password this then enable below
'strCnn = strCnn & ";Password=<pwd>;User ID=<userID>"
strCmdTxt = Empty

strCmdTxt = "SELECT DISTINCT [Unit] & [Tier] & [Room] & [Bed] AS House,
" & _
"tblStudentHistory.DOCNumber AS [DOC#], tblStudentHistory.Time,
[LastName] & ', ' " & _
"& [FirstName] AS NAME, 'MSC Education' AS DESTINATION FROM
tblStudentHistory " & _
"INNER JOIN tblStudents ON tblStudentHistory.DOCNumber =
tblStudents.DOCNumber " & _
"WHERE (((tblStudentHistory.Quarter) = 'Summer 2007')) ORDER BY
tblStudentHistory.Time, " & _
"[LastName] & ', ' & [FirstName];"


'Clear contents of columns A through E to input to refresh query info
Columns("A:G").Select
Selection.Delete


' Create the QueryTable on the ActiveSheet at the range stated.
With ActiveSheet.QueryTables.Add(Connection:=strCnn,
Destination:=Range("A3"))
If .QueryType = xlOLEDBQuery Then .CommandType = xlCmdDefault
.CommandText = strCmdTxt
.RefreshStyle = xlOverwriteCells
.HasAutoFormat = False
.RefreshOnFileOpen = False
.Refresh
End With

'Replaces unit names with initials
ReplaceUnit

'Fill column F with time and formula for 12 hour clock
CreateTimeFormula

'Hide original times column and display formatted data after moving the
column
Columns("C:C").Select
Worksheets("Sheet1").Columns("C").Hidden = True
End Sub

Sub CreateTimeFormula()
'
' CreateTimeFormula Macro
' Created to fill column F with time and formula for 12 hour clock
'
Columns("D").Select
Selection.Insert
Range("D3").Select
ActiveCell.FormulaR1C1 = "TIME"
Range("D4").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",MOD(RC[-1],0.5))"
Range("D4").Select
Selection.AutoFill Destination:=Range("D4856"), Type:=xlFillDefault
Worksheets("Sheet1").Range("D4856").NumberFormat = "h:mm"

Range("A4").Select
End Sub