Access 2003, SQL, Docmd.findrecord "findrecord not available now"

[Elaine]

I am using this in other forms in the same frontend mdb and it works fine.
In one form, however, it does not. I have tried the form's record source as
both the a table and a query.

Anyone found a reason or solution for this? Thanks.

 

[Ken Snell \(MVP\)]

You'll have to tell us a lot more details about the form, where/when you're
using this, how you're using this, etc. This error message usually shows up
when (1) some other event is blocking the moving to another record; (2) an
error exists in the programming that is running at that time; (3) your setup
is not proper for using this action; etc.

 

[Elaine]

Ken,
It is one form (no sub forms). I have tried the record source being a table
as well as a query. There is a combo box that selects records from the table
in which I want to select the record. The user selects the record and in the
on click of that field, the following code runs:

Private Sub SrchRec_Click()
On Error GoTo Err_SrchRec_Clk
GlbCBIDSrch = Empty
GlbCBIDSrch = Me.SrchRec.Column(0)
Dim Fnd As Variant
Fnd = Me.SrchRec.Column(0)

Forms![frmcbopen]![CmpltNum].SetFocus
DoCmd.FindRecord Fnd

Forms![frmcbopen]![CmpltTyp].SetFocus

Exit_SrchRec_Clk:
Exit Sub
Err_SrchRec_Clk:
ErrorMessage ("CBOpen Srch Record Click")
Resume Exit_SrchRec_Clk
End Sub
This exact code used in the same manner works on other forms in this same
mdb. There are 124 fields on the form and in the sql table which is more
than in the other forms/tables. Aside from that, I see no differences.

I would prefer to us a query with a like statement looking at the primary
field being equal to the field on the form. In that way the user does not
have a record appearing until they select something. However, at this point,
anything is ok if it works! (Note a bit of frustration going on... )

Thank you for your help, Elaine

 

[Ken Snell \(MVP\)]

I recommend using the AfterUpdate event of the combobox, not the Click
event, to run search code.

Is CmpltNum the name of the form on which this combobox is located? Are you
trying to search within the same form?

Is the combobox bound to a field in the form's recordsource table or query?
Is that the field in which you want to find the selected value?

Assuming all answers are yes, here is the code that I recommend:

Private Sub SrchRec_AfterUpdate()
If Len(Me.Srch.Rec.Column(0) & "") > 0 Then
With Me.RecordsetClone
.FindFirst Me.SrchRec.ControlSource & "=" & Me.Srch.Rec.Column(0)
If .NoMatch = False Then Me.Bookmark = .Bookmark
End With
End If
End Sub

The above assumes that the field to which the combo box is bound is a
numeric field. If it's text or date type, then a slight change is needed in
the code syntax -- but I'll not post that unless you indicate that the field
is one of these.

Otherwise, using your FindRecord code, this should work:

Private Sub SrchRec_AfterUpdate()
If Len(Me.Srch.Rec.Column(0) & "") > 0 Then
DoCmd.FindRecord Me.Srch.Rec.Column(0)
End If
End Sub


--

Ken Snell
<MS ACCESS MVP>

Ken,
It is one form (no sub forms). I have tried the record source being a
table
as well as a query. There is a combo box that selects records from the
table
in which I want to select the record. The user selects the record and in
the
on click of that field, the following code runs:

Private Sub SrchRec_Click()
On Error GoTo Err_SrchRec_Clk
GlbCBIDSrch = Empty
GlbCBIDSrch = Me.SrchRec.Column(0)
Dim Fnd As Variant
Fnd = Me.SrchRec.Column(0)

Forms![frmcbopen]![CmpltNum].SetFocus
DoCmd.FindRecord Fnd

Forms![frmcbopen]![CmpltTyp].SetFocus

Exit_SrchRec_Clk:
Exit Sub
Err_SrchRec_Clk:
ErrorMessage ("CBOpen Srch Record Click")
Resume Exit_SrchRec_Clk
End Sub
This exact code used in the same manner works on other forms in this same
mdb. There are 124 fields on the form and in the sql table which is more
than in the other forms/tables. Aside from that, I see no differences.

