Setting Record Source for Form or Report as Stored Proc in VBA

[sard0nicpan]

I don't want to set my record source for my forms/reports in the Properties box, but instead in VBA (by the way, its an Access Project front ending to Sql server DB).

On the Open (report) or Open (form) event I attempted the following:

Me.Report.RecordSource = "Exec dbo.spA1" & myParam
 or 
Me.mForm.RecordSource = "Exec dbo.spA1" & myParam

As I left the subroutine, Access would declare that it could not find the record source or that it did not exist.

So what am I missing? When I manually type in the same thing in the Properties box, it works. However, I would like to code it programmatically because the Parameters that go with the stored procedure change dependent on conditions.

So I guess I have a few questions:
1. What have I missed?
2. On which events should I set the recordset for Forms/Reports?

Thanks in advance,
SP

 

[RoyVidar]

Try adding a space between the SP name and the parameter:

[tt]Me.Report.RecordSource = "Exec dbo.spA1 " & myParam[/tt]

There's a forum dedicated ADP's - forum958 - might check it out...

Roy-Vidar

 

[sard0nicpan]

Thanks Roy,

Unfortunately, that typing error was not in my actual code, but instead in the example I typed above (that is, my project code had the space as you suggested).

SP

 

[cmmrfrds]

I would like to code it programmatically because the Parameters that go with the stored procedure change dependent on conditions."

This does not stop you from coding the parameters under the data tab. Just make the parameters variable and return through a function.

Example.
Public myvar as string

Public Function myfunction() as string
myfunction = myvar
End function

under Form data tab.
Input Parameters properties.
@parm1=myfunction(),@parm2=myfunction2()

 

[sard0nicpan]

cmmrfrds,

You are definitely right, it's something that I was to fall back on if I could not do it all in the code.

However, I still cannot figure why it does not work in VBA. The Project is in Access 2000, could it be due to limitations in 2000 vis a vis 02, 03?

Anyway thanks,
SP

 

[cmmrfrds]

Okay, try putting the full string in a variable and using the variable as the source.

Dim mystr as String

mystr = "Exec dbo.spA1 " & myParam

Me.RecordSource mystr

If string param.
mystr = "Exec dbo.spA1 '" & myParam & "'

 

[sard0nicpan]

OK, the fires been put out . . .

I just redid my connection to the DB and it worked as I originally coded it. That was really strange . . .
I knew there was nothing wrong with the line of code--I'd seen the same thing posted in several places.

Thanks for your responses,

SP