Problem with VBA when trying to Open Report 1

[StefanScheidegger]

Dear colleagues

I am trying to open a report from a form with VBA. My current code is the following:

___________________________

Option Compare Database

Private Sub Befehl14_Click()

On Error GoTo Err_Befehl14_Click

Dim stDocName As String

stDocName = "Bericht"
DoCmd.OpenReport "Bericht", acPreview, , "[PeriodeID] = " & Me.PeriodeID

Exit_Befehl14_Click:
Exit Sub

Err_Befehl14_Click:
MsgBox Err.Description
Resume Exit_Befehl14_Click

End Sub

_______________________

Unfortunately, it is not working. the following error message comes up:

Microsoft Jet-Database-Module does not recognize '[PeriodeID] as valid field name.

Do you know what is wrong? [PeriodeID] is correct.

Kind regards,

Stefan

 

[danvlas]

Make sure [PeriodeID] cannot come from more than one table/query. If it does, then you'll have to fully qualify it (and double check the name of the textbox displaying the value):


DoCmd.OpenReport stDocName, acViewPreview, , "[TableName].[PeriodeID] = " & Me("PeriodeID&quot

Anyway, I would suggest changing Me.PeriodeID with Me("PeriodeID&quot. Your notation makes Access search for a property first...Not that there is one.

Good luck


Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[StefanScheidegger]

Dear Daniel

Thank you very much for your feedback. I have tried to implement your ideas. However, it is not working yet. Here some questions:

- the source query is a crosstab. Could that be the reason for the problem?

- Do I have to amend anything in the report (maybe put another label, etc.) in order to open the report from a form with a filter?

- Do you have any other ideas what could be the problem (there is still the same error message).

Thanks very much for your support. Kind regards,

Stefan

 

[jmnekrkt]

I too was having the same type of problem. Crosstab queries do not like parameters. There is a microsoft article that explains how crosstabs evaluate the expression. I finally solved the problem, with excellent help from this forum by using a filter on the report.

Try adding the following to the open event of the report

Me.Filter = "PeriodeID = " & Forms![FormName].[PeriodeID]
Me.FilterOn = True

 

[StefanScheidegger]

Dear jmnekrkt

Thanks for this excellent hint. Another questions, the "PeriodeID" is an autonumber field and does not appear in the source crosstab query and on the report (just in a table). Does this matter?

Can you guide me to the spot where I have to put the filter (I am not yet very experienced in Access, but everybody was once a beginner.)

Thanks very much and kind regards,

Stefan

 

[jmnekrkt]

if periodeid does not exist in the crosstab results, the filter will not work. I'm not clear on how you are actually trying to select what to print.

As for adding the code, open the form properties and select the "on open" event. Your code goes in there.

I feel your pain...I too am a realtive beginner

 

[StefanScheidegger]

Thanks once more for your help. Your comments are appreciated!

Could it also be a problem that the report is a report with dynamic column headings based on a crosstab?

Could that be the reason that Access brings the error message "Microsft Jet-Database-Modul did not recognize [PeriodeID] as valid field name"?

If this is the problem, do you have any ideas to solve it.

regards, Stefan

 

[jmnekrkt]

This is the microsoft article I spoke of:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q209778

Access pretty much does not work well with dynamic fields based on crosstabs. You may have to define them another way...similar to the way the filter code works.