Navigating Records on unbound form with VBA

[redbull]

Alright, I understand what your saying. Because it is reconnecting to the data every time.. move next will one move +1 from the first record.. has nothing to do with the last record at all. SO how would I fix something like this? and yea, I have to use an unbound form for this.

[RuralGuy]

Because of this section of the code:

If Not r.EOF Then
  r.MoveNext
End If

[redbull]

I guess I am still not understanding...I tried this and received the same result..

Sub NextRecordbtn()
  Dim r As DAO.Recordset


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


r.MoveNext


     '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

[redbull]

I think I found a solution... I added a loop that will make sure the current record is the same that the form displays.. after that is determined.. it will r.movenext... what do you think?

Sub NextRecordbtn()
  Dim r As DAO.Recordset


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


Do Until Forms("frmmainnew").txtaddUser1.Value = r![f13] & " " & r![f14]
r.MoveNext
Loop


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

[RuralGuy]

If it works the way you want then go with it.

[redbull]

Alright, marking this headache as solved. Here is the final piece of the code.

Sub NextRecordbtn()
  Dim r As DAO.Recordset


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


Do Until Forms("frmmainnew").txtaddUser1.Value = r![f13] & " " & r![f14]
r.MoveNext
Loop


If r.EOF = False Then
  r.MoveNext
End If


If r.EOF = True Then
  r.MoveLast
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

[rpeare]

what you're doing is opening the same recordset over and over again, reading the first record and populating the form with that record

Let's say your query has the following records:

Query
Record 1
Record 2
Record 3

You are opening this query

when you open your query it defaults to the first record (RECORD 1)
If you're not at the EOF, you're reading a line (r.movenext)
This takes you to RECORD 2
If you're at the EOF you're going to the first record

Then you're populating your form with the lookup values.

The code as you have it will *ALWAYS* return the 2nd record in your recordset unless your recordset is 1 record.

This is what I'm saying if you're determined to use your query as is it has to be sorted in the same order every time so you can figure out through your code where in your recordset you are

Let's assume for the sage of argument that there is a PK inclucded in your query "Query" and all your data is sorted by that PK
Further, let's assume that your form has a field called [PKField] which shows the pk (the field can have it's visible property set to false)

Set r = CurrentDb.OpenRecordset("Query")  
' Query we want 
If isnull(PKField) then     
'populates the data if there is no current record
    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]     
    Foms("frmmainnew").Text311.Value = r![F3]     
    Forms("frmmainnew").Text299.Value = r![F41]     
    Forms("frmmainnew").Text316.Value = r![F2]
Else
    'cycle through all records in your recordset until the PK is greater than the current PK
    do while r.eof <> true
        if PKField <= r.fields("PK") then
            r.movenext         else
            'populates the data if there is no current record
            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
            exit sub
       End if
   loop 
end if

You'll have to modify it so that if you are at the last record it cycles to the first but that's fairly simple to do if you do a record count first, compare the record count to the current record count and if they're equal go back to the first record.

I'd caution you that if you have a lot of records in your query "Query" this will be very inefficient because it will have to cycle through all records every time you want to switch records.

You are far better of limiting your query to one record and finding a way to draw the critera from it from your form rather than cycle through data every time you want to next/prev.