Programmatic population of combobox - IDE behaviour different to compiled exe
Hello everyone.
I am new to VB6 programming and have a problem I cannot get to the root of; any help you can give me would be appreciated.
I am running in an XP Pro environment.
I have a form with three comboboxes and various other controls. When the user selects an option from the first combobox, this option is used to refine the available options in the second combobox. Selecting one of these options in the second refines the options in the third, and selecting an option in the third populates the other controls with the required data. This then allows the form to trap data entered by the user.
It is possible for the user to close the system in such a way as to require me to trap the data as selected and entered so far, and offer the user the option to reload to the point that they were at before closing the form in the first place.
The issue is that I can restore the data to the form perfectly in the IDE, but when the code is compiled to an exe, only the first two comboboxes will be populated, even when there is data for all three comboboxes and other controls. The other controls are not populated either.
I have used App.LogEvent throughout, and the log tells me that the compiled code is doing what I think it should be doing, it just isn't apparently doing it!
I have compiled the exe in P-Code, and native code optimized both for fast and small, and this has made no difference. All of the required references are available and are the correct versions.
The code I use to populate all the comboboxes is listed below, with error checking and other stuff omitted.
Sorry for the length of this post. I hope you can help as I am at my wit's end, and I am sure it's obvious, but right now, I can't see the wood for the trees.
Public Function SetComboBoxSelectedItem(ByVal ComboBox As Control, _
ByVal StringToMatch As String) As Boolean
Dim Count As Integer
With ComboBox
For Count = 0 To (.ListCount - 1)
If StringToMatch = .List(Count) Then
.ListIndex = Count
SetComboBoxSelectedItem = True
Exit Function
Else
End If
Next Count
End With
SetComboBoxSelectedItem = False
End Function
# 1 Re: Programmatic population of combobox - IDE behaviour different to compiled exe
you published only the code you use to select an item in a combo box list, but not the one that populates the list itself.
Keep in mind that events in the IDE CAN be sent/received differently than in the compiled version.
Add log messages to verify that 1) all the list are correctly populated and 3) when an event is received, the list is *effectively* populated. I saw situations where the list was still empty after all the AddItem calls, and I fixed the problem putting a DoEvents after populating the list. Sometimes things are not as they seems... and this is a hard problem.
Marco
mstraf at 2007-11-11 17:25:53 >
# 2 Re: Programmatic population of combobox - IDE behaviour different to compiled exe
Marco,
Thanks for your reply.
I have tried the DoEvents route as well; sorry, I should have said so. That didn't change things, unfortunately. I've also tried the Refresh method of each control after the implied population of the list, combined with a Refresh of the form, and a wait loop, and all combinations of the above that I can think of. I have even locked and unlocked the screen update using
Public Declare Function LockWindowUpdate Lib "user32" _
(ByVal hwndLock As Long) As Long
I'm absolutely stumped.
The log messages (and I've put one in every other line) imply that the lists have been populated, but they haven't.
As per your comments, I've added the two routines I've written to populate the lists of the dropdowns.
I've stripped out the LogEvent lines and other extraneous code. As you can see, I'm trying to emulate SQL Group By functionality, as I cannot guarantee MSDE on the user's machines, nor can I guarantee a link to the server. I therefore call (via the client code) a stored procedure from the SQL Server instance where the data resides, download a copy of the relevant table to the user's local hard drive, and use the code below to sort through that downloaded data.
The function GetDataFromLocalDisconnectedRecordset is called from each of the dropdowns, and has been written to be as generic as I am capable of programming.
Thanks for your help so far.
Simon.
Private Sub Populate_Dropdown2_Using_Data_Selected_In_Dropdown1()
On Error GoTo ErrorHandler
Dim Control1 As Control
Dim Control2 As Control
Dim Control3 As Control
Dim Cycle As Long
Dim ReturnedArray() As String
With Me
Set Control3 = .DropDown3
Control3.Clear
Set Control3 = Nothing
Set Control2 = .DropDown2
Control2.Clear
Set Control1 = .DropDown1
ReturnedArray = GetDataFromLocalDisconnectedRecordset(LocalDisconnectedRecordsetLocation, _
"Customer", _
.DropDown1, _
"Jobname")
For Cycle = 0 To UBound(ReturnedArray) - 1
Control2.List(Cycle) = ReturnedArray(Cycle)
Next Cycle
Set Control2 = Nothing
Set Control1 = Nothing
End With
Exit Sub
ErrorHandler:
Set Control3 = Nothing
Set Control2 = Nothing
Set Control1 = Nothing
'log the error via another function
End Sub
Public Function GetDataFromLocalDisconnectedRecordset(ByVal LocalRecordset As String, _
ByVal Control1FieldName As String, _
Optional ByVal FirstFieldValueToMatch As String, _
Optional ByVal Control2FieldName As String, _
Optional ByVal SecondFieldValueToMatch As String, _
Optional ByVal Control3FieldName As String, _
Optional ByVal ThirdFieldValueToMatch As String, _
Optional ByVal Control4FieldName As String, _
Optional ByVal FourthFieldValueToMatch As String) _
As String()
On Error GoTo ErrorHandler
Dim ExtractedData As ADODB.Recordset
Dim ArrayToReturn() As String
Dim Count As Long
Dim Distinct As String
Dim Filter As String
Dim Missing As Integer
Dim FieldToGroup As String
If LenB(FirstFieldValueToMatch) = 0 Then
Missing = Missing + 1 'firstfield is missing
Else
End If
If LenB(SecondFieldValueToMatch) = 0 Then
Missing = Missing + 2 'firstfield may be there
Else
End If
If LenB(ThirdFieldValueToMatch) = 0 Then
Missing = Missing + 4 'firstfield and secondfield may be there
Else
End If
If LenB(FourthFieldValueToMatch) = 0 Then
Missing = Missing + 8 ' first three fields may be there
Else
End If
Select Case Missing
Case 15
Filter = vbNullString
FieldToGroup = Control1FieldName
Case 14
Filter = Control1FieldName & " = '" & FirstFieldValueToMatch & "'"
FieldToGroup = Control2FieldName
Case 12
Filter = Control1FieldName & " = '" & FirstFieldValueToMatch & _
"' AND " & Control2FieldName & " = '" & SecondFieldValueToMatch & "'"
FieldToGroup = Control3FieldName
Case 8
Filter = Control1FieldName & " = '" & FirstFieldValueToMatch & _
"' AND " & Control2FieldName & " = '" & SecondFieldValueToMatch & _
"' AND " & Control3FieldName & " = '" & ThirdFieldValueToMatch & "'"
FieldToGroup = Control4FieldName
Case 0
'log the error via another function
Exit Function
Case Else
'log the error via another function
Exit Function
End Select
Set ExtractedData = New ADODB.Recordset
ReDim ArrayToReturn(0)
ExtractedData.Open LocalRecordset
'apply filter to recordset
ExtractedData.Filter = Filter
'emulate SQL group by on last available field from parameters
With ExtractedData
If Not .EOF Then
.MoveFirst
.Sort = FieldToGroup
Distinct = .Fields(FieldToGroup)
ArrayToReturn(Count) = Distinct
Count = Count + 1
ReDim Preserve ArrayToReturn(Count)
.MoveNext
Do Until .EOF
Distinct = .Fields(FieldToGroup)
If Array(Count - 1) <> Distinct Then
Count = Count + 1
ReDim Preserve ArrayToReturn(Count)
ArrayToReturn(Count - 1) = Distinct
Else
End If
.MoveNext
Loop
.Close
Else
'log the error via another function
ReDim Preserve ArrayToReturn(Count)
ArrayToReturn(Count) = "No data!"
End If
End With
Set ExtractedData = Nothing
GetDataFromLocalDisconnectedRecordset = ArrayToReturn
Exit Function
ErrorHandler:
Set ExtractedData = Nothing
'log the error via another function
End Function
# 3 Re: Programmatic population of combobox - IDE behaviour different to compiled exe
In this line:
ReturnedArray = GetDataFromLocalDisconnectedRecordset(LocalDisconnectedRecordsetLocation, _
"Customer", _
.DropDown1, _
"Jobname")
you are assuming that DropDown1.Text (the default value) has indeed some value in it, but that is not necessary true if you just loaded the combo. Did you put a LogEvent inside GetDataFromLocalDisconnectedRecordset method, to print the values of the various parameters?
Marco
mstraf at 2007-11-11 17:27:54 >
# 4 Re: Programmatic population of combobox - IDE behaviour different to compiled exe
Marco,
There is indeed a value in the default .Text parameter.
I did LogEvent all called procedures and all parameters passed to GetDataFromLocalDisconnectedRecordset were populated as I expected them to be.
I use GetDataFromLocalDisconnectedRecordset to first populate the dropdown, and then SetComboBoxSelectedItem to select the item from the list of available data.
Simon.
# 5 Re: Programmatic population of combobox - IDE behaviour different to compiled exe
maybe is better if you post the code with all the LogEvent calls, otherwise we get old on this :)
BTW do you know that you can include the code using the code and /code tags? For examples see here ( http://forums.dev-archive.com/misc.php?do=bbcode)
mstraf at 2007-11-11 17:30:05 >
# 6 Re: Programmatic population of combobox - IDE behaviour different to compiled exe
Marco,
Apologies for the delay. Thanks for the info about the code tag, I didn't know about those tags. :)
Not many events listed below, but the code is called iteratively for the controls, I just cut out a lot of that as the code would be immense, otherwise.
I hope I've included what you want.
Sub Main()
App.StartLogging "C:\Log.txt", 2
End Sub
Private Sub Populate_Dropdown2_Using_Data_Selected_In_Dropdown1()
On Error GoTo ErrorHandler
Dim Control1 As Control
Dim Control2 As Control
Dim Control3 As Control
Dim Cycle As Long
Dim ReturnedArray() As String
With Me
Set Control3 = .DropDown3
Control3.Clear
Set Control3 = Nothing
Set Control2 = .DropDown2
Control2.Clear
Set Control1 = .DropDown1
App.LogEvent .DropDown1, 4
ReturnedArray = GetDataFromLocalDisconnectedRecordset(LocalDisconnectedRecordsetLocation, _
"Customer", _
.DropDown1, _
"Jobname")
For Cycle = 0 To UBound(ReturnedArray) - 1
Control2.List(Cycle) = ReturnedArray(Cycle)
Next Cycle
Set Control2 = Nothing
Set Control1 = Nothing
End With
Exit Sub
ErrorHandler:
Set Control3 = Nothing
Set Control2 = Nothing
Set Control1 = Nothing
'log the error via another function
End Sub
Public Function GetDataFromLocalDisconnectedRecordset(ByVal LocalRecordset As String, _
ByVal Control1FieldName As String, _
Optional ByVal FirstFieldValueToMatch As String, _
Optional ByVal Control2FieldName As String, _
Optional ByVal SecondFieldValueToMatch As String, _
Optional ByVal Control3FieldName As String, _
Optional ByVal ThirdFieldValueToMatch As String, _
Optional ByVal Control4FieldName As String, _
Optional ByVal FourthFieldValueToMatch As String) _
As String()
On Error GoTo ErrorHandler
Dim ExtractedData As ADODB.Recordset
Dim ArrayToReturn() As String
Dim Count As Long
Dim Distinct As String
Dim Filter As String
Dim Missing As Integer
Dim FieldToGroup As String
If LenB(FirstFieldValueToMatch) = 0 Then
Missing = Missing + 1 'firstfield is missing
Else
End If
App.LogEvent Missing, 4
If LenB(SecondFieldValueToMatch) = 0 Then
Missing = Missing + 2 'firstfield may be there
Else
End If
App.LogEvent Missing, 4
If LenB(ThirdFieldValueToMatch) = 0 Then
Missing = Missing + 4 'firstfield and secondfield may be there
Else
End If
App.LogEvent Missing, 4
If LenB(FourthFieldValueToMatch) = 0 Then
Missing = Missing + 8 ' first three fields may be there
Else
End If
App.LogEvent Missing, 4
Select Case Missing
Case 15
Filter = vbNullString
FieldToGroup = Control1FieldName
Case 14
Filter = Control1FieldName & " = '" & FirstFieldValueToMatch & "'"
FieldToGroup = Control2FieldName
Case 12
Filter = Control1FieldName & " = '" & FirstFieldValueToMatch & _
"' AND " & Control2FieldName & " = '" & SecondFieldValueToMatch & "'"
FieldToGroup = Control3FieldName
Case 8
Filter = Control1FieldName & " = '" & FirstFieldValueToMatch & _
"' AND " & Control2FieldName & " = '" & SecondFieldValueToMatch & _
"' AND " & Control3FieldName & " = '" & ThirdFieldValueToMatch & "'"
FieldToGroup = Control4FieldName
Case 0
'log the error via another function
Exit Function
Case Else
'log the error via another function
Exit Function
End Select
App.LogEvent Filter, 4
App.LogEvent FieldToGroup, 4
Set ExtractedData = New ADODB.Recordset
ReDim ArrayToReturn(0)
ExtractedData.Open LocalRecordset
'apply filter to recordset
ExtractedData.Filter = Filter
'emulate SQL group by on last available field from parameters
With ExtractedData
If Not .EOF Then
.MoveFirst
.Sort = FieldToGroup
Distinct = .Fields(FieldToGroup)
ArrayToReturn(Count) = Distinct
Count = Count + 1
ReDim Preserve ArrayToReturn(Count)
.MoveNext
Do Until .EOF
Distinct = .Fields(FieldToGroup)
If Array(Count - 1) <> Distinct Then
Count = Count + 1
ReDim Preserve ArrayToReturn(Count)
ArrayToReturn(Count - 1) = Distinct
Else
End If
.MoveNext
Loop
.Close
Else
'log the error via another function
ReDim Preserve ArrayToReturn(Count)
ArrayToReturn(Count) = "No data!"
End If
End With
Set ExtractedData = Nothing
GetDataFromLocalDisconnectedRecordset = ArrayToReturn
Exit Function
ErrorHandler:
Set ExtractedData = Nothing
'log the error via another function
End Function
# 7 Re: Programmatic population of combobox - IDE behaviour different to compiled exe
Right, after 12,000 App.LogEvent lines and 12,000 Debug.Print statements, I have an explanation that satisfies me.
It appears, and I emphasize that word, that the IDE and the compiled app handle the processing of events slightly differently.
The first dropdown is also the first in the form's tab order. When I execute the code in the IDE, the Enabled property of the dropdown is set to false after population of the control, with the code proceeding from there. It appears that when the code is finished, and control is passing back to the user, the system checks that the dropdown is not enabled and ends the code process at that point.
However, when the code is compiled, it appears that the GotFocus and LostFocus events of the dropdown are triggered BEFORE the Enabled status is checked. These events execute code of their own, some of which (for various reasons - possibly poor programming, I can't say) emulates what I am trying to do in the code below.
I say that this 'appears' to be the case, as it is the only explanation I have that can match the facts. What I have done as a workround is to add a very small textbox control to the form, hidden it behind the first dropdown using Format | Order | Send to Back, and set it to be the first control in the tab order. This means that, in the compiled version, the dropdown does not receive the focus at what I consider to be an inappropriate time, and so the code of the Focus events does not get triggered, and my code delivers the result I expect.
I hope the above may help someone else in a similar situation, without the expenditure of time that I have had to waste on this.
Simon.
