Navigating Records on unbound form with VBA

[redbull]

I am facing an odd issue, I have a form that reads records from a query.. It loads the first record into the form, without issue.

Sub FormLoaded()
Dim r As DAO.Recordset


Set r = CurrentDb.OpenRecordset("Results") ' Query we want


 Forms("frmmainnew").lbladdUser1bad.Visible = False
  Forms("frmmainnew").lbladdUser1bad.Visible = False
'load first Row into form
  Forms("frmmainnew").txtaddUser1.Value = r![f13] & " " & r![f14]
  Forms("frmmainnew").txtphone1.Value = r![F11]
  Forms("frmmainnew").txtRoles.Value = r![F15]
  Forms("frmmainnew").Text305.Value = r![F19]
  Forms("frmmainnew").Text308.Value = r![F16]
  Forms("frmmainnew").Text311.Value = r![F3]
  Forms("frmmainnew").Text299.Value = r![F41]
  Forms("frmmainnew").Text316.Value = r![F2]
  'Forms("frmmainnew").Text311.Value = Replace(r![F3], ",", "") & ";"
  'Forms("frmmainnew").Text311.Value = Replace(r![F3], ", ", ";")
  
 ' Forms("frmmainnew").Text311.Value = Replace(r![F3], ", ", ";")
  r.Close
  Set r = Nothing
End Sub

Now I have have 2 buttons at the bottom of the form, one for next record and another for previous record...


Code for next record

Sub NextRecordbtn()
Dim r As DAO.Recordset


Set r = CurrentDb.OpenRecordset("Results") ' Query we want
r.MoveNext


If r.EOF = True Then
r.MoveFirst
End If

  Forms("frmmainnew").lbladdUser1bad.Visible = False
  Forms("frmmainnew").lbladdUser1bad.Visible = False
'load first Row into form
  Forms("frmmainnew").txtaddUser1.Value = r![f13] & " " & r![f14]
  Forms("frmmainnew").txtphone1.Value = r![F11]
  Forms("frmmainnew").txtRoles.Value = r![F15]
  Forms("frmmainnew").Text305.Value = r![F19]
  Forms("frmmainnew").Text308.Value = r![F16]
  Forms("frmmainnew").Text311.Value = r![F3]
  Forms("frmmainnew").Text299.Value = r![F41]
  Forms("frmmainnew").Text316.Value = r![F2]
  r.Close
  Set r = Nothing
End Sub

The problem I am having is weird... I know that I have 3 records that result in the query named "Results", the next record button will bring the form from record 1, to record 2... however it will not move from the 2nd to the 3rd record...I am sure it is something super simple, but I cannot figure it out for the life of me.

[RuralGuy]

Is .EOF True when you are on the Last Record? Why are you using an Unbound form?

[redbull]

If I run this...

r.MoveLast
If r.EOF Then
msgbox "last recrod"
End If

it does recognize the last record as EOF and is true. However, when using r.movenext it will never move to the last record.

[RuralGuy]

The code you published says "If .EOF Then .MoveFirst End If"
Again...why do you feel you need an Unbound Form. Is it just a test?

[redbull]

The code published does not ever get to the end of the file. It gets to the record before the end of file, or EOF -1 if you will. I don't really know why I'm using an unbound form, might not have even been worth mentioning.
I guess to sum it up.. r.movenext will not move the the last record. I have debugged it, but the code runs thru without actually moving to the last record...I don't know what I'm doing wrong at this point.

[RuralGuy]

So you single stepped your NextRecordbtn() code?

[redbull]

Yea, it went thru, but did not actually move to the next record.

[RuralGuy]

Did it jump over this part?
If r.EOF = True Then
r.MoveFirst
End If

[redbull]

Yea, it jumps over it. does r.movenext not have the ability to move to the last record?

[RuralGuy]

I just verified that r.MoveNext is able to move to the Last Record. It bothers me that you are creating a NEW Recordset every time you invoke that function. When you create a Recordset it is set to the 1st record. Your NextRecordbtn() should just stay on the 1st record.

[redbull]

How do I correct that?

[RuralGuy]

How about binding this form to the Query/Table? It will eliminate all of this code. Working with Unbound forms adds quite a bit of overhead for the programmer.

[rpeare]

I was thinking the same thing as RG, you're closing the dataset every time you retrieve a recordset so you have no reference in your data WHERE in the recordset you were 'before' so it doesn't know where to go 'next'. Is the data in your recordset sorted in a specific way? For instance if your table has a PK and you are ALWAYS sorting data based on that pk you can, instead of pulling from a recordset that has ALL your records pull from a dataset that has 1 record, that being the PK immediately prior to, or subsequent to the record you're currently viewing. But this would only work if you can reliably sort your data the same way every time.

[RuralGuy]

Another solution if you must use an unbound form is to open the Recordset into a Public variable and keep it open while the form is open and then close it when you close the form.

[redbull]

So the code below would not be used? I guess I don't understand why it will move to the next record, as long as the next record is not the last record.

Sub NextRecordbtn()
  Dim r As DAO.Recordset


Set r = CurrentDb.OpenRecordset("Query")  ' Query we want




If Not r.EOF Then
  r.MoveNext
End If


If r.EOF Then
  r.MoveFirst
End If


  Forms("frmmainnew").lbladdUser1bad.Visible = False
  Forms("frmmainnew").lbladdUser1bad.Visible = False


  Forms("frmmainnew").txtaddUser1.Value = r![f13] & " " & r![f14]
  Forms("frmmainnew").txtphone1.Value = r![F11]
  Forms("frmmainnew").txtRoles.Value = r![F15]
  Forms("frmmainnew").Text305.Value = r![F19]
  Forms("frmmainnew").Text308.Value = r![F16]
  Forms("frmmainnew").Text311.Value = r![F3]
  Forms("frmmainnew").Text299.Value = r![F41]
  Forms("frmmainnew").Text316.Value = r![F2]
r.Close
Set r = Nothing
End Sub