Link to home
Start Free TrialLog in
Avatar of caandal
caandal

asked on

is there any way to set a forms recordsource before opening it in vba

Hi  there
Is there any way to set a form's recordsource property in Access using VBA before opening the specified form?
Thanks
Avatar of dprager
dprager


Me.formname.Form.RecordSource = "SELECT SQL Statement"
docmd.openform "formname"

thinking that should do the trick.
Avatar of caandal

ASKER

Hi dprager
Here is the code that is running but is producing the attached error.

stDocName = "Template1"
                Forms!Template1.Form.RecordSource = "vTemplate1"
                stLinkCriteria = "[Refid]=" & ReportCheck
                DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria
                Forms![Template1]![FName] = "Cache"
Thanks
Error.jpg
wich line gives the problem becaus the error is quit clear.
ASKER CERTIFIED SOLUTION
Avatar of rockiroads
rockiroads
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
dprager has shown you how to set the recordsource, I just told you that you need to open the form first.

What is vTemplate1, is this the name of a query? if so then it will be fine else do what dprager says, use sql or tablename
urm vTemplate1 a query or table? thats what I meant
Avatar of caandal

ASKER

Hi Guys

I have no problem opening the form normally  But I need to be able to change the recordsource of the form before I open it.  The system is not finding the form Template1 becuase it is not open - hence my question
Avatar of caandal

ASKER

vTemplate1 is a sql view that I have an ODBC link to
If you manually set the forms recordsource to vTemplate1, does it work fine?

Is there any difference between vTemplate1 and the current form's recordsource?

Did u try opening the form as hidden edit, then changing the recordsource, then closing it?
Avatar of caandal

ASKER

Hi there

Yes it works fine.  vTemplate1 returns all the records in the recordset the current record source is also a view but only returns a few rows based on the where clause in the query
Avatar of caandal

ASKER

never opened a form as hidden edit but it sounds good.  What do I have to specify in the code?  Will the form the retain that recordsource once I close it?
I gave a code example earlier

it does retain that recordsource, yes. if you dont then, it needs to be reset, maybe on exit of form or after form called
Avatar of caandal

ASKER

Star you are  - burning the candle at both ends - did not see it.
Cool. Bet your enjoying the cricket at the moment, whooping the aussies :)
Avatar of caandal

ASKER

Yip enjoying that I must admit - how're things on your side?
All good man, thanks for asking :) Currently waiting for the IPL to start, looking forward to that.
Avatar of caandal

ASKER

I think that is going to be brilliant - all the damn tickets were sold out here in about 2 and half hours
I was disappointed a little since there was an outside chance of being held in the UK. Still you have the better weather. 2.5 hours is incredible. Im hoping it is as good as it was last year. There is a danger of it being better than the 20/20 world cup! which incidentally is in UK and Im going too :)
Avatar of caandal

ASKER

Ahhh Noooo!
Refer to Beached whale at
http://www.youtube.com/results?search_type=&search_query=beached+whale&aq=0&oq=beached

You lucky Chap - I would love to be there
Evens out I guess, u got IPL and we got 20/20. atmosphere should be good in both tournaments
first thing I noticd in that link was the fat woman getting stuck!
Avatar of caandal

ASKER

Cheers  go well enjoy
Thanks :)