Retrieve Record based on criteria in form from table

[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.

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!!!!!!!

 

[insideout786]

Wayne-I-M
I am trying to retreive data on the form from the table
hope this helps!

 

[insideout786]

Ohh, I see...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.


Hope this is helpful!

 

[John Vinson]

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.

There *IS* no record "on the form but not in the table". Records exist
in Tables and *ONLY* in tables! The form is just a window.

You might temporarily change the contents of a textbox on the Form,
but moving off the record or closing the form should generate an error
when it fails to save the record, unless you've turned off warning
messages. Do you have any macros or code which include SetWarnings
False?

John W. Vinson[MVP]