I have a form with multiple combo boxes that allows the user to filter a query. Currently, the users click a button labeled "Selections" that takes them to the Filter by Form window where they make their selections. After the selections are made, they click a button labled "Run". This "Run" button is actually the "Apply Filter" button from the Filter/Sort toolbar with a different name. There is the possibility that the users could make choices that would result in no records being returned. When this happens, a completely blank form results, and the only option is to close the form and reopen it. I would like to have a dialog box pop up in the case that a user makes choices that result in no records that explains what has happened and will allow them to return to the "Filter by Form" window. I assume that I will need to write some code under the "Apply Filter" button, but I do not know where to access this code because the button is part of the toolbar. I also do not know what code to use. Can anyone help?<br><br>Angie Wenzel<br>
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Pop-up box in form
- Thread starter angiew
- Start date
you can write this code in the Open event of the form you wanna apply filter to...<br><br>Private Sub Form_Open(Cancel as Integer)<br> Dim MyRs as recordset<br> set MyRs = Me.RecordsetClone<br><br> MyRs.MoveLast<br> MyRs.MoveFirst<br> <br> ' if there's no record, Exit<br> if MyRs.RecordCount < 1 Then<br> Msgbox "your msg"<br> Cancel = true<br> End If<br>End Sub<br><br>i didnt try it now, but i think it works, Hope this would help YOU... <p>Mohamed Aly<br><a href=mailto:samara_79@hotmail.com>samara_79@hotmail.com</a><br><a href= > </a><br>
- Thread starter
- #3
Thanks for your response!<br><br>I tried your code and got the following error:<br><br>Run-time error '13':<br>Type mismatch<br><br>Then it gave me the option to debug, and the line highlighted by the debugger was:<br><br>set MyRs = Me.RecordsetClone<br><br>Any ideas?<br><br>Angie Wenzel<br>
try this<br> <br> Set MyRs = Forms![Formname].Form.RecordsetClone<br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.
- Thread starter
- #5
I think you are using Access 2000, if so you have to make reference to DAO 3,51. then modify the code like the following....<br><br>Private Sub Form_Open(Cancel as Integer)<br> Dim MyRs as DAO.recordset <<< JUST edit this LINE...<br> set MyRs = Me.RecordsetClone<br><br> MyRs.MoveLast<br> MyRs.MoveFirst<br> <br> ' if there's no record, Exit<br> if MyRs.RecordCount < 1 Then<br> Msgbox "your msg"<br> Cancel = true<br> End If<br>End Sub<br><br>Hope this would help YOU... <p>Mohamed Aly<br><a href=mailto:samara_79@hotmail.com>samara_79@hotmail.com</a><br><a href= > </a><br>
- Thread starter
- #7
Yes, I am using Access 2000. Sorry I didn't specifiy that earlier. I tried the new line of code you posted and got a different error:<br><br>Compile error:<br>User-defined type not defined.<br><br>The debugger highlights the new line of code:<br>MyRs as DAO.recordset<br><br>Any other ideas?<br><br>Angie<br>
you are in the right way, but you have missed just one thing that you must make a Reference to DAO 3.51 or DAO 3.6<br><br>1) go to Visual Basic Editor<br>2) chosse Tools, References<br>3) from the dialog box, check "Microsoft DAO 3.51 Object library" or "Microsoft DAO 3.6 Object library"<br>4) now, try again your code<br><br>and let me know if it works or not..... <p>Mohamed Aly<br><a href=mailto:samara_79@hotmail.com>samara_79@hotmail.com</a><br><a href= > </a><br>
- Thread starter
- #9
This took care of the error problems, but now it hasn't changed anything. I still get a blank form and have to completely close it if the selections do not match any records in the query. What am I doing wrong?<br><br>Angie <br>
- Thread starter
- #11
Ok, could you tell us what's the RecordSource for your filtered form, and if the filter apply to the form or to the query that the form based on...<br><br>Anyway, i think that your filter apply directly to the form after it got the Records, and that's why our code dosen't work fine. (I mean that the form already has the whole Records from its RecordSource "Table, Query, SQL" and of course our MyRs.RecordCount always greater than 1 )<br><br>In order to make the code works, you have two choices<br>1) apply the filter to a query that the form's RecordSource based on<br>2) set the form's RecordSource = SQL statement which will match your selected filter<br><br>at last you have to delete the following two lines from the code above<br> MyRs.MoveLast<br> MyRs.MoveFirst<br>if your Recordset really dosen't have any records, Access would give you a runtime error about these two line "no current record". But because your form has records but only filtered it didn't give you that error.<br><br>i hope you could now know what's the wrong and try to work around it, Good Luck<br><br>let me know about your progress, and you are always welcome... <p>Mohamed Aly<br><a href=mailto:samara_79@hotmail.com>samara_79@hotmail.com</a><br><a href= > </a><br>
- Thread starter
- #13
The Record Source for the form is the underlying query. I will explain the database a little more and maybe this will help. The database contains sales information for my company. The sales managers will open the form, allowing them to select filter criteria for a report. They click the
RickBooker
Technical User
*Then it gave me the option to debug, and the line *highlighted by the debugger was:<br>*<br>*set MyRs = Me.RecordsetClone<br><br>You may want to try:<br><br> set MyRs = Me.Recordset.Clone
- Thread starter
- #15
yeah, i got one new idea about your problem. and i'll discuss it now, Suppose you have the following table(this example shows you how you can control your forms, you may change your database to be like this)...<br><br>1) Table1:<br>ID Names City Tel <br>1 Mohamed Alex 5012297 <br>2 Ahmed Alex 5021336 <br>3 Mohamed Alex 5203645 <br>4 Aly Cairo 2036456 <br><br>2) and "Form1" which has two combo boxes:<br>combo0, RowSource: SELECT DISTINCT [Names] FROM Table1;<br>combo2, RowSource: SELECT DISTINCE [City] FROM Table1;<br>and has one command button (RUN) which opens "Form2"<br><br>3) and "Form2" which its RecordSource: SELECT [Table1].[ID], [Table1].[Names], [Table1].[City], [Table1].[Tel] FROM Table1 WHERE ((([Table1].[Names])=[Forms]![Form1]![Combo0]) And (([Table1].[City])=[Forms]![Form1]![Combo2])); <br>this form has also 4 text boxes for the 4 fields in the above Table1 (ID, Names, City, Tel)<br>and our code:<br>Private Sub Form_Open(Cancel As Integer)<br> Dim MyRs As DAO.Recordset<br> Set MyRs = Me.RecordsetClone<br> <br> If MyRs.RecordCount < 1 Then<br> MsgBox "Your MSG"<br> Cancel = True<br> End If<br>End Sub<br><br>4) Now, try to run "Form1" and select two values from the combo boxes. Then click "RUN" button in that form. and see what happens. If "Form2" has records it will open, otherwise it will give you a msg and unload...<br><br>Let me know about that... <p>Mohamed Aly<br><a href=mailto:samara_79@hotmail.com>samara_79@hotmail.com</a><br><a href= > </a><br>
- Thread starter
- #17
- Thread starter
- #18
Just wanted to let everyone know that I was able to modify a toolbar button to meet my needs. Thanks for all of your help and ideas. Though they didn't meet my needs this time, I learned a lot and may be able to apply them in the future. Thanks to you all!!<br><br>Angie<br>
- Status
Similar threads
- Locked
- Question
