Error Run time 2105 You cant go the the specified record

[JeffMKlein]

I have a Main form that has a subform that queries the clients table. When
I have this Main form open and try to open another form that references info
from the client table (this form automatically goto's a new record when
opened), I am getting an error. "You cant go the the specified record." "You
may be at the end of the recordset."

If I do not have the main form open the secondary form opens and goes to the
new record just fine.

I think this a problem with the current recordset but I can't figure it
out. Any suggestions?

 

[Allen Browne]

Some things that might prevent this from working:

a) The first record is dirty (uncommitted edits).
Workaround:
With Forms("Form1")
If .Dirty Then
.Dirty = False
End If
'then open your other form
End With

b) You are immediately dirtying the second form, e.g. it has code in its
Current event that assigns a value to a bound control.

c) You are using pessimistic locking.

d) There are memo fields (or OLE Object fields) in the forms.

 

[Guest]

Some things that might prevent this from working:

a) The first record is dirty (uncommitted edits).
Workaround:
With Forms("Form1")
If .Dirty Then
.Dirty = False
End If
'then open your other form
End With

b) You are immediately dirtying the second form, e.g. it has code in its
Current event that assigns a value to a bound control.

c) You are using pessimistic locking.

d) There are memo fields (or OLE Object fields) in the forms.

Good morning, Mr. Browne. I've had a similar problem, I hope you can shed
some light on it.
I have a form with a combo box, which calls a subform function to navigate
to an appropriate record. That function boils down to "DoCmd.GoToRecord , ,
acNext" (or acPrevious), where it throws an error "2105: You can't go to the
specified record."

I thought it might be the "Dirty" problem you mentioned, but I can't change
or even view Dirty in my combo box's _AfterUpdate() sub. The master form has
no recordsource, it just wraps the subform, which is mapped to an ODBC table
via a simple select query.
The subform has no code except the sub I'm calling, and a line in Form_Open:
"DoCmd.GoToRecord , , acNewRec".
There are no memo or OLE fields in either form.
It produces this error when in a new or existing record.
Thanks!

 

[Allen Browne]

What is in the Link Master Fields and Link Child Fields of the subform
control?

If your main form is unbound, it will not have a Dirty property. To see if
the subform is dirty, open the Immediate WIndow (Ctrl+G), and enter
something like this:
? Forms![Form1].[Form2].Form.Dirty
where Form1 is the name of the main form, and Form2 is the name of the
subform control.

The other obvious case where you cannot go to a new record is if you are
already at the new record, so:
If Not Me.NewRecord Then ...

 

[Guest]

What is in the Link Master Fields and Link Child Fields of the subform
control?

If your main form is unbound, it will not have a Dirty property. To see if
the subform is dirty, open the Immediate WIndow (Ctrl+G), and enter
something like this:
? Forms![Form1].[Form2].Form.Dirty
where Form1 is the name of the main form, and Form2 is the name of the
subform control.

The other obvious case where you cannot go to a new record is if you are
already at the new record, so:
If Not Me.NewRecord Then ...

There is nothing in the Link fields; the master form is just a wrapper with
a close button and this JumpTo combo box.
The subform is not Dirty at any stage in the subform sub.
The subform sub returns immediately if it's already in the correct record.
This is the code:

Public Sub JumpToOrder(PONumber As Integer)
If PONumber = 0 Or IsNull(PONumber) Then Exit Sub
If PONumber = PurchaseOrder Then Exit Sub
If IsNull(PurchaseOrder) Or PONumber < PurchaseOrder Then ' Search
backwards
Do
DoCmd.GoToRecord acDataForm, "PurchaseOrderSubForm", acNext '
Attempt A
DoCmd.GoToRecord , , acNext ' Attempt B
Loop Until PONumber = PurchaseOrder
Else ' Search forwards
Do
DoCmd.GoToRecord , , acPrevious
Loop Until PONumber = PurchaseOrder
End If
End Sub

"Attempt A" generates error 2489: The object PurchaseOrderLogSubform isn't
open; commenting it out and trying "Attempt B" generates error 2105: You
can't go to the specific record.
Thanks for your time.

 

[Allen Browne]

The subform is not open in its own right, so that's why you received error
2489.

The line:
RunCommand acCmdRecordsGoToNew
will move the active form to a new record.
But that means we have to make the subform active first.
Try something like this:

With Me.PurchaseOrderSubForm
.SetFocus
.Form![SomeControl].SetFocus
If Not .Form.NewRecord Then
RunCommand acCmdRecordsGoToNew
End If
End With

(Replace "SomeControl" with the name of a control in the subform.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

What is in the Link Master Fields and Link Child Fields of the subform
control?

If your main form is unbound, it will not have a Dirty property. To see
if
the subform is dirty, open the Immediate WIndow (Ctrl+G), and enter
something like this:
? Forms![Form1].[Form2].Form.Dirty
where Form1 is the name of the main form, and Form2 is the name of the
subform control.

The other obvious case where you cannot go to a new record is if you are
already at the new record, so:
If Not Me.NewRecord Then ...

There is nothing in the Link fields; the master form is just a wrapper
with
a close button and this JumpTo combo box.
The subform is not Dirty at any stage in the subform sub.
The subform sub returns immediately if it's already in the correct record.
This is the code:

Public Sub JumpToOrder(PONumber As Integer)
If PONumber = 0 Or IsNull(PONumber) Then Exit Sub
If PONumber = PurchaseOrder Then Exit Sub
If IsNull(PurchaseOrder) Or PONumber < PurchaseOrder Then ' Search
backwards
Do
DoCmd.GoToRecord acDataForm, "PurchaseOrderSubForm", acNext '
Attempt A
DoCmd.GoToRecord , , acNext ' Attempt B
Loop Until PONumber = PurchaseOrder
Else ' Search forwards
Do
DoCmd.GoToRecord , , acPrevious
Loop Until PONumber = PurchaseOrder
End If
End Sub

"Attempt A" generates error 2489: The object PurchaseOrderLogSubform isn't
open; commenting it out and trying "Attempt B" generates error 2105: You
can't go to the specific record.
Thanks for your time.

 

[Guest]

The subform is not open in its own right, so that's why you received error
2489.

The line:
RunCommand acCmdRecordsGoToNew
will move the active form to a new record.
But that means we have to make the subform active first.
Try something like this:

With Me.PurchaseOrderSubForm
.SetFocus
.Form![SomeControl].SetFocus
If Not .Form.NewRecord Then
RunCommand acCmdRecordsGoToNew
End If
End With

(Replace "SomeControl" with the name of a control in the subform.)

When Access realizes which control I'm trying to work with, I still get
error 2105: You can't go to the specified record. Incidentally, I'm moving
to a specific record as specified by the combo box, not going to a new record.
I don't understand why there's no .MoveNext and .MovePrevious or .Seek
method for subform controls. .Form doesn't help, either. There should be a
standard interface for collections.
AHA! There is, via Recordset. This works perfectly:

Public Sub JumpToOrder(PONumber As Integer)
If PONumber = 0 Or IsNull(PONumber) Then Exit Sub
If PONumber = PurchaseOrder Then Exit Sub
If IsNull(PurchaseOrder) Or PONumber < PurchaseOrder Then ' Search
backwards
Do
Recordset.MovePrevious
Loop Until PONumber = PurchaseOrder
Else ' Search forwards
Do
Recordset.MoveNext
Loop Until PONumber = PurchaseOrder
End If
End Sub

I'll add an error handling routine.