Pop-up box in form

angiew · Aug 7, 2000

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? Angie Wenzel

samara · Aug 7, 2000

you can write this code in the Open event of the form you wanna apply filter to... Private Sub Form_Open(Cancel as Integer)    Dim MyRs as recordset    set MyRs = Me.RecordsetClone   MyRs.MoveLast   MyRs.MoveFirst     ' if there's no record, Exit   if MyRs.RecordCount < 1 Then     Msgbox "your msg"     Cancel = true   End If End Sub i didnt try it now, but i think it works, Hope this would help YOU... Mohamed Aly <a href=mailto:samara_79@hotmail.com>samara_79@hotmail.com</a> <a href= > </a>

angiew · Aug 7, 2000

Thanks for your response! I tried your code and got the following error: Run-time error '13': Type mismatch Then it gave me the option to debug, and the line highlighted by the debugger was: set MyRs = Me.RecordsetClone Any ideas? Angie Wenzel

DougP · Aug 8, 2000

try this          Set MyRs = Forms![Formname].Form.RecordsetClone DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a> Ask me how Bar-codes can help you be more productive.

angiew · Aug 8, 2000

Doug, I tried your line of code and got the same error with that line still highlighted by the debugger.  Any other ideas? Angie

samara · Aug 8, 2000

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.... Private Sub Form_Open(Cancel as Integer)    Dim MyRs as DAO.recordset   <<< JUST edit this LINE...    set MyRs = Me.RecordsetClone   MyRs.MoveLast   MyRs.MoveFirst     ' if there's no record, Exit   if MyRs.RecordCount < 1 Then     Msgbox "your msg"     Cancel = true   End If End Sub Hope this would help YOU... Mohamed Aly <a href=mailto:samara_79@hotmail.com>samara_79@hotmail.com</a> <a href= > </a>

angiew · Aug 8, 2000

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: Compile error: User-defined type not defined. The debugger highlights the new line of code: MyRs as DAO.recordset Any other ideas? Angie

samara · Aug 9, 2000

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 1) go to Visual Basic Editor 2) chosse Tools, References 3) from the dialog box, check "Microsoft DAO 3.51 Object library" or "Microsoft DAO 3.6 Object library" 4) now, try again your code and let me know if it works or not..... Mohamed Aly <a href=mailto:samara_79@hotmail.com>samara_79@hotmail.com</a> <a href= > </a>

angiew · Aug 9, 2000

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? Angie

samara · Aug 9, 2000

man, could you send your database to my e.mail and i'll take care of it... Mohamed Aly <a href=mailto:samara_79@hotmail.com>samara_79@hotmail.com</a> <a href= > </a>

angiew · Aug 9, 2000

I wish I could, but it has sensitive material regarding the company I work for.  Besides, I really need to learn how to do this kind of stuff. Thanks for all your help! Angie

samara · Aug 9, 2000

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... 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 ) In order to make the code works, you have two choices 1) apply the filter to a query that the form's RecordSource based on 2) set the form's RecordSource = SQL statement which will match your selected filter at last you have to delete the following two lines from the code above   MyRs.MoveLast   MyRs.MoveFirst 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. i hope you could now know what's the wrong and try to work around it, Good Luck let me know about your progress, and you are always welcome... Mohamed Aly <a href=mailto:samara_79@hotmail.com>samara_79@hotmail.com</a> <a href= > </a>

angiew · Aug 9, 2000

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 · Aug 9, 2000

*Then it gave me the option to debug, and the line *highlighted by the debugger was: * *set MyRs = Me.RecordsetClone You may want to try:  set MyRs = Me.Recordset.Clone

angiew · Aug 9, 2000

Thanks for the idea, but it didn't change anything.  Any other ideas? Angie

samara · Aug 10, 2000

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)... 1) Table1: ID Names     City Tel 1 Mohamed         Alex 5012297 2 Ahmed Alex 5021336 3 Mohamed         Alex 5203645 4 Aly Cairo 2036456 2) and "Form1" which has two combo boxes: combo0, RowSource: SELECT DISTINCT [Names] FROM Table1; combo2, RowSource: SELECT DISTINCE [City] FROM Table1; and has one command button (RUN) which opens "Form2" 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])); this form has also 4 text boxes for the 4 fields in the above Table1 (ID, Names, City, Tel) and our code: Private Sub Form_Open(Cancel As Integer)     Dim MyRs As DAO.Recordset     Set MyRs = Me.RecordsetClone          If MyRs.RecordCount < 1 Then         MsgBox "Your MSG"         Cancel = True     End If End Sub 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... Let me know about that... Mohamed Aly <a href=mailto:samara_79@hotmail.com>samara_79@hotmail.com</a> <a href= > </a>

angiew · Aug 10, 2000

I appreciate your idea, but my database is huge and it would be rather tedious to change it at this point.  I really just need something to fix this one problem.  Can you think of anything else? Angie

angiew · Aug 10, 2000

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!! Angie

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!

Pop-up box in form

Technical User

Programmer

Technical User

MIS

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Technical User

Technical User

Programmer

Technical User

Technical User

Similar threads

Log in

Part and Inventory Search

Sponsor