Error 2109- find record in subform

L

Loralee

I have an unbound dialog form (frmAddFirstPractitioner) that collects
information from the user. When the user clicks the "okay" button on
frmAddFirstPractitioner, a sub saves the new record and launches another form
with a subform on it. I want the form/subform to open with the new record
open. The form that is launched is frmSitePractionerData and the subform is
frmSitePractitioner. The primary key for the subform (which is created in
the sub when the sub saves the record) goes in the field PractionerNum, which
is on the subform.

This is how it goes: frmSite (launches)-> frmAddFirstPractitioner
(launches) -> frmSitePractitionerData(Which contains frmSitePractitioner)

I keep getting Error 2109 "There is no field named DoCmd.GoToControl
"frmSitePractitionerData!practioner!practionerNum" in the current record".

I have tried the several ways to get to practionerNum but all the things
I've tried (listed below) have not worked.

Am I not referring to the control PractionerNum correctly?

Thanks-

Loralee

****************

Private Sub cmdAddFirst_Click()
Dim strLicense As String
Dim strFirstname As String
Dim strLastname As String
Dim intSiteID As Integer
Dim intPractNum As Integer


If ValidatePractitioner Then
strLicense = txtLicense
strFirstname = txtFirstName
strLastname = txtLastName
intSiteID = txtSiteID

If PractitionerNew(strLicense, strLastname, strFirstname, intSiteID)
= True Then'add new practitioner
MsgBox "Practitioner is new - add him"
Call AddNewPractitioner(strLicense, strLastname, strFirstname,
intSiteID)

DoCmd.OpenForm "frmSitePractionerData", , , "[SiteID]=" &
intSiteID ', , , txtnewpractionernum

' DoCmd.GoToControl "practionerNum" ' <- this works when
frmSitePractionerData is already open but bombs when coming from frmSite
' DoCmd.FindRecord txtNewPractionerNum <-temporarily commented
out but likely is really being omitted when run

----> DoCmd.GoToControl
"frmSitePractitionerData!frmSitePractitioner!practionerNum" <---------Error
2109 here

DoCmd.Close acForm, "frmaddfirstpractitioner", acSaveNo
Else
MsgBox "Practitioner is already in the database."
End If
End If

*************************
' DoCmd.GoToRecord acDataForm,
"Forms!frmsitepractionerdata!frmSitePractioner", acGoTo,
Me.txtNewPractionerNum
'above bombed saying frmSitePractionerData is not open when
opened as New Practitioner from add new practitioner

'DoCmd.GoToControl
"frmSitePractitionerData!practioner_Data.Form!practionerNum"
practioner_Data is the name of the subform control <- This causes error 2109
also

(txtNewPractionerNum is created by addNewPractitioner and is placed into a
txtbox on frmAddFirstPractitioner).
 
T

tina

the GoToControl command is not going to get you the right record anyway, so
it doesn't matter how you use it in this case.

first, since you're trying to get to a specific record in a subform's
recordset, you need to be sure that the record you're after will be included
in that recordset. if the mainform/subform are linked, that may not be the
case, unless you're controlling what records are returned in the mainform's
recordset and also making sure that the mainform record (which is linked to
the desired subform record) is the current record when the mainform opens.

if the mainform and subform are not linked, the above isn't a consideration.
in that case, you can either restrict the subform records to the record you
want using the subform's Filter property, as

With Forms("frmSitePractitionerData")!SubformControlName.Form
.Filter = "practitionerNum = " & Me!practitionerNum
.FilterOn = True
End With

or find the record you want and go to it without excluding other records
from the subform recordset, as

Dim rst As DAO.Recordset, frm As Form

Set frm = Forms("frmSitePractitionerData")!SubformControlName.Form
Set rst = frm.RecordsetClone

rst.FindFirst "practitionerNum = " & Me!practitionerNum
If Not rst.NoMatch Then
frm.Bookmark = rst.Bookmark
End If

Set rst = Nothing

