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

[Laurel]

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

 

[Allen Browne]

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.)

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

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

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

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?

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.)


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

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

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

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

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

 

[Laurel]

Thanks. I'll try all of these but can't this weekend. (Didn't want people
to think I'd lost interest!)

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