[RESOLVED] Datagrid Help

[Jamie_Garland]

Can anyone help me please.

I want it so when i click on the grid that record shows in the text boxs can someone help using the code below.

Public CN As New ADODB.Connection
Public RS As New ADODB.Recordset
 
Private Sub cmdClose_Click()
CN.Close
Unload Me
End Sub
 
Private Sub cmdDelete_Click()
    With RS
        If MsgBox("Are You Sure?", vbCritical + vbYesNo, "Confirm") = vbYes Then
 
        CN.Execute "DELETE * FROM Subjects WHERE Subject ='" & .Fields("Subject") & "'"
        MsgBox "Deleted", vbInformation, "Deleted"
        .Requery
        End If
    End With
End Sub
 
Private Sub cmdFirst_Click()
On Error Resume Next
With RS
    .MoveFirst
If .EOF = True Then
    .MoveLast
End If
            txtSubject.Text = .Fields("Subject")
            txtNoOfBooks.Text = .Fields("NoOfBooks")
            txtDays.Text = .Fields("IssueDays")
            txtFine.Text = .Fields("Finecharge")
            txtReverse.Text = .Fields("ReserveCharge")
End With
 
End Sub
 
Private Sub cmdLast_Click()
On Error Resume Next
With RS
    .MoveLast
If .EOF = True Then
    .MoveFirst
End If
            txtSubject.Text = .Fields("Subject")
            txtNoOfBooks.Text = .Fields("NoOfBooks")
            txtDays.Text = .Fields("IssueDays")
            txtFine.Text = .Fields("Finecharge")
            txtReverse.Text = .Fields("ReserveCharge")
End With
End Sub
 
Private Sub cmdNew_Click()
    cmdSave.Enabled = True
    txtSubject.Text = ""
    txtNoOfBooks.Text = ""
    txtDays.Text = ""
    txtFine.Text = ""
    txtReverse.Text = ""
    RS.AddNew
 
End Sub
 
Private Sub cmdNext_Click()
On Error Resume Next
With RS
    .MoveNext
If .EOF = True Then
    .MoveFirst
End If
            txtSubject.Text = .Fields("Subject")
            txtNoOfBooks.Text = .Fields("NoOfBooks")
            txtDays.Text = .Fields("IssueDays")
            txtFine.Text = .Fields("Finecharge")
            txtReverse.Text = .Fields("ReserveCharge")
End With
 
End Sub
 
Private Sub cmdPrevious_Click()
On Error Resume Next
With RS
    .MovePrevious
If .EOF = True Then
    .MoveLast
End If
            txtSubject.Text = .Fields("Subject")
            txtNoOfBooks.Text = .Fields("NoOfBooks")
            txtDays.Text = .Fields("IssueDays")
            txtFine.Text = .Fields("Finecharge")
            txtReverse.Text = .Fields("ReserveCharge")
End With
 
End Sub
 
Private Sub cmdSave_Click()
 With RS
         If MsgBox("Are You Sure?", vbQuestion + vbYesNo, "Confirm") = vbYes Then
        If RS.EOF Then
            .AddNew
        End If
        If txtSubject.Text <> "" And txtNoOfBooks.Text <> "" And txtDays.Text <> "" And txtFine.Text <> "" And txtReverse.Text <> "" Then
        .Fields("Subject") = txtSubject.Text
        .Fields("NoOfBooks") = txtNoOfBooks.Text
        .Fields("IssueDays") = txtDays.Text
        .Fields("FineCharge") = txtFine.Text
        .Fields("ReserveCharge") = txtReverse.Text
        .Update
        MsgBox "Saved", vbInformation, "Saved"
    End If
    
End If
End With
End Sub
 
Private Sub cmdSearch_Click()
With RS
    .Close
    RS.Open "Select * FROM Subjects WHERE subject ='" & txtSearch.Text & "'"
    txtSearch.Text = .Fields("Subject")
    
    .Requery
End With
 
End Sub
 
Private Sub Command1_Click()
On Error Resume Next
With RS
    If MsgBox("Update The Record?", vbQuestion + vbYesNo, "Confirm") = vbYes Then
    .Fields("Subject") = txtSubject.Text
    .Fields("NoOfbooks") = txtNoOfBooks.Text
    .Fields("IssueDays") = txtDays.Text
    .Fields("FineCharge") = txtFine.Text
    .Fields("ReverseCharge") = txtReverse.Text
    .Update
    .Requery
MsgBox "The current record has been updated", vbInformation, "Update Successful"
End If
End With
End Sub
 
