[insik]

Hi, i don't know much in vb. I have a problem deleting specific field in the table. Instead of deleting specific field in the table, the entire row that is selected in the flexgrid is being deleted. please give me idea what code should I add to delete my last row in the flexgrid at the same time delete the data from the database. This is the code i have so far.

Private Sub cmdUnload_Click()
    Dim db As Database
    Dim sql As String
   

   
        If MsgBox("Are you sure you want to unload this teacher?", vbYesNo) = vbYes Then

            
             sql = "Delete From ScheduleFile Where Teacher='" & grd.TextMatrix(grd.Row, 7) & "'"
    
            Set db = OpenDatabase(App.Path & "\vbdatabase.mdb")
    
        db.Execute sql
        MsgBox "Teacher has been unloaded.."
        db.Close
      
            
      End If

End Sub

[smohd]

A little confusing here:

Quote

Instead of deleting specific field in the table, the entire row that is selected in the flexgrid is being deleted

Quote

please give me idea what code should I add to delete my last row in the flexgrid at the same time delete the data from the database

You want to delete a row or a cell? In the database, there is no way to delete only one cell data, but if the column accepts null, you can set the value to null or empty using the UPDATE clause.

Also this is not VB.Net, so I move this to VB forum

[insik]

Not the entire row, only one data in the row.

[maj3091]

Then, as smohd said in his post, you need to use an update query to change the value to Null, you can't delete a single field.

Look here for more information on update queries.

[insik]

This is the code i have right now.

Private Sub cmdUnload_Click()
    
        If MsgBox("Do you really want to Unload this Teacher?", vbYesNo) = vbYes Then
        
        rsSCF.Edit
            rsSCF!Teacher = "NULL"
        rsSCF.Update
    
        MsgBox "Successfully Unloaded..", vbInformation
    
        End If

End Sub

the error is no current record

[maj3091]

The code you've posted doesn't tell the full story?

Where does rsSFC get populated? You need to have a record selected to update it using the functionality you have posted there.

Might I suggest you have a read through this tutorial, which might explain things a little better.

[insik]

Thank u maj3091, but the record that i want to be edited is the data that I double clicked in the flexgrid. And update the specific field to be null.

[maj3091]

And as I said in my previous post, you haven't posted all the code related to this issue.

The error message is telling you that there is no record selected.

How do you populate rsSFC?

[insik]

This is my whole code:

Option Explicit
Dim r As Integer
Dim k As Integer
Dim flag As Boolean
Private m_LastSel As Integer ' dont declare this variable in the function or it will not work
 
        ' then we have the function

Private Sub SelectRow(iRow As Integer)
Dim i As Integer
With grd

        ' First we unselect previous Selection (if there is one )

If m_LastSel > 0 Then
     .Row = m_LastSel
     For i = 0 To .Cols - 1
        .Col = i
        .CellBackColor = RGB(255, 255, 255)
        .CellForeColor = RGB(0, 0, 0)
     Next
End If

        ' now select new selection and destroy any multiselection as input is .RowSel Value
        ' and you are setting roW = RowSel here

.Row = iRow
For i = 0 To .Cols - 1
    .Col = i
    .CellBackColor = RGB(0, 129, 255)
    .CellForeColor = RGB(255, 255, 255)
Next
m_LastSel = iRow
End With
End Sub
 
Private Sub cmdUnload_Click()
        
        If MsgBox("Are you sure you want to unload this teacher?", vbYesNo) = vbYes Then
         Set rsSCF = db.OpenRecordset("Select * From ScheduleFile where Teacher='" & grd.RowSel & "'")
         
            rsSCF.Edit
                rsSCF!Teacher = ""
            rsSCF.Update
    
        MsgBox "Successfully Unloaded..", vbInformation
            If (rsSCF!Teacher = "") Then
                
                rsSCF.Edit
                rsSCF!ScheduleTag = 0
                rsSCF.Update
                
                Else
                
                rsSCF.Edit
                rsSCF!ScheduleTag = 1
                rsSCF.Update
                
            End If
        
        End If

