Hi Guys,

I don't know how to explain my problem, hope this make sense.

I have one unbound frmContacts using ADO. On form load records can be 1 or many it will depend on which search form the user used
because I have two search form:
1) frmSingleSearch - where user can select only one from the list and frmContacts will load and populate the rs accordingly.
2) frmAdvanceSearch - where user can defined their own criteria and frmContacts will load and populate the rs accordingly.

Then I have one txtRecordPosition showing the records absolute position while user navigating to the recordset e.g. Record 3 of
35...

OK here's the scenario, for instance I'm on the Record 8 of 122 then I edit some of the information:

Option Compare Database
Option Explicit
Dim rs As ADODB.Recordset
-------------------------------------------
cmdEdit_Click:
	UnLockMe 'Unlock All Fields
	GetOldValue 'Save Current Value On Each Field Tag
	cmdBtnFalse 'Disabled all cmdButton except for cmdSave & cmdUndo

-------------------------------------
then Save... where the problem is!

cmdSave_Click

    Dim cn As ADODB.Connection
    Dim strContactsSQL As String
    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset


    If Me.txtMODE = "New" Then
	  strContactsSQL = "SELECT * FROM m01TblContacts"
	  rs.Open strContactsSQL, cn, adOpenKeyset, adLockOptimistic
        rs.AddNew
        SaveCurrentValue
        rs.Update
        Me.m01ContactsID = rs("m01ContactsID")
    Else 'Edit
	  strContactsSQL = "SELECT * FROM m01TblContacts WHERE m01ContactsID=" & Me.m01ContactsID
	  rs.Open strContactsSQL, cn, adOpenKeyset, adLockOptimistic
        SaveCurrentValue
        rs.Update
        cmdBtnTrue
    End If

----------

Private Sub SaveCurrentValue()

        If Not IsNull(Me.m01Title) Then rs("m01Title") = Me.m01Title
        If Not IsNull(Me.m01FirstName) Then rs("m01FirstName") = Me.m01FirstName
        If Not IsNull(Me.m01MiddleInitial) Then rs("m01MiddleInitial") = Me.m01MiddleInitial
	  etc...
end sub
-----------

From here everything's fine...

The problem is after I saved the changes I made, I losed the records absolute position, it returns to Record 1 of 1 instead of
Record 8 of 122. As far I can see the problem originated when I overwrites the strContactsSQL and I can't figure out how to resolved
it.

Basically out this long story, my main objective only is to:
1) Return my original RS based on the frmSearchAdvance
2) Return the records absolute position after edit/save and continue the nos. while navigating.

Sorry for this novel...

Any help would be highly appreciated.
TIA,
George

