New Record in Subform Problem

[Guest]

I don't believe I can't get this to work!
Form/Subform Linked on ClientID (Master) - MDB_CLIENT (Child)

I have a table of mdb files. Each client may have 0 to many records in
tblMdbList which is the record source of the sub form. tblClient is the
record source of the main form.

If I select a client with records in the subform, all is well.
If I select a client with no records in the sub form, I get an error (see in
code below) and the subform does not display (subform is single record on a
tab)
I used the usual method to open goto a new record in the subform, but it is
not working in this case and I don't see anything (that I know of) that makes
this an unusual case.

Private Sub cboClient_AfterUpdate()
'
On Error GoTo cboClient_AfterUpdate_Error

With Me.RecordsetClone
.FindFirst "[ClientID] = '" & Me![cboClient] & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Me.subMDB.Form!cboRegion.Requery
If Me.subMDB.Form.Recordset.RecordCount > 0 Then
Me.subMDB.Form!cboRegion = Me.subMDB.Form!txtMdbRegion
Else
Me.subMDB.SetFocus

Error 2105 (You can't go to the specified record)

DoCmd.GoToRecord , , acNewRec
End If
End If
End With

cboClient_AfterUpdate_Exit:

 

[Ken Snell \(MVP\)]

First things I'd look for:
-- updatable query is subform's record source
-- subform's AllowAdditions property is set to yes

--

Ken Snell
<MS ACCESS MVP>

I don't believe I can't get this to work!
Form/Subform Linked on ClientID (Master) - MDB_CLIENT (Child)

I have a table of mdb files. Each client may have 0 to many records in
tblMdbList which is the record source of the sub form. tblClient is the
record source of the main form.

If I select a client with records in the subform, all is well.
If I select a client with no records in the sub form, I get an error (see
in
code below) and the subform does not display (subform is single record on
a
tab)
I used the usual method to open goto a new record in the subform, but it
is
not working in this case and I don't see anything (that I know of) that
makes
this an unusual case.

Private Sub cboClient_AfterUpdate()
'
On Error GoTo cboClient_AfterUpdate_Error

With Me.RecordsetClone
.FindFirst "[ClientID] = '" & Me![cboClient] & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Me.subMDB.Form!cboRegion.Requery
If Me.subMDB.Form.Recordset.RecordCount > 0 Then
Me.subMDB.Form!cboRegion = Me.subMDB.Form!txtMdbRegion
Else
Me.subMDB.SetFocus

Error 2105 (You can't go to the specified record)

DoCmd.GoToRecord , , acNewRec
End If
End If
End With

cboClient_AfterUpdate_Exit:

 

[Guest]

Boy do I feel stupid. I had accidently set the Allow Additions to No. Don't
know why I didn't check that first.
--
Dave Hargis, Microsoft Access MVP

First things I'd look for:
-- updatable query is subform's record source
-- subform's AllowAdditions property is set to yes

--

Ken Snell
<MS ACCESS MVP>

I don't believe I can't get this to work!
Form/Subform Linked on ClientID (Master) - MDB_CLIENT (Child)

I have a table of mdb files. Each client may have 0 to many records in
tblMdbList which is the record source of the sub form. tblClient is the
record source of the main form.

If I select a client with records in the subform, all is well.
If I select a client with no records in the sub form, I get an error (see
in
code below) and the subform does not display (subform is single record on
a
tab)
I used the usual method to open goto a new record in the subform, but it
is
not working in this case and I don't see anything (that I know of) that
makes
this an unusual case.

Private Sub cboClient_AfterUpdate()
'
On Error GoTo cboClient_AfterUpdate_Error

With Me.RecordsetClone
.FindFirst "[ClientID] = '" & Me![cboClient] & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Me.subMDB.Form!cboRegion.Requery
If Me.subMDB.Form.Recordset.RecordCount > 0 Then
Me.subMDB.Form!cboRegion = Me.subMDB.Form!txtMdbRegion
Else
Me.subMDB.SetFocus

Error 2105 (You can't go to the specified record)

DoCmd.GoToRecord , , acNewRec
End If
End If
End With

cboClient_AfterUpdate_Exit: