Dear All,

  1. How can i enter data in flex grid and then press save button to enter data in access db with out using text boxes?
  2. How to bind record set with flex grid?

Please give me sample code which should for add edit delete data from db using flex grid.

Thanks in advance.

Recommended Answers

All 9 Replies

I'm really in good mood right now. i write a simple code about how to add data to flexgrid and save it, also load it to flexgrid.
For edit and delete? do it by yourself. since 265 posts you should know about daniweb rules :)

Add references : Microsoft ActiveX Data Object 2.5 Library
Add 2 flexgrid : FlxGrdDemo for input data and FlxGrdDemo2 for load data.
Add 2 button for save and load.

After from load you can see default data in database. Just add new Au_Id and Author in first flexgrid. Use Keyboard Arrow to move and Enter to make new row.

Public Conn As New ADODB.Connection

Private Sub Access_Connector()
    Dim db_file As String

    ' Get the data.'
    db_file = App.Path
    If Right$(db_file, 1) <> "\" Then db_file = db_file & "\"
    db_file = db_file & "Authors.mdb"

    Set Conn = New ADODB.Connection
    Conn.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & db_file & ";" & _
        "Persist Security Info=False"
    Conn.Open
End Sub

Private Sub LoadData()

    Dim c As Integer
    Dim r As Integer
    Dim col_wid() As Single
    Dim field_wid As Single

    Access_Connector ' Connect to access'

    Set rs = New ADODB.Recordset
    rs.Open "SELECT * from Authors", Conn, adOpenDynamic, adLockBatchOptimistic

    ' Use one fixed row and no fixed columns.'
    FlxGrdDemo2.Rows = 2
    FlxGrdDemo2.FixedRows = 1
    FlxGrdDemo2.FixedCols = 0

    ' Display column headers.'
    FlxGrdDemo2.Rows = 1
    FlxGrdDemo2.Cols = rs.Fields.Count
    ReDim col_wid(0 To rs.Fields.Count - 1)
    For c = 0 To rs.Fields.Count - 1
        FlxGrdDemo2.TextMatrix(0, c) = rs.Fields(c).Name
        col_wid(c) = TextWidth(rs.Fields(c).Name)
    Next c

    ' Display the values for each row.'
    r = 1
    Do While Not rs.EOF
        FlxGrdDemo2.Rows = FlxGrdDemo2.Rows + 1
        For c = 0 To rs.Fields.Count - 1
            FlxGrdDemo2.TextMatrix(r, c) = rs.Fields(c).Value

            ' See how big the value is.'
            field_wid = TextWidth(rs.Fields(c).Value)
            If col_wid(c) < field_wid Then col_wid(c) = field_wid
        Next c

        rs.MoveNext
        r = r + 1
    Loop

    ' Close the recordset and connection.'
    rs.Close
    Conn.Close

    ' Set the column widths.'
    For c = 0 To FlxGrdDemo2.Cols - 1
        FlxGrdDemo2.ColWidth(c) = col_wid(c) + 450
    Next c

End Sub

Private Sub Command1_Click()
Access_Connector ' Connect to access'

With FlxGrdDemo
    For i = 1 To .Rows - 1

        Set rs = Nothing
        Set rs = New ADODB.Recordset

        rs.Open "SELECT * from Authors", Conn, adOpenStatic, adLockOptimistic
        rs.AddNew
        rs!Au_id = .TextMatrix(i, 0)
        rs!Author = .TextMatrix(i, 1)
        rs.Update

    Next i
    MsgBox "Data added", vbInformation, "Add Data"

    rs.Close
    Conn.Close
End With
End Sub

Private Sub Command2_Click()
Access_Connector
LoadData
End Sub

Private Sub FlxGrdDemo_KeyPress(KeyAscii As Integer)
Select Case KeyAscii
    Case vbKeyReturn
        ' When the user hits the return key'
        ' this code will move the next cell or row.'
        With FlxGrdDemo
            If .Col + 1 <= .Cols - 1 Then
                .Col = .Col + 1

            ElseIf .Row + 1 <= .Rows - 1 Then
                .Row = .Row + 1
                .Col = 0
            Else
                FlxGrdDemo.AddItem "" 'add new row'
                .Row = .Rows - 1 'set position in new row
                .Col = 0 'set position in new row

            End If
        End With

    Case vbKeyBack
        ' Delete the previous character when the'
        ' backspace key is used.'
        With FlxGrdDemo
            If Trim(.Text) <> "" Then _
            .Text = Mid(.Text, 1, Len(.Text) - 1)
        End With
    Case Is < 32
        ' Avoid unprintable characters.'
    Case Else 'Else print everything'
        With FlxGrdDemo
            .Text = .Text & Chr(KeyAscii)
        End With
