moving record to record using DAO.movenext

[northernbeaver]

I have an unbound form with navigation command buttons at the bottom of them. what is the best way to move from record to record. right now I have a field on the form that is the unique key to the current record. on click of the button I will create a recordset of the table, search through until I find the current unique key. once found move to the next record then repopulate the form. this seems a bit slow to me and a lot of searching going on. is there a better way Im missing? should I keep the recordset active and bookmark the current record and move next when the button is clicked? is that even possible?
if it is what event of the form would I declare the recordset? on load?

 

[Crowley16]

why would you possibly want to do it this way?

you can just set the recordsource of the form to bound to the "next" record

 

[Crowley16]

why would you possibly want to do it this way?

you can just set the recordsource of the form to bound to the "next" record

or why not just keep the form's built in navigation buttons for moving to the next and previous records

 

[LittleSmudge]

I have a field on the form .. ..

No you don't.

You have a field in the underlying TABLE
You have a CONTROL on the Form.

move to the next record
How do you define NEXT ?
The sequence of records in a table contains no information. Therefore 'next' can be based on the last sort that some other user applied to the table.

What do you mean by 'next' ?


Along with Crowley, I struggly to perceive why you are doing such a diffecult thing. Crowley has suggested a much simpler solution - or is there a really good reason why you must have an unbound form ?





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[northernbeaver]

trust me guys I share your frustation. the controls on the forms are unboud because the main programmer at my company is addicted to it. he feels bound forms are unsafe and data can be overwritten or lost too easily. so I have to code my companies way. *SIGH*

 

[MichaelRed]

hmmmmmmmmmmmmmmmmmmmmmmmmmm ... mmmmmmmmmmmmmmmmmm ...

I share (to some degree) your ;main programmer's 'fear' of bound controls -although in many applications / instances I do use them. On the other hand their use is not neasly so complex as many make them out to be and they do porvide an additional buffer between the user and the data.

The main way I would 'bridge' the gap between the tow concepts is to use a class module for the data with the forms communicating ONLY with the class module. In this mode, the movement between records might be based on a combo box which showa the (user perspective) "Key" information re the records which the user selects and the look up is done directly on the seledcted record.





MichaelRed
mlred@verizon.net

 

[northernbeaver]

Well so far I have gone with attempting to bookmark my way through. Ive created a public variable of the variant type
under the command button for move to the next record I have the following:

Private Sub cmdNext_Click()
Dim dbs As DAO.Database
Dim rstCustomerMain As DAO.Recordset
Dim strSQL1 As String

strSQL1 = "Select * from tblCustomerMain"
Set dbs = CurrentDb()
Set rstCustomerMain = dbs.OpenRecordset(strSQL1)

rstCustomerMain.Bookmark = CustomerMainBookmark
rstCustomerMain.MoveNext
If rstCustomerMain.EOF = True Then rstCustomerMain.MoveFirst

Me.autoid = rstCustomerMain("autoid")
Me.CompanyName = rstCustomerMain("companyName")
Me.BillingAddress = rstCustomerMain("BillingAddress")
Me.BillingAddress2 = rstCustomerMain("BillingAddress2")
Me.City = rstCustomerMain("city")
Me.ProvinceState = rstCustomerMain("Provincestate")
Me.Country = rstCustomerMain("country")
Me.PostalCode = rstCustomerMain("PostalCode")
Me.Notes = rstCustomerMain("notes")
Me.ContactName = rstCustomerMain("contactname")
Me.PhoneNumber1 = rstCustomerMain("phonenumber1")
Me.PhoneNumber2 = rstCustomerMain("phonenumber2")
Me.PhoneNumber3 = rstCustomerMain("phonenumber3")
Me.CA = rstCustomerMain("CA")
CustomerMainBookmark = rstCustomerMain.Bookmark
rstCustomerMain.Close
dbs.Close

End Sub

this code works exactly once. I click the comand button it updates the form with the next record. upon clicking the button a second time I get the following error;

Not a valid bookmark. (Error 3159)

the CustomerMainBookmark is the public variable set up as a variant type. any ideas why it works one rotation and not the next?

 

[MichaelRed]

Since you recreate the rs on each instantiation of the procedure, the bookmark(s) are not (necessarily) the same in each instantation.

Further, the procedure (as shown) never moves the record pointer. Thus -even if it did not fail (on error) I see no way for it to ever complete.

Some additional 'nit picks': Since it is a "FORM" procedure the reference to me is not necessary; You can save sone typing using with rst ... end with to denote the recordset variables.

Last (and probably least) the procedure does not show any change of eitrher the form or recordset. Presumably these are in other procedures.





MichaelRed
mlred@verizon.net

 

[northernbeaver]

this is my lack of knowledge with bookmarks coming through but here goes;

If I recreate the RS the same way every time in everyplace then the bookmarks should always point to the desired record no?

this is the theory that my code is supposed to be doing
the public variable bookmark keep is a bookmark of the current record. upon pressing the next button the RS is made and then moves to the bookmark. from here I now know where I am, I use the .movenext command to go to the next record. I update the fields on the form with the new RS info then overwrite the bookmark with the new current record then close the recordset. as i said this process seems to work once with no error but i dont think my code is resetting the public variable with the new recordset. any ideas?

 

[MichaelRed]

Re-read the treatsie on bookmark.





MichaelRed
mlred@verizon.net