INSERT INTO Lab2(Class,equipment,No,[Select]) VALUES ('M3bK1 ','Chair ','20 ','þ')

INSERT INTO Lab2 (Class,equipment,[No],[Select])

Public try1 As String

try1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\try1.mdb"

Public TotalRec As Long

Private Sub cmdSave_Click()
    Rem ------------------------------------------------------------
    'Call Module1.conn      ' connection already open in Form_Load()!!!
    'Call Module1.Recordset ' don't need a new recordset to save data
    Rem ------------------------------------------------------------
    
    Dim lngRecordsAffected As Long
    Dim sSelect As String

    For i = 0 To MSFlexGrid1.Row - 1
        sql = "INSERT INTO Lab2 "
        sql = sql & "(Class,Equipment,[No],[Select])"
        sql = sql & " VALUES "
        sql = sql & "('"
        sql = sql & Combo1.Text & "','"
        sql = sql & MSFlexGrid1.TextMatrix(i + 1, 1) & "','"
        sql = sql & MSFlexGrid1.TextMatrix(i + 1, 2) & "',"
        'sql = sql & MSFlexGrid1.TextMatrix(i + 1, 2) & " ','"  '<- wrong
        'sql = sql & MSFlexGrid1.TextMatrix(i + 1, 3) & "')"    '<- wrong
        
        Rem You need to translate value for Yes/No field
        sSelect = IIf(MSFlexGrid1.TextMatrix(i + 1, 3) = "þ", "-1", "0")
        
        sql = sql & sSelect & ")"
        
        Debug.Print sql     '<- IMPORTANT to see what sql string contains!
        'Rem I can use 'Stop' temporally to pause execution and check sql statement
        'rem in Immediate window. Remove or comment before compile
        Stop
        
        Rem Save each row
        cn.Execute sql, lngRecordsAffected

        Debug.Print "row saved: " & lngRecordsAffected
    Next i
        
    ' save data
    
    Rem ------------------------------------------------------------
    Rem Wrong! Don't need a recordset
    Rem ------------------------------------------------------------
    'If rs.State = adStateOpen Then rs.Close
    'rs.Open sql, cn, adOpenDynamic, adLockOptimistic
    'Set rs = Nothing

End Sub

Option Explicit

Private Const FLEXYES As Integer = 254
Private Const FLEXNO As Integer = 168

'Dim TR As Long, indx As Long

Private Sub cmdSave_Click()
  Dim sSQL As String
  Dim sSelect As String
  Dim lRow As Long
  Dim adoConn As ADODB.Connection
  
  Set adoConn = OpenConnection
  
  ' Because you used some reserved words as column names in your
  ' MS Access tables you have to use [] around the field names
  
  ' Using SQL INSERT will add a new record EVERY time.
  ' Also if the specific "class,equiment,no" already exists!
  With MSFlexGrid1
    For lRow = .FixedRows To .Rows - 1
      sSQL = "INSERT INTO Lab2 ([Class], [Equipment], [No], [Select])"
      sSQL = sSQL & " VALUES "
      sSQL = sSQL & "('"
      ' Be carefull with spaces with between ' '
      sSQL = sSQL & Combo1.Text & "', '"
      sSQL = sSQL & .TextMatrix(lRow, 1) & "', '"
      sSQL = sSQL & .TextMatrix(lRow, 2) & "', "
      If Asc(.TextMatrix(lRow, 3)) = FLEXYES Then sSelect = "1" Else sSelect = "0"
      sSQL = sSQL & sSelect & ")"
      ' No need to use a recordset if you want to use an UPDATE or INSERT query
      adoConn.Execute sSQL
    Next lRow
  End With
  
  CloseConnection adoConn
End Sub

Private Sub Combo1_Click()
  Call MyFlexiData
End Sub


Private Sub Form_Load()
  Me.Move (Screen.Width - Me.Width) / 2, (Screen.Height - Me.Height) / 2 'Center the form
  
  With MSFlexGrid1
    .FormatString = "<ID|<Equipment|<No|<Select"
    .ColWidth(0) = 0.3 * 1440
    .ColWidth(1) = 1 * 1440
    .ColWidth(2) = 1.43 * 1440
    .ColWidth(3) = 600
    .Row = 0                                 '* -> these three steps decide the position of text in a cell
    .CellAlignment = flexAlignCenterCenter   '*
    .Rows = 1
  End With
  
  Call MyCombo
  Call MyFlexiData
  
End Sub

Private Sub MyCombo()
  'for Data List of Block Combo
  Combo1.Clear
  Combo1.AddItem "M3bK1"
  Combo1.AddItem "M2bK1"
  Combo1.AddItem "M1bK1"
End Sub

Private Sub MyFlexiData()
  Dim sSQL As String
  Dim adoRS As ADODB.Recordset
  Dim adoConn As ADODB.Connection
  
  If VBA.Trim(Combo1.Text) = "" Or VBA.Trim(Combo1.Text) = "M2bK1" Then
     sSQL = "SELECT * FROM Lab1 WHERE Class='" & VBA.Trim(Combo1.Text) & "' ORDER by Equipment"
  Else
    If VBA.Trim(Combo1.Text) = "" Or VBA.Trim(Combo1.Text) = "M3bK1" Then
        sSQL = "SELECT * FROM Lab1 WHERE Class='" & VBA.Trim(Combo1.Text) & "' ORDER by Equipment"
    End If
  End If
 
  Set adoConn = OpenConnection
  
  Set adoRS = adoConn.Execute(sSQL)
  If Not adoRS.BOF = False Then
    Set adoRS = Nothing 'for closing RS recordset
    Exit Sub
  End If


  With MSFlexGrid1
    Do While Not adoRS.EOF
      .Rows = .Rows + 1      'for adding row in MSFlexGrid
      .TextMatrix(.Rows - 1, 0) = .Rows - 1 & "."
      .TextMatrix(.Rows - 1, 1) = "" & adoRS.Fields("Equipment")
      .TextMatrix(.Rows - 1, 2) = "" & adoRS.Fields("no")
       adoRS.MoveNext
    Loop
  End With
  adoRS.Close
  
  Set adoRS = Nothing 'for closing RS recordset
  CloseConnection adoConn
  
  fillChkbox 168
  
End Sub


Private Sub fillChkbox(character As Integer)
  Dim lRow As Long
  
  With MSFlexGrid1
    .Col = .Cols - 1
    For lRow = .FixedRows To .Rows - 1
      .Row = lRow
      .CellFontName = "Wingdings"
      .CellFontSize = 14
      .CellAlignment = flexAlignCenterCenter
      .TextMatrix(lRow, .Col) = Chr$(character)
    Next lRow
  End With
End Sub

Private Sub MSFlexGrid1_Click()
  Dim lCol As Long, lRow As Long
  
  With MSFlexGrid1
    lRow = .MouseRow
    lCol = .MouseCol
    If lCol = .Cols - 1 And lRow >= .FixedRows Then
      If Asc(.TextMatrix(lRow, lCol)) = FLEXNO Then
        .TextMatrix(lRow, 3) = Chr$(FLEXYES)
      Else
        .TextMatrix(lRow, lCol) = Chr$(FLEXNO)
      End If
    End If
  End With
  
End Sub

Private Const try1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\Project1\try1.mdb;Persist Security Info=False"

Public Function OpenConnection() As ADODB.Connection
  Set OpenConnection = New ADODB.Connection
  OpenConnection.Open try1
End Function

Public Sub CloseConnection(myConn As ADODB.Connection)
  If Not myConn Is Nothing Then
    myConn.Close
    Set myConn = Nothing
  End If
End Sub