End Select
End Sub
Private Sub FlxGrdDemo_KeyUp(KeyCode As Integer, Shift As Integer)
Select Case KeyCode
    ' Copy'
    Case vbKeyC And Shift = 2 ' Control + C'
        Clipboard.Clear
        Clipboard.SetText FlxGrdDemo.Text
        KeyCode = 0
    ' Paste'
    Case vbKeyV And Shift = 2 'Control + V'
        FlxGrdDemo.Text = Clipboard.GetText
        KeyCode = 0
    ' Cut'
    Case vbKeyX And Shift = 2 'Control + X'
        Clipboard.Clear
        Clipboard.SetText FlxGrdDemo.Text
        FlxGrdDemo.Text = ""
        KeyCode = 0
    ' Delete'
    Case vbKeyDelete
        FlxGrdDemo.Text = ""
End Select
End Sub

Private Sub Form_Load()
With FlxGrdDemo
    .Cols = 2
    .Rows = 2
    .FixedCols = 0
    .TextMatrix(0, 0) = "Au_Id"
    .TextMatrix(0, 1) = "Author"
End With

End Sub

In vb we put comments in codes using single quote. But something wrong if i don't close it with single quote too.

'This is a comments
Dim i as Integer ' declare i as integer type
Dim j as String

For i = 0 to 5 ' This comments too
   Msgbox i 
Next i

Test

commented: You should get paid for this codes +3

In vb we put comments in codes using single quote. But something wrong if i don't close it with single quote too.
'This is a comments
Dim i as Integer ' declare i as integer type
Dim j as String
For i = 0 to 5 ' This comments too
Msgbox i
Next i

Please just ignoring the last statement. It just for a test.

You have too much time on your hands Jx_Man

First of all thanks Jx_Man fantastic code example

Now let me ask some questions

  1. I want to check if record already exists in db before inserting new record?
  2. Can i have column name with out under score e.g

i want to write column name as Employee ID insetad Emp_id

  1. How to display whole name actually some column names hided?

Please view the attached image file thanks.

Here is the image file

  1. I want to check if record already exists in db before inserting new record?
  2. Can i have column name with out under score e.g
  1. Just do small modification in saving codes. Binding all data in flexgrid and compare it with data in db.

    Private Sub Command1_Click()
    Access_Connector ' Connect to access'
    
    With FlxGrdDemo
        For i = 1 To .Rows - 1
    
            Set rs = Nothing
            Set rscheck = Nothing
            Set rs = New ADODB.Recordset
            Set rscheck = New ADODB.Recordset
    
            rs.Open "SELECT * from Authors", Conn, adOpenStatic, adLockOptimistic
            rscheck.Open "SELECT * from Authors where Au_id = " & FlxGrdDemo.TextMatrix(i, 0), Conn, adOpenStatic, adLockOptimistic
    
            If rscheck.RecordCount > 0 Then
                MsgBox "AU_ID '" & .TextMatrix(i, 0) & "' already in database"
            Else
                rs.AddNew
                rs!au_id = .TextMatrix(i, 0)
                rs!Author = .TextMatrix(i, 1)
                rs.Update
                MsgBox "Data added", vbInformation, "Add Data"
            End If
        Next i
    
    
    rs.Close
    rsCheck.Close
    Conn.Close
    End With
    End Sub
    
  2. I think there are codes about Autowidth of column size in last of LoadData procedure. Read the code! Learning it! don't just copy and paste without understanding it!!.
    Do small modification (again!!) in your form load event :

    Private Sub Form_Load()
    With FlxGrdDemo
        .Cols = 2
        .Rows = 2
        .FixedCols = 0
        .TextMatrix(0, 0) = "Au_Id"
        .TextMatrix(0, 1) = "Author"
    
        'This following codes for autowidth of column size!!!'
        For i = 0 To .Cols - 1
            .ColWidth(i) = TextWidth(.TextMatrix(0, i)) + 300
        Next i
    End With
    
    
    LoadData
    End Sub
    

Sorry i was ill, some quries as still pending.

  1. How to cahnge column name in MSFlexgrid?

  2. How to delete a Selected Row?

  3. Suppose i have a text box and i enter then click on search button to show Record in MSFlexGrid?

  4. How to udpade a selected record?

  5. How to clear the Contents of Flex Grid, i mean when i click on save button it should clear the Grid.

Sorry i was ill, some quries as still pending.

1. How to cahnge column name in MSFlexgrid?

2. How to delete a Selected Row?

3. Suppose i have a text box and i enter then click on search button to show Record in MSFlexGrid?

5. How to udpade a selected record?

6. How to clear the Contents of Flex Grid, i mean when i click on save button it should clear the Grid.

I think, i give you more than enough..
How about make some effort for your each questions and post your own code.
Until now, i didn't see any single codes from you..
You always ask more and more questions without showing some effort. You just drive me to writing all the codes for you.

to clear data from flexgrid

flexgrid.clear

note-- flexgrid is the name of your msflexgrid..you can change it into any name you prefer in the properties

hope this help.. :P

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.