There might be something that you can't pass to a function or sub but I don't know what that might be. As long as Access can work with it, I suppose the limitation would be size. You cannot say, well if it's too big to be in the database, it's not an issue. That would be incorrect, for as long as you have the required references, a function or sub could work with anything, such as an entire Excel workbook.
In your case, the question might not be 'can I pass a recordset to a procedure', rather is there an easier or better way to do something. I might be forgetting the exact purpose of the Weekday function, but I believe it's for getting the number of days between two dates that are not Saturdays or Sundays, so you don't need your function to do that all, and certainly not in a looping fashion ("First it checks to see if a date is a Sunday or a Saturday"). BTW, each time it reaches "If Weekday(...) this function runs against the variable, and if that means remote access, off it goes again across cyberspace. I'm not sure of the exact purpose of your function and what you're doing with it, but if it is to get the number of working days between two dates, the Weekday function returns a number which includes holidays. For that, no remote access required. A query against your holiday table returns the number of holidays between those two dates. The difference is the number of working days between the two dates. That could be a local table if management of this will not be an issue. Obviously some maintenance is required since some holidays move around in the calendar year, meaning a redistribution of the FE containing such a table. The thing to consider is whether or not you're overdoing the reach out to the linked table for your purposes.
Final note on your question. If a public variable is at the module level, or in the case of a form or report, in the declarations section (top) its scope is either database (former) or form/report (latter) which means it's available if the db is open or the form/report is open. Probably not too useful for a report since it doesn't have any controls you can use to interact with that variable. So at the module level, any form could use it. As long as that variable is alive, the machine memory allocated to it is taken up, so I'd have two ways of closing the recordset and reclaiming the memory space. That is to say, when a single form that uses it is closed (obviously you cannot do this on each form if more than one form needs it) as well as during db shutdown. This means telling people to use the db close button you provide to minimize resource drain (or live with the effects if any) or disable the db close button, which can end up being left disabled for all Access databases. I never had a problem with that beyond maybe having to tell someone to open the db and close it properly since the button was re-enabled during the shutdown routine.
- "doesn't work" is no help.Error #s/text do. State what's happening.
- Use code tags for code/sql; show where errors occur
Make all suggested changes in copies of your database or to its objects