command buttons cmdPrevious & cmdNext not navigating to records

[moises]

Hi guys,



I have a custom command button cmdPreviousRec and cmdNextRec for navigation, but when i click the cmdPreviousRec its not navigating to the records and the cmdNextRec is up to 2nd record only.

Please can anyone help me.

Code below is the i've working:


Private Sub cmdNextRec_Click()

On Error Resume Next

Set db = CurrentDb

Set rs = db.OpenRecordset("SELECT * FROM tblAMHMace")

If Not rs.EOF Then

rs.MoveNext

Me.txtReferenceNo = rs!ReferenceNo

Me.txtDate = rs!DateLog

Me.txtDocType = rs!DocType

Me.txtSubject = rs!Subject

Me.txtRecipient = rs!Recipient

Me.txtCompany = rs!Company

Me.cboInitiator = rs!Initiator

Me.cboStatus = rs!Status

Me.txtPreparedBy = rs!PreparedBy

ElseIf rs.EOF Then

MsgBox "No more records in the database...", , "Database Information"

rs.MoveLast

End If

rs.Close

Set rs = Nothing

End Sub

Private Sub cmdPreviousRec_Click()

On Error Resume Next

Set db = CurrentDb

Set rs = db.OpenRecordset("SELECT * FROM tblAMHMace")

If Not rs.BOF Then

rs.MovePrevious

Me.txtReferenceNo = rs!ReferenceNo

Me.txtDate = rs!DateLog

Me.txtDocType = rs!DocType

Me.txtSubject = rs!Subject

Me.txtRecipient = rs!Recipient

Me.txtCompany = rs!Company

Me.cboInitiator = rs!Initiator

Me.cboStatus = rs!Status

Me.txtPreparedBy = rs!PreparedBy

ElseIf rs.BOF Then

MsgBox "Moving past the first record...", , "Database Information"

rs.MoveFirst

End If

rs.Close

Set rs = Nothing

End Sub

[John_G]

Both your command buttons reopen the recordset each time they are clicked clicked, so they always start at the "first" record. You should only open the recordset once (in form load), and have all the code reference the same recordset, after it has been opened.

Is there any particular reason you are doing things this way - does the form have a specified record source?

[moises]

Because when i remove the recordset in the subject buttons no record is displayed. Below code is the form_load code:

Private Sub Form_Load()

Set db = CurrentDb

Set rs = db.OpenRecordset("SELECT * FROM tblAMHMace")

cmdSave.Enabled = False

rs.MoveFirst

Me.txtReferenceNo = rs!ReferenceNo

Me.txtDate = rs!DateLog

Me.txtDocType = rs!DocType

Me.txtSubject = rs!Subject

Me.txtRecipient = rs!Recipient

Me.txtCompany = rs!Company

Me.cboInitiator = rs!Initiator

Me.cboStatus = rs!Status

Me.txtPreparedBy = rs!PreparedBy

rs.Close

Set db = Nothing

Set rs = Nothing

End Sub

[John_G]

First, declare the variables db and rs with a Dim statement in the declarations section of the form's code, at the top, and outside of any procedure.

Dim db as Database, rs as Recordset.

You do this so that any procedure in the form code will recognize those variables.

Then, in the On Load procedure, open the recordset and populate the form fields, but do not close the recordset or the db. Take out the three lines

rs.Close
Set db = Nothing
Set rs = Nothing

and put them in the On Close event of the form.

[June7]

Why are you using an unbound form?

[moises]

Hi John,

Big thanks for the help. Now my navigation button is working properly.

Merry Christmas

[peterFisp]

Hi guys,
New to the forum but not to Access. While I used it since the mid-nineties (for what I considered some fancy stuff; and still use some through a Virtual Machine with XP Pro), I never really got the hang of VB and what I need to do to properly address a database.

So here is my question: As for the above, I am trying to add custom navigation buttons to my one form. When I simply add the following code:

Private Sub cmdPrevious_Click()
On Error GoTo Err_cmdPrevious_Click

DoCmd.GoToRecord , , acPrevious

Exit_cmdPrevious_Click:
Exit Sub

Err_cmdPrevious_Click:
Select Case Err.Number
Case 2105
MsgBox "Pete: you are already at the beginning", vbOKOnly
Case Else
MsgBox Err.Number & "-" & Err.Description
End Select
Resume Exit_cmdPrevious_Click

End Sub


To my knowledge, the only other entry in all of 'code' is the prefix of "Option Compare Database" at the top of the form.


Do I need things like
Set db
Set RS???
to make this work. As I said, this kind of stuff I never quite worked out (which is why my older DB will not convert higher than Access 97).
I know I am asking a lot but would really appreciate your input since I am keen to make this form work for me.

[peterFisp]

And if I may add, my form is based on a Recordset and has Recordtype of Dynaset.

[ssanfu]

Welcome to the forum peterFisp.

By posting under moises' thread to ask a question, you have done what is termed "Hi-jacking the thread".

This bad because very few, if any, will see your post because it is under someone else's name. You should start your own thread and reference moises' thread (if it relates to your problem/question).
Especially since this thread has been marked solved!


But in answer to your question, I've used this code and never had problems

Private Sub btnPrevious_Click()
    On Error GoTo HandleError
    DoCmd.GoToRecord , , acPrevious
    Exit Sub

HandleError:
    Resume Next
End Sub

[peterFisp]

Hi Paul, well there you go: you never stop learning. Hi-jacking a thread - that makes sense and I apologize, especially because over the years I have always appreciated and found very useful forums and the input one gets. I always thought opening a new thread was like opening a new carton of milk when there was already in use.

So I thank you for alerting me; and for providing input regardless. I have used DoCmd before and shall try that.

cheers; Peter - down under

[ssanfu]

Paul???? Who is Paul? My name is Steve.

I now see you are using A2003. I'll have to fire up one of the old confusers with A2000 on it and check the code.... but I'm sure it should work.
Cheers
Steve - Top of the World