Display message when no records found?

[ErikRP]

I have three sub forms on a main form. Each of the sub forms is attached to its own query and returns the a field of text from the record when a certain field, PROCEDURE, equals a specified value. (Each subform is looking for a specific value.)

Everything is working great, unless there is no record for a given procedure. In that case the individual subform is blank. What I want it to do is display a message, like "This service is not covered." I don't want a message box or anything like that to pop up, I want it to look like it is the same field of text, just a different message. Hopefully that made sense!

Does anyone have any suggestions on how to do this? I'm thinking that this will involve code, which I haven't used too much.

Thanks so much!

 

[Elana]

Let's experiment and see if I can help you without actually looking at your app:

Let's try to work with just one of the subforms right now, and if you get this to work, you can adapt it to the other 2 subforms.
On one of your subforms, insert a textbox whose control source says ="This service not covered"
Set its visible property to false

In the afterupdate event of your Procedure field on your main form, place code:


Dim dbs As Database
dim rst as recordset

Set rst = Forms!yourmainform!yoursubform.Form.RecordsetClone

if rst.recordcount = 0 then

'(display the invisible textbox on the subform)

Forms!yourmainform!yoursubform.Form![invisibletextboxname].visible = true

else

'do nothing - display the associated records

end if

rst.close

Let me know how this works out -

 

[ErikRP]

Hmmn... It seems like the right idea but it isn't quite working. I should add a couple of other points about what I'm doing/showing.

- The form is more of a report than a form, as I'm not adding any information

- I do not have the PROCEDURE field anywhere on the main form or subforms, only in the queries

What I did was to place the code you suggested on various events on the mainform, but nothing happened, the subform remained blank.

I'll keep trying a few things here based on the code that you've suggested. I think this is going to solve the problem and there's just one small thing to change.

Thanks!

 

[Elana]

If you can post your database, I can take a look at it and perhaps give you another idea how to solve the problem.

 

[ErikRP]

Here is the file. I have stripped the data out and added two test cases. Use policy 12345 as a base and 54321 as one that should have the text "Service not eligible", for procedure Fluoride.

Thanks!

 

[Elana]

Okay, here is the code that I placed on the OnOpen event of the main form.

Plus, I disassociated the subform label so it will always display. Plus, I added a text box behind the subform to display the text you want when there are no records.


Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDb()
Set rst = Forms!DemoMain![DemoS3 subform].Form.RecordsetClone

If rst.RecordCount = 0 Then

'don't display the form

Forms!DemoMain![DemoS3 Subform].Form.Visible = False

'display textbox behind the subform
Me.txtProcNotAvailable.Visible = True


Else

'do nothing - display the associated records

End If

rst.Close

 

[Elana]

Here is the db:

 

[ErikRP]

Thanks for your help, Elana!

Could you tell me how you got it to work? Was I missing something or did you change something?

Also, how do I get this to work for the other two procedures as well. Is it as simple as "replicating" of the fluoride to match the other two procedures?

Thanks again!

 

[ErikRP]

Actually I think I have come up with something that appears to work. Can you have a quick check to see if this makes sense?

Thanks again for your help. Not only did it help, but it made sense and (I think ) I've learned something new!


Private Sub Form_Open(Cancel As Integer)
Dim dbs As Database
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim rst3 As Recordset

Set dbs = CurrentDb()
Set rst1 = Forms!DemoMain![DemoS1 Subform].Form.RecordsetClone
Set rst2 = Forms!DemoMain![DemoS2 Subform].Form.RecordsetClone
Set rst3 = Forms!DemoMain![DemoS3 Subform].Form.RecordsetClone

If rst1.RecordCount = 0 Then

'don't display subform1

Forms!DemoMain![DemoS1 Subform].Form.Visible = False

'display textbox behind the subform
Me.txtProc1NotAvailable.Visible = True

Else
'do nothing - display the associated records
End If

rst1.Close

If rst2.RecordCount = 0 Then

'don't display subform2

Forms!DemoMain![DemoS2 Subform].Form.Visible = False

'display textbox behind the subform

Me.txtProc2NotAvailable.Visible = True

Else
'do nothing - display the associated records
End If

rst2.Close

If rst3.RecordCount = 0 Then

'don't display subform3

Forms!DemoMain![DemoS3 Subform].Form.Visible = False

'display textbox behind the subform
Me.txtProc3NotAvailable.Visible = True

Else
'do nothing - display the associated records
End If

rst3.Close
End Sub

 

[Elana]

Looks good to me - if it's working, that means you did it right!

E