Re: Re-post: Yet another problem with "no current record"

Tech-Archive recommends: Fix windows errors by optimizing your registry

See below for details, but I think I may not have made myself clear. The
error doesn't happen inside this script. It only happens when I click on
one of my controls (list box, text box or command button) AFTER I have
successfully retrieved no rows, which is what I wanted to do. So the
question is, not how can I change what the query does, but how can I stop
the "no current record" error when I click on one of my controls when I do,
actually, have no current record. Why should clicking on the control care?

Since I waited a few days to respond to this, could you just REPLY "got it,"
even if you have no more ideas? So I know the conversation continued?

thanks
las

"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:eo45$aTyGHA.4840@xxxxxxxxxxxxxxxxxxxxxxx
Okay, some suggestions on where to start looking, so we can at least
eliminate factors that are not causing the problem.

1. Drop the line:
GoTo RequeryPlace
It is not doing anything.
The Else block won't exeucte if the If block is executing.
And the code after RequeryPlace executes for both cases anyway. (Not sure
if you intend that.)

Yup, did that. But made no difference.


2. I'm not sure if your brackets are right in the first Else. Try:
If IsNull(Me.txtGrade_Date) OR IsNull(Me.cboSubject_Code) OR
IsNull(Me.cboType) _
OR (Nz(Me.txtDescription, vbNullString) = vbNullString) Then
cmdGO.Enabled = False
Else
cmdGO.Enabled = True
End If


Made no difference - again, no problem in the logic within the routine that
I'm aware of.

3. Try replacing Recordset with RecordsetClone, to see if it makes a
difference.
Perhaps replace:
If Not (Me.Recordset.BOF And Me.Recordset.EOF) Then
with
If Me.RecordsetClone.RecordCount = 0 Then

No change.


4. Is the text box txtCriterion applied against a Text field, or against a
Number field? If Number, drop the quotes, i.e.:
Me.txtCriterion = -999

Interestingly, no change here either. I thought text boxes always contained
strings. The Criterion value ist tested against a number, so I tried
removing the quotes. Again - no change in the forms behavior.


5. Leave the Requery line commented out, and run the form to the point
where you would expect to see the error if it was not commented out. Then
open the query directly from the Database window. Does the query itself
give the 'No Current Record' error?

Here's where I figured I hadn't made myself clear. The error doesn't happen
within this script. It happens after I've successfully requiried to empty
out the form. I don't know how to "run the form to the point where you
would expect to see the error." Is there some sort of "pre-click" event I
could put in the controls?


6. Can you avoid the problem by leaving the form's AllowAdditions to Yes
so that it can show the new record when there is nothing else to show?
(You can block the new record by cancelling the form's BeforeInsert
event.)

I could, but I would rather not. I think it would be pretty confusing to
the user. I assume it would solve the problem.


7. Any code in the Current event of the form?
Particularly, anything that is attempting to operate on a value in a
control (bound or not)?
More info on this one:
http://allenbrowne.com/bug-06.html

Nope - no code there. The only form-level code I have is in Form_Open.


8. The source query for this form:
Does it contain multiple tables?
With outer joins?
Any aggregation (e.g. GROUP BY or DISTINCT)?
Any yes/no fields on the outer side of the join?
More info on this:
http://allenbrowne.com/bug-14.html

No, just one table. Here's the SQL.

SELECT fncStudentName([student_id]) AS Expr1, tblAcademics.Grade,
tblAcademics.Comment, tblAcademics.Assessment_Code
FROM tblAcademics
WHERE
(((tblAcademics.Assessment_Code)=[Forms]![frmAcademicsEntry]![txtCriterion])
AND
((tblAcademics.Assessment_Date)=[Forms]![frmAcademicsEntry]![txtGrade_Date])
AND
((tblAcademics.Subject_Code)=[Forms]![frmAcademicsEntry]![cboSubject_Code]))
ORDER BY fncStudentName([student_id]);


--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Laurel" <FakeMail@xxxxxxxxxxx> wrote in message
news:OGJWhCTyGHA.3492@xxxxxxxxxxxxxxxxxxxxxxx
I posted this in "getting started" almost a week ago, and my experience is
that if it isn't answered in the first day or two, it isn't going to
happen. So I'm hoping I'll get a different audience here - hope that's
alright. I've Googled "groups," and didn't find an answer. Could someone
suggest some things to try, even if you don't have a sure answer? I've
made lots of similar forms, but never had this particular problem.

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

I have a form with 4 unbound text boxes and a button. The RecordSource
for
the form uses values in the text boxes. When I open the form, all is
fine.
No rows show because the criteria are all null and there are no rows in
the
database that match that situation (at least I assume that's why no rows
show up.) I set "No Additions" to True in my code, so no empty row shows
up
either. Once I've entered values, if the user clicks the button, then,
after a bunch of other stuff, a Me.Requery is executed and rows show up.

My problem happens after I set one of the values to -999 and execute
another
Me.Requery, to empty out the form of rows so the user can start over. (No
rows will be returned with that value). After I do that, whenever I
click
on any of the unbound text boxes or the button, I get a "No current
record"
message. I don't have to enter any data, pull down a dropdown or
anything.
And the message doesn't seem to affect the rest of the performance of the
form. The user can enter new data, click the button, and get a new set
of
records. As far as I can see this "No current record" is not tied to any
code I've written. In other words, I can't find the actual event where
the
message is being displayed in the debugger.

If I click the same text box or button a second time, the message doesn't
appear.

Again - I don't need to enter data or pull down the dropdown for the
error
to happen. Just "click". The only code I have associated with the text
boxes is "after_update" - nothing in "click".

Here's the routine that causes the problem. It is executed from the
after_update events on the controls. If I comment out the Me.Requery at
the
bottom, the problem goes away.

Any ideas?

Private Sub SetControlStates()
'This function serves as a template for other new functions
Dim ls_temp As String

On Error GoTo Err_SetControlStates

'First test for non-description re-entered on already loaded form
(need
new description)
If (cmdGO.Enabled = True) And (ib_DescriptionEntered = False) Then
[txtDescription] = ""
cmdGO.Enabled = False
GoTo RequeryPlace
Else 'Either description entered, or first time entry
If (Not (IsNull(txtGrade_Date))) And (Not
(IsNull(cboSubject_Code)))
_
And (Not (IsNull(cboType))) And (Not (IsNull(cboClass_Code)))
_
And (Not (IsNull(txtDescription)) Or (Trim(txtDescription) =
"")) Then
cmdGO.Enabled = True
Else
cmdGO.Enabled = False
End If
End If

RequeryPlace:
If Not (Me.Recordset.BOF And Me.Recordset.EOF) Then
'Empty out old records
[txtCriterion] = "-999"
' Me.Requery
Else

End If
Exit_SetControlStates:
Exit Sub

Err_SetControlStates:
ls_temp = "SetControlStates:" _
& vbCrLf & " " & Err.Description
MsgBox ls_temp

Resume Exit_SetControlStates
End Sub




.

Quantcast