Remembering position after requery

[Reynardine]

Hi Guys,

I have a problem with a pretty simple little databse I am putting together, which I hope someone on here would be able to help me with.



Basically, I have a form that is based partially on a query, which I want to requery whenever they click "done". However that particular record will not longer be in the recordset so I cannot use the current ID as the reference to go back to after the requery. So the sequence would be to 1. look at the current record and remember that position (not the ID name as it will be different after the requery), 2. Requery the form, 3 go to that record on the form.

I have been struggling to get the code to work though. What I have so far though is:

'update the table with the latest result
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngPK As String
Set db = CurrentDb
Set rs = db.OpenRecordset("T_setups")
With rs
.AddNew

![Customer ID] = Me!Customer_ID
![Advisor] = Forms![Main]![Agent_filter]
![Call 1 date] = Me!Date
![Call 1 outcome] = Me!Outcome
![Resolved] = Me!Resolved
![Notes] = Me!Notes
![telephone] = Me!telephone
![Terminal type] = Me!TerminalType
.Update

End With

Me.Requery

Me!Notes = ""
Me!Resolved = False
Me!Outcome = ""
End Sub

As you can see though, with this it just keeps going back to record one after each requery. One of the criteria of the query is that this ID does not exist in the T_setups_ table, so after each requery, this one will disappear. I am also struggling with making changes via the DOA recordset and making the actual form go to that record thorugh the docmd.gotorecord method.

Anyway, any help or advise you can offer will be greatly appreciated.

[orange]

In plain English WHAT are you trying to do?

You say

'update the table with the latest result

but in database terms
you don't Update a table per se, you update records.

It appears you are appending to a table. That is, INSERTing a new record into an existing table.

[Reynardine]

Yes, it appends the details from the form (based on a query of several different tables) onto a new table called T_Setups.

One of the criterias for the form which this is based on is that the record does not exist in T_Setups, so in order to make it disppear from the records they can scroll through via the form I am needing to requery it. I just want it to make sure it does not go to the beginning each time this happens, but instead goes to the next record in the recordset the query is based on.

Thanks

[RuralGuy]

Perhaps this link will supply some answers for you: http://www.baldyweb.com/Requery.htm

[Reynardine]

Thankyou. This is kind of what I was looking for,a nd I have aplyed around with it a bit already.

The only problem I am having is that I want to refer to the Position of the pointer rather than the ID, like in the example. It is because that ID will no longer be available after the requery.

Do you know if there is any way to do that? Position in the recordset rather than the ID of the record?

[RuralGuy]

A Form has a CurrentRecord property (read only) that you could probably get creative with and use. It might require a loop to get back to where you were in the Recordset.

[Reynardine]

I have sort of made a little question. I have added the code:

currentrecord = Form.currentrecord

DoCmd.GoToRecord acDataForm, "main", acGoTo, currentrecord

Howvere I am struggling with it saying error 2105 - you cant go to the specified record

Anyone have any idea why this is?

[RuralGuy]

Start by *not* naming two different objects the same. What value is being returned by the "currentrecord = Form.currentrecord" line?

[Reynardine]

It is returning the correct number. It returns the number of the record I am on when I run the script. For example, 7.

I should probably rename it though, so thanks for that.

[RuralGuy]

Do you have your line of code just *after* a Requery?

[Reynardine]

I think the requery would have to come between the two, however I have removed the requery for the sake of testing it. With or without it, it gives the same error though.

[RuralGuy]

Where do you have the code? Did you put it behind a Button Click event?

[Reynardine]

It's a click event on the form

[RuralGuy]

Do you want to post your db for review? Compact and Repair and then Zip it up to post in the advanced posting section.

[RuralGuy]

Did you solve this thread or just go on to something else?