Breakpoint in VBA code

[wchelly]

This question is based on a thread from the query forum which you can find here
http://www.access-programmers.co.uk/forums/showthread.php?t=233742

I have set a breakpoint in my code but I clearly do not know how to use this tool. When I debug, it does nothing that means anything to me. I have tried to use a print function to determine where the code is going bad but nothing seems to happen. The print function I inserted into my code is

Debug.Print "Starting Code Section 1"

Either I do not know how to do this, or the code doesn't function at all. I tried to insert it in several different areas and it does nothing. I know a little VBA, probably just enough to get myself into trouble so I need some direction on how to figure out how to get this code working.

The code I am using is from allenbrowne.com....it's his ConcatRelated function. I can post it if needed.

 

[boblarson]

When you set a breakpoint, the code will stop executing where you have it set when you run the procedure as if you would normally. So, in your case it would be the thing to open the query which you are using that function in Then you click F8 to step through each step. You can hover your mouse over variables and form references to get the current values, or you can use the Watch window (but I find that a little more of a pain to use).

 

[wchelly]

Thank you for bearing with me...
I have inserted a breakpoint, removed the Debug pring statement and am now trying to press F8 to step through each step. Nothing seems to happen when I click F8. I would expect for it to highlight each line one by one, but it's not doing anything.

When you set a breakpoint, the code will stop executing where you have it set when you run the procedure as if you would normally. So, in your case it would be the thing to open the query which you are using that function in Then you click F8 to step through each step. You can hover your mouse over variables and form references to get the current values, or you can use the Watch window (but I find that a little more of a pain to use).

 

[mdlueck]

I have inserted a breakpoint, removed the Debug pring statement and am now trying to press F8 to step through each step. Nothing seems to happen when I click F8. I would expect for it to highlight each line one by one, but it's not doing anything.

And you did cause the code that you have the break point within to be actually run via normal behavior? Break points stop the execution and allow for stepped execution when said breakpoints is encountered during normal program execution.

 

[wchelly]

Your response led me to believe that there was something really obvious that I wasn't understanding soooo....

I set the breakpoint....then I ran the query (which I hadn't done before)

It tripped the debugger and highlighted the line where I set the breakpoint. After that I was able to step through the code. However It is unclear what to do from here.

PASTED BELOW IS THE CODE FOLLOWING THE BREAKPOINT...It steps through everything line by line following the breakpoint (breakpoint denoted by ** below) THE ONCE IT REACHES "End Function" IT RETURNS TO THE BREAKPOINT. IS THIS WHAT IS SUPPOSE TO HAPPEN?

Public Function ConcatRelated(strField As String, _
strTable As String, _
Optional strWhere As String, _
Optional strOrderBy As String, _
Optional strSeparator = ", ") As Variant
On Error GoTo Err_Handler
'Purpose: Generate a concatenated string of related records.
'Return: String variant, or Null if no matches.
'Arguments: strField = name of field to get results from and concatenate.
' strTable = name of a table or query.
' strWhere = WHERE clause to choose the right values.
' strOrderBy = ORDER BY clause, for sorting the values.
' strSeparator = characters to use between the concatenated values.
'Notes: 1. Use square brackets around field/table names with spaces or odd characters.
' 2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
' 3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
' 4. Returning more than 255 characters to a recordset triggers this Access bug:
' http://allenbrowne.com/bug-16.html
Dim rs As DAO.Recordset 'Related records
Dim rsMV As DAO.Recordset 'Multi-valued field recordset
Dim strSql As String 'SQL statement
Dim strOut As String 'Output string to concatenate to.
Dim lngLen As Long 'Length of string.
Dim bIsMultiValue As Boolean 'Flag if strField is a multi-valued field.

'Initialize to Null
** ConcatRelated = Null

STEPS THROUGH THE FOLLOWING CODE
'Build SQL string, and get the records.
strSql = "SELECT " & strField & " FROM " & strTable
If strWhere <> vbNullString Then
strSql = strSql & " WHERE " & strWhere
End If
If strOrderBy <> vbNullString Then
strSql = strSql & " ORDER BY " & strOrderBy
End If
Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
'Determine if the requested field is multi-valued (Type is above 100.)
bIsMultiValue = (rs(0).Type > 100)

'Loop through the matching records
Do While Not rs.EOF
If bIsMultiValue Then
'For multi-valued field, loop through the values
Set rsMV = rs(0).Value
Do While Not rsMV.EOF
If Not IsNull(rsMV(0)) Then
strOut = strOut & rsMV(0) & strSeparator
End If
rsMV.MoveNext
Loop
Set rsMV = Nothing
ElseIf Not IsNull(rs(0)) Then
strOut = strOut & rs(0) & strSeparator
End If
rs.MoveNext
Loop
rs.Close

'Return the string without the trailing separator.
lngLen = Len(strOut) - Len(strSeparator)
If lngLen > 0 Then
ConcatRelated = Left(strOut, lngLen)
End If
Exit_Handler:
'Clean up
Set rsMV = Nothing
Set rs = Nothing
Exit Function

HERE IT RETURNS TO THE ORIGINAL BREAKPOINT

And you did cause the code that you have the break point within to be actually run via normal behavior? Break points stop the execution and allow for stepped execution when said breakpoints is encountered during normal program execution.

 

[mdlueck]

After that I was able to step through the code.

Yeaaa, progress!

However It is unclear what to do from here.

What do you think you want to do in stepped execution / debug mode?

The Immediate Windows (Ctrl-G) accepts interactive lines of code and executes them "immediately". You may inspected variables (Debug.Print), you may change variables, etc...

You may add variables / objects to the Watches window and get real-time status of the contents of those variables.

Depends on what you want to do, really.

 

[wchelly]

Depends on what you want to do, really.

Well the end result that I need is this....

The code needs to do what it says it does and exclude null values AND zero string values so that the comma is ONLY there IF a value exists.

How to get there is not apparent...

But my first thought is that I need to put something in the code...Like a debug.print, so that I can tell if the code is functioning.

 

[wchelly]

I put in the breakpoint and Debug.Print Statements, one before and one after the Debug. There is no print statement that I see. Of course i don't really know where to look for it. I'm expecting it just to pop up somewhere, but I don't see it.

Below is only part of the code....the * is where the breakpoint is placed. I need some direction on "how to" do this.

Dim rs As DAO.Recordset 'Related records
Dim rsMV As DAO.Recordset 'Multi-valued field recordset
Dim strSql As String 'SQL statement
Dim strOut As String 'Output string to concatenate to.
Dim lngLen As Long 'Length of string.
Dim bIsMultiValue As Boolean 'Flag if strField is a multi-valued field.
Debug.Print "Starting Code Section 1"
'Initialize to Null
* ConcatRelated = Null
Debug.Print "Starting Code Section 1"
'Build SQL string, and get the records.
strSql = "SELECT " & strField & " FROM " & strTable
If strWhere <> vbNullString Then
strSql = strSql & " WHERE " & strWhere
End If

 

[pbaldy]

Here's a visual on the Debug.Print output:

http://www.baldyweb.com/ImmediateWindow.htm

 

[jdraw]

Further to Paul's example, I found a free video on youtube.
The debuging part starts just before the 17:00 minute mark.

There is some sales and marketing at the start, but the video is free.

http://www.youtube.com/watch?v=hCyY7NE1Kp4

Please post back your comments.

 

[wchelly]

Thanks! I was able to watch the video from my phone, and was able to gleen some good ideas from it to help me. I will watch it again when I get home, and view Paul's link also (I can't access them here).

 

