MS Access Report Record Source

[wlc04]

I would like to be able to set the record source of a report programatically , but I am not having much luck. I have 2 queries which could be the source depending on other parameters in the program, so depending on these parameters I want to change the record source.

Any ideas?

[sashi]

Hi Wendy,

Are you working with MS Access and Microsoft Data Environment?.. check out the link below.. it helps i hope.. good luck my fren.. take care..

http://www.sqlmag.com/Article/ArticleID/5668/sql_server_5668.html

[crazyware]

I would like to be able to set the record source of a report programatically , but I am not having much luck. I have 2 queries which could be the source depending on other parameters in the program, so depending on these parameters I want to change the record source.

Any ideas?

You can Pass any number of Parameters to Data Reports using MS Access an VB

for this you need to follow some steps below

1.Create Data Environement ( example "DE")
2.Create Command with "SQL Statement" (example "Command1")

3.in this SQL Statement Give all the Parameters you want to Pass


for example

SELECT * FROM myTable WHERE ( Name=nm) AND (Subject=sb)

4. Design your DataReport (example "DR1"

4.Now in your FORM which passes the Parameters give the following code

pass the parameters as

dim myNm,mySub as string
myNm=txtname.tx t



DE.Commands(2). CommandText = "SELECT * FROM myTable WHERE (Name=myNm) AND (Subject=mySub) "
DE.Command1 myNm,mySub

load dr1
dr1.show


*************** ******* you can pass only one parameter as below********** *

DE.Commands(1). CommandText = "SELECT * FROM myTable WHERE (Name=myNm)"
DE.Command1 myNm,mySub

' but you have to pass second variable (ucan pass any dummy var)

load dr1
dr1.show

*************** ******* you can pass only one parameter as below********** *

u need not to pass in the same order as it is in SQL Statement

DE.Commands(1). CommandText = "SELECT * FROM myTable WHERE (Subject=mySub) "
DE.Command1 mySub,DummyVar

'Here you need to pass the parameters which you use in above statement
'need not to pass as in SQL Statement in Command1

*************** *************** *************** *************** *************** *************** *
All the Best

load dr1
dr1.show

[wlc04]

Thanks, but I figured it out. :)

[gman_00ph]

Private Sub Form_Load()
With Data1
.DatabaseName = App.Path + "\mydbase.m db"
.RecordSource = "Select * from usertable"
.Refresh
End With
End Sub
--------------------------------------------------------------------------------
"mydbase.md b" is created in Access97

im using a standard Data Component for "Data1"
And DBGrid Component for "DBGrid1"

DBGrid1 Properties is set to:
DataSource = Data1
--------------------------------------------------------------------------------
everything works fine but if i try set a password to my database
it gives me a

Run-Time error '3031': Not a valid Password.

does anybody the code to acces my database in this point or
can somebody help me in the code so that i may access my database.
i must not make any major changes in the codes.

thanks in advance...

[gman_00ph]

ops wrong spot, sorry

[gara742]

You can Pass any number of Parameters to Data Reports using MS Access an VB

for this you need to follow some steps below

1.Create Data Environement ( example "DE")
2.Create Command with "SQL Statement" (example "Command1")

3.in this SQL Statement Give all the Parameters you want to Pass


for example

SELECT * FROM myTable WHERE ( Name=nm) AND (Subject=sb)

4. Design your DataReport (example "DR1"

4.Now in your FORM which passes the Parameters give the following code

pass the parameters as

dim myNm,mySub as string
myNm=txtname.tx t



DE.Commands(2). CommandText = "SELECT * FROM myTable WHERE (Name=myNm) AND (Subject=mySub) "
DE.Command1 myNm,mySub

load dr1
dr1.show


*************** ******* you can pass only one parameter as below********** *

DE.Commands(1). CommandText = "SELECT * FROM myTable WHERE (Name=myNm)"
DE.Command1 myNm,mySub

' but you have to pass second variable (ucan pass any dummy var)

load dr1
dr1.show

*************** ******* you can pass only one parameter as below********** *

u need not to pass in the same order as it is in SQL Statement

DE.Commands(1). CommandText = "SELECT * FROM myTable WHERE (Subject=mySub) "
DE.Command1 mySub,DummyVar

'Here you need to pass the parameters which you use in above statement
'need not to pass as in SQL Statement in Command1

*************** *************** *************** *************** *************** *************** *
All the Best

load dr1
dr1.show

This sounds remarkably like something I'm trying to do. Currently I have 7 forms that were created to report essentially the same information about standards coverage. They each feed off the only two numbers from a query related to whichever standard X.

My goal is to combine those forms through a macro or module of some sort so that when the form (or report since that's what should probably have been created in the first place) is first opened it has blanks where the numbers I want reported should be. The user would then click a button and be presented a menu for which of the 7 source queries they'd like to plug in.

First, is something like this even possible; and second what would be the best way to accomplish this?

Note: My SQL wasn't ever my strongest suit so go a little slow if you get into code answering this.

Thanks much!

[gara742]

Never mind I found a work around.