I would prefer to us a query with a like statement looking at the primary
field being equal to the field on the form. In that way the user does not
have a record appearing until they select something. However, at this
point,
anything is ok if it works! (Note a bit of frustration going on... )

Thank you for your help, Elaine

You'll have to tell us a lot more details about the form, where/when
you're
using this, how you're using this, etc. This error message usually shows
up
when (1) some other event is blocking the moving to another record; (2)
an
error exists in the programming that is running at that time; (3) your
setup
is not proper for using this action; etc.

 

[Ken Snell \(MVP\)]

Sorry, typos in my codes:

Private Sub SrchRec_AfterUpdate()
If Len(Me.Srch.Rec.Column(0) & "") > 0 Then
With Me.RecordsetClone
.FindFirst Me.SrchRec.ControlSource & "=" & Me.SrchRec.Column(0)
If .NoMatch = False Then Me.Bookmark = .Bookmark
End With
End If
End Sub


Private Sub SrchRec_AfterUpdate()
If Len(Me.SrchRec.Column(0) & "") > 0 Then
DoCmd.FindRecord Me.SrchRec.Column(0)
End If
End Sub


--

Ken Snell
<MS ACCESS MVP>

I recommend using the AfterUpdate event of the combobox, not the Click
event, to run search code.

Is CmpltNum the name of the form on which this combobox is located? Are
you trying to search within the same form?

Is the combobox bound to a field in the form's recordsource table or
query? Is that the field in which you want to find the selected value?

Assuming all answers are yes, here is the code that I recommend:

Private Sub SrchRec_AfterUpdate()
If Len(Me.Srch.Rec.Column(0) & "") > 0 Then
With Me.RecordsetClone
.FindFirst Me.SrchRec.ControlSource & "=" & Me.Srch.Rec.Column(0)
If .NoMatch = False Then Me.Bookmark = .Bookmark
End With
End If
End Sub

The above assumes that the field to which the combo box is bound is a
numeric field. If it's text or date type, then a slight change is needed
in the code syntax -- but I'll not post that unless you indicate that the
field is one of these.

Otherwise, using your FindRecord code, this should work:

Private Sub SrchRec_AfterUpdate()
If Len(Me.Srch.Rec.Column(0) & "") > 0 Then
DoCmd.FindRecord Me.Srch.Rec.Column(0)
End If
End Sub


--

Ken Snell
<MS ACCESS MVP>

Ken,
It is one form (no sub forms). I have tried the record source being a
table
as well as a query. There is a combo box that selects records from the
table
in which I want to select the record. The user selects the record and in
the
on click of that field, the following code runs:

Private Sub SrchRec_Click()
On Error GoTo Err_SrchRec_Clk
GlbCBIDSrch = Empty
GlbCBIDSrch = Me.SrchRec.Column(0)
Dim Fnd As Variant
Fnd = Me.SrchRec.Column(0)

Forms![frmcbopen]![CmpltNum].SetFocus
DoCmd.FindRecord Fnd

Forms![frmcbopen]![CmpltTyp].SetFocus

Exit_SrchRec_Clk:
Exit Sub
Err_SrchRec_Clk:
ErrorMessage ("CBOpen Srch Record Click")
Resume Exit_SrchRec_Clk
End Sub
This exact code used in the same manner works on other forms in this same
mdb. There are 124 fields on the form and in the sql table which is more
than in the other forms/tables. Aside from that, I see no differences.

I would prefer to us a query with a like statement looking at the primary
field being equal to the field on the form. In that way the user does
not
have a record appearing until they select something. However, at this
point,
anything is ok if it works! (Note a bit of frustration going on...
)

Thank you for your help, Elaine

You'll have to tell us a lot more details about the form, where/when
you're
using this, how you're using this, etc. This error message usually shows
up
when (1) some other event is blocking the moving to another record; (2)
an
error exists in the programming that is running at that time; (3) your
setup
is not proper for using this action; etc.

--

Ken Snell
<MS ACCESS MVP>



I am using this in other forms in the same frontend mdb and it works
fine.
In one form, however, it does not. I have tried the form's record
source
as
both the a table and a query.

Anyone found a reason or solution for this? Thanks.