Private Sub Command2_Click()
  If Command2.Caption = "Search" Then
        Command2.Caption = "Stop"
        frmSubjects.Height = 6255
        Frame1.Visible = True
    Else
        Command2.Caption = "Search"
        frmSubjects.Height = 5040
        Frame1.Visible = False
    End If
 
End Sub
 
 
 
Private Sub Command4_Click()
With RS
    .Close
    
    RS.Open "Select * FROM Subjects"
    
    .Requery
End With
 
End Sub
 
Private Sub Form_Load()
    CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db1.mdb"
    RS.CursorLocation = adUseClient
    RS.Open "SELECT * FROM Subjects ORDER BY Subject ASC", CN, adOpenStatic, adLockOptimistic
    Set DataGrid1.DataSource = RS
    
      With RS
        If Not .BOF Then
            txtSubject.Text = .Fields("Subject")
            txtNoOfBooks.Text = .Fields("NoOfBooks")
            txtDays.Text = .Fields("IssueDays")
            txtFine.Text = .Fields("Finecharge")
            txtReverse.Text = .Fields("ReserveCharge")
            
             lblStatus.Caption = "Total Records: " & CStr(RS.RecordCount)
        End If
    End With
  
  DataGrid1.Columns(0).Caption = "Subject"
  DataGrid1.Columns(1).Caption = "Issue Days"
  DataGrid1.Columns(2).Caption = "No Of Books"
  DataGrid1.Columns(3).Caption = "Fine Charge"
  DataGrid1.Columns(4).Caption = "Reverse Charge"
End Sub

[ganeshmoorthy]

'In the click event of the datagrid paste these lines
    DataGrid1.Row = Datagrid1.Row
    DataGrid1.Col = 1 'Change the column no if this doesnt contain Subject
    txtSubject.Text = DataGrid1.Text 
    DataGrid1.Col = 2 'Change the column no if this doesnt contain NoofBooks
    txtNoOfBooks.Text = DataGrid1.Text
    DataGrid1.Col = 3 'Change the column no if this doesnt contain IssueDays
    txtDays.Text = DataGrid1.Text
    DataGrid1.Col = 4 'Change the column no if this doesnt contain FineCharge
    txtFine.Text = DataGrid1.Text
    DataGrid1.Col = 5 'Change the column no if this doesnt contain ReserveCharge
    txtReverse.Text = DataGrid1.Text

[Jamie_Garland]

The code below dosent work.... What i need is for when a client clicks on the datagrid and they select what record to view it then shows that record thats been clicked in the text boxes. All the code does is change the data.?

Private Sub Datagrid1_Click()
Datagrid1.Row = Datagrid1.Row
Datagrid.col = 1
txtSubject.text = Datagrid1.text
Datagrid.col = 2
txtNoOfBooks.text = Datagrid1.text
Datagrid.col = 3
txtDays.text = Datagrid1.text
Datagrid.col = 4
txtFine.text = Datagrid1.text
Datagrid.col = 5
txtReserve.text = Datagrid1.text
End Sub

[ganeshmoorthy]

What is it doing currently when you click on the data grid.

[leinad31]

If your gonna be using RS to navigate via the datagrid then DON'T close the recordset, repopulating with new records as this clears your previous list.

If your using the textboxes to edit the values then you shouldn't be using the datagrid. You might want to consider transfering the selection list to a listview in report view... you can resort the rows per column in a listview, this might come in handy. But for the listview implementation to work you need to have a primary key in your recordset. When a listitem is clicked, get the primary key (stored in the tag property of the listitem) and use search/find method on the RS based on primary key... current record would be updated and you can transfer from RS to the textboxes.

The only drawback is you also have to maintain the listview, if a record is edited then you will also have to update the listview... if a record is added then you will also have to add a listitem... if a record is deleted then you will have to delete the listitem from the listview (the listview has a find method which you can use).

[ganeshmoorthy]

Jamie: try this,

Set txtSubject.DataSource = Adodc1.Recordset
    txtSubject.DataField = Adodc1.Recordset.Fields("Subject").Name
 
    Set txtNoOfBooks.DataSource = Adodc1.Recordset
    txtNoOfBooks.DataField = Adodc1.Recordset.Fields("NoOfBooks").Name
 
    '...bind the same way for other text boxes too
    'And delete all the codes in the DataGrid1_Click()

[Jamie_Garland]

It changes all the data around and then it dosent work

[ganeshmoorthy]

Im sorry, I dint mention properly, you have to copy this code into your Form_Load() event and delete all the codes there in the DataGrid1_click() event.

[Jamie_Garland]

This code still dosent work..

