Calling a recordset from another form

I created a form that calls information from a database, and on the side it has other buttons to call other forms that have their own databases to take information from. There is usually 2 fields that are common throughout the databases and from the main form I create a filter that is applied to the appropriate form that is opened by clicking on the buttons. My question is this:

I want to call a Msgbox() function once a user clicks on a button if there are no records brought up once a filter is applied, and in this msgbox it will ask the user whether or not they want to create a new record, or return to the main one. I can't quite seem to figure it out.

Hope someone can help me solve this problem of mine.

If the recordset is still opened on another form, you can refer to it by using
FormName.RecordsetName......

-Al

ps- I'm not positive what exactly you're asking, so if this isn't what you're looking for, please elaborate on what exactly you need.

If the recordset is still opened on another form, you can refer to it by using
FormName.RecordsetName......

-Al

ps- I'm not positive what exactly you're asking, so if this isn't what you're looking for, please elaborate on what exactly you need.

Hmmmm...

If I select a record from the first form (lets call this form1), and then click on button1 on form1 (which opens form2), I want to check form2's recordsource for records with a previously defined filter from form1. After it is checked, a messagebox is supposed to come up (if there are no records) that says 'There are no records for this selection. Would you like to create a new record? Y/N'. If the user clicks Yes, then it opens form2 and starts the process for a new record. If the user clicks No, then it will not open form2.

I'm sorry if this sounds very confusing, but there is ALOT of code as well as thought that went into making this application what it is right now. If I was to attach all the code to this post it would be like 10mb. If you still don't understand anything please say so. I will try my best to break everything down.

EDIT: Al, at the very beginning of the application (Form1), Form2 has not been opened and its recordset has not been defined.

Ok, I see now. Maybe this is over-simplifying it, but why not set a global variable (in a module, so all forms can access it) when you press the button of form1, and use that in your filter on form2?
-Al

The filter works fine the way it is. I just want to know how I can call the recordsource for form2 in form1, put a filter on it, and check to see if there are any records available.

Well.. no replies. I guess it can't be done.

I have another idea:

Is it possible to do a SQL Select command on the appropriate table when you click on button one, turn it into a recordset (or clone), then analyze it with EOF and BOF to return a messagebox if there are no records?

Why not just check the record count of the recordset instead of checking EOF or BOF?

Why not just check the record count of the recordset instead of checking EOF or BOF?

Yea I can do that instead. I'm just not sure about the commands that let me open a table to create a recordset and do the record count.

Well.. no replies. I guess it can't be done.

I have another idea:

Is it possible to do a SQL Select command on the appropriate table when you click on button one, turn it into a recordset (or clone), then analyze it with EOF and BOF to return a messagebox if there are no records?

Yes. You can have the click event of a button perform your select, get your data into a recordset and check and see how many records are in that recordset. Check MSDN as they should have some examples on how to do this. If not let me know and I can give you some examples.

Good luck.

However, depending upon the database/cursor location, .Recordcount may not be available. Using the SQL COUNT aggregate function is available on every SQL implementation that of which I'm aware, though. It's very easy to first issue a Select Count(Field1) as rsCount from YourTable Where... , retrieve the value of the rsCount alias, then, close the recordset, and re-query with field name(s) in place of the COUNT aggregate function.

True true...Thanks for catching my slip!

Not a slip, D.H. - just a slightly more generic approach.