Stuck Pls - recordset

[shuj66]

I have a form that displays data from a SQL Server through adodb.

Users can navigate through the recordsets by clicking on commands button - First,Next,Prev,Last or they can double click on an item in List1(displays ID,Status) and then get the respective information.

The command buttons work fine. The Listbox_dblClick seems to be displaying the right information when I double Click it but somehow it seems to go back to the first record each time so I am not able to use it and update records as it will update the first record all the time. I don't have this problem when I go to the specific record using the command buttons.

Before updating or deleting I have a message box that popup with the TicketID and CustomerID. With the commad button I get the current one but when I double click an item in the list1, it displays the right info but when I go to do an update or delete it shows the first ticketid and cutomerid all the time.

Also, when navigating through the records, List2 grabs data from another table and displays certain info.

When I don't include the Function Main in the List1_DblClick() it seem to show the right record but as soon as i include it there it displays the right info. but when I try to update it shows the first one. I need it there to display the items in List2 otherwise nothing is displayed. Can someone help me figure out a way to do what I want to do.


Here is what my code looks like:

Private Sub List1_DblClick()
rs.MoveFirst

For x = 0 To List1.ListIndex - 1
rs.MoveNext
Next

Fill_Fields
Label21.Caption = rs.AbsolutePosition & " of " & rs.RecordCount

Call Main
Call Fill_HistoryList

End Sub

'Connect to the database and open the recordset.
Set Conn1 = New ADODB.Connection
Set rs = New ADODB.Recordset
Set conn2 = New ADODB.Connection
Set rsHistory = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rsHistory.CursorType = adOpenKeyset
rsHistory.LockType = adLockOptimistic
rsHistory.CursorLocation = adUseClient
Dim DBPath As String

strCnn = "Provider=SQLOLEDB.1;Password=;Persist
Security Info=True;User ID=admin;Initial
Catalog=Monitor;Data Source=SQL"
strCnn2 ="Provider=SQLOLEDB.1;Password=;Persist
Security Info=True;User ID=admin;Initial
Catalog=Monitor;Data Source=SQL"

Conn1.Open strCnn
rs.Open "SELECT * FROM Ticket WHERE CustID = '" &
form1.txtCustId.Text & "'", Conn1, , , adCmdText
strSQL = "SELECT * FROM Ticket WHERE custID = '" &
form1.txtCustId.Text & "'"

conn2.Open strCnn2
rsHistory.Open "SELECT * FROM ticketHistory WHERE ticketid
= '" & Val(form2.txtFields(0).Text) & "'", conn2, , ,
adCmdText
StrSQLHistory = "SELECT * FROM ticketHistory WHERE ticketid
= '" & Val(form2.txtFields(0).Text) & "'"

End Sub


Function Fill_Fields()
'Fill the textboxes with the values from the database.
'--------------------------------------------------------

If Not (rs.BOF = True Or rs.EOF = True) Then
txtFields(0).Text = rs!TicketID
txtFields(1).Text = rs!CustID
Me.Caption = "Trouble Ticket" & " - " & txtFields(1).Text & " " &
txtFields(0).Text

'Checks if we are at the first or last record.
cboTicket.Text = rs.Fields("TicketID") 'as above

If rs!TicketStatus <> "" Then
txtFields(2).Text = rs.Fields("TicketStatus") 'txtFields(2) =
TicketStatus and display that data
Else
txtFields(2).Text = ""
End If

If rs!PostedBy <> "" Then
txtFields(3).Text = rs.Fields("PostedBy") 'txtFields(3) =
PostedBy and display that data
Else
txtFields(3).Text = ""
End If

If rs!AssignedTo <> "" Then
txtFields(4).Text = rs.Fields("AssignedTo") 'txtFields(4) =
AssignedTo and display that data
Else
txtFields(4).Text = ""
End If

End Function

Sub Fill_HistoryList()
with rshistory

List2.Clear

Do While Not rsHistory.EOF
formatDate = rsHistory!HistoryDate
FormatTime = rsHistory!HistoryTime
formatDate = (Format(formatDate, "Medium Date"))
FormatTime = (Format(FormatTime, "Medium Time"))

List2.AddItem rsHistory!UserName & " " & formatDate
& " " & FormatTime & " " & rsHistory!TicketID & " " &
rsHistory!History
rsHistory.MoveNext
Loop

End With

End Sub

[Ecniv]

On the double click store the ID of the selected item into a variable held on the forms code. Then when you want to delete or update you use this variable. When you reset/clear the form, reset the variable to -1 then you can do checks for whether the item has been selected or not.


Vince

[shuj66]

Could you be a little bit more specific. A coded example would help.
Thanx.

[shuj66]

I think I found the answer.

Instead of calling the whole function, all I had to do was:

rsHistory.Close
rsHistory.Open "SELECT * FROM ticketHistory WHERE
ticketid = '" & Val(txtFields(0).Text) & "'", conn2, , ,
adCmdText
Fill_HistoryList

Thanks.