End Sub


 
 
 
Private Sub grd_DblClick()
   cmdUnload.Enabled = True
   
                lblSubject.Caption = grd.TextMatrix(grd.Row, 1)
                lblEDP.Caption = grd.TextMatrix(grd.Row, 2)
                lblTime.Caption = grd.TextMatrix(grd.Row, 3)
                lblDays.Caption = grd.TextMatrix(grd.Row, 4)
                lblRoom.Caption = grd.TextMatrix(grd.Row, 5)
                'lblUnits.Caption = grd.TextMatrix(grd.Row, 6)
                lblTeacher.Caption = grd.TextMatrix(grd.Row, 7)
 
        
        
End Sub

Private Sub grd_SelChange()
SelectRow (grd.RowSel)
End Sub




Private Sub txtSection_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 Then
       
         If (txtSection.Text) = "" Then
            MsgBox "Please input Section", vbCritical
            
            Else
           
          If (UCase(txtSection.Text) = rsSCF!Section) Then
        
           
             Set rsSCF = db.OpenRecordset("SELECT * FROM ScheduleFile WHERE Section = '" & txtSection.Text & "'")
                
                Do Until rsSCF.EOF
            
                                
                    grd.TextMatrix(r, 1) = rsSCF!SubjectCode
                    grd.TextMatrix(r, 2) = rsSCF!EDPCode
                    grd.TextMatrix(r, 3) = rsSCF!Time
                    grd.TextMatrix(r, 4) = rsSCF!Days
                    grd.TextMatrix(r, 5) = rsSCF!Room
                    
                    grd.TextMatrix(r, 7) = rsSCF!Teacher
                
                    rsSCF.MoveNext
                
                    r = r + 1
                    grd.Rows = grd.Rows + 1
                Loop
               
            Else
                MsgBox "Section Not found!", vbCritical
                txtSection.Text = ""
                txtSection.SetFocus
           End If
        End If
                
       
    End If
  
End Sub

Public Sub Form_load()
    Call setAllTables
    grd.Rows = 2
    r = 1
    
    
End Sub

and this is in my module:

Option Explicit
Dim r As Integer
Dim k As Integer
Dim flag As Boolean
Private m_LastSel As Integer ' dont declare this variable in the function or it will not work
 
        ' then we have the function

Private Sub SelectRow(iRow As Integer)
Dim i As Integer
With grd

        ' First we unselect previous Selection (if there is one )

If m_LastSel > 0 Then
     .Row = m_LastSel
     For i = 0 To .Cols - 1
        .Col = i
        .CellBackColor = RGB(255, 255, 255)
        .CellForeColor = RGB(0, 0, 0)
     Next
End If

        ' now select new selection and destroy any multiselection as input is .RowSel Value
        ' and you are setting roW = RowSel here

.Row = iRow
For i = 0 To .Cols - 1
    .Col = i
    .CellBackColor = RGB(0, 129, 255)
    .CellForeColor = RGB(255, 255, 255)
Next
m_LastSel = iRow
End With
End Sub
 
Private Sub cmdUnload_Click()
        
        If MsgBox("Are you sure you want to unload this teacher?", vbYesNo) = vbYes Then
         Set rsSCF = db.OpenRecordset("Select * From ScheduleFile where Teacher='" & grd.RowSel & "'")
         
            rsSCF.Edit
                rsSCF!Teacher = ""
            rsSCF.Update
    
        MsgBox "Successfully Unloaded..", vbInformation
            If (rsSCF!Teacher = "") Then
                
                rsSCF.Edit
                rsSCF!ScheduleTag = 0
                rsSCF.Update
                
                Else
                
                rsSCF.Edit
                rsSCF!ScheduleTag = 1
                rsSCF.Update
                
            End If
        
        End If

