Recordsource report

[sven2]

Hello,

How can you change the recordsource of a report by vba refering to a query?

The folowing isn't working:

me.recordsource = "My query" ...

Can somebody help me with the correct vba code.

Thanks in advance,
Sven.

 

[KenHigg]

Where do you have the code?

 

[sven2]

Hello,

I have the code on the open report event. The fault that I get is that the query doesn't exist ...

 

[KenHigg]

It should work then. Hum... Are you sure you have it spelled correctly? Copy and paste / Post the entire code block from the on open event so we can take a look...

 

[sven2]

Hello,

i did the copy paste, the name is correct ...

this is the code:

Private Sub Report_Open(Cancel As Integer)
DoCmd.Echo False

DoCmd.Maximize

Select Case [Forms]![FrmRapportering]![cbomachine]

Case "PM1"

Select Case [Forms]![FrmRapportering]![kdrtijdperiode]

Case 1

Me.RecordSource = "Q_Rapport_10MoederrollenPM1"

Case 2

Me.RecordSource = "Q_Rapport_25MoederrollenPM1"

End Select

Case "PM6"

Select Case [Forms]![FrmRapportering]![kdrtijdperiode]

Case 1

Me.RecordSource = "Q_Rapport_10MoederrollenPM6"

Case 2

Me.RecordSource = "Q_Rapport_25MoederrollenPM6"

End Select

Case "PM7"

Select Case [Forms]![FrmRapportering]![kdrtijdperiode]

Case 1

Me.RecordSource = "Q_Rapport_10MoederrollenPM7"

Case 2

Me.RecordSource = "Q_Rapport_25MoederrollenPM7"

End Select

End Select


the select case is working just fine but the system can't find the query's ?? Is it because I am working with an adp project? (BE = SQL server).

Sven.

 

[sven2]

Hello,

I have made a simple query (test) and put in the open report event the code:
me.recordsource = "test"

Also then I get an error can't find the recordsource test ...
What can be the problem?

 

[MSAccessRookie]

I use similar code in a Label Printing procedure,and it works for me. The only thing that I noticed is that I think the whole procedure can be boiled down to the following:

[COLOR=black][FONT=Verdana]Private Sub Report_Open(Cancel As Integer)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    DoCmd.Echo False[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    DoCmd.Maximize[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Select Case [Forms]![FrmRapportering]![kdrtijdperiode][/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        Case 1[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]            Me.RecordSource = "Q_Rapport_10MoederrollenPM1" & [Forms]![FrmRapportering]![cbomachine][/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        Case 2[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]            Me.RecordSource = "Q_Rapport_25MoederrollenPM1" & [Forms]![FrmRapportering]![cbomachine][/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    End Select[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3]End Sub[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]

[/SIZE][/FONT]

Note that this is only an observation, and is not intended to assist in solving the problem.

 

[sven2]

Hi,

I agree, the code can be boiled but the thing is why can't access find the query? (I can run the query in the query screen) ...

Has it something to do with the fact that I am working with an adp project? If not, what can be the reason?

Best regards,
Sven.

 

[boblarson]

Hi,

I agree, the code can be boiled but the thing is why can't access find the query? (I can run the query in the query screen) ...

Has it something to do with the fact that I am working with an adp project? If not, what can be the reason?

Best regards,
Sven.

That is the likely reason. ADP's are different from MDB's. I don't use ADP's and prefer to use MDB's even when working with SQL Server. So, you may have found one of the ADP limitations.

 

[KenHigg]

That is the likely reason. ADP's are different from MDB's. I don't use ADP's and prefer to use MDB's even when working with SQL Server. So, you may have found one of the ADP limitations.

Hum...Yeah. That's my guess.

(edit - good catch Bob)