Solved Continuous form scrolls to top. Can I prevent this? (2 Viewers)

[zebrafoot]

Hello everyone,

I have a form that has a subform on it. The subform is formatted to be a continuous form and shows a summary of jobs to do. The user clicks a button on the subform and the full details of the task appear elsewhere on the main form. This all works fine, except that when the user clicks the button, the subform scrolls back to the top. This is slightly annoying because the list of jobs may be quite long and ideally I'd like the user to be able to retain their position in the list whilst checking the details, rather than having to scroll back through the list.

Is there a simple way to stop the subform returning to the top?

Many thanks,
Pete

 

[MajP]

The user clicks a button on the subform and the full details of the task appear elsewhere on the main form.

Can you provide the code behind the button to do this. If you click on a record in the subform and that code move to the details in the main form for that record. the subform should stay in place unless you have code that moves it away. This could be done if you have something like a requery in the code.

In the button you can set it back to its original position. Something like

Dim CurrentID as long
'Hold the current record primary key
CurrentID = Me.YourPrimaryKeyName

... code that does the details and is probably moving the subform

' resest the position
Me.recordset.findfirst "MyPrimaryKey = " & CurrentID

 

[zebrafoot]

Can you provide the code behind the button to do this. If you click on a record in the subform and that code move to the details in the main form for that record. the subform should stay in place unless you have code that moves it away. This could be done if you have something like a requery in the code.

In the button you can set it back to its original position. Something like

Dim CurrentID as long
'Hold the current record primary key
CurrentID = Me.YourPrimaryKeyName

... code that does the details and is probably moving the subform

' resest the position
Me.recordset.findfirst "MyPrimaryKey = " & CurrentID

Thank you for taking the time to answer. For some reason, the form is now working as you described above. I don't know what has changed, but it seems ok.

 

[MajP]

It might still be worthwhile to see your button code and then we can see if there is something that may cause this under certain conditions.

 

[zebrafoot]

It might still be worthwhile to see your button code and then we can see if there is something that may cause this under certain conditions.

Yes, ok. As it happens, it doesn't work now, so I do need to get to the bottom of this.

Subform has a number of fields including JobID. When a button on the subform is clicked, I was capturing the JobID and opening the form again at that record (I'm guessing that's at the heart of the behaviour I'm witnessing).

Dim intJob As Integer
intJob = Me.JobID

DoCmd.OpenForm "frmTodayNew", acNormal, , "JobID=" & intJob

Then, a whole load of boxes that were previously invisible are made visible, so the user can see the title of the job, customer name, location etc etc.

What I actually think I need it to do is to GO TO the correct job number, but I can't work out how to do that from a button in a subform.

 

[Gasman]

I would have thought they would be linked by JobID? So selecting a subform record, would just show linked mainform data?
I have that functionality in this form, however that is actually an ESF (Emulated Split Form), which you could possible use the standard split form?

 

[MajP]

The user clicks a button on the subform and the full details of the task appear elsewhere on the main form.

That code is usually to open a new form. As you describe it, it sounds like you have a main form, with one or two subforms on it.
Is this a split form where the main form is the details and the subform is a list. If you click in the list you see the details.

If so then the code should be something like

Me.Parent.Recordset.findFirst "JobID = " & me.jobID

A subforms Parent returns a reference to the main form.

 

[zebrafoot]

That code is usually to open a new form. As you describe it, it sounds like you have a main form, with one or two subforms on it.
Is this a split form where the main form is the details and the subform is a list. If you click in the list you see the details.

If so then the code should be something like

Me.Parent.Recordset.findFirst "JobID = " & me.jobID

A subforms Parent returns a reference to the main form.

Hi MajP,

That works perfectly! Thanks once again for your assistance, it's very much appreciated.

Pete

 

[MajP]

DoCmd.OpenForm "frmTodayNew", acNormal, , "JobID=" & intJob

FYI, I had to check this in chat because I would not think it would do anything so I am suprised it worked at all.

in access if I call the docmd.open form from the subform for an already open form main form and pass a criteria will it go to that record and will it cause a requery

So it should not go to that record and should not cause a requery.

Behavior of with criteria when the main form is already open
• If the main form is already open and you call from the subfomr
DoCmd.OpenForm "frmMain", , , "ID=123"

Nothing happens

• Access will not requery the form’s recordsource.
• The argument is ignored once the form is already open.
• The form simply gets focus — it does not jump to the record you specified.

 

[zebrafoot]

FYI, I had to check this in chat because I would not think it would do anything so I am suprised it worked at all.


So it should not go to that record and should not cause a requery.

I've used it for a couple of things in a different database where I couldn't work out the correct way to go to a given record.
Just for my understanding, if I'm in a form and want to go to a specific record (assuming unique ID), is the correct syntax:

me.recordset.findfirst "IDwanted=" & somenumber

?

 

[MajP]

I can think of at least three ways
1 recordset.findfirst
2 set the bookmark
3 use docmd.goto record

with a bookmark

With Forms!frmMain.RecordsetClone
    .FindFirst "ID = 123"
    If Not .NoMatch Then
        Forms!frmMain.Bookmark = .Bookmark
    End If
End With

with a docmd

' Go to a specific record number (e.g., 5th record)
DoCmd.GoToRecord , , , 5

In your example if the list allows additions, there is a possibility of clicking on the last row "new record". The jobID is null

SO you may want to check that so you do not cause an error.

If not isnull(me.jobID) then
 Me.Parent.Recordset.findFirst "JobID = " & me.jobID
end if

 

[MajP]

FYI,
This sounds like a "split form". A detail section and a continuous section. If so you may like

Emulating the split form

Like many users of this forum, I abandoned split forms a long time ago as I found them difficult to adapt if you want to modify them in any way In a recent thread another user wanted to keep the idea but to emulate it using a single form with a continuous subform. See this thread...

www.access-programmers.co.uk

 

[zebrafoot]

SO you may want to check that so you do not cause an error.

If not isnull(me.jobID) then
 Me.Parent.Recordset.findFirst "JobID = " & me.jobID
end if

Great, that's incredibly helpful.