My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 What if there are no records?
|
|
 Aug 26 2007, 03:28 PM
Post
#1
|
|
|
UtterAccess Guru Posts: 753 From: Rendon, Texas  |
Hello UA,
I'm using a Public Sub to fill an unbound control (txtRCount), on several forms, to count the records in the record set. All works well except when there are no records. I am using the Filter and FilterOn of continuous forms and would like to know when there are no matching records so I can pop a MsgBox and let the user know this and then set FilterOn to False. Below is the Public Sub I'm using. Could someone lend a hand and tell me how to trap for no records. (FYI I am using the MsgBox "no records" just to help me see whats happening. TIA, Shane CODE Public Sub UpdateRecordCount(frm As Form)
Dim rs As DAO.Recordset Set rs = frm.RecordsetClone rs.MoveLast rs.MoveFirst If rs.RecordCount = 0 Then MsgBox "No Records" Else frm.txtRCount = rs.RecordCount End If rs.Close Set rs = Nothing End Sub |
 |
 
|
|
Aug 26 2007, 04:01 PM
Post
#2
|
|
|
Utterly Yorkshire and Forum/Wiki Editor Posts: 15,881 From: Devon UK |
Try changing it to......
CODE Dim rs as Object Set rs = frm.RecordsetClone If rs.RecordCount > 0 Then rs.MoveLast rs.MoveFirst Msgbox rs.Recordcount Else Msgbox "No records" End If rs.Close Set rs = Nothing **Untested air code** |
|
|
|
|
Aug 26 2007, 06:30 PM
Post
#3
|
|
|
UtterAccess Guru Posts: 753 From: Rendon, Texas |
Thanks Alan. I gave your code a ride but ended up with the same results. I have several command buttons that filter the continuous form according to today's date, overdue, future, and all. I am wanting to stop or at least go to all records if the filter turns up with no records. Right now with my code and yours both, it gives me nothing when there are no matching records.
Shane |
|
|
|
|
Aug 26 2007, 06:35 PM
Post
#4
|
|
|
Utterly Yorkshire and Forum/Wiki Editor Posts: 15,881 From: Devon UK |
How are you calling your sub routine and what value are you passing for the Form object...?
|
|
|
|
|
Aug 26 2007, 06:42 PM
Post
#5
|
|
|
UtterAccess Guru Posts: 753 From: Rendon, Texas |
This is how I'm calling it:
Call UpdateRecordCount(Me) As far as what value I'm passing, I'm afraid I'm not sure what your asking me. |
|
|
|
|
Aug 26 2007, 07:04 PM
Post
#6
|
|
|
Utterly Yorkshire and Forum/Wiki Editor Posts: 15,881 From: Devon UK |
Mmm........I've just tested it using the Filter and FilterOn properties and it's working fine for me.....
Can you post the full code you're using in the On Click of your command button, or better still a copy of your db zipped to less than 500k and no sensitive data and I'll take a look for you........ |
|
|
|
|
Aug 26 2007, 07:09 PM
Post
#7
|
|
|
UtterAccess Guru Posts: 753 From: Rendon, Texas |
Hey Alan, below is the code behind one of the buttons. I am changing the records based off of the control that the due date is set for, so filter the recordset matching today's date or in this example dates that are in the future or past today's date.
I have the Call for: Call UpdateRecordCount(Me) in the OnCurrent event of the form (incase that means something to you also) Sorry I can not zip my db for both reasons. It's too big and it does have sensitive data. Thanks for your help Shane CODE Private Sub cmdFuture_Click() Me.Filter = "dtDue > #" & Date & "#" Me.FilterOn = True End Sub |
|
|
|
|
Aug 26 2007, 07:19 PM
Post
#8
|
|
|
Utterly Yorkshire and Forum/Wiki Editor Posts: 15,881 From: Devon UK |
No problem with not posting your db....
Try this........ CODE Private Sub cmdFuture_Click() Me.Filter = "dtDue > #" & Date & "#" Me.FilterOn = True Call UpdateRecordCount(Me) End Sub with the code I posted earlier in your public sub....... Also, instead of having several command buttons, you could have an option group with radio buttons for as many filter options you need and just one command button......... |
|
|
|
|
Aug 26 2007, 07:29 PM
Post
#9
|
|
|
UtterAccess Guru Posts: 753 From: Rendon, Texas |
Thanks for the help Alan. It does work if I call if from the OnClick event behind the cmdButton but not if I call it from the forms OnCurrent event. We have it working now so I roll with this. I also appreciate the advise on the option group and in many cases do just that but this is on a toolbar "looking" thingy I designed and I thought it would look better this way.
Thanks again, Shane |
|
|
|
|
Aug 26 2007, 07:37 PM
Post
#10
|
|
|
Utterly Yorkshire and Forum/Wiki Editor Posts: 15,881 From: Devon UK |
No problem...glad you got it working
It wont work in the On Current event as you haven't yet applied a filter when you move to a new record (which is when the On Current event fires), unless you add code to do it. You'll need to call the public sub from the On Click of each of your command buttons |
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 18th May 2013 - 08:25 PM |