Solved

ADO DataGrid

Posted on 2003-12-05
7
1,055 Views
Last Modified: 2006-11-17
Hi All,
I have a ADO datagrid, a ADO data control(Adodc1) and an update button on a form.
My problem is that when a selected row on the grid is updated and the recordset is reloaded, the cursurs(or arrow on the grid) always jumps back to the first row of the dataset displayed in the datagrid. I would like the reloaded data (containing the updated record) to be visible in the grid without having to scroll all the way to find the updated record.

I also do not want the updated record to be moved as firstrecord in the dataset.

Anyone have any suggestens?

Thx in Adv.
0
Comment
Question by:MarjonChirill
7 Comments
 
LVL 1

Accepted Solution

by:
carpe-diem earned 250 total points
Comment Utility
You could use the AbsolutePosition property of the recordset as long as you only update records (as opposed to adding or deleting which will change the number and position of records)

use;
Dim intCurrentRecord As Integer

With Adodc1.Recordset
          intCurrentRecord = .AbsolutePosition
End With

Do your thing here, then after you reload the data

With Adodc1.Recordset
         .AbsolutePosition = intCurrentRecord
End With

0
 
LVL 4

Expert Comment

by:TomLaw1999
Comment Utility
What code do you have behind the update button?
0
 

Author Comment

by:MarjonChirill
Comment Utility
Private Sub Update_Click()  
 'Definieer database
    OpenConnection
   
    'Definieer Volgnr
    Dim Volgnr As Long
   
    'Controleer datum velden
    If Not datumCheck(BDatum.Text) Then
        BDatum.BackColor = QBColor(14)
        MsgBox "Please Enter A Valid Date dd-mm-yyyy...", vbInformation
        Exit Sub
    End If
   
    If Not datumCheck(EDatum.Text) Then
        EDatum.BackColor = QBColor(14)
        MsgBox "Please Enter A Valid Date dd-mm-yyyy...", vbInformation
        Exit Sub
    End If
   
    'Controleer tijd
    If Not IsTime(BTijd.Text) Then
        BTijd.BackColor = QBColor(14)
        MsgBox "Please Enter A Valid Time hh:mm:ss...", vbInformation
        Exit Sub
    End If
   
    If Not IsTime(Etijd.Text) Then
        BTijd.BackColor = QBColor(14)
        MsgBox "Please Enter A Valid Time hh:mm:ss...", vbInformation
        Exit Sub
    End If
   
    User = Environ("UserName")
    Volgnr = DataGrid1.Columns(0).CellText(DataGrid1.Bookmark)
   
    'Velden welke toegevoegd moeten worden
    Dim Velden As String
    Velden = "VOLGNUMMER" & ", " & "BEGIN_DATUM"
    Velden = Velden & ", " & "EIND_DATUM"
    Velden = Velden & ", " & "NAAM" & ", " & "SOORT" & ", " & "OORZAAK"
    Velden = Velden & ", " & "OPMERKING" & ", " & "BEPERKING" & ", " & "PMAX"
    Velden = Velden & ", " & "DATUM_LAMU" & ", " & "GEBR_LAMU"
   
    SQL_STATEMENT = " UPDATE BEPERKING"
    SQL_STATEMENT = SQL_STATEMENT & " SET BEGIN_DATUM = " & BDatum.Text & BTijd.Text
    SQL_STATEMENT = SQL_STATEMENT & " SET EIND_DATUM = " & EDatum.Text & Etijd.Text
    SQL_STATEMENT = SQL_STATEMENT & " SET NAAM = " & NaamEenh.Text
    SQL_STATEMENT = SQL_STATEMENT & " SET SOORT = " & SoortCombo1.Text
    SQL_STATEMENT = SQL_STATEMENT & " SET OORZAAK = " & OorzaakCombo2.Text
    SQL_STATEMENT = SQL_STATEMENT & " SET OPMERKING = " & Opmerking(0).Text
    SQL_STATEMENT = SQL_STATEMENT & " SET BEPERKING = " & Beperkingmw.Text
    SQL_STATEMENT = SQL_STATEMENT & " SET PMAX = " & Maxverm.Text
    SQL_STATEMENT = SQL_STATEMENT & " SET DATUM_LAMU = " & Date & Time
    SQL_STATEMENT = SQL_STATEMENT & " SET GEBR_LAMU = " & User
    SQL_STATEMENT = SQL_STATEMENT & " WHERE VOLGNUMMER = " & Volgnr
    cn.Execute SQL_STATEMENT, dbFailOnError
End Sub
-------------------
This is the code. I check some dates, read the username, and capture the key of the record, and then update the database record.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
Do a

Recordset.Refresh
Recordset.Find = key field

after the you run the update statement.  Why are you using a DataControl?

Leon
0
 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
Do a

Recordset.Refresh
Recordset.Find = key field

after the you run the update statement.  Why are you using a DataControl?

Leon
0
 

Author Comment

by:MarjonChirill
Comment Utility
carpe-diem,
It works! Thank you! I will 'accept' your solution.
--------------
Leonstryker,
I tried your statements but I am getting a Run-time error '3001'. (Arguments are of the wrong type..).
To solve the error, I altered the statement to:

'Volgnr being my key field

Dim strVolgnr As String
strVolgnr = Cstr(Volgnr)

'Because Adodc1.Recordset does not have a 'Refresh' property i altered the stement as seen below
DataGrid1.Refresh
Adodc1.Recordset.Find(strVolgnr)

But...still didn't work. I got the same Run-time error '3001'

Anyway Thx for trying to help...
Marjon
0
 

Expert Comment

by:gavakian
Comment Utility
Gus does anyone know how i declare a global variables and how to exchange pointer to databases between forms.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now