ADO Recordset Error

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • smartchap
    New Member
    • Dec 2007
    • 236

    #16
    Dear Kyosuke
    I hope it solved your queries. If any other query, ask & I will try to help.
    One thing, file vbskfr2.ocx was not in the zip file provided by you and I don't know what is the use of this file in your program.

    Comment

    • Kyosuke18
      New Member
      • May 2009
      • 11

      #17
      hi smartchap,

      I read your message thanks for the help.. regarding about vbskfr2.ocx is a vb skinner.. I change the design of the form instead of using a classic form. I just follow ur instructions on the green or asterisk.. I have problems again regarding flexgrid.. since you've entered the

      RowClicked = Me.MSFlexGrid1. TextMatrix(Me.M SFlexGrid1.RowS el, 4)

      in the MSFlexGrid1_Cli ck event. When I clicked this flexgrid without doing a search the error pop ups that "Subscript out of range".. I try this solution just to avoid the error but when I search it, Example "Because of you", since there are 2 titles but different artist, when I clicked the Because of you by Ne-yo which is in the first row(assuming) it will play.. But when I clicked the Because of you by 98 degrees, only the 1st because of you by Ne-yo is still playing and not from 98 degress.. Here is the code below:

      If Rowclicked = "" Then
      Msgbox "No selected fields", vbCritical + vbOk, "Flexgrid"
      MSFlexgrid1.Ena bled = False
      ElseIf Rowclicked = Me.MSFlexGrid1. TextMatrix(Me.M SFlexGrid1.RowS el, 4) Then
      MSFlexgrid1.Ena bled = True
      End If

      I add another form for adding, deleting and updating a record.. In adding a record, I don't have problems regarding that because it works on a adding.. but I have problem in deleting a record or deleting an entire row.. The code below is deletion of selected row that I added.. Now the problem is when the selected row is deleted in the flexgrid it deletes, but when I open the database name "songs1" in microsoft access the selected row that I deleted didn't.. I've tried using the sql syntax "DELETE * FROM SONGS" in the recordset that I declared and the whole record is deleted.. so I think I should use the WHERE clause next to SONGS, but I can't type the record one by one just to delete, for example "DELETE * FROM SONGS WHERE Title = "xdhsd", Artist = "dhjd", Genre = "Pop", Album = "Popfest", Year = "1954" " what should I do?

      Dim rs As New ADODB.Recordset
      Dim cn As New ADODB.Connectio n
      Dim ab As String
      Dim d As String

      cn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & App.Path & "\Songsdb.mdb;P ersist Security Info=False" '*
      rs.Open "Delete * from Songs1", cn, adOpenDynamic, adLockPessimist ic

      d = MsgBox("Do you want to delete a record?", vbQuestion + vbYesNo, "Delete Record")

      If MSFlexGrid1.Row s > MSFlexGrid1.Fix edRows + 1 Then
      MSFlexGrid1.Rem oveItem (MSFlexGrid1.Ro w)
      Else
      MSFlexGrid1.Row s = MSFlexGrid1.Fix edRows
      End If

      And again, Thank you very much for the help and some guides that you've gave to me everytime I have problems regarding vb programming.. I really appreciate it.. Attach here with is the zip file regarding the program.. I just modify the codes and add some.. I also try my best to find a solution so that my program will work.. Thanks

      Kyosuke18=)
      Attached Files

      Comment

      • smartchap
        New Member
        • Dec 2007
        • 236

        #18
        Dear Kyosuke18
        I thought u must have found the results, but today when I checked my subscriptions came to know ur problems. Sorry for the delay.
        Regarding ur problems of 'Subscript out of range' and not playing second or other selected record, make changes in Click event of MSFlexGrid as follows:
        Code:
        Private Sub MSFlexGrid1_Click() '*
            'RowClicked = Me.MSFlexGrid1.RowSel'*
            'If RowClicked = 0 Then RowClicked = 1'*
            'MsgBox RowClicked'*
            If Me.MSFlexGrid1.Rows < 2 Then 'i.e.if clicked MSF without any entry in MSF then not reqd error "Subscript out of range".
                MsgBox "Please search some data first."
                Exit Sub
            End If
            RowClicked = Me.MSFlexGrid1.TextMatrix(Me.MSFlexGrid1.RowSel, 4) '*
        End Sub
        I am sure ur problems will be solved. Regarding deletion & addition plz give me some time and I will give u solution.

        Comment

        • smartchap
          New Member
          • Dec 2007
          • 236

          #19
          Because when u click on Grid without doing a search, i.e. when there is no filled row, variable RowClicked remains blank and in ur subroutine it is blank so gives error 'Subscript out of range' and also new selected item (i.e. row) is not entered in RowClicked so the errors.

          Comment

          • smartchap
            New Member
            • Dec 2007
            • 236

            #20
            I think for deleting also u must use RowClicked variable and delete in both MSFlexGrid as well as Songs.mdb

            Comment

            • smartchap
              New Member
              • Dec 2007
              • 236

              #21
              Dear Kyosuke
              I analysed ur Form4. In delete subroutine Command2_Click u have used
              rs.Open "Delete * from Songs1", cn, adOpenDynamic, adLockPessimist ic

              I think it will delete all records at a time. Second in If...EndIf loop how will it know which record to delete?
              Please modify the event. remove
              rs.Open "Delete * from Songs1", cn, adOpenDynamic, adLockPessimist ic

              line and also loop
              use a Do..While or for..next loop. Search from first record till last record. As soon as record No is equal to RowClicked (check if +1 is required) delete that record in both MSFlexGrid as well as in Songs1.mdb after confirming from user by displaying details of record to be deleted. I think this will help u.

              Comment

              Working...