[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 656
  • Last Modified:

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
0
caandal
Asked:
caandal
  • 10
  • 10
  • 2
1 Solution
 
dpragerCommented:

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

thinking that should do the trick.
0
 
caandalAuthor Commented:
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
0
 
dpragerCommented:
wich line gives the problem becaus the error is quit clear.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
rockiroadsCommented:
You have to first open the form eg

'Open Template1 for edit but hidden
DoCmd.OpenForm "Template1", acDesign, , , acFormEdit, acHidden
Forms!Template1.Form.RecordSource = "vTemplate1"
'Close form, save changes
DoCmd.Close acForm, "Template1", acSaveYes

'Open Form
stDocName = "Template1"
stLinkCriteria = "[Refid]=" & ReportCheck
DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria
Forms![Template1]![FName] = "Cache"

0
 
rockiroadsCommented:
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
0
 
rockiroadsCommented:
urm vTemplate1 a query or table? thats what I meant
0
 
caandalAuthor Commented:
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
0
 
caandalAuthor Commented:
vTemplate1 is a sql view that I have an ODBC link to
0
 
rockiroadsCommented:
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?
0
 
caandalAuthor Commented:
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
0
 
caandalAuthor Commented:
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?
0
 
rockiroadsCommented:
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
0
 
caandalAuthor Commented:
Star you are  - burning the candle at both ends - did not see it.
0
 
rockiroadsCommented:
Cool. Bet your enjoying the cricket at the moment, whooping the aussies :)
0
 
caandalAuthor Commented:
Yip enjoying that I must admit - how're things on your side?
0
 
rockiroadsCommented:
All good man, thanks for asking :) Currently waiting for the IPL to start, looking forward to that.
0
 
caandalAuthor Commented:
I think that is going to be brilliant - all the damn tickets were sold out here in about 2 and half hours
0
 
rockiroadsCommented:
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 :)
0
 
caandalAuthor Commented:
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
0
 
rockiroadsCommented:
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!
0
 
caandalAuthor Commented:
Cheers  go well enjoy
0
 
rockiroadsCommented:
Thanks :)
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 10
  • 10
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now