Message box when there are no records in subform.

[Guest]

Hello,

I have a subform bound to an unbound textbox and I'm trying to figure out
how to have a msgbox appear if no records show from the entry in the textbox.

I've looked elsewhere online and cant find anything I think can help.

Thank You
Dave

 

[Carl Rapson]

Hello,

I have a subform bound to an unbound textbox and I'm trying to figure out
how to have a msgbox appear if no records show from the entry in the
textbox.

I've looked elsewhere online and cant find anything I think can help.

Thank You
Dave

First, let's clarify. You're populating your subform when a value is entered
into the textbox. Is this correct? If so, what code are you using to
populate the subform? In other words, how does the value in the textbox get
translated into the RecordSource for the subform? If you're building a query
and setting the subform's RecordSource to that query, then all you would
have to do is use that same query to open a recordset and check the Count
property of the recordset to see if there are any records.

If none of this is correct, you're going to have to furnish more details
about what you're doing.

Carl Rapson

 

[Guest]

Thank you for your reply

I have an unbound textbox(InstName) and I used the subform wizard and I
used a qury(LogoutQry2) for the record source. With the wizard I linked it
to that unbound textbox.

The subform name is LogoutTafSbfm

I orginally tried this code, but it only work when I had allow additions
checked in the sub frm properties.
But, I cant have that checked

Private Sub Form_Current()
If Me.Recordset.RecordCount = 0 Then
MsgBox "No open Taf Records were found"
End If
End Sub

Thank you Again
David

 

[Carl Rapson]

I don't understand what you mean by linking the subform to the unbound
textbox. Where did you make that link?

What I suggest is to use the same query (LogoutQry2) in conjunction with the
textbox value to open a recordset object and check the RecordCount property
of the recordset. You would do this in the AfterUpdate event of the textbox.
Am I correct in guessing that the textbox value is to be used in a WHERE
clause added to the query? Create a QueryDef object based on the query and
use that to fetch its SQL code, then add the textbox value to the SQL code
as a WHERE clause. Something like this:

Dim qry As DAO.QueryDef
Dim strSQL As String
Dim rs as DAO.Recordset

Set qry = CurrentDb.CreateQueryDef("LogoutQry2", strSQL)
strSQL = qry.SQL
' Add your textbox value here
strSQL = strSQL & " WHERE [some field in the query]='" & textbox & "'"
Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveLast
rs.MoveFirst
If rs.RecordCount = 0 Then...

Of course, this assumes you're using DAO; I'm sure you can do something
similar with ADO but I'm not familiar with the syntax. This is also just
"air code", you'd have to flesh it out to make it work.

Carl Rapson