Forms collection issue (Access 2002 SP1)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Brent

Reply | Quote

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.

Brent

Reply | Quote

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.

Reply | Quote