George, sounds like you are trying to make your own record navigation bar.
Here's some code that uses the
smart navigation concept (used in one of Wrox's text book).

1. In the form I have my command buttons that will navigate thru the records
(cmdFirst, cmdPrev, cmdNext, and cmdLast).
2. Next to these command buttons I have 2 text boxes (txtCurrentRecord,
txtRecords).
3. And finally I use a field on the form (the primary key of the recordset)
to check if records are present (FormID is what I used for the example)
In the form Current Event you can copy the following code below.

Dim recClone As Recordset
    Dim intNewRecord As Integer
    Dim intCounter As Integer


    Set recClone = Me.RecordsetClone()
    intNewRecord = IsNull(FormID)

    If intNewRecord Then
        Me.cmdFirst.Enabled = True
        Me.cmdNext.Enabled = False
        Me.cmdPrev.Enabled = True
        Me.cmdLast.Enabled = True
        Me.txtRecords = recClone.RecordCount
        Me.txtCurrentRecord = recClone.AbsolutePosition + 1
        Exit Sub
    End If


    If Me.NewRecord Then
        Me.txtCurrentRecord = recClone.AbsolutePosition + 1
        Me.txtRecords = recClone.RecordCount
    End If
    If recClone.RecordCount = 0 Then
        Me.cmdFirst.Enabled = False
        Me.cmdNext.Enabled = False
        Me.cmdPrev.Enabled = False
        Me.cmdLast.Enabled = False
    Else
        recClone.Bookmark = Me.Bookmark

        recClone.MovePrevious
        cmdFirst.Enabled = Not (recClone.BOF)
        cmdPrev.Enabled = Not (recClone.BOF)
        recClone.MoveNext

        recClone.MoveNext
        cmdLast.Enabled = Not (recClone.EOF)
        cmdNext.Enabled = Not (recClone.EOF)
        recClone.MovePrevious
        Me.txtCurrentRecord = recClone.AbsolutePosition + 1
        Me.txtRecords = recClone.RecordCount
    End If

    recClone.Close

Good Luck,
Derrick Flores
San Antonio, TX


----- Original Message -----
From: "George Oro" <george@c...>
To: "Access" <access@p...>
Sent: Saturday, January 18, 2003 7:27 AM
Subject: [access] RS absolute position


> Hi Guys,
>
> I don't know how to explain my problem, hope this make sense.
>
> I have one unbound frmContacts using ADO. On form load records can be 1 or
many it will depend on which search form the user used
> because I have two search form:
> 1) frmSingleSearch - where user can select only one from the list and
frmContacts will load and populate the rs accordingly.
> 2) frmAdvanceSearch - where user can defined their own criteria and
frmContacts will load and populate the rs accordingly.
>
> Then I have one txtRecordPosition showing the records absolute position
while user navigating to the recordset e.g. Record 3 of
> 35...
>
> OK here's the scenario, for instance I'm on the Record 8 of 122 then I
edit some of the information:
>
> Option Compare Database
> Option Explicit
> Dim rs As ADODB.Recordset
> -------------------------------------------
> cmdEdit_Click:
> UnLockMe 'Unlock All Fields
> GetOldValue 'Save Current Value On Each Field Tag
> cmdBtnFalse 'Disabled all cmdButton except for cmdSave & cmdUndo
>
> -------------------------------------
> then Save... where the problem is!
>
> cmdSave_Click
>
>     Dim cn As ADODB.Connection
>     Dim strContactsSQL As String
>     Set cn = CurrentProject.Connection
>     Set rs = New ADODB.Recordset
>
>
>     If Me.txtMODE = "New" Then
>   strContactsSQL = "SELECT * FROM m01TblContacts"
>   rs.Open strContactsSQL, cn, adOpenKeyset, adLockOptimistic
>         rs.AddNew
>         SaveCurrentValue
>         rs.Update
>         Me.m01ContactsID = rs("m01ContactsID")
>     Else 'Edit
>   strContactsSQL = "SELECT * FROM m01TblContacts WHERE m01ContactsID=" &
Me.m01ContactsID
>   rs.Open strContactsSQL, cn, adOpenKeyset, adLockOptimistic
>         SaveCurrentValue
>         rs.Update
>         cmdBtnTrue
>     End If
>
> ----------
>
> Private Sub SaveCurrentValue()
>
>         If Not IsNull(Me.m01Title) Then rs("m01Title") = Me.m01Title
>         If Not IsNull(Me.m01FirstName) Then rs("m01FirstName") 
Me.m01FirstName
>         If Not IsNull(Me.m01MiddleInitial) Then rs("m01MiddleInitial") 
Me.m01MiddleInitial
>   etc...
> end sub
> -----------
>
> From here everything's fine...
>
> The problem is after I saved the changes I made, I losed the records
absolute position, it returns to Record 1 of 1 instead of
> Record 8 of 122. As far I can see the problem originated when I overwrites
the strContactsSQL and I can't figure out how to resolved
> it.
>
> Basically out this long story, my main objective only is to:
> 1) Return my original RS based on the frmSearchAdvance
> 2) Return the records absolute position after edit/save and continue the
nos. while navigating.
>
> Sorry for this novel...
>
> Any help would be highly appreciated.
> TIA,
> George
>
>
>
>
>
>
>
>
>
>

Thanks Derrick! your code is nice, unfortunately it doesn't solved my main problem. Thanks again...

Who ever followed this, I arrived to this solution:
For instance I used the frmSearchAdvance, therefore my frmContacts will Load & populate more than 1 records. E.g I'm on Record 8
of
122, when I click cmdEdit I saved the records absolute position in one public variable

Option Compare Database
Option Explicit
Dim rs As ADODB.Recordset
dim lngAbsolutePosition as long
---
cmdEdit
	lngAbsolutePosition = rs.AbsolutePosition

cmdSave
	Open my rs = current id selected so basically my rs set on form load overwritten.
	Update accordingly
Here's what I add:
	Call Form_Load ' to return my original rs which 122 Records.
	rs.Move lngAbsolutePosition ' it returns to record 9 instead of 8 therefore
	Call cmdPrevious_Click 'to return to Record 8 of 122
---------

It's quite OK & tricky but it solved my prob for a while. Is there any good approach to solved this?

TIA,
George




