set focus on id after requery - continous form

[sdel_nevo]

Hi Everyone



I have a problem that I can't seem to get my head around

my main form is called "UNEXDashboard" and is built from a query, each line on the form has a button called "EditUnexButton" when this is clicked it opens the form called "UNEXDetails"

when the UNEXDetails form is closed, the unexdashboard form is re-queried and any new data is visible, this works really well but

how can I stop the UNEXdashboard form from setting the focus to the first record after the re-query, but instead keep the focus on the record that has just been closed?

any help would be fantastic.

sorry if this has been asked before

Steve

[ranman256]

1. when you click the save button to refresh, grab the ID ,refresh main screen then do a find on the ID.

2. You don't need a button on every row.(it's too busy,cluttered) a single button on form header does the same for whatever record the cursor is on.

[sdel_nevo]

Hi Ranman256

this is the code that I have been playing with
this is fired from the form UNEXDetails close button on click event


Dim rs As String
Dim Rs1 As String
Rs1 = Forms!UNEXDEtails.ID
DoCmd.RunCommand acCmdSaveRecord
Forms!UNEXDashboard!.Requery
DoCmd.FindRecord Rs1

DoCmd.Close acForm, "UNEXDetails", acSaveYes

but its giving this error

RUN-Time error '2162'
A macro set to one of the current fields properties failed because of an error in a findrecordset action argument

any ideas?

Steve

[sdel_nevo]

Hi All


I think that filtering on the continuous form may well be making things harder, for me at least as the

the first record on my form has the id of 90

I have been playing with the code and changed it to this
Dim rs As String
rs = Forms!UNEXDashboard.ID
DoCmd.RunCommand acCmdSaveRecord
Forms!UNEXDashboard!.Requery
DoCmd.GoToRecord acDataForm, "UNEXDashBoard", acGoTo, rs
DoCmd.Close acForm, "UNEXDetails", acSaveYes

This re-queries the form then goes to the 90th record on the UNEXDashboard form but this record has an id of 134

I am stuck on how to set the focus to the id

am I right in thinking that DoCmd.GoToRecord is coursing the issue here and if so what can I change it to so it's looking for the id instead?

Steve

[ItsMe]

Not sure what is going on with your error. As for your original question in post #1, try using Bookmark. Like Ranman mentioned, you need to determine what record you are currently on.

https://www.accessforums.net/forms/f...box-43718.html

[sdel_nevo]

Hi ItsMe

many thanks for the link I will look into this.

how do I determine what record I'm currently on, would this be taken from the UNEXDashboard form or the UNEXdetails form that is opened from the UNEXdashboard form?

sorry for all the questions,

Steve

[sdel_nevo]

Hi All

I think I have cracked it

Dim rs As String
Dim Rs1 As String
rs = Forms!UNEXDASHBOARD.ID
Rs1 = Forms!UNEXDETAILS.ID
DoCmd.RunCommand acCmdSaveRecord
Forms!UNEXDASHBOARD!.Requery
'DoCmd.GoToRecord acDataForm, "UNEXDashBoard", acGoTo, Rs1 'this is going to the number of records not the id, try dcount instead
Forms!UNEXDASHBOARD.Recordset.FindFirst "[id] = " & rs

DoCmd.Close acForm, "UNEXDetails", acSaveYes

[ItsMe]

You are doing this
Forms!UNEXDashboard!.Requery

So, you need to understand the current record before Requery. When you Requery, it will move to the first record. Then, go to the record you made a note of.

Doing this from another form can be difficult. But, all you need to do is reference the fully qualified name in your code (like you are doing in your existing equerry line).

The simplest approach may be to store the PK in a variable. Something like
dim myPK as Long
myPK = Forms!UNEXDashboard!.TheFieldName.Value

Now that you know the PK and it is stored
Forms!UNEXDashboard!.Requery

Now you need to move to the correct record. Maybe an ac command or something like GoTo. Not remembering exact code right now. It's been a while...

[sdel_nevo]

Hi Itsme

many thanks for getting back

this code
Dim rs As String
Dim Rs1 As String
rs = Forms!UNEXDASHBOARD.ID
Rs1 = Forms!UNEXDETAILS.ID
DoCmd.RunCommand acCmdSaveRecord
Forms!UNEXDASHBOARD!.Requery
Forms!UNEXDASHBOARD.Recordset.FindFirst "[id] = " & rs
DoCmd.Close acForm, "UNEXDetails", acSaveYes

is doing exactly what I'm after, the link you sent really helped a lot and its very appreciated.
I will mark this as closed as I'm happy that this is doing what I require.

Take care

Steve

[ItsMe]

Glad to hear. Looks like you have the swing of things!

Edit, BTW acSaveYes is not necessary and may cause problems in a RunTime only environment. If I recall correctly it is a command for saving form design not for saving data to a table.