End Sub


 
 
 
Private Sub grd_DblClick()
   cmdUnload.Enabled = True
   
                lblSubject.Caption = grd.TextMatrix(grd.Row, 1)
                lblEDP.Caption = grd.TextMatrix(grd.Row, 2)
                lblTime.Caption = grd.TextMatrix(grd.Row, 3)
                lblDays.Caption = grd.TextMatrix(grd.Row, 4)
                lblRoom.Caption = grd.TextMatrix(grd.Row, 5)
                'lblUnits.Caption = grd.TextMatrix(grd.Row, 6)
                lblTeacher.Caption = grd.TextMatrix(grd.Row, 7)
 
        
        
End Sub

Private Sub grd_SelChange()
SelectRow (grd.RowSel)
End Sub




Private Sub txtSection_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 Then
       
         If (txtSection.Text) = "" Then
            MsgBox "Please input Section", vbCritical
            
            Else
           
          If (UCase(txtSection.Text) = rsSCF!Section) Then
        
           
             Set rsSCF = db.OpenRecordset("SELECT * FROM ScheduleFile WHERE Section = '" & txtSection.Text & "'")
                
                Do Until rsSCF.EOF
            
                                
                    grd.TextMatrix(r, 1) = rsSCF!SubjectCode
                    grd.TextMatrix(r, 2) = rsSCF!EDPCode
                    grd.TextMatrix(r, 3) = rsSCF!Time
                    grd.TextMatrix(r, 4) = rsSCF!Days
                    grd.TextMatrix(r, 5) = rsSCF!Room
                    
                    grd.TextMatrix(r, 7) = rsSCF!Teacher
                
                    rsSCF.MoveNext
                
                    r = r + 1
                    grd.Rows = grd.Rows + 1
                Loop
               
            Else
                MsgBox "Section Not found!", vbCritical
                txtSection.Text = ""
                txtSection.SetFocus
           End If
        End If
                
       
    End If
  
End Sub

Public Sub Form_load()
    Call setAllTables
    grd.Rows = 2
    r = 1
    
    
End Sub

Sorry. This is my module code.

Public db As Database
Public rsSCF As Recordset
Public rsSUF As Recordset
Public rsTF As Recordset
Public rsTLF As Recordset
Public rsTSF As Recordset



Public Sub setAllTables()
    Set db = OpenDatabase(App.Path & "\vbdatabase.mdb")
    Set rsSCF = db.OpenRecordset("ScheduleFile")
    Set rsSUF = db.OpenRecordset("SubjectFile")
     Set rsTF = db.OpenRecordset("TeacherFile")
     Set rsTLF = db.OpenRecordset("TeachersLoadFile")
      Set rsTSF = db.OpenRecordset("TeacherSpecializationFile")
End Sub

Public Function checkTeacher(code As String) As Boolean
    rsTF.Index = "ndxID"
    rsTF.Seek "=", Trim(code)
        If rsTF.NoMatch Then
            checkTeacher = True
        Else
            checkTeacher = False
        End If
End Function

Public Function checkSubject(Subject As String) As Boolean
    rsSCF.Index = "ndxSubjectCode"
    rsSCF.Seek "=", Trim(Subject)
        If rsSCF.NoMatch Then
            checkSubject = True
        Else
            checkSubject = False
        End If
End Function

[maj3091]

Slightly confused....you have a table called ScheduleFile in the database and you want to clear the Teacher field for a single record in that table, is that correct??

If so....

Looking at the line below:

Set rsSCF = db.OpenRecordset("Select * From ScheduleFile where Teacher='" & grd.RowSel & "'")

I'm guessing this query isn't right as rsSCF isn't being populated?

If you print this query to the immediate window does it look right to you in regards to selecting the row you want to edit?

debug.print "Select * From ScheduleFile where Teacher='" & grd.RowSel & "'")

You could also check the RecordCount property of the recordset.

This post has been edited by maj3091: 01 February 2012 - 09:11 AM