run either code from the sub procedure that opens frmSitePractitionerData,
*after* the OpenForm command. and replace "SubformControlName" with the
*name of the subform control within the mainform*.

hth


Loralee said:
I have an unbound dialog form (frmAddFirstPractitioner) that collects
information from the user. When the user clicks the "okay" button on
frmAddFirstPractitioner, a sub saves the new record and launches another form
with a subform on it. I want the form/subform to open with the new record
open. The form that is launched is frmSitePractionerData and the subform is
frmSitePractitioner. The primary key for the subform (which is created in
the sub when the sub saves the record) goes in the field PractionerNum, which
is on the subform.

This is how it goes: frmSite (launches)-> frmAddFirstPractitioner
(launches) -> frmSitePractitionerData(Which contains frmSitePractitioner)

I keep getting Error 2109 "There is no field named DoCmd.GoToControl
"frmSitePractitionerData!practioner!practionerNum" in the current record".

I have tried the several ways to get to practionerNum but all the things
I've tried (listed below) have not worked.

Am I not referring to the control PractionerNum correctly?

Thanks-

Loralee

****************

Private Sub cmdAddFirst_Click()
Dim strLicense As String
Dim strFirstname As String
Dim strLastname As String
Dim intSiteID As Integer
Dim intPractNum As Integer


If ValidatePractitioner Then
strLicense = txtLicense
strFirstname = txtFirstName
strLastname = txtLastName
intSiteID = txtSiteID

If PractitionerNew(strLicense, strLastname, strFirstname, intSiteID)
= True Then'add new practitioner
MsgBox "Practitioner is new - add him"
Call AddNewPractitioner(strLicense, strLastname, strFirstname,
intSiteID)

DoCmd.OpenForm "frmSitePractionerData", , , "[SiteID]=" &
intSiteID ', , , txtnewpractionernum

' DoCmd.GoToControl "practionerNum" ' <- this works when
frmSitePractionerData is already open but bombs when coming from frmSite
' DoCmd.FindRecord txtNewPractionerNum <-temporarily commented
out but likely is really being omitted when run

----> DoCmd.GoToControl
"frmSitePractitionerData!frmSitePractitioner!practionerNum" <---------Error
2109 here

DoCmd.Close acForm, "frmaddfirstpractitioner", acSaveNo
Else
MsgBox "Practitioner is already in the database."
End If
End If

*************************
' DoCmd.GoToRecord acDataForm,
"Forms!frmsitepractionerdata!frmSitePractioner", acGoTo,
Me.txtNewPractionerNum
'above bombed saying frmSitePractionerData is not open when
opened as New Practitioner from add new practitioner

'DoCmd.GoToControl
"frmSitePractitionerData!practioner_Data.Form!practionerNum"
practioner_Data is the name of the subform control <- This causes error 2109
also

(txtNewPractionerNum is created by addNewPractitioner and is placed into a
txtbox on frmAddFirstPractitioner).
Click to expand...
 
L

Loralee

Thank you, Tina. That worked like a charm.

