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.

 

[dameeti]

As to your follow-up response:

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?

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. Note: this only applies to a form with subform. Single forms of course always have the focus & no problemo.

That explains a few areas that were a bit cloudy before as they apply to VBA usage.

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.

Okay, I understand your flicker point completely. Let me just clarify my usage. The code in question only executes once when the user changes customers by specifying an item owned by a customer (hence the main form will almost always be updated/repainted as a matter of course). I do a query and the new customer and its related items are the current recordset [—] and then this code attempts to select to the actual item of interest within the sub-table (hence it will also (almost always) trigger a screen repaint).

So the net result: the screen will "flicker" no matter what I do, and it will only happen only when the customer is changed when the combo box is being used by the user to perform a search. So... I don't think any flicker will matter in this case, and the code could be a tad cleaner when reading. But I do completely agree with you on the technique in general of only performing the code when it's actually needed (especially when the underlying code involves more than a simple logic test).

Hmmm... after ALL of these various back-and-forths, this appears to be the crux of the matter:

The subform's routines must always be clear about the field reference, specifically when the routine might be called from outside the form. Int this case, I think the main problem was the usage of the Me.FieldName. Since the Me. referred to the current active form and the main form still had that focus, it was trying to find the field within the main form (not the form that contained the field and the called code) [—] which makes total sense when you consider the overall picture of referencing fields.

So by making sure that the desired form always has the required focus, the Me. short-cut reference will always retain that proper focus. This leads me to believe the final code should look like:

If Screen.ActiveControl.Parent.Name <> "SubFormName" Then _
    Forms!MainFormName!SubFormName.SetFocus
Me.TextFieldName.SetFocus 'Set focus on control
DoCmd.FindRecord DesiredValue

   or even

If Screen.ActiveControl.Parent.Name <> "SubFormName" Then _
    Me!SubFormName.SetFocus  'Set focus on form
Me.TextFieldName.SetFocus  'Set focus on control
DoCmd.FindRecord DesiredValue

Which leads to one final question:

If I need to reference a field within the main form after the call to that search routine has completed, how can I restore the focus back to the original form? In my case, I'm actually going to want to leave the focus upon the new subform, BUT can I code that search routine to restore the previous focus when done? Something like:

SavedFocus = Me    'Save pointer to current form?
If Screen.ActiveControl.Parent.Name <> "subFormName" Then _
    Me!subFormName.SetFocus
Me.TextFieldName.SetFocus 'Set focus on control
DoCmd.FindRecord DesiredValue
[COLOR=purple]Me = SavedFocus[/color] 'Restore current active form ??

If this would work, how would I define "SavedFocus"?

... or another way:

Dim PreviousForm as String, NeedForm as Boolean

PreviousForm = Screen.ActiveControl.Parent.Name
ChgForm = PreviousForm <> "subFormName"
If ChgForm Then Me!subFormName.SetFocus
Me.TextFieldName.SetFocus
DoCmd.FindRecord DesiredValue
If ChgForm Then _
    [COLOR=purple][b]Hmmm... what goes here to restore the previous form's focus?[/b][/color]

Am I close?