> -----Original Message-----
> From: Derrick Flores [mailto:derrickflores@s...]
> Sent: Sunday, January 19, 2003 5:36 AM
> To: Access
> Subject: [access] Re: RS absolute position
>
>
> George, sounds like you are trying to make your own record navigation bar.
> Here's some code that uses the
> smart navigation concept (used in one of Wrox's text book).
>
> 1. In the form I have my command buttons that will navigate thru the records
> (cmdFirst, cmdPrev, cmdNext, and cmdLast).
> 2. Next to these command buttons I have 2 text boxes (txtCurrentRecord,
> txtRecords).
> 3. And finally I use a field on the form (the primary key of the recordset)
> to check if records are present (FormID is what I used for the example)
> In the form Current Event you can copy the following code below.
>
> Dim recClone As Recordset
>     Dim intNewRecord As Integer
>     Dim intCounter As Integer
>
>
>     Set recClone = Me.RecordsetClone()
>     intNewRecord = IsNull(FormID)
>
>     If intNewRecord Then
>         Me.cmdFirst.Enabled = True
>         Me.cmdNext.Enabled = False
>         Me.cmdPrev.Enabled = True
>         Me.cmdLast.Enabled = True
>         Me.txtRecords = recClone.RecordCount
>         Me.txtCurrentRecord = recClone.AbsolutePosition + 1
>         Exit Sub
>     End If
>
>
>     If Me.NewRecord Then
>         Me.txtCurrentRecord = recClone.AbsolutePosition + 1
>         Me.txtRecords = recClone.RecordCount
>     End If
>     If recClone.RecordCount = 0 Then
>         Me.cmdFirst.Enabled = False
>         Me.cmdNext.Enabled = False
>         Me.cmdPrev.Enabled = False
>         Me.cmdLast.Enabled = False
>     Else
>         recClone.Bookmark = Me.Bookmark
>
>         recClone.MovePrevious
>         cmdFirst.Enabled = Not (recClone.BOF)
>         cmdPrev.Enabled = Not (recClone.BOF)
>         recClone.MoveNext
>
>         recClone.MoveNext
>         cmdLast.Enabled = Not (recClone.EOF)
>         cmdNext.Enabled = Not (recClone.EOF)
>         recClone.MovePrevious
>         Me.txtCurrentRecord = recClone.AbsolutePosition + 1
>         Me.txtRecords = recClone.RecordCount
>     End If
>
>     recClone.Close
>
> Good Luck,
> Derrick Flores
> San Antonio, TX
>
>
> ----- Original Message -----
> From: "George Oro" <george@c...>
> To: "Access" <access@p...>
> Sent: Saturday, January 18, 2003 7:27 AM
> Subject: [access] RS absolute position
>
>
> > Hi Guys,
> >
> > I don't know how to explain my problem, hope this make sense.
> >
> > I have one unbound frmContacts using ADO. On form load records can be 1 or
> many it will depend on which search form the user used
> > because I have two search form:
> > 1) frmSingleSearch - where user can select only one from the list and
> frmContacts will load and populate the rs accordingly.
> > 2) frmAdvanceSearch - where user can defined their own criteria and
> frmContacts will load and populate the rs accordingly.
> >
> > Then I have one txtRecordPosition showing the records absolute position
> while user navigating to the recordset e.g. Record 3 of
> > 35...
> >
> > OK here's the scenario, for instance I'm on the Record 8 of 122 then I
> edit some of the information:
> >
> > Option Compare Database
> > Option Explicit
> > Dim rs As ADODB.Recordset
> > -------------------------------------------
> > cmdEdit_Click:
> > UnLockMe 'Unlock All Fields
> > GetOldValue 'Save Current Value On Each Field Tag
> > cmdBtnFalse 'Disabled all cmdButton except for cmdSave & cmdUndo
> >
> > -------------------------------------
> > then Save... where the problem is!
> >
> > cmdSave_Click
> >
> >     Dim cn As ADODB.Connection
> >     Dim strContactsSQL As String
> >     Set cn = CurrentProject.Connection
> >     Set rs = New ADODB.Recordset
> >
> >
> >     If Me.txtMODE = "New" Then
> >   strContactsSQL = "SELECT * FROM m01TblContacts"
> >   rs.Open strContactsSQL, cn, adOpenKeyset, adLockOptimistic
> >         rs.AddNew
> >         SaveCurrentValue
> >         rs.Update
> >         Me.m01ContactsID = rs("m01ContactsID")
> >     Else 'Edit
> >   strContactsSQL = "SELECT * FROM m01TblContacts WHERE m01ContactsID=" &
> Me.m01ContactsID
> >   rs.Open strContactsSQL, cn, adOpenKeyset, adLockOptimistic
> >         SaveCurrentValue
> >         rs.Update
> >         cmdBtnTrue
> >     End If
> >
> > ----------
> >
> > Private Sub SaveCurrentValue()
> >
> >         If Not IsNull(Me.m01Title) Then rs("m01Title") = Me.m01Title
> >         If Not IsNull(Me.m01FirstName) Then rs("m01FirstName") 
> Me.m01FirstName
> >         If Not IsNull(Me.m01MiddleInitial) Then rs("m01MiddleInitial") 
> Me.m01MiddleInitial
> >   etc...
> > end sub
> > -----------
> >
> > From here everything's fine...
> >
> > The problem is after I saved the changes I made, I losed the records
> absolute position, it returns to Record 1 of 1 instead of
> > Record 8 of 122. As far I can see the problem originated when I overwrites
> the strContactsSQL and I can't figure out how to resolved
> > it.
> >
> > Basically out this long story, my main objective only is to:
> > 1) Return my original RS based on the frmSearchAdvance
> > 2) Return the records absolute position after edit/save and continue the
> nos. while navigating.
> >
> > Sorry for this novel...
> >
> > Any help would be highly appreciated.
> > TIA,
> > George
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
>
>