Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Forms collection issue (Access 2002 SP1)

    I have a main form with a tabbed subform inserted in it. The main form only has a lookup combo box that finds records based on the name of person I type in the combo box. The tabbed subform has three "subforms" and I'm having problems with the Contact Information subform. When I enter a name that doesn't exist in the database, I want the lookup form to automatically split and insert the name I typed in, into the subform. Whenever this happens, I get the error message, "fsubContactInfo is not open".

    Here's full code I wrote. The problem line is the "DoCmd" (line 9). Can anyone help me with this?

    Private Sub cboNameLookup_NotInList(NewData As String, Response As Integer)
    ' Move to LastName Field if name is not listed.
    Dim ctl As Control
    Dim strFirstName As String
    Dim strLastName As String

    Set ctl = Me![cboNameLookup]
    ctl.Undo
    Response = acDataErrContinue
    DoCmd.GoToRecord acDataForm, "fsubContactInfo", acNewRec

    strLastName = Mid(NewData, 1, InStr(NewData, Chr(44)) - 1)
    Debug.Print "First name:" & strFirstName
    strFirstName = Mid(NewData, InStr(NewData, Chr(32)) + 1)
    Debug.Print "Last name:" & strLastName

    Me!FirstName = strFirstName
    Me!LastName = strLastName

    Me!Address.SetFocus
    cmdRefresh.Visible = True
    cmdCancel.Visible = True

    End Sub



    +++++++++++++++++++++
    Brent Anderson

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Forms collection issue (Access 2002 SP1)

    Subforms are not "open" and they are not part of the Forms collection. You must always refer to a subform through its parent. See Refer to Form and Subform properties and controls on the Access Web for a comprehensive overview.

    Instead of

    DoCmd.GoToRecord acDataForm, "fsubContactInfo", acNewRec

    use

    Me.fsubContactInfo.SetFocus
    RunCommand acCmdRecordsGoToNew

    Notes:
    (1) You must use the name of the subform as a control on the main form here; this is not necessairly the same as the name it has in the database window.
    (2) Even if you have set focus to the subform, Me will still refer to the form from which the code is run, i.e. the main form. If FirstName, LastName and Address are controls on the subform, you should use

    Me!fsubContactInfo!FirstName = strFirstName
    Me!fsubContactInfo!LastName = strLastName
    ...
    Me!fsubContactInfo!Address.SetFocus

    again using the name of the subform as a control on the main form.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms collection issue (Access 2002 SP1)

    HansV,

    Thanks so much for your help. It had been a year or two since I last programmed in VBA, so my skills was somewhat rusty. [img]/forums/images/smilies/smile.gif[/img]

    Brent

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •