Set Value - Macro Error

[DreamerZ]

A user upgraded to MS Office Pro 2003 and proceeded to open a MS 2000 DB.

The DB uses a macro to open the report with a form asking for data before running the query and displaying the report. It's the standard macro to do this task.

I have an "Open Form" action that's called in the On Open of the report.

I then have a "Set Value" with the Item as [Visible] and Expression as No. This is where the error occurs. The settings do work in 2000, 2002, 97. This command takes the values from the form and uses them in the query.

So, is there another way in 2003 to before the opening of a report, show a form, ask for some values, and on close of that form display the report with the values entered?

TIA.

DreamerZ

DreamerZ

 

[M8KWR]

what is the code you are using to open the report.

whatever code you are using, after the have asked for the report to open, then use

docmd.close

to close down the form that you are getting the information from.

I hope this helps, if not let me know. I probably have read it completely wrong having one of those mornings

 

[DreamerZ]

I'm running the macro on report open (On Open: ReportbyDate.Open ReportbyDate). This opens the form. The data is entered on the form and then a button is clicked that runs the macro again (On Click: ReportbyDate.Ok ReportbyDate). This Ok portion uses the Set Value to take the info from the form and use it in the query that the report uses.

I know I can use the query input boxes (no form), but I have 2 separate queries to look at either an individual or the whole group. Basically, if the person entry is blank, the whole group query is used. If a person is entered, the indvidual query is used.

The work around is to get rid of the macro and just use the query inputs. I would then have to create 2 reports. One for all individuals and another for the whole group. I don't want to have to do that.

BTW, there is a close command in the macro.

Thx,


DreamerZ

 

[M8KWR]

I am unsure if what i am going to suggest is what you want, so please bare with me.

So you need a single report to query on a text box on a form, that will either contain a users name, or be blank.

You can do this by using

Like [forms]![FORMNAME]![TEXTBOX_NAME] & "*"

this will either report on just one persons name, or if its blank then display everyone. It will only display if this field in the table has been filled in, if there are blank value it will ignore them.

If you have 2 peoples name simplier such as "Steve" and "SteveB", when Steve puts his name in it would pull "SteveB" in as well. Just to warn you.


Does this suit your requirements.

 

[DreamerZ]

The problem isn't with the query. It's with the macro and the SetValue action. When running the report and then clicking view from the form, I get an error on the SetValue action saying it's invalid.

The SetValue has ITEM: Visible and Expression: No

Maybe to get a better understanding of what the macro is and what it does, search for "Create a form to enter report criteria" in the Access Help. The error is occurring specifically on #5, the Ok section.


DreamerZ

 

[M8KWR]

Sorry about the confussion i do not use macros.

So on your form you have a field called "No"!!! Thoses seem the default values, and you have not put you own in them.

You would need to put the setvalue as:-

ITEM: [forms]![form name]![field name]
Epression: [field on report]

I would do it a different way in updating the query on the report to look at the ID Number on the form.

If i am still missing the point please excuse me. I hate using macros.

 

[DreamerZ]

Yeah, you would think I have to set the Item and Expressions in the macros, but it's worked fine until the upgrade to 2003.

Alright, so if I didn't use a macro, can you give me some code examples to do what I need (open form, get some data, open report with data from form).

Thx.



DreamerZ

 

[M8KWR]

So you are trying to run the report, and use the record ID number from the form to preview a report.

Is this correct?

If so then in your report, go into the query, if its a table then you would have to create one. The quick and easy way is drag the "*" into the bottom bit, and then also drag the ID number from the table down as well. In the criteria of the ID number field you would need to write something like

[forms]![FORM_NAME]![FIELD NAME i.e IDNumber etc]

Hope this helps.

Any trouble let me know and i will guide you further. So you can get it working.

 

[DreamerZ]

Looking at the Northwind sample, I was able to figure out what I needed to do. Last silly question though. When I open the form to get the information for the report, I have code to preview the report and close the form. The report, however, is minimized, rather than placed on top. Any reason for that? In the Northwind sample, that doesn't happen and I can't find the code that does it.

Thoughts?


DreamerZ

 

[M8KWR]

it should do that. Usually Access holds the last values for the view of the report. Go into the report in design view, the preview it. Save it. And that should work, i have the issue some time ago.

Glad to here its all working