How to invoke a procedure within a subform

[dameeti]

I have been trying to invoke a routine within the subform of a form, to no avail.

The routine I'm trying to call will do a search within the records of the subform's table, but when the routine is started, it fails to work the same as when it's called by a command button within the subform itself.

It's become obvious that it works differently when called from code from within the main form vs. by a control method when invoked by a control within the subform.

The routine within the subform in question is simple: set the focus ("desiredField.SetFocus") on the desired field, and do a search ("DoCmd.Findrecord desiredValueVar") for a particular value within that field.

So when I hit the button on the subform that invokes the said routine, all is good. However, when I enter the value in the combobox on the main form that then invokes that same subform routine, the routine starts, but it fails to find that exact same value within the subform that worked when called from within the subform.

Any thoughts and/or suggestions? Thanks in advance.

 

[jpstroud]

What event do you have the subroutine firing off of? If it's firing too early, it would give you the wrong results (ie, if it's on OnChange). Setting it to AfterUpdate may work for you.

Otherwise, try posting the code as you have it now.

 

[PHV]

Take a look at the RecordsetClone, FindFirst, NoMatch and Bookmark methods/properties of the Form and DAO.Recordset objects.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dameeti]

Actually, the routine being invoked is part of a routine in the main form, after the recordset has been established. The main form uses the primary table, and the subform is the collection of records related to the current record in the main form.

I have have the code in front of me, but in essence, the routine in the main form takes a value from a combo field -- the desired item number -- finds the customer that owns that particular item number. It then creates a recordset with all records owned by that customer -- and that works just fine.

THEN I try to invoke one of the subform's routines that searches within subform's table of item numbers and attempts to position the table so that the entered item number is the current record within the subform.

Recall that this same routine works fine when triggered by a command button within the subform, but does fail when it's invoked by a main form's routine after it establishes the recordset -- so it's not a case of the trigger being "too fast" (at least I don't think so).

Any thoughts? (I hope I clarified and didn't instead confuse here.) Again, thanks for any help.

 

[jpstroud]

Is it failing due to invalid references? When calling a subform control, sub, etc, you need to refer to it as Forms!MasterForm.Subform. With the Subform name being the name of the subform control (not always the name of the subform itself - find it by pulling up the properties of the subform while in design view of the main form). Whereas, on the subform itself you wouldn't have to refer to it in this particular way and therefor would get an error calling it from the main form, but not the subform.

 

[dameeti]

No, the subform's routine was successfully invoked. I know because a MsgBox message appeared (from within the called routine) as predicted, so I know I got to it okay. Then the SetFocus worked (well, no errror message appeared), but then the actual DoCmd.FindRecord call within the subform routine failed to find anything -- no error that I'm aware of -- but the subform's record position didn't appear to change, so it was left at the first record within the recordset.

I'm not at work right now, but I'll post the actual code later today so you can see the particular code used.

 

[jpstroud]

Can you post the code that you're calling so we can take a peek at it?

 

[jpstroud]

Just a thought, and I'll know more once I see the code, but I'm thinking the subroutine is being called correctly, but it's searching your main form instead of your subform as you expect.

 

[dameeti]

In the main form:

Call [Listings].Form.FindABItem(itemNumber)

In the sub-form:

Public Sub FindABItem(ABItem As Integer)
On Error GoTo Err_FindABItem
.
.
.
    MsgBox "Starting at item #" & Me.Item_Number & " and searching for #" & ABItem
    Me.Item_Number.SetFocus
    MsgBox "Focus has been set."
    DoCmd.FindRecord ABItem
    MsgBox "Ending the actual search."
.
.
.
Err_FindABItem:
    MsgBox "Error: Unable to find Item #" & ABItem
.
.
.
End Sub

FYI, ABItem is passed into the routine and it showed up correctly in the MsgBox debug message.

When the code in the sub-form is invoked by a button from within the sub-form, all is well and it finds the desired record. Also the first record's value (as shown in the first MsgBox also was shown correctly, so the values are being reported accurately.

When the code in the sub-form is invoked by the combo field’s AfterUpdate (in the main form), it fails to find the same value.

After running it again with the debug as shown, I find that I was wrong in the initial message – it does invoke an error. The routine triggered by the On Error did kick in with an error that the file was unable to be found – the first two MsgBox messages appeared, but not the third, telling me that it was the FindRecord that failed and triggered the error.

Any thoughts or suggestions?

 

[PHV]

Have you tried to set the focus to the subform control before calling the sub ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jpstroud]

See my earlier note. You're incorrectly referencing the Item_Number control. You need to do as PHV suggested and move the focus to the subform before trying to do the search, or pass the subform control name as a variable when you run the subroutine.

 

[dameeti]

My first reaction would be to say maybe you're right....

But the SetFocus is called within the called routine and no error occurs -- I know this because the next line is a MsgBox and it executes (i.e., I get the msg in the screen). It's not until the NEXT line (the FindRecord) that the On Error triggers.

Based on that, I don't believe the error is within the SetFocus call.

Or WILL the SetFocus even trigger an error condition if it fails?