If this would restore the focus so that any follow-up statements within the main routine continue to be directed to the original (in this case, the main) form, then that would make the search routine very form-independent (always a nice attribute). (I'm big on modular, when appropriate.)

If the previously noted solution you provided solves my problem (and I suspect it will), then I believe you (and all of the other posts that help delve into this problem) may have solved my problem [—] and you gave me some insight into the methodology of VBA/Access.

Thanks to all and to all a good night (okay, a good afternoon).

Happy New Year
David (now a much-happier camper)

 

[TheAceMan1]

dameeti . . . . .

First be aware:

[blue]A form/subform being controlled by VBA, [purple]is not required to have focus[/purple] unless the programmer or some method dictates it should be so . . .[/blue]

As such a form/subform [blue]can be controlled from just about anywhere in VBA[/blue]. [purple]This is a very powerful feature of Access.[/purple]

Now:

[blue]If I need to reference a field within the main form after the call to that search routine has completed, how can I restore the focus back to the original form?[/blue]

Thats a bit of a tough one. There is a [purple]PrviousControl[/purple] method for the [purple]Screen Object[/purple], but none for form. Its also unreliable as the previous control is required to have had the focus at least once or an error is raised.

From experience, as the programmer if I don't know the previous focus position and require it in a routine, I would pass it as an additional arguement to the routine. This is easy to do and saves extra coding to get the same info.

See Ya! . . . . . .

 

[dameeti]

If I need to reference a field within the main form after the call to that search routine has completed, how can I restore the focus back to the original form?

Thats a bit of a tough one. There is a PrviousControl method for the Screen Object, but none for form. Its also unreliable as the previous control is required to have had the focus at least once or an error is raised.

I'm guessing that I can merely do something like:

PrevControl = Screen.PreviousControl
PrevForm = Screen.PrevControl.Parent
[COLOR=purple]' Does above reference the current form or parent of?[/color]
ChgForm = PrevForm.Name <> "subFormName"
If ChgForm Then Me!subFormName.SetFocus
Me.TextFieldName.SetFocus
DoCmd.FindRecord DesiredValue
If ChgForm Then
    PrevForm.SetFocus
    PrevControl.SetFocus
Endif

Am I way off, or on the right track? Guess I'll try when I get to work.

David

 

[TheAceMan1]

dameeti . . . . .

[blue]PrevFormName = Screen.PrevControl.Parent.Name[/blue]

See Ya! . . . . . .

 

[dameeti]

Per your last post...

PrevFormName = Screen.PrevControl.Parent.Name

Then my previous post...

PrevControl = Screen.PreviousControl
PrevForm = Screen.PreviousControl.Parent
ChgForm = PrevForm.Name <> "subFormName"

Between the lines in my post, I think I already got your suggestion already in there. I grab the control pointer, and then when I need to reference it (from the point of view of the name), I use the .Name property.

After a little more looking around various forums on Access, I'm thinking I don't want PreviousControl but instead I want ActiveControl since when I reference it, it's still the Active one, right? It's not Previous until after I change it, so I grab it before I change it and then I'm happy.

I think I need to have the following:

Public Sub FindABItem(DesiredValue As Integer)
On Error Goto Err_FindABItem

Dim PrevForm As Form
Dim PrevControl As Control
Dim ChgForm As Boolean

PrevControl = Screen.ActiveControl
PrevForm = Screen.ActiveForm
ChgForm = PrevForm.Name <> "subFormName"
If ChgForm Then Me!subFormName.SetFocus
Me.FieldName.SetFocus
DoCmd.FindRecord DesiredValue
If ChgForm Then
    PrevForm.SetFocus
    PrevControl.SetFocus
Endif

Exit_FindABItem:
    Exit Sub

Err_FindABItem:
    MsgBox "Error finding record: " & ...
    Resume Exit_FindABItem

End Sub

This would seem to be the complete routine. In summary, it would 1) save the current focus (both form & field), 2) do the search, and 3) restore the focus so the calling routine has no concern about how the called routine did it's thing.

This appears to be what I need. Thanks to ALL that helped.

Happy Holidays to all.

 

[dameeti]

Now this is getting strange. I just read that Screen.ActiveForm will refer to the main form, even if the subform has the actual focus.

So I guess the correct reference would be (I'm hoping) Screen.ActiveControl.Parent, and to get the name of the active form (even if it's a subform), it would be Screen.ActiveControl.Parent.Name.

Details, details, details.

David

 

[dameeti]

Okay, the latest chapter in this soap opera.

I tried the following code:

Forms!frmCustomerEntry!theform.SetFocus

I received the error 2465 ("MS Access can't find the field 'theform' referred to in your expression."). :-(

In case I needed the .Form property, I then tried:

Forms!frmCustomerEntry.Form!theform.SetFocus

Same error # and message. :-( :-( So in case it didn't want to set the focus upon a form, but could directly set the focus upon the field within the subform directly, I tried this:

Forms!frmCustomerEntry.Form!theform.Item_Number.SetFocus

Same error # and message. :-( :-( :-(

In every case, it did have the proper name for both the main and subform, so I know my references are properly named when checking the current form.

Interestingly enough, when I checked Screen.ActiveForm.Name, it always showed the main form, even if the active control was in the subform. So for me to find out where I'm calling it from, I have to instead check Screen.ActiveControl.Parent.Name and that seems to refer to the subform name.

I'm at a loss. I can't seem to set the focus upon either the subform or the field itself. All fail with the same error (2465) -- can't find the form in question.

Any thoughts?

Happy New Year to all... even those staying in tonight reading this tonight.

I'm off. Be good. (And if you can't be good, take pictures.) ;-)