ADO Recordset Navigation ??

[24jedi]

I am new to this, so please bare with me.
I am building a form to use an ADO recordset to populate a form.

[image: http://www.munyak.com/images/bbs_proc.gif]

Questions:

1.When I select the combobox to populate the listbox, the listbox
property lstYTD.rowsource pads the end of the string with ;;;;
Why and can I do something about this ??

ex..1/5/2004,3243,604844.36,63;1/6/2004,4558,1134846.57,68;;;;;;;

2.Within the code I am closing the record set. If the recordset is
closed, how then do I navigate the records for the given query using
the navigation buttons.

If you could either point me to a tut or provide sample code, I would
appreciate it. Thanks

Private Sub cboClaimCode_Click()
'
'
'clear out the listbox control lstYTD contents
lstYTD.RowSource = " "

Dim intClaimCode As Integer
Dim I As Integer
Dim cnnDB As ADODB.Connection
Dim rstDB As ADODB.Recordset
Dim fldDB As ADODB.Field
Dim strSQL As String

Set cnnDB = CurrentProject.Connection
Set rstDB = New ADODB.Recordset

'select strSQL criteria from combobox index
intClaimCode = cboClaimCode
Select Case intClaimCode
Case 10
'get data from YTD_10.dbf query
strSQL = "SELECT YTD10.PROCDATE, Sum(YTD10.TOTCLAIMS) AS
SumOfTOTCLAIMS, Sum(YTD10.TOTCHARGE) AS SumOfTOTCHARGE,
Count(YTD10.DESC) AS CountOfDESC FROM YTD10 GROUP BY YTD10.PROCDATE"
Case 20
'get data from YTD_20.dbf query
strSQL = "SELECT YTD20.PROCDATE, Sum(YTD20.TOTCLAIMS) AS
SumOfTOTCLAIMS, Sum(YTD20.TOTCHARGE) AS SumOfTOTCHARGE,
Count(YTD20.DESC) AS CountOfDESC FROM YTD20 GROUP BY YTD20.PROCDATE"
Case 50
'get data from YTD_50.dbf query
strSQL = "SELECT YTD50.PROCDATE, Sum(YTD50.TOTCLAIMS) AS
SumOfTOTCLAIMS, Sum(YTD50.TOTCHARGE) AS SumOfTOTCHARGE,
Count(YTD50.DESC) AS CountOfDESC FROM YTD50 GROUP BY YTD50.PROCDATE"
Case Else
MsgBox "Claim Type Not Configured", vbCritical
Exit Sub 'error handling
End Select

'Open recordset
With rstDB
..Open Source:=strSQL, _
ActiveConnection:=cnnDB, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic, _
Options:=adCmdTableDirect

'test populate textboxes
rstDB.MoveFirst
txtProcdate1 = rstDB!PROCDATE
txtFileCount1 = rstDB!CountOfDESC
txtClaimCount1 = rstDB!SumOfTOTCLAIMS
txtDollarAmt1 = rstDB!SumOfTOTCHARGE

'populate listbox control lstYTD
I = 0
Do Until rstDB.EOF
lstYTD.AddItem rstDB!PROCDATE & "," &
rstDB!SumOfTOTCLAIMS & "," & _
rstDB!SumOfTOTCHARGE & "," &
rstDB!CountOfDESC, I
rstDB.MoveNext
I = I + 1
Loop

..Close
End With

cnnDB.Close
Set rstDB = Nothing
Set cnnDB = Nothing

End Sub


Private Sub cmdMoveFirst_Click()
'What code would go here to move to first record...etc
'

End Sub


Private Sub cmdMoveLast_Click()

End Sub

Private Sub cmdMoveNext_Click()

End Sub

Private Sub cmdMovePrev_Click()

End Sub

 

[24jedi]

~ bump ~

 

[24jedi]

~ bump ~