Any other suggestions?

 

[PHV]

Based on that, I don't believe the error is within the SetFocus call
Have you even tried ?
Another note: have you done some research based on my first post ?

 

[dameeti]

Okay, the most recent update on this problem.

I'm able to successfully call the routine within the subform. Under some circumstances, the .SetFocus does appear to work -- well, I don't get an error, and I know this because the following line (a MsgBox does execute and I get the expected message.

What I tried (and seemed to get me into the routine):

Forms!frmMainFormName.Form!RoutineName(NeededParameter)

But after more detailed attempts, it appears that the .SetFocus within the subform's code doesn't seem to have any effect (well, not the desired one).

So I tried some new things. For one, I performed the .SetFocus within the main form's code instead. It triggers the error statements within that routine, so that's an utter failure. :-( I've tried:

Forms!mainFormName.Form!fieldName.SetFocus

... but it doesn't seem to work properly. The VBA editor doesn't seem to think it's syntactally incorrect but I suspect I might not have the syntax in the proper form.

My first question: what would be the proper calls for me to 1) set the focus onto a field in the subform (from the main form), and 2) trigger the FindRecord command (from the main form but search within the subform).

Since it's only two lines, I guess I wouldn't have a major problem with putting it directly into the main form -- but I also would like to know how to successfully invoke the subform's routine for the future (so when it gets more complicated, I know the proper direction), so any ideas there as well would be appreciated.

And Merry Christmas to all.

 

[TheAceMan1]

How are ya dameeti . . . . .

To circumvent the focus problem, insert the [purple]purple[/purple] where you see it:

[blue]   MsgBox "Starting at item #" & Me.Item_Number & " and searching for #" & ABItem
   
   [purple][b]If Screen.ActiveControl.Parent.Name <> "subFormName" Then
      Forms!MainFormName![b]subFormName[/b].SetFocus
   End If[/b][/purple]
   
   Me.Item_Number.SetFocus
   MsgBox "Focus has been set."
   DoCmd.FindRecord ABItem
   MsgBox "Ending the actual search."[/blue]

See Ya! . . . . . .

 

[dameeti]

