GoToRecord in subform

[squinn03]

One of my forms needs to trigger a sub-form (actually it's a sub-sub-form) to navigate to a specified record.

To accomplish this, the main form called a procedure inside the sub-form, which uses a "GoToRecord" to navigate to the appropriate record.

Interestingly enough, it's the main form navigates to the specified record and not the sub-form (I need the main form to stay at the current record and the sub form to go to the specified record).

Even if I explicitly call "setfocus" on the sub form it still does not work. I tried callin "GoToControl", but it gives an error message that "The Command or Action 'GoToControl' is not available now".

Any ideas

Thx

 

[pbaldy]

I don't use that much, but what is your exact code? I seem to remember that you can specify the form.

 

[squinn03]

'This code is in the sub-sub-form and is called from the main form:

'Forms!Mainform.SetFocus

'Goto subform then Goto subform's subform
' DoCmd.GoToControl "subform"
' DoCmd.GoToControl "subsubform"

'Find the chosen Record (according to Antenna Number)
'Go to the Record

If Not IsNull(...) Then
intRecNum = Find_Rec_Number(...)
Me.Requery

For i = 1 To intRecNum - 1
DoCmd.GoToRecord , , acNext
Next


End If

'The Above code takes the main from to found record.
'If the DoCmd's are added, the error "Can't go to control" occurs (because it's already at the control?)

 

[pbaldy]

Well, I was remembering the arguments for DoCmd.GoToRecord, which would seem to allow you to specify the form whose records you want to manipulate:

DoCmd.GoToRecord acDataForm, "FormName", acNext

 

[squinn03]

Thanks for the tip.

Now I'm getting the error "the form 'sub-sub-form' is not open"

I have a similar example elsewhere in the database where the subform name was not specified in the gotoRecord (because the gotoControl actually worked).

 

[pbaldy]

Because it's a subform you'd have to use the full reference, not just the name of the form. Good reference here:

http://www.mvps.org/access/forms/frm0031.htm

If that doesn't work, can you post a sample db?

 

[squinn03]

Not quite sure how to pass the "full reference" (since i need to pass a string as the form name for the gotoRecord).

I tried using something like "Forms!Mainform!Subform1.Form!Subform2.SourceObject" but it still says that the (sub-sub)form is not open.



Sorry, can't post this DB

Thx

 

[KeithG]

I believe your original code will work if SetFocus to your subForm instead of your Main form. Wouldn't it be easier just to filter the subform?

 

[squinn03]

SetFocus on the sub form does not help. The main form still navigates to the record.

I'm not sure if I can do filtering as that would require some major changes. I'm just trying to fix a bug right now, which is that a form refresh causes the sub-sub-form to go to the first record. I thought i could just use a goto record as a quick fix.

please see my original post:


http://www.access-programmers.co.uk/forums/showthread.php?t=127080

thanks again

 

[squinn03]

GoToControl can be used to go to the subform, however, GoToControl will not work for the sub-sub form (whether I go to the sub form first or not).

so how can I get the focus to the sub-sub form (given that SetFocus() does not work). Also, using GoToRecord and passing the sub-sub form also does not work.

 

[Webbenstein]

Hello,

For anyone who comes across this thread here is my method for solving this problem...

In your subform add a public sub like so:

Public Sub GoToLastRecord()

    'Go to the last record in the subform.
    DoCmd.GoToRecord , , acLast

End Sub

And then in your main form call it like so:

'Go to the newly added record.
Forms!frmMyForm!subfrmMySubForm.Form.GoToLastRecord

And your subform will navigate to, in this case, the last record.

Hope this helps

 

[simcha80]

Can't get it to work

Webbenstein,

I'm trying to do something similar, except that I want the subform to go to a new record (to save the user scrolling down).

When I try to start out by applying your code, I get the following error message:

Run-time error '2445':

You entered an expression that has an invalid reference to the property Form/Report.​

So how did you get it working?

Thanks.

 

[pbaldy]

What is your code and what line does it stop on?

 

[simcha80]

Code is exactly the same as Webbenstein's, except with my forms' names substituted in.

It stops executing on the line:

Forms!frmMyForm!subfrmMySubForm.Form.GoToLastRecord

 

[boblarson]

Okay, two things

1. Is the subform CONTAINER (the control housing the subform on the main form) actually named subfrmMySubForm? If not, you should be using that name instead of the subform name.

2. The bangs are messed up here. You should use:
Forms!frmMyForm.subfrmMySubForm.Form.GoToLastRecord
(Only 1 bang because you are referring to the forms collection but after that you aren't referring to a collection).

 

[pbaldy]

Is GoToLastRecord a public sub in the subform? Is subfrmMySubForm the name of the subform control, in case it's different than the name of the subform itself?

 

[boblarson]

Is GoToLastRecord a public sub in the subform?

Good point on this.

 

[peterlee516]

Hi guys,

I'm sure there is an easy solution to this but I don't know it. haha

I have a form and a subform. I have a command button that I want to automatically populate 2 values into my subform.

I've tried.
1)Forms![tblGLCCtransactions subform]![AllocationAmount] = Me.InvoiceAmount

2)Me.tblGLCCtransactions_subform.Setfocus
Forms![tblGLCCtransactions subform]![AllocationAmount] = Me.InvoiceAmount

3) And finally with what I read on this thread...
DoCmd.GoToControl "tblGLCCtransactions subform"
Forms![tblGLCCtransactions subform]![AllocationAmount] = Me.InvoiceAmount

Does not work. Can anyone help?

Thank again for everyone's help!

I've learned a great deal these past few months.

 

[pbaldy]

Here's a good reference tool:

http://www.mvps.org/access/forms/frm0031.htm

 

[peterlee516]

Thank you for the reference.

I'm still getting a "can't find" message

I've tried:

1)Forms!MAINFORM!tblGLCCtransactions_subform.AllocationAmount = Me.InvoiceAmount

2)Me!tblGLCCtransactions_subform.AllocationAmount = Me.InvoiceAmount


Do I need to add this (DoCmd.GoToControl "tblGLCCtransactions subform")?