Retrieve next record based on current record

[insideout786]

I have a table called tblCPA and Form called frmCPA. tblCPA has one
primary key called Date and foreign key called Location.

My problem is that I need to retrieve next record based on the date and

location on the form. for example if I am on the 1/1/2007 for New York,

I need to retrieve the next record which would be 1/2/2007 for New
York.


I used Do loop to find the record and setting all the form text fields
using me.date = rs!date.....etc. but that actually overwrites the
existing record and I get an error saying primary key already exist in
the table when I try to colse out of the form.

just a note :
data is not actually gone from table...but it just give me
an message that primary key can not be duplicated, when I try to close
the form. I guess that happens because the do loop overwrites the
existing record in the form but not in the table.


here is my example of me trying to retreive the record based on the
date and location


Function setForm()
Set db = CurrentDb
Set rs = db.OpenRecordset("tblcpa", dbOpenTable)
rs.MoveFirst
Do
If rs.Date = gbldate And rs.Location= gbllocation Then
Me.Date = rs!Date
Me.Location = rs!Location
Me.TotalDropOff = rs!TotalDropOff
Me.Bookmark = rs.Bookmark
Exit Do
End If
rs.MoveNext
Loop
End Function



Can someone help!!!!!!!

 

[Dirk Goldgar]

I have a table called tblCPA and Form called frmCPA. tblCPA has one
primary key called Date and foreign key called Location.

My problem is that I need to retrieve next record based on the date
and

location on the form. for example if I am on the 1/1/2007 for New
York,

I need to retrieve the next record which would be 1/2/2007 for New
York.

Two preliminary observations:

(1) It's not a good idea to have a field named "Date". That's a
reserved word, and though you *can* use it, doing so will force you into
various expedients -- keeping the name in square brackets, for
example -- to ensure that references to the field are properly
understood.

(2) Does it make sense for the [Date] field to be the primary key? That
does imply that there can never be two records for the same date. If
that's the case, fine.

Now, for what you want to do, you'd use code along these lines if you're
not working in an ADP file:

'----- start of code -----
With Me.RecordsetClone

.FindFirst _
"Location = " & Chr(34) & Me!Location & Chr(34) & _
" And [Date] > #" & Format(Me![Date], "mm/dd/yyyy" & "#"

If .NoMatch Then
MsgBox "No more records for this location."
Else
Me.Bookmark = .Bookmark
End If

End With
'----- end of code -----

 

[insideout786]

this code runs find except when I add new record in the table. After
adding new record it run the code below to find newly added record but
can't find a record even when it exist in the table.

Only when I close out of the form and come back in, that it finds the
record.
Any Ideas why?????

I have a table called tblCPA and Form called frmCPA. tblCPA has one
primary key called Date and foreign key called Location.

My problem is that I need to retrieve next record based on the date
and

location on the form. for example if I am on the 1/1/2007 for New
York,

I need to retrieve the next record which would be 1/2/2007 for New
York.

Two preliminary observations:

(1) It's not a good idea to have a field named "Date". That's a
reserved word, and though you *can* use it, doing so will force you into
various expedients -- keeping the name in square brackets, for
example -- to ensure that references to the field are properly
understood.

(2) Does it make sense for the [Date] field to be the primary key? That
does imply that there can never be two records for the same date. If
that's the case, fine.

Now, for what you want to do, you'd use code along these lines if you're
not working in an ADP file:

'----- start of code -----
With Me.RecordsetClone

.FindFirst _
"Location = " & Chr(34) & Me!Location & Chr(34) & _
" And [Date] > #" & Format(Me![Date], "mm/dd/yyyy" & "#"

If .NoMatch Then
MsgBox "No more records for this location."
Else
Me.Bookmark = .Bookmark
End If

End With
'----- end of code -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)