(I had accidentally cut out part of the code to find the match (FindRecord)
that followed. But it never got to finding the record because it could not
find the control. With your suggestion, I don't need the FindRecord.

Thanks again- Loralee
--
Loralee


tina said:
the GoToControl command is not going to get you the right record anyway, so
it doesn't matter how you use it in this case.

first, since you're trying to get to a specific record in a subform's
recordset, you need to be sure that the record you're after will be included
in that recordset. if the mainform/subform are linked, that may not be the
case, unless you're controlling what records are returned in the mainform's
recordset and also making sure that the mainform record (which is linked to
the desired subform record) is the current record when the mainform opens.

if the mainform and subform are not linked, the above isn't a consideration.
in that case, you can either restrict the subform records to the record you
want using the subform's Filter property, as

With Forms("frmSitePractitionerData")!SubformControlName.Form
.Filter = "practitionerNum = " & Me!practitionerNum
.FilterOn = True
End With

or find the record you want and go to it without excluding other records
from the subform recordset, as

Dim rst As DAO.Recordset, frm As Form

Set frm = Forms("frmSitePractitionerData")!SubformControlName.Form
Set rst = frm.RecordsetClone

rst.FindFirst "practitionerNum = " & Me!practitionerNum
If Not rst.NoMatch Then
frm.Bookmark = rst.Bookmark
End If

Set rst = Nothing

run either code from the sub procedure that opens frmSitePractitionerData,
*after* the OpenForm command. and replace "SubformControlName" with the
*name of the subform control within the mainform*.

hth


Loralee said:
I have an unbound dialog form (frmAddFirstPractitioner) that collects
information from the user. When the user clicks the "okay" button on
frmAddFirstPractitioner, a sub saves the new record and launches another form
with a subform on it. I want the form/subform to open with the new record
open. The form that is launched is frmSitePractionerData and the subform is
frmSitePractitioner. The primary key for the subform (which is created in
the sub when the sub saves the record) goes in the field PractionerNum, which
is on the subform.

This is how it goes: frmSite (launches)-> frmAddFirstPractitioner
(launches) -> frmSitePractitionerData(Which contains frmSitePractitioner)

I keep getting Error 2109 "There is no field named DoCmd.GoToControl
"frmSitePractitionerData!practioner!practionerNum" in the current record".

I have tried the several ways to get to practionerNum but all the things
I've tried (listed below) have not worked.

Am I not referring to the control PractionerNum correctly?

Thanks-

Loralee

****************

Private Sub cmdAddFirst_Click()
Dim strLicense As String
Dim strFirstname As String
Dim strLastname As String
Dim intSiteID As Integer
Dim intPractNum As Integer


If ValidatePractitioner Then
strLicense = txtLicense
strFirstname = txtFirstName
strLastname = txtLastName
intSiteID = txtSiteID

If PractitionerNew(strLicense, strLastname, strFirstname, intSiteID)
= True Then'add new practitioner
MsgBox "Practitioner is new - add him"
Call AddNewPractitioner(strLicense, strLastname, strFirstname,
intSiteID)

DoCmd.OpenForm "frmSitePractionerData", , , "[SiteID]=" &
intSiteID ', , , txtnewpractionernum

' DoCmd.GoToControl "practionerNum" ' <- this works when
frmSitePractionerData is already open but bombs when coming from frmSite
' DoCmd.FindRecord txtNewPractionerNum <-temporarily commented
out but likely is really being omitted when run

----> DoCmd.GoToControl
"frmSitePractitionerData!frmSitePractitioner!practionerNum" <---------Error
2109 here

DoCmd.Close acForm, "frmaddfirstpractitioner", acSaveNo
Else
MsgBox "Practitioner is already in the database."
End If
End If

*************************
' DoCmd.GoToRecord acDataForm,
"Forms!frmsitepractionerdata!frmSitePractioner", acGoTo,
Me.txtNewPractionerNum
'above bombed saying frmSitePractionerData is not open when
opened as New Practitioner from add new practitioner

'DoCmd.GoToControl
"frmSitePractitionerData!practioner_Data.Form!practionerNum"
practioner_Data is the name of the subform control <- This causes error 2109
also

(txtNewPractionerNum is created by addNewPractitioner and is placed into a
txtbox on frmAddFirstPractitioner).
Click to expand...
Click to expand...
 
T

tina

you're welcome :)


Loralee said:
Thank you, Tina. That worked like a charm.

(I had accidentally cut out part of the code to find the match (FindRecord)
that followed. But it never got to finding the record because it could not
find the control. With your suggestion, I don't need the FindRecord.

Thanks again- Loralee
--
Loralee


tina said:
the GoToControl command is not going to get you the right record anyway, so
it doesn't matter how you use it in this case.

first, since you're trying to get to a specific record in a subform's
recordset, you need to be sure that the record you're after will be included
in that recordset. if the mainform/subform are linked, that may not be the
case, unless you're controlling what records are returned in the mainform's
recordset and also making sure that the mainform record (which is linked to
the desired subform record) is the current record when the mainform opens.

if the mainform and subform are not linked, the above isn't a consideration.
in that case, you can either restrict the subform records to the record you
want using the subform's Filter property, as

With Forms("frmSitePractitionerData")!SubformControlName.Form
.Filter = "practitionerNum = " & Me!practitionerNum
.FilterOn = True
End With

or find the record you want and go to it without excluding other records
from the subform recordset, as

Dim rst As DAO.Recordset, frm As Form

Set frm = Forms("frmSitePractitionerData")!SubformControlName.Form
Set rst = frm.RecordsetClone

rst.FindFirst "practitionerNum = " & Me!practitionerNum
If Not rst.NoMatch Then
frm.Bookmark = rst.Bookmark
End If

Set rst = Nothing

run either code from the sub procedure that opens frmSitePractitionerData,
*after* the OpenForm command. and replace "SubformControlName" with the
*name of the subform control within the mainform*.

hth


Loralee said:
I have an unbound dialog form (frmAddFirstPractitioner) that collects
information from the user. When the user clicks the "okay" button on
frmAddFirstPractitioner, a sub saves the new record and launches
Click to expand...
another
form
with a subform on it. I want the form/subform to open with the new record
open. The form that is launched is frmSitePractionerData and the
Click to expand...
subform
is
frmSitePractitioner. The primary key for the subform (which is created in
the sub when the sub saves the record) goes in the field
Click to expand...
PractionerNum,
which
is on the subform.

This is how it goes: frmSite (launches)-> frmAddFirstPractitioner
(launches) -> frmSitePractitionerData(Which contains frmSitePractitioner)

I keep getting Error 2109 "There is no field named DoCmd.GoToControl
"frmSitePractitionerData!practioner!practionerNum" in the current record".

I have tried the several ways to get to practionerNum but all the things
I've tried (listed below) have not worked.

Am I not referring to the control PractionerNum correctly?

Thanks-

Loralee

****************

Private Sub cmdAddFirst_Click()
Dim strLicense As String
Dim strFirstname As String
Dim strLastname As String
Dim intSiteID As Integer
Dim intPractNum As Integer


If ValidatePractitioner Then
strLicense = txtLicense
strFirstname = txtFirstName
strLastname = txtLastName
intSiteID = txtSiteID

If PractitionerNew(strLicense, strLastname, strFirstname, intSiteID)
= True Then'add new practitioner
MsgBox "Practitioner is new - add him"
Call AddNewPractitioner(strLicense, strLastname, strFirstname,
intSiteID)

DoCmd.OpenForm "frmSitePractionerData", , , "[SiteID]=" &
intSiteID ', , , txtnewpractionernum

' DoCmd.GoToControl "practionerNum" ' <- this works when
frmSitePractionerData is already open but bombs when coming from frmSite
' DoCmd.FindRecord txtNewPractionerNum <-temporarily commented
out but likely is really being omitted when run

----> DoCmd.GoToControl
"frmSitePractitionerData!frmSitePractitioner!practionerNum" <---------Error
2109 here

DoCmd.Close acForm, "frmaddfirstpractitioner", acSaveNo
Else
MsgBox "Practitioner is already in the database."
End If
End If

*************************
' DoCmd.GoToRecord acDataForm,
"Forms!frmsitepractionerdata!frmSitePractioner", acGoTo,
Me.txtNewPractionerNum
'above bombed saying frmSitePractionerData is not open when
opened as New Practitioner from add new practitioner

'DoCmd.GoToControl
"frmSitePractitionerData!practioner_Data.Form!practionerNum"
practioner_Data is the name of the subform control <- This causes
Click to expand...
error
2109
also

(txtNewPractionerNum is created by addNewPractitioner and is placed into a
txtbox on frmAddFirstPractitioner).
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

find record in main form then find record in the subform 7
Duplicate record in Form and Subform 0
Error 3315 Runtime 5
After update find record on subform 5
Locate record in a subform 0
Refresh Subform Current Record? 3
looking up fields in a table to compare to a textbox on a form 5
How to add record in Subform which is in another Subform 0

Top