Global NotInList Event for Unbound Subform 1

[wvandenberg]

I am building a custom wizard and am trying to implement faq702-5205 GLOBAL Not In List Event Handler. However, I am getting the error: "Run-time error '2465' ...can't find the field 'sfrImportWizardPage1' referred to in your expression." on the following line:

          Set sfrm1 = frmMain(frmNames.Item(n)).Form

My main form (frmImportWizard) has an unbound subform "frmPage". In the OnLoad event for the main form, I do this:

    Me.frmPage.SourceObject = "sfrImportWizardPage1"

The NotInList event for the combo looks like this:

Private Sub cboSource_NotInList(NewData As String, Response As Integer)
    If AddToList(Me, "tblDataSources", "DataSourceDescription") Then
        Response = acDataErrAdded
    Else
        Me.cboSource.Undo
        Response = acDataErrContinue
    End If
End Sub

It seems that the global NotInList procedure is setting my main form as the parent of sfrImportWizardPage1 when rather than frmPage.

Any suggestions on getting rid of this error?

Thanks,
Wendy

 

[MajP]

Any suggestions on getting rid of this error?

If I am understanding you correctly there is no error. You have a form "Main" with a subform control called "frmPage". The source object within the subform control is "sfrImportWizardPage1". The Parent object of "sfrImportWizardPage1" is the form not the subform control.

You can use the Parent property to refer to the parent of a control, section, or control that contains other controls. The Parent property returns a control object if the parent is a control; it returns a AccessObject object if the parent is an Microsoft Access object.

You can use the Parent property to determine which form or report is currently the parent when you have a subform or subreport that has been inserted in multiple forms or reports.

For example, you might insert an OrderDetails subform into both a form and a report. The following example uses the Parent property to refer to the OrderID field, which is present on the main form and report. You can enter this expression in a bound control on the subform.

=Parent!OrderID

The Parent property of a label control is the control the label is linked to. The Parent property for a check box, option button, or toggle button in an option group is the name of the option group control. The Parent property of an option group control is the name of the form.

 

[TheAceMan1]

How are ya wvandenberg . . .

Sorry about the mess!

On & off I've been redesigning the FAQ, as there's a bit of overkill in accounting for subforms (not needed). So replace the [blue]AddToList[/blue] function with the following:

[blue]Public Function AddToList(curForm As Form, tblName As String, _
                          fldName As String) As Long
   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   Dim flg As Boolean, Cbx As ComboBox
   
   Set Cbx = curForm(Screen.ActiveControl.Name)
   AddToList = acDataErrContinue
      
   'The Global NotInList Event
   Msg = "'" & Cbx.Text & "' is not in the ComboBox List!" & _
         "@Click 'Yes' to add it." & _
         "@Click 'No' to abort."
   Style = vbInformation + vbYesNo
   Title = "Not In List Warning!"
   
   If uMsg() = vbYes Then
      Set db = CurrentDb()
      'The SQL loads only one record in the recordset.
      'This prevents a large number of records
      'from loading and taxing resources.
      SQL = "SELECT TOP 1 * FROM " & tblName & ";"
      Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
      
      rst.AddNew
         If IsNumeric(rst(fldName)) Then
            rst(fldName) = Val(Cbx.Text)
         Else
            rst(fldName) = Cbx.Text
         End If
      rst.Update
      
      AddToList = acDataErrAdded
      Set rst = Nothing
      Set db = Nothing
   End If
   
End Function[/blue]

In the [blue]NotInList[/blue] event of the combos, change the call to:

[blue]   [green]'YourTableName? - table you wish to add the new data to.
   'YourFieldName? - the field in the table that will receive the data.[/green]
   Response = AddToList(Me, "[purple][b]YourTableName[/b][/purple]", "[purple][b]YourFieldName[/b][/purple]")[/blue]

This is a version that should work until I get the master updated.

[blue]Let me know how ya make out! . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[wvandenberg]

The Parent object of "sfrImportWizardPage1" is the form not the subform control.

That's what I thought too but the wierd thing is that when I run

?Forms("frmImportWizard")("frmPage").Form.Name

in the immediate window before I step into the cboSource_NotInList event, it returns "frmPage". But, when I step into the cboSource_NotInList event, this

?Forms("frmImportWizard")("frmPage").Form.Name

returns sfrImportWizardPage1 and this

?Forms("frmImportWizard")("frmPage")("sfrImportWizardPage1").Form.Name
?Forms("frmImportWizard")("sfrImportWizardPage1").Form.Name

produces run-time error I mentioned previously.

I can't make any sense of it. Any ideas?

Wendy

 

[wvandenberg]

Hi TheAceMan1. I took a break for supper and didn't finish writing my reply to MajP until just now. I will try out your suggestion and let you know how it goes.

Wendy

 

[wvandenberg]

Thank you TheAceMan1, you're really living up to your name ;-)

Wendy

 

[MajP]

?Forms("frmImportWizard")("frmPage").Form.Name
returns sfrImportWizardPage1

that makes sense to me

and

?Forms("frmImportWizard")("frmPage")("sfrImportWizardPage1").Form.Name
?Forms("frmImportWizard")("sfrImportWizardPage1").Form.Name
produces run-time error I mentioned previously

This makes sense and shoud produce an error because this is improper improper notation. In VBA you can do
Object.collection("item name")
So you can do something like
Forms("myForm").controls("subfrmcntrl").form.name
Now vba has default properties. For example the controls collection is the default property of a form. A default property does not have to be listed. The value property is a good example

?myControl.value
can be shortened to
?myControl

So since the controls collection is the default property of a form object you can write
?Forms("frmImportWizard").controls("frmPage").form.name
or shorten by deleting the default properties
Forms("frmImportWizard")("frmPage").form

However this won't work
?Forms("frmImportWizard")("frmPage")("sfrImportWizardPage1").Form.Name
because ("sfrImportWizardPage1") is not an item of a collection belonging to frmPage

Kind of the same issue here
?Forms("frmImportWizard")("sfrImportWizardPage1").
the form "frmImportWizard" does not have a control called "sfrImportWizardPage1"

So now the only question is why
?Forms("frmImportWizard")("frmPage").Form.Name
would return "frmPage" the name of the subform control and not the name of the form within the subform control. Unfortunately I need to think about that one.

Anyone else have an idea?

 

[PHV]

My understanding:
Forms("frmImportWizard")("frmPage").Form.Name = sfrImportWizardPage1
Forms("frmImportWizard")("frmPage").Name = frmPage

 

[MajP]

I agree PHV but he claims that somehow he gets
Forms("frmImportWizard")("frmPage").Form.Name
to return "frmPage"

wvandenburg,
any chance you did what PHV said and wrote this
?Forms("frmImportWizard")("frmPage").Name
and thought you wrote
?Forms("frmImportWizard")("frmPage").Form.Name