VBA Code Not Recognizing subform property (1 Viewer)

[EFJK]

Hi all,




Well, I've been banging my head against the wall on this one for quite some time now, and now am asking for some assistance.


I have a form that contains two subforms. I have this code, which is working properly for the first tabbed subform (on a tab control), but gives me an error when the same code is used to reference the 2nd subform on the 2nd tab.


Here is the code:

Option Compare Database

Sub expandBatch()

' Function to expand multiquantity records (in Batch) into Qty = 1 records in SNL for a given batch.

'--- EXPAND FOR EXTRAS ---


Dim counter As Integer ' this will hold the initial Qty for each record in Batch


Dim db As DAO.Database
Set db = CurrentDb

Dim rstPSE As DAO.Recordset
Dim rstBatch As DAO.Recordset
Set rstPSE = db.OpenRecordset("dbo_T_ProductionScheduleExtras", dbOpenDynaset, dbSeeChanges)

[B]Set rstBatch = Forms![F_ScheduleGenerator]![SF_ProductionScheduleExtras].Form.RecordsetClone[/B]

With rstBatch
    .MoveFirst
    
    Do Until .EOF
        counter = !QtyOrdSell
        Do While counter > 0
            rstPSE.AddNew
            rstPSE!BuildDate = rstBatch!ReqShipDate
            rstPSE!TransID = rstBatch!TransID
            rstPSE!ItemId = rstBatch!ItemId
            rstPSE!CustName = rstBatch!CustName
            rstPSE!Model = rstBatch!ModelDesc
            rstPSE!Volt = rstBatch!Volt
            rstPSE!Phase = rstBatch!Phase
            rstPSE!Options = rstBatch![cf_Option Values]
            rstPSE!CustName = rstBatch!CustName
            rstPSE!CustPO = rstBatch!CustPONum
            rstPSE.Update
            
            Debug.Print counter
            counter = counter - 1
            
        Loop 'counter decrementing
        
        rstBatch.MoveNext
        
        Debug.Print "next record in Batch"
   Loop ' on .eof

   
End With



rstBatch.Close
rstPSE.Close

Set rstBatch = Nothing
Set rstPSE = Nothing

End Sub

The error i get is a run-time error '2465', telling me that it cannot find the referenced field "SF_ProductionScheduleExtras". Obviously this appears to just be a referencing issue, but I cannot figure out why. I'm wondering if it's a focus issue? It has worked great for the other subform control that I originally wrote it for.




Thanks for any help you can provide!




Erik

 

[theDBguy]

Hi Erik. Welcome to the forum. Where is this code placed? Is it in a Standard Module? I was just curious why you're using an absolute reference rather than a relative one.

 

[EFJK]

It's in a standard module. I went with an absolute as I was clunking my way through the code originally. Using "Me!" is giving me an error as well

 

[theDBguy]

It's in a standard module. I went with an absolute as I was clunking my way through the code originally. Using "Me!" is giving me an error as well

Referring to subform controls can get tricky. Can you post a sample db even if it only has the module and form/subform objects in it? We can then take a look.

 

[The_Doc_Man]

EFJK - it happens now and then that you run afoul of the complex syntax Access needs to use to find something. Here's a quick and (very) dirty thumbnail for what appears to be your case.

Me as a form or report reference is only valid in the class module for the specific form or report. The visibility of that form or report is local to that class module and only "local" references can be made using Me directly (though Me can contribute to an external-pointing reference, see discussion below.)

If you are using TAB controls on a single form, then from the class module the sub-form control names are visible as Me.[subform1] or Me.[subform2] because the TAB control is merely a visibility artifice. Everything on the main form, inside or outside of the TAB controls, is part of the same form. Me can be used this way because the next step in the reference guides VBA to the place where the intended element is found. The controls on a subform (when referenced from the main control's class module) are of the style Me.[SubformN].Form.ControlName, and again you can see that the reference chain leads VBA to the object by following properties of each successive member of the overall (Me) object.

However, from one of the subforms, you are in the wrong scope to directly see that other subform. When focus is on the subform, controls on the current subform are Me.ControlName, but controls on the OTHER subform will be something more similar to Me.Parent.[OtherSubForm].Form.ControlName-on-other-subform (roughly speaking). (Note: For that to be accurate, you can't use Frames because in that case, the parent of a form is its Frame object. I HATE frames.)

From a GENERAL module, however, things get more difficult because keyword Me has no meaning. Even if called from a Class module, the references in a general module were COMPILED when the calling form wasn't calling yet. Therefore, VBA had no class object to assign to Me. Therefore, the secret to understanding it is that Me is a COMPILE-TIME reference and from a General module, there is nothing to be referenced.

You can still pass in a form (or form name) to the general module and work from there, but it just gets uglier in the syntax. I.e.

Public Function DiddleForm( f as Access.Form ) As Boolean
...
    f.controlname = some value
    f.controlname.enabled  = True
...

or

Public Sub DiddleNamedForm( fn as String )
Dim f as Access.Form
    Set f = Forms(fn)
...
    f.controlname = value
    f.controlname.Enabled = True
...

 

[Uncle Gizmo]

Set rstBatch = Forms![F_ScheduleGenerator]![SF_ProductionScheduleExtras].Form.RecordsetClone

If the “SF” designation here:- “SF_ProductionScheduleExtras” means that this refers to a subform, then that is the problem, because that is not a subform. That position in the code is the subform window, or more correctly the subform/subreport control. The control which houses the subform which is then “.Form”

However it is very likely that that is the correct name of the subform/subreport control because MS Access automatically creates that control with the same name of as the form that is within it.

However seeing as you have two subforms on the same form, then I'm wondering if you have got in a muddle with the naming of that subform/subreport control?

For instance might it be named:- SF_ProductionScheduleExtras2

Whenever I add subforms to a main form and this intermediary control, the subform/subreport control is created, I always rename this intermediary control to avoid confusion.

So in my case that line of code would read something like this:-

Set rstBatch = Forms![F_ScheduleGenerator]!subFrmWinProductionScheduleExtras.Form.RecordsetClone

If you avoid strange characters like underscores then you won't need the square brackets...