BobRodes's Profile User Rating: *****

Reputation: 588 Enlightened
Active Posts:
3,049 (1.34 per day)
19-May 09
Profile Views:
Last Active:
User is offline Jun 26 2015 11:41 PM

Previous Fields

OS Preference:
Favorite Browser:
Favorite Processor:
Who Cares
Favorite Gaming Platform:
Your Car:
Dream Kudos:
Expert In:

Latest Visitors

Icon   BobRodes is a paragon of ignorance.

Posts I've Made

  1. In Topic: How to select a dataset from the datagrid to textbox or combobox

    Posted 26 Jun 2015

    What have you tried so far? Getting the contents of a row from a DataGrid is has to do with looking at row and column properties.
  2. In Topic: I can not access all the tables in my Database

    Posted 26 Jun 2015

    Are all of your tables in the same database (KBOW)?
  3. In Topic: VB6 search option in ComboBox - filter as you type

    Posted 26 Jun 2015

    Cool! Did you get Backspace and Delete keys to work properly as well? Also, what happens if a user clicks in the middle of the text and either backspaces or deletes the current character?
  4. In Topic: Trying to have combobox display sql database table.. need help thanks

    Posted 26 Jun 2015

    Ok. The one thing that you aren't getting in my code is that you can put all three SQL commands in one query, separated by semicolons. Have a closer look at my SQL string. You can get rid of the redundant code if you do it the way I have it.

    Also, you really should not open a recordset just to execute a command; that just means that you don't know how to use the ADO Command object. I suggest that you read up on it! It's much simpler to use for a command like "SET_ANSI_NULLS_ON".
  5. In Topic: How to handle individual control events in datarepeater items

    Posted 26 Jun 2015

    I haven't tried Macey's code, but the fact that you can get at the constituent controls suggests that an approach like his is workable. In VB6, there simply wasn't a way to directly expose events of repeated controls through the DataRepeater control. So, while I'm sure my solution will still work, a solution like this one is probably easier.

    Anyway, here's the code, with notes. The context is a modal dialog box with a DataRepeater control, that has a number of optional dealer cash incentives in a car sales operation. The user can pick and choose between them. You can also ignore the ADO Data Control; I had to use it to work around a bug in the DataRepeater control. But this solution worked like a charm and gave me exactly what I wanted.

    This code sample contains three files: frmDealerCash.frm, ctlDealerCash.ctl, and DealerCashEvents.cls. This code addresses a problem with the DataRepeater control: the control does not allow a consumer (such as a form that has a DataRepeater control placed on it, as in this sample) to directly handle events raised by the repeated control.  So, for example, if you want to run a procedure whenever a textbox's value changes, you can raise the textbox event to the ActiveX control that is being repeated in the DataRepeater control, but the DataRepeater control won't expose the event to its container.  So, the form will never know that the event occurred.
    The solution is to create a class that listens for events raised by the repeated control and raises them.  One of the properties of the repeated control is a pointer to this class, and the DataRepeater control can see this pointer as a property of its RepeatedControl object property.  This sample does this; detailed notes are in the class file at the bottom.
    '**************** frmDealerCash.frm **********************
    (Form setup code deleted, except the DataRepeater control and its RepeatedControlName property.  
     The form also contains an ADO data control called Adodc1 and various command buttons and labels.)
       Begin MSDataRepeaterLib.DataRepeater drDealerCashList 
          BeginProperty RepeatedControlName {21FC0FC0-1E5C-11D1-A327-00AA00688B10} 
             _StreamID       =   -1412567295
             _Version        =   65536
             Name            =   "EdgeControls.ctlDealerCash"
    '******** Use of the ADO Data Control in this form **********
    'The Adodc1 ADO Data Control is necessary as a workaround for a bug in the DataRepeater control.  The DR control fails to
    'update changes to the screen in certain circumstances, when directly bound to a recordset and calling that recordset's
    'Move methods.  The workaround is to bind the ADO data control to the recordset, and bind the DataRepeater to the ADO Data
    'control, and then call the move methods of the ADO data control's Recordset property instead.  Also, using the ADO Data
    'Control causes any changes to the underlying recordset to be automatically updated, which is why we need to copy the
    'rsDlrOptional recordset to an array (rsBackup) when we open this form.  If the user cancels after making changes, we can
    'use the rsBackup array to reset the recordset to the state it was in when the form was opened.
    Option Explicit
    Dim WithEvents doCtlEvents As DealerCashEvents   'See notes to DealerCashEvents (this is the broker class)
    Dim cRecNo As Integer                'Current record number
    Dim cInitBoxValue As String          'Initial Box value, used in case we need to cancel the user's input
    Dim cBoxValue As String              'Current Box value
    Dim cBookMarkFrom As Variant         'Where we started before validating
    Dim cBookMarkTo As Variant           'Where we're going after validating
    Dim rsBackup() As Variant            'Back up the recordset in case the user cancels changes and we need to revert
    Dim cCancelChanges As Boolean        'Holds the current value of the form's CancelChanges property
    'This is where we set the DealerCashEvents pointer to the same object as the one created in the ActiveX control's Initialize event.
    Private Sub drDealerCashList_RepeatedControlLoaded()
    Set doCtlEvents = drDealerCashList.RepeatedControl.Events
    End Sub
    'This is the event handler for the event in the repeated control.
    Private Sub doCtlEvents_CashToUseChanged() 'our custom event
    With Adodc1.Recordset
        If .BOF Or .EOF Then 'Change event can get called by the system in this situation
            Exit Sub
        End If
    End With
    cBoxValue = drDealerCashList.RepeatedControl.CashToUse
    'In other words, cBoxValue = the current value of ctlDealerCash.txtCashToUse.Text.  You can't refer to the property that
    'way, though, since ctlDealerCash only exists in a design time context, not a runtime context.
    End Sub
    Public Property Get CancelChanges() As Boolean
    CancelChanges = cCancelChanges
    End Property
    Private Sub cmdCancel_Click()
    cCancelChanges = True
    Unload Me
    End Sub
    Private Sub cmdClearCash_Click()
    Dim cBookMark As Variant
    With Adodc1.Recordset
        Do Until .BOF
            !reb_cash_to_use = "0.00" 'automatically updates rsDlrOptional
    End With
    End Sub
    Private Sub cmdOk_Click()
    Unload Me
    End Sub
    Private Sub drDealerCashList_CurrentRecordChanged()
    'The DataRepeater control doesn't directly support a Validate event, wherein you can cancel the move from the current
    'record if the field value doesn't meet validation criteria.  So, we have to go back to the record just vacated and attempt
    'to validate it.  If successful, we need to return to the record the user moved to, and if unsuccessful, we need to stay
    'where we are.  The cBookMarkFrom and cBookMarkTo variables store the relevant record locations.  The cInitBoxValue and
    'cBoxValue, used when validating changes to the txtCashToUse TextBox, are initialized here as well.
    With Adodc1.Recordset
        If .BOF Or .EOF Then
            Exit Sub
        End If
        If IsEmpty(cBookMarkFrom) Then  'This will happen the first time around, since this event gets fired when the DR is loaded.
            cBookMarkFrom = .Bookmark   'Store present bookmark, and initialize cInitBoxValue and cBoxValue
            cInitBoxValue = !reb_cash_to_use
            cBoxValue = cInitBoxValue
            Exit Sub
        End If
        cBookMarkTo = .Bookmark                 'Store bookmark of destination record before returning to record just left
        .Bookmark = cBookMarkFrom               'Return to record just left
        If ValidateCashToUse(cBoxValue) Then    'Validate it, and if successful, go back to destination record and initialize
            .Bookmark = cBookMarkTo             'BoxValue variables.
            cBookMarkFrom = cBookMarkTo
            cInitBoxValue = !reb_cash_to_use
            cBoxValue = cInitBoxValue
        End If
    End With
    End Sub
    Private Sub drDealerCashList_KeyDown(KeyCode As Integer, Shift As Integer)
    ' Reprogramming the default Tab key behavior: Tab will move to next record, Shift-Tab will move to previous one.
    If KeyCode = 9 Then  
        With Adodc1.Recordset
            If Shift Mod 2 = 1 Then
            'I. e. Shift key is pressed. Shift argument is a decimal representation of a binary 3 digit number, which in
            'turn represents a flag for Alt, Ctrl, and Shift keys from most to least significant digits.  If least
            'significant binary digit is 1 in binary, shift mod 2 will equal 1, and it won't if it isn't.  Therefore,
            'Shift mod 2 = 1 if Shift has been pressed, and 0 if it hasn't.
                If .BOF Then
                End If
                If .EOF Then
                End If
            End If
        End With
        KeyCode = 0
    End If
    End Sub
    'The TextBox's Validate event is unworkable to use due to the abovementioned bug.  This is
    'a function called in the form unload event that uses the ADO data control to work around it.
    Private Function ValidateCashToUse(ByVal cVal As String) As Boolean
    If cInitBoxValue = cBoxValue Then
        ValidateCashToUse = True
        Exit Function
    End If
    If cBoxValue = "" Then
        drDealerCashList.RepeatedControl.CashToUse = "0.00"
        ValidateCashToUse = True
        Exit Function
    End If
    On Error Resume Next 'Ignore errors from the DataRepeater control, handle legitimate errors inline
    cVal = FormatNumber(cVal, 2)
    If Err.Number <> 0 Then
        MsgBox "Invalid Cash Value"
        ValidateCashToUse = False
    ElseIf CDbl(cVal) > CDbl(Adodc1.Recordset!reb_max_cash) Then
        MsgBox "Cash Value can't be greater than Max Cash Value"
        ValidateCashToUse = False
        With drDealerCashList.RepeatedControl
            .CashToUse = FormatNumber(.CashToUse, 2)
        End With
        ValidateCashToUse = True
    End If
    End Function
    Private Sub Form_Activate()
    End Sub
    Private Sub Form_Unload(Cancel As Integer)
    If cCancelChanges Then
        Exit Sub
    End If
    With Adodc1.Recordset
        If Not ValidateCashToUse(cBoxValue) Then
            .Bookmark = cBookMarkFrom
            Cancel = True
            .Bookmark = .Bookmark
        End If
    End With
    End Sub
    Private Sub Form_Load()
    cCancelChanges = False
    End Sub
    '******************** ctlDealerCash.ctl *******************************
    'This control is the repeated control in the DataRepeater control, and represents a single line item from the bound recordset.
    '(The code to expose events to the DataRepeater control's container is at the bottom.)
    '(Not showing Control setup code)
    Option Explicit
    Dim CtlEvents As DealerCashEvents
    'See notes in the DealerCashEvents class.  This object will broker events between this control and clients of the
    'DataRepeater control which contains it, since events can't be passed directly through the DataRepeater control.
    'Description, MaxCash, and CashToUse are the properties that will be bound in the DataRepeater control to fields in the
    'bound recordset, in this case, the rsDlrOptional recordset created in the main control (ctlGetCarList).
    Public Property Get Description() As String
    Attribute Description.VB_MemberFlags = "4"
    Description = lblDescription.Caption
    End Property
    Public Property Let Description(cVal As String)
    lblDescription.Caption = cVal
    End Property
    Public Property Get MaxCash() As String
    Attribute MaxCash.VB_MemberFlags = "1004"
    MaxCash = lblMaxCash.Caption
    End Property
    Public Property Let MaxCash(cVal As String)
    lblMaxCash.Caption = cVal
    End Property
    Public Property Get CashToUse() As String
    CashToUse = txtCashToUse.Text
    End Property
    Public Property Let CashToUse(cVal As String)
    txtCashToUse.Text = cVal
    End Property
    'Code to expose events to the DataRepeater control's container
    'The Events property returns the instance of DealerCashEvents that we have created here.  (see notes to DealerCashEvents)
    Public Property Get Events() As DealerCashEvents
    Set Events = CtlEvents
    End Property
    'The following two event handlers are the ones that we want to raise to the container of the DataRepeater control (in this
    'case, frmDealerCash) via the DealerCashEvents object.
    Private Sub txtCashToUse_Change()
    PropertyChanged "CashToUse"
    CtlEvents.RaiseDCControlEvent "CashToUseChanged"
    End Sub
    Private Sub txtCashToUse_Validate(Cancel As Boolean)
    PropertyChanged "CashToUse"
    CtlEvents.RaiseDCControlEvent "CashToUseValidate"
    End Sub
    Private Sub Usercontrol_Initialize()
    Set CtlEvents = New DealerCashEvents
    End Sub
    '***************************** DealerCashEvents.cls ***************************************
      MultiUse = -1  'True
      Persistable = 0  'NotPersistable
      DataBindingbehavior = 0  'vbNone
      DataSourcebehavior  = 0  'vbNone
      MTSTransactionMode  = 0  'NotAnMTSObject
    Attribute VB_Name = "DealerCashEvents"
    Attribute VB_GlobalNameSpace = True
    Attribute VB_Creatable = True
    Attribute VB_PredeclaredId = False
    Attribute VB_Exposed = True
    'The intent of the DataRepeater (DR) control is to allow strong customization of the way in which a recordset is displayed.  
    'To use the DR control, the developer first creates a custom ActiveX control which shows a single record from an ADO  
    'Recordset object.  The DR control will then create multiple instances of the ActiveX control and use them to display rows
    'in the Recordset.  The underlying point to all of this is that I wanted to capture and handle the Change and Validate events 
    'in the TextBox in which the user inputs cash values.  This proved to be a bit challenging.  Multiple layers are involved:
    '1.  The TextBox, a constituent control of the custom ActiveX control.
    '2.  The custom ActiveX control, which becomes the RepeatedControl property of the DR control.
    '3.  The DR control itself.
    '4.  The container for the DR control, in this case a Form object.
    'We need to handle the TextBox's Change and Validate events in the Form object.  While there is a mechanism to map these events
    'to the ActiveX control (in 2 above), there is no mechanism to map the ActiveX control's events to the DataRepeater control.  
    'Furthermore, the Form only has access to the DataRepeater control's events, not to those of the ActiveX control.  So, there
    'is no direct way to expose the events of the TextBox to the Form so that they can be handled there.
    'The solution to this problem is to create a broker class as follows (I'll refer to the repeated control as ctlDealerCash and to 
    'the client as frmDealerCash):
    '1. Create a class (in this case, this one, DealerCashEvents) which declares an event to raise each time a
    '   constituent control (of ctlDealerCash) raises an event we want to handle, one event for each constituent control
    '   event.
    '2. The class needs one method, which takes a string argument evaluating to one of the events to raise, and may also
    '   include optional variant arguments which can hold any arguments to constituent control events.  (In this class, we
    '   have the RaiseDCControlEvent method, with 3 arguments, 2 of which are optional.)  This method will check the first
    '   argument, and then raise the appropriate event as declared at the top in the general declarations section, passing
    '   any subsequent arguments as the arguments the constituent control's raised event requires.
    '3. In ctlDealerCash:
    '   a. In the ActiveX control's (in this case, it's called CtlEvents) UserControl_Initialize event, create an instance of 
           the class described in (1) above.
    '   b. Create a property (in this case, it's called Events) that will have a reference to this instance as its value.
    '   c. In the event handler for each of the constituent control events we're exposing:
    '      1) Call the PropertyChanged method.
    '      2) Call the Method of this class that passes the event triggers through, passing as the first argument the name
    '         of the event in this class that corresponds to the event we want to raise, as well as any arguments the event
    '         needs (CtlEvents.RaiseDCControlEvent "EventName", [arg1], [arg2]).
    '4. In frmDealerCash:
    '   a. Declare a variable of the class type in (1) above (we are calling it doCtlEvents), using the WithEvents keyword.  
           (If we omit the WithEvents keyword, we won't be notified of any events from this class.)
    '   b. In the RepeatedControlLoaded event handler for our DataRepeater control, set this variable equal to the Events
    '      property of the ctlDealerCash control.  We now have a reference to a DealerCashEvents object in the Form object,
    '      and a reference to the same DealerCashEvents object in the ActiveX control.  This is the point of the work we 
    '      have done: we can use the event handlers in the ActiveX control to call a method in the broker class that raises
    '      an event that is visible to the form.  In so doing we have created an object that brokers the raising of events 
    '      between constituent controls of the repeated control of the DataRepeater control and the DataRepeater control's 
    '      container.  
    '   c. Write event handlers for the events raised by this class.
    'Once this mechanism is in place, it's easy to expose more events: 
    '1.  Declare another event in this class, 
    '2.  In the repeated ActiveX control, write an event handler which calls the event raising method of this class (in this 
    '    case RaiseDCControlEvent).
    '2.  Add a line to the event raising method which raises the new event.
    '4.  Write an event handler in the client.
    Option Explicit
    Public Event CashToUseChanged()     'Maps to the ctlDealerCash.txtCashToUse_Change event.
    Public Event CashToUseValidate()    'Maps to the ctlDealerCash.txtCashToUse_Validate event.
    Public Sub RaiseDCControlEvent(whichEvent As String, Optional arg1, Optional arg2)
    Select Case whichEvent
        Case "CashToUseChanged"
            RaiseEvent CashToUseChanged
        Case "CashToUseValidate"
            RaiseEvent CashToUseValidate
    End Select
    End Sub

My Information

Member Title:
Lovable Curmudgeon
58 years old
September 15, 1956
Manchester, Tennessee
IT, Music.
Forum Leader:
Full Name:
Bob Rodes
Years Programming:
Programming Languages:
BASIC, VB6, VB.Net, C#, Java, Javascript, CSS, also UML

Contact Information

Click here to e-mail me


Page 1 of 1
  1. Photo

    lucky3 Icon

    31 Dec 2012 - 02:30
    Congratulations for the "Expert of the Year" 1st place award!
  2. Photo

    Elda Icon

    23 Jul 2012 - 19:09
    Congratulations. You're an expert. you deserved it. :)
  3. Photo

    trevster344 Icon

    15 Jun 2012 - 17:53
    I found your post in the thread, that is now locked in the .net forum, to be quite humorous. Just wanted to say I appreciate your work and effort helping people out, because sometimes I don't have the patience myself to sit there spoon feeding people information. Thanks :)
  4. Photo

    15Dhan Icon

    01 May 2012 - 18:15
    Congrats .. IDOL :)
  5. Photo

    haidar976 Icon

    18 Aug 2011 - 23:55
    How can you manage all these languages without be confusing between them? I tried to learn C++ then after few months changed to VB6 I completely forgot every thing about C++, I cant work with two language in the same time
    You are expert man
  6. Photo

    BobRodes Icon

    15 Feb 2011 - 15:02
    Thanks for the recognition. I'm honored.
  7. Photo

    Dogstopper Icon

    14 Feb 2011 - 19:35
    Congratulations on Expert!
Page 1 of 1