If no record, show default subform 'no records'

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

Hi All

I have a form and some buttons and a subform

Press the buttons and it changes the subform in the subform control,
which is filtered based on textboxes on the mainform.

If the filter doesn't have any record, I want to show a custom form that
says there are no records for that student.

How do I do this?

Thanks in advance

Jeff
 
Jeff,

You could make a simple form that shows the information you want, maybe
all it has is a Label with the "no records" text.

Then, in the code that runs with the buttons, maybe something like this...
If DCount("*","QueryThatReturnsTheFilteredRecords")=0 Then
Me.NameOfSubformControl.SourceObject = "NoRecordsForm"
End If
 
Steve said:
Jeff,

You could make a simple form that shows the information you want, maybe
all it has is a Label with the "no records" text.

Then, in the code that runs with the buttons, maybe something like this...
If DCount("*","QueryThatReturnsTheFilteredRecords")=0 Then
Me.NameOfSubformControl.SourceObject = "NoRecordsForm"
End If
Click to expand...

Thanks for your advice

I have this code on the On Current event of a subform:

'Declare the variables.
Dim Result

'Assign values to the variables to be used in the criteria.

Result = Nz(DLookup("[AlunoRef]", "tbAlunosCurso", "[AlunoRef] = "
& Me.AlunoRef.Value & " And [AnoDoCurso] = " & Me.AnodoCurso.Value & "
And [SemestredoCurso] = " & Me.SemestredoCurso.Value), "0")

If Result = "0" Then
Me.SubDetails.SourceObject = "No Records"
MsgBox "No Results Found"
Else
Me.SubDetails.SourceObject = "AlunoPag"
MsgBox Result
End If


This works fine when I open the main form. It works.

The problem is when I click a button to change the form in the subform
control to (lets say, AlunoDetails) and then click back to make the
subform AlunoPag again, it seems to flash quickly and then gives the
error "Can't open any more tables" and then Access Crashes on me. It
appears like it's counting and counting so many records.

Any ideas,

jeff
 
Jeff,

I can't explain specifically where the problem is arising. But I would
instinctively avoid using the Current event of a subform in this way.
Especially as in this case you are using the Current event of the
subform to run code which changes the form contained in the subform,
which in turn triggers another Current event... Would it serve your
purpose to run the code on the Click event of the main form Command
Buttons instead?
 
Steve said:
Jeff,

I can't explain specifically where the problem is arising. But I would
instinctively avoid using the Current event of a subform in this way.
Especially as in this case you are using the Current event of the
subform to run code which changes the form contained in the subform,
which in turn triggers another Current event... Would it serve your
purpose to run the code on the Click event of the main form Command
Buttons instead?
Click to expand...

Thanks Steve,

I followed your advice and put it in a loop to go through once and now
it's functioning well.

Appreciate your help greatly,

Jeff
 
Back
Top