×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Pop-up box in form

Pop-up box in form

Pop-up box in form

(OP)
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

RE: Pop-up box in form

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
samara_79@hotmail.com

RE: Pop-up box in form

(OP)
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

RE: Pop-up box in form

try this
    
    Set MyRs = Forms![Formname].Form.RecordsetClone

DougP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

RE: Pop-up box in form

(OP)
Doug,

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

Angie

RE: Pop-up box in form

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
samara_79@hotmail.com

RE: Pop-up box in form

(OP)
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

RE: Pop-up box in form

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
samara_79@hotmail.com

RE: Pop-up box in form

(OP)
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

RE: Pop-up box in form

man, could you send your database to my e.mail and i'll take care of it...

Mohamed Aly
samara_79@hotmail.com

RE: Pop-up box in form

(OP)
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

RE: Pop-up box in form

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
samara_79@hotmail.com

RE: Pop-up box in form

(OP)
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

RE: Pop-up box in form

*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

RE: Pop-up box in form

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

Angie

RE: Pop-up box in form

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
samara_79@hotmail.com

RE: Pop-up box in form

(OP)
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

RE: Pop-up box in form

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close