Passing Form Public Variable to query

[stanlen]

I have created a form using Access 2000. I declare a public variable

Public lngStartDate as long

I set the variable to 20070101.

I then open a report based on a query. The criteria for the queria is ...WHERE iDate = Forms!frmMyForm .lngStartDate.

The report opens fine and I get the expected data.

My problem is that when I do the same thing using Access 2003, I am prompted for Forms!frmMyForm .lngStartDate when the report opens.

Any help would be appreciated.

[Stewart Ross]

I have created a form using Access 2000. I declare a public variable

Public lngStartDate as long

I set the variable to 20070101.

I then open a report based on a query. The criteria for the queria is ...WHERE iDate = Forms!frmMyForm .lngStartDate.

The report opens fine and I get the expected data.

My problem is that when I do the same thing using Access 2003, I am prompted for Forms!frmMyForm .lngStartDate when the report opens.

Any help would be appreciated.

Hi Stanlen. I have had similar issues relating to queries that worked OK in previous versions of Access not working in Access 2003. There are other known inconsistencies - a Select query with a criterion forms!anyform!a nyfield in it will fail if it is changed to a crosstab, for instance, even though it works fine in select mode.

To resolve this I added a public function to fetch the value from the form's controls collection, then changed the criterion reference to a function call instead. You are using a public variable, which is a little different.

The function I developed to return a control value from a form is

Public Function FormFieldValue(FormName As String, FieldName As String)
    FormFieldValue = Forms(FormName).Controls(FieldName)
End Function

If you assign the default value to a control on your form (perhaps setting its visible property false if you don't wish users to see it) instead of a public variable you could use the function directly by replacing criterion

Forms!frmMyForm .lngStartDate with

=FormFieldValue ("frmMyForm" , "name of control")

If you need to retain the public variable then you can add a public function to the form to return the variable instead.

Public Function FrmStartDate() 
  StartDate = lngStartDate
End Function

and substitute this call for the criterion:

=FrmStartDate()

Hope this helps.

-Stewart

[wjburke2]

I have the same problem in a form. I declared a Date field as public in the general declaration section and referance it in a query on a subform. I do this so the user can select records from two lists. If they clicks on list one I set the id field to its value if they click on list two i set it to that value. Works in 2000 will not in 2003. I was thinking of puting the field invisable in the main form and using that field. I do this on other forms. Another solution I have goten is to put it in a module Module1 that seems to work also. A shame Access 2003 did not advertise this new feature and a solution in the convertion requirements.