MS Access and Parameters not passing to Report Query 1

[hmckillop]

Hello all who read this, I have a quick question that is puzzling me.

I have just migrated the database store from access to SQL 2005, but I want to continue using the Access front end, e.g. forms, reports vba etc.

Most of the forms work ok but I cant get the reports to work and here are the steps I have looked at.
When I profile SQL 2005 it shows the correct statement executing but the parameters have not been set.

I then checked that the correct value was specified for criteria e.g. [FORMS]![FrmName]!ObjectName.Value etc (i.e. I checked the value of this in VBA immediate window and it was correct)
When i change the criteria to be "?" and then am prompted for a value - this then works ok when i double type the value.

I am stumped, any help much appreciated

"I'm living so far beyond my income that we may almost be said to be living apart

 

[PWise]

check for extra spaces in parameters


[FORMS]![FrmName]![ObjectName__].Value

 

[hmckillop]

Thanks for response, but No Spaces, checked that also - if the "criteria" is incorrect then a dialog prompt will appear when the report is run.
Anyone else able to Help?


"I'm living so far beyond my income that we may almost be said to be living apart

 

[cmmrfrds]

Can you briefly explain the steps you are using to open the report with the parameters. If vba code then show the basic code.

 

[hmckillop]

Thanks for reply.

I have a form command button (after some details are collected/provided through this form) which launches the report.

            DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
            DoCmd.openReport "OrderActivity_ByOrderer_Personal", acViewPreview, , , acWindowNormal

Then the report opens with an entry in the recordsource called qry_XXX which is a query containing reference to 4 parameters on the form via criteria field and references such as [Forms]![Main_Menu]![MainSubForm]![PeriodBeginDate]
In code lines the [Forms]![Main_Menu]![MainSubForm]![PeriodBeginDate] holds the correct value ok via immediate window.

The query is then executed against SQL 2005 but parameters are not passed.
But if I change criteria to be say "X?" then I get prompted for values via dialog - which if I enter in values the report is successfully generated and values are successfully passed to SQL.

I know I must be missing something obvious but I cant see it.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[cmmrfrds]

I wonder if the values in
[Forms]![Main_Menu]![MainSubForm]![PeriodBeginDate]
may be out of scope when you open the report. I normally don't reference Form variables but instead pass then through a public function.

For example.
In the standard module do.
Public variable1 as string

Public Function ReturnVar1 as String
ReturnVar1 = variable1
End Function

In the Form do.
variable1 = [Forms]![Main_Menu]![MainSubForm]![PeriodBeginDate]

In the Report reference variable1 in the Parameter as.

ReturnVar1()

 

[hmckillop]

I will give that a go and come back to you, thanks for prompt reply, I appreciate it as I am under pressure here.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[hmckillop]

Spot on, that was the problem. Many thanks I have been tearing my hair out this couple of days, so much appreciated. Thanks have a star


"I'm living so far beyond my income that we may almost be said to be living apart