Okay, asusming that fixes the problem (I won't know until I get back to work on Monday), why is this even needed?

The implciation being that the current active control isn't the form in which the routine is executing. I never even considered that.

Since the previous lines (e.g., the MsgBox inside the routine) successfuly executed, I figured that it became active. Are my assumptions wrong? (I'm guessing so.)

Is there any disadvantage to always putting in the explicit .SetFocus in the routine, rather than test first?

Also, could I, instead of your suggested addition, just add the following code instead:

Forms!subForm.SetFocus

... and be done with it -- or do I have to reference it through the main form as:

Forms!frmMainForm!subForm.SetFocus

Final question for this post:
What would be the proper syntax for the two statements if I tried to execute them within the main form's code. I'm guessing:

Forms!frmSubForm.Form!txtField.SetFocus
DoCmd.FindRecord DesiredValue

Am I even close? Or do I HAVE to reference it through the main, as:

Forms!frmMainForm!frmSubForm.Form!txtField.SetFocus
DoCmd.FindRecord DesiredValue

Just trying to get a handle on the possibilities and/or requirements I may have for other routines in the future.

Thanks for the help. I'll try it when I get back to work.

And Merry Christmas to all... and to all, a good night. (Gee, that so sounds good, I ought to copyright that.) ;-)

 

[TheAceMan1]

[blue]Since the previous lines (e.g., the MsgBox inside the routine) successfuly executed, [blue]I figured that it became active[/blue]. [purple]Are my assumptions wrong?[/purple] (I'm guessing so.)[/blue]

[purple]The answer is yes.[/purple]

There are three main methods by which the focus can be changed:
[ol][li][blue]The UserInterface.[/blue] User changes focus by selecting controls & objects.[/li]
[li][blue]The opening & closing of additional forms/reports.[/blue] Using an additional form as an example, when its opened, focus moves to the newly opened form. When is closed, the last opened form receives the focus back (indicated by active/inactive titebars).[/li]
[li][blue]VBA.[/blue] Specifically using the [purple]SetFocus Method[/purple] or the [purple]DoCmd GotoControl/GotoRecord Methods.[/purple][/li][/ol]
If none of the above methods occur or are being used, the focus stays where it is . . . . . [blue]and this is how it should be![/blue]

In your case, when you call your routine using the button on the subform, [blue]the subform already has the focus.[/blue] [purple]In fact the button on the subform has the focus![/purple] In contrast, when you make a selection from the combo on the mainform, [purple]the mainform (or more precisely the combo on the mainform) has the focus.[/purple]

Now . . . in cases where you have subforms (bear in mind subforms go three levels deep or: MainForm with subform - with subform - with subform) and you want to setfocus to a control, Microsoft dictates you parse thru setting the focus to the subforms until you get to the subform of interest, then set the focus to the control of interest. (In VBA help, look for [blue]SetFocus[/blue], at the bottom is a [blue]Tip[/blue] that reiterates this).

While were on the subject & justout of curiosity, [purple]what is the purpose of setting the focus to [purple]Item_Number[/purple] in your routine?[/purple] I'm sure you have your reasons, but it has nothing to do with finding the record your looking for!

As for the code I provides. Yes . . . . you could just as easily set focus to the subform from the combo (and its your choice), but thinking ahead allows for additional calls to the routine from other sources (maybe you decide to add one or more additional combos to the mainform). Each in this case would have to have a setfocus line. In this way the routine takes care of the focus itself and it becomes one less thing you have to track. Also note: you could remove the [blue]If Then[/blue] statement and just set the focus to the subform everytime. Its just my habit not to run code lines unnecessarily.

[blue]Is there any disadvantage to always putting in the explicit .SetFocus in the routine, rather than test first?[/blue]

There is no disadvantage here. In fact in a big way, its required. Unless as I said before, you repeatedly setfocus to the subform in th routine, you need to know wether or not the subform had focus at the time! . . . . Again your choice . . .

[blue]What would be the proper syntax for the two statements if I tried to execute them within the main form's[/blue]

[blue]Me!subFormName.SetFocus [green]'First the subform[/green]
Me!subFormName.Form!TextFieldName [green]'Then the control[/green][/blue]

See Ya! . . . . . .

 

[dameeti]

While we're on the subject and just out of curiosity, what is the purpose of setting the focus to Item_Number in your routine? I'm sure you have your reasons, but it has nothing to do with finding the record your looking for!

Actually, the only reason I was trying to set the focus on one of the fields was solely to find the desired record within the subform. The mere fact that you felt that it wasn't the reason I was doing it does catch my attention. Is there another way I could achieve the same results (changing records within the subform)?

I thought I had to set the focus upon the desired field before I performed a DoCmd.FindRecord -- is there another way that would be more appropriate?

Also note: you could remove the If Then statement and just set the focus to the subform everytime. Its just my habit not to run code lines unnecessarily.

Well, I agree with you about not executing code when not needed. But by the same right, doing the If test would be done every time as well, so I was thinking that since it's only one line of code, rather than do the test every time and the resultant code less often, I felt 'why not just do the .SetFocus every time and be done with it. Now if it was a series of statements, then I would've done it as you had suggested ... check once and avoid multiple statements being executed, but, what the heck, one statement doesn't sound like much of a burden.

 

[dameeti]

A follow question:

If I set the focus upon the field within the subform, does that implicitly set the focus upon the form? Or must I first set the focus on the form and then set the focus upon the field itself?

I thought I had set the focus upon the field, or is the problem that since it tried to set the focus upon the field but the subform was already in focus, it couldn't find it -- hence the error condition?

In your last code snippet:

Me!subFormName.SetFocus 'First the subform
Me!subFormName.Form!TextFieldName 'Then the control

really should've been:

Me!subFormName.SetFocus 'First the subform
Me!subFormName.Form!TextFieldName.SetFocus 'Then the control

Is this correct, or did you mean to merely reference the field without any method being invoked (which I would've thought was improper syntax)??

If this is correct (that both need the .SetFocus method call at the end of the line), then that's telling me that I need to set the focus upon each casading level (form, field, etc.) or it will fail.

Thanks for the clear explanation... you've explained a few minor (but apparently important) points I need to be aware of.

 

[TheAceMan1]

[blue]I thought I had to set the focus upon the desired field before I performed a DoCmd.FindRecord -- is there another way that would be more appropriate?[/blue]

Well . . . . in your code, you supply the [blue]data required[/blue] for the [blue]FindRecord[/blue] method to do its job. If the record is found, [blue]the method makes that record the [purple]current record[/purple].[/blue] When this occurs, two things happen:
[ol][li]The [blue]Record Pointer[/blue] moves to the current record.[/li]
[li]The focus moves from the field on the old record, to the same field on the new current record.[/li]
[li][purple]The same is true for all methods of VBA that change the current record![/purple][/li][/ol]
To specifically answer this:

[blue]No! . . . a control does not have to have the focus in order to read/write its data[/blue]

For all practical intent & purposes, don't worry about focus unless there's something specific you want to do with it, or, its required by VBA (which is rare).

Now . . . a little more heads-up on the focus code I gave. Although its executed everytime, the focus method is executed only if required. If you put the focus method in an infinite loop all by itself and watch the screen, you'll see a flicker every now and then. In fact, while your in form view, just click back & forth between the mainform and subform and you may be able to see it! . . . . Again . . . . your choice.

As to your follow-up question:

[blue]If I set the focus upon the field within the subform, does that implicitly set the focus upon the form? Or must I first set the focus on the form and then set the focus upon the field itself?[/blue]

In form view selecting a control sets the focus to that control, but yes!, that form/subform also has the focus.

In VBA No!. Its the two step process (form then control) if that form/subform doesn't already have the focus. [purple]Note: this only applies to a form with subform. Single forms of course always have the focus & no problemo.[/purple]

And yes your right,I forgot to add SetFocus to:
[blue]Me!subFormName.Form!TextFieldName[/blue]

See Ya! . . . . . .