Data repeater is not updating table with new values.

[michellecole]

I'm working in VB6 with MS Access 2000.

I have a data repeater control that is displaying properly, but it is not updating the underlying table with new values entered by the user.

I built an ActiveX data repeater control, pubCtl.ctl, and saved it in a project called controlPrj.vbp. In a separate project, Billing_RemoteDeposit.vbp, I added a data repeater control, DataRepeater1, on a form called frmReviewBilling and set the RepeatedControlName to ControlPrj.pubCtl in the properties. I also added an Adodc and named it Adodc2. Adodc2 is the Data Source for DataRepeater1. Adodc2 uses an ODBC, the RecordSource = SELECT * FROM tblBillingWorkTable WHERE CustomerID = '& sSearchCustID &' ORDER BY tblBillingWorkTable.BILL_Level DESC , tblBillingWorkTable.AcctToCharge, tblBillingWorkTable.ServiceCode. The CommandType = adcmdText.

I had a tough time getting the proper records to display in DataRepeater1 initially because of the variable, sSearchCustID, in the recordsource which changes as the user advances through CustomerID records in a different Adodc called adodc1 which lives on the same form.

I'm assuming everything is wired up correctly since DataRepeater1 displays the correct data for each CustomerID record as the user scrolls through adodc1, so... why wouldn't the table be updated when the user updates a volume amount?

I set the RecordSource and DSN in the property pages of adodc2, but because the variable in the sql statement, sSearchCustID, is not known at startup - I set the RecordSource again and refresh the adodc at load and whenever the text box that displays the CustomerID of records from the first adodc changes:

Private Sub txtCustomerID_Change()
UpdateADODC2Properties
End Sub

Private Sub Form_Load()
UpdateADODC2Properties
End Sub

Private Sub UpdateADODC2Properties()
sSearchCustID = RTrim(LTrim(frmReviewBilling.txtCustomerID.Text))
Adodc2.ConnectionString = "DSN=RemoteDepositBilling;UID=sa;PWD= ;DSQ=RemoteDepositBilling"
Adodc2.RecordSource = "SELECT * FROM tblBillingWorkTable WHERE CustomerID = '" & sSearchCustID & "' ORDER BY tblBillingWorkTable.BILL_Level DESC , tblBillingWorkTable.AcctToCharge, tblBillingWorkTable.ServiceCode"
Adodc2.Refresh
End Sub


Here is my code for the pubCtl.ctl:

Private Sub txtVolumeAmt_Change()
PropertyChanged "volume"
End Sub

Public Property Get volume() As Double
volume = txtVolumeAmt
End Property

Public Property Let volume(ByVal newVolumeorCharge As Double)
txtVolumeAmt = newVolumeorCharge
End Property

Public Property Get cid() As String
cid = txtCustID
End Property

Public Property Let cid(ByVal newCustomerID As String)
txtCustID = newCustomerID
End Property

'**********************************************************

Any help with be most appreciated!

Thank you,
Michelle

 

[BobRodes]

<why wouldn't the table be updated when the user updates a volume amount?

I haven't looked over your code in detail, but it sounds like the same bug I ran into. I put some notes to the workaround in the code, which I don't have here. Let me look at the notes and get back to you. In any case, I do believe it's a known bug in the DR control.

HTH

Bob

 

[BobRodes]

Ah. Google, every programmer's friend. Here we go:

http://support.microsoft.com/?kbid=197428

describes the bug and workaround.

If you want a way to expose events of constituent controls of the repeated control in the DR (e. g. you want to interact with the KeyPress event of a text box on your repeated control from the form using the DR control), check faq222-3666.

HTH

Bob

 

[michellecole]

Bob, thank you for your response. I'm off to a class today but will try the workaround first thing in the morning and let you know how it goes.

Michelle

 

[michellecole]

The workaround does work, but it consists of adding a button to the form that the user must click after modifying data on EACH record

Takes away from the essense of the control. Is there any other control that works similarly (displays data in columns that can be updated)?

Thank you for the response - I need to make it a habit to check Microsoft's website before posting - since I always have better luck finding answers here - I start here first!

Michelle

 

[BobRodes]

> it consists of adding a button to the form that the user must click after modifying data on EACH record

I didn't have to do that! Mine works fine. What did you do exactly? I'll go dig up my code and doc if we can't fix it.

Bob

 

[BobRodes]

Ok, I'm home now. Here are the notes that I have, and some code:

'******** 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.

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

You can work with the CurrentRecordChanged event to handle the modification. Using bookmarks with the ADODC control will get the DataRepeater to align with the underlying recordset. If you're not doing any validation, you can try Bookmark = Bookmark (with the ADODC) to sync everything.

HTH

Bob

 

[BobRodes]

Looking at your code again, it's not clear that you have a CurrentRecordChanged event, which you'll need.

Bob

 

[michellecole]

Your "CurrentRecordChanged event" Workaround works beautifully!

Many Thanks!!!
Michelle

 

[BobRodes]

You're welcome. That's actually a requirement rather than a workaround. The workaround is in the use of the ADODC control to mimic the movements in the underlying recordset.

Let me clarify some things for anyone else reading this thread. First, the bug I mentioned happens when you bind a DataRepeater control to an ADO Recordset, not when you bind to a Data Control. The workaround is to bind to a data control and use its move methods rather than the ADO recordset's. I should have looked at Michelle's code more carefully before answering; she doesn't use an ADO Recordset at all.

Second, if you change records, the DataRepeater fires the CurrentRecordChanged event, which you can use to interact with it during record changes.

So, to answer Michelle's post, I should have just said that she was missing the CurrentRecordChanged event, but I got so caught up in my own past experience that I missed it (sorry Michelle). Give someone a hammer and everything looks like a nail.

Bob