Private Sub Form_Load()
 
    CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db1.mdb"
    RS.CursorLocation = adUseClient
    RS.Open "SELECT * FROM users ORDER BY uname ASC", CN, adOpenStatic, adLockOptimistic
    Set DataGrid1.DataSource = RS
 
  
    With RS
        If Not .BOF Then
            txtUser.Text = .Fields("uname")
            txtPass.Text = .Fields("Pass")
        End If
    End With
 
    DataGrid1.Columns(0).Caption = "Username"
    DataGrid1.Columns(1).Caption = "Password"
    
    DataGrid1.Row = DataGrid1.Row
    DataGrid1.Col = 0 'Change the column no if this doesnt contain Subject
    txtUser.text = DataGrid1.Text
    DataGrid1.Col = 1 'Change the column no if this doesnt contain NoofBooks
    txtPass.Text = DataGrid1.Text
 
 
 
End Sub

[Ember]

I tried a testApp and it worked, but the TextBoxes did not take the changes before I DblClicked. Strange, but whether I moved the

DataGrid1.Row = DataGrid1.Row
    DataGrid1.Col = 0 'Change the column no if this doesnt contain Subject
    txtUser.text = DataGrid1.Text
    DataGrid1.Col = 1 'Change the column no if this doesnt contain NoofBooks
    txtPass.Text = DataGrid1.Text

to MouseDown or MouseUp a DblClick was needed. Maybe DataGrid1.SetFocus in MouseDown and the above code in Click will do?
Morten

[Jamie_Garland]

I tryed that but when i double click on the record and it changed the persons details..

[Ember]

You are quite right.
I changed the DataGrid1 with a MSHFlexGrid named DataGrid1.
Then it workes with the following code:

Private Sub DataGrid1_Click()
'In the click event of the datagrid paste these lines
    DataGrid1.Row = DataGrid1.Row
    DataGrid1.Col = 1 'Change the column no if this doesnt contain Subject
    txtSubject.Text = DataGrid1.Text
    DataGrid1.Col = 2 'Change the column no if this doesnt contain NoofBooks
    txtNoOfBooks.Text = DataGrid1.Text
    DataGrid1.Col = 3 'Change the column no if this doesnt contain IssueDays
    txtDays.Text = DataGrid1.Text
    DataGrid1.Col = 4 'Change the column no if this doesnt contain FineCharge
    txtFine.Text = DataGrid1.Text
    DataGrid1.Col = 5 'Change the column no if this doesnt contain ReserveCharge
    txtReverse.Text = DataGrid1.Text
End Sub
 
Private Sub DataGrid1_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
'  DataGrid1.SetFocus
'In the click event of the datagrid paste these lines
    DataGrid1.RowSel = DataGrid1.Row
End Sub
 
Private Sub Form_Load()
    CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db1.mdb"
    RS.CursorLocation = adUseClient
    RS.Open "SELECT * FROM Subjects ORDER BY Subject ASC", CN, adOpenStatic, adLockOptimistic
  
    Set DataGrid1.DataSource = RS
    
      With RS
        If Not .BOF Then
            txtSubject.Text = .Fields("Subject")
            txtNoOfBooks.Text = .Fields("NoOfBooks")
            txtDays.Text = .Fields("IssueDays")
            txtFine.Text = .Fields("Finecharge")
            txtReverse.Text = .Fields("ReserveCharge")
            
             lblStatus.Caption = "Total Records: " & CStr(RS.RecordCount)
        End If
    End With
  
  DataGrid1.ColHeaderCaption(0, 0) = "Subject"
  DataGrid1.ColHeaderCaption(0, 1) = "Issue Days"
  DataGrid1.ColHeaderCaption(0, 2) = "No Of Books"
  DataGrid1.ColHeaderCaption(0, 3) = "Fine Charge"
  DataGrid1.ColHeaderCaption(0, 4) = "Reverse Charge"
End Sub

Hope you manage to make this run for you too ... Morten

[brucevde]

The row does not change until after the Click event. Use the DataGrid RowColChange event.

Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
    If Not IsEmpty(LastRow) Then 'dont do anything unless the row changes
        txtUser.Text = DataGrid1.Columns(0).CellText(DataGrid1.Bookmark)
        txtPass.Text = DataGrid1.Columns(1).CellText(DataGrid1.Bookmark)
    End If
End Sub

[Jamie_Garland]

Thanks now that we have that sorted can you tell me how to get it so when i search for a member and so it only shows the record in the datagrid.

[brucevde]

Close the Recordset, Open it with a new query, Rebind the DataGrid to the Recordset

Private Sub cmdSearch_Click()
    RS.Close
    RS.Open "Select * FROM Subjects WHERE subject ='" & txtSearch.Text & "'"
    DataGrid1.ReBind
End Sub