[jdraw]

Glad we could help.
You may find more on Youtube --- search for Ms Access debugging...
good luck.

 

[wchelly]

When running the following code, it never prints Bug9-12, even though it is processing multiple values. However I have no idea why...

Do While Not rs.EOF
Debug.Print "Bug8"
If bIsMultiValue Then
'For multi-valued field, loop through the values
Set rsMV = rs(0).Value
Debug.Print "Bug9"
Do While Not rsMV.EOF
Debug.Print "Bug10"
If Not IsNull(rsMV(0)) Then
strOut = strOut & rsMV(0) & strSeparator
Debug.Print "Bug11"
End If
rsMV.MoveNext
Debug.Print "Bug12"
Loop
Set rsMV = Nothing
ElseIf Len(rs(0) & vbNullString) > 0 Then
strOut = strOut & rs(0) & strSeparator
Debug.Print "Bug13"
End If
rs.MoveNext
Debug.Print "Bug14"

 

[ChrisO]

It would appear that:-
bIsMultiValue
is False even though you think it is True.

Try a debug print of that before the line:-
If bIsMultiValue Then

Chris.

 

[wchelly]

Yes----It prints "Bug8" which is right before the line-
If bIsMultValue Then

It would appear that:-
bIsMultiValue
is False even though you think it is True.

Try a debug print of that before the line:-
If bIsMultiValue Then

Chris.

 

[boblarson]

He meant

Debug.Print bIsMultiValue
If bIsMultiValue Then

So did it print out the word "True"

 

[wchelly]

It appears that bIsMultiValue is the result of a determination of weather or not the requested field is multi-valued. I don't know how this is determined..."Type is above 100". Does that mean that it has at least 100 characters (even if they are blank)?

Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
'Determine if the requested field is multi-valued (Type is above 100.)
bIsMultiValue = (rs(0).Type > 100)
Debug.Print "Bug7"
'Loop through the matching records
Do While Not rs.EOF
Debug.Print "Bug8"
If bIsMultiValue Then
'For multi-valued field, loop through the values

 

[wchelly]

OHHH....
No it printed the word "False"

 

[boblarson]

Okay, create the SQL like this (this is a sample so you will need to put in your own field names - replace the red parts with your field and table names)

SELECT Table1.MyID, Count(Table1.MyMVField.Value) AS CountOfMyMVField_Value
FROM Table1
GROUP BY Table1.MyID;

And where CountOfMyMVField_Value is greater than 1 it has more than one selection.

 

[wchelly]

Below is how my sql would look. I have Four fields that are being concatinated, but the four fields do not always have the same count. The correct counts seem to populate in the query.


SELECT Material_Description.ShipperID,
Count(Material_Description.Material.Value) AS CountOFMATMVFIELD_Value
Count(Material_Description.Package.Value) AS CountOFPKGMVFIELD_Value
Count(Material_Description.Equipm.Value) AS CountOFEQUMVFIELD_Value
Count(Material_Description.ID.Value) AS CountOFIDMVFIELD_Value
FROM Material_Description
GROUP BY Material_Description.ShipperID;

Okay, create the SQL like this (this is a sample so you will need to put in your own field names - replace the red parts with your field and table names)

SELECT Table1.MyID, Count(Table1.MyMVField.Value) AS CountOfMyMVField_Value
FROM Table1
GROUP BY Table1.MyID;

And where CountOfMyMVField_Value is greater than 1 it has more than one selection.