I want to have a Query which can make only the fields marked "Yes" in my form (table) visible in my report, how can this be done. I cannot write code , hence I am struggling. Can someone help please?

In the query design grid, add your fields and in the criteria section of the checkbox field put the following in:

True


This will return only the records with the checkboxes that are ticked. You can them base the report on the query.

Sorry if it's not a checkbox then just put

Yes

In the criteria section of the relevant field

Hope this helps

Andy

Notsogood, if you are going to leave that form open when the report loads, then you can do this, but you will have to write some code. It's really not all that painful, and it's a great thing to learn.

It won't really depend on the query, but on the way you mark the fields on the form. The report will then check the form to see which fields are marked "Yes".

Write back if you want to do this, and we can provide more detail.

I did put "Yes" in the criteria of the query but the report is still showing fields irrespective whether they are yes or no. Can you suggest some other way please?

Yes dcx693, I would be interested in the details. Thanks

What I basically want to say is something like this:
Forms![Table1]![criteria1]=Yes, then Criteria1_label should be visible.
How can I write the code for it? Thanks

Yes, that's basically it. The code should basically be:
If Forms!your_form_name!checkbox_name=True Then
Me.name_of_label.Visible=False
End If

It needs to go into the Open event of your report.

This code is giving me an error message:
Run-time-error 2450;

Can you help me please?

Did you change the sample form, check box, and label names I used in my code to the actual names of those objects in your code?

The error message you have said, would relate to either a field change or the form is closed that you have referenced.

This is the actual error message:

Microsoft Access can't find the form '|' referred to in a macro expression or Visual Basic code.@*
The form you referenced may be closed or may not exist in this database.
* Microsoft Access may have encountered a compile error in a Visual Basic module for the form.@@1@211735@1


Andy

Yes I have changed the names of the form and scheckboxes etc. Cannot understand what the compilation error is? In Visual Basic, the top line is highlighted in yellow. Can you help me please?

What's your VB code?

Private Sub Report_Open(Cancel As Integer)
If Forms!Form1!satisfactory = True Then
Me.satisfactory_Label.Visible = True
End If
End Sub

Please don't laugh at my code.

Try this:

Private Sub Report_Open(Cancel As Integer)
If Forms("Form1").satisfactory = True Then
Me.[satisfactory_Label].Visible = True
End If
End Sub

I don't use reports often but don't they have an Activate event which may be better for this than the Open event?

This time the code is fine but the msg says it cannot find the form. I can see the form but Access cannot.

What is the difference between Open report and activate. I am a complete novice and didn't quite get it .

Or this:


Private Sub Report_Open(Cancel As Integer)
If Forms!Form1.satisfactory = True Then
Me.satisfactory_Label.Visible = True
End If
End Sub

Have the checked that Form1 is the name of the form and not the caption?

Andy

Originally posted by Notsogood
What is the difference between Open report and activate.

The Open Event (reports)
The event occurs before a report is previewed or printed.

The Activate Event (reports)
The event occurs when a report receives the focus and becomes the active window.

If you are unsure on anything in Access then put the cursor on it and press F1.

Yes I have checked, the Form name is Form1. The caption is Form1 too.

Has the form been saved? Does it appear in the database window in the forms section.

Yes, the form is saved and appears on the databse window too.

Can you post either a sample db with the form and report in or post the exact error message that displays.

Thanks

Andy

Run -time error: 2450

Microsoft Acees cannot find the form 'Form1' reffered to in a macro expression or Visual Basic code.

The form has to be open when you run open this report? ;)

I have put the code in Activate event, kept the form open, then opened the report......it worked perfect.

But it didn't do the job it was supposed to do. I did not tick the check box....the label still apeared !! What now ?

Originally posted by dcx693
Notsogood, if you are going to leave that form open when the report loads, then you can do this....

dcx told you to leave it open when he suggested this method.

As for what's actually going on, I don't use reports...:(

How are you triggering the record in the report?

The reason I ask is because how does the report know which record to look at the field. The results would be ambigious.

This is why I said about using a query, by putting

True

OR

-1


In the criteria of the query. This would be the most sensible and practical way to return the results you want, unless you are using the report to print a particular/current record.

If you are then you need to specify the link criteria between the form and report.

Andy

Your idea was great, it is working except for a slight problem.....the label is not in the report but the little checkbox is still apearing !!!!
Any special code required for that? Please help me.

Neil,

Sorry to be pain, but the label in the report shouldn't need to be shown because the query should return the records with the checkboxes that are true or ticked, if you have done the query

Which idea have you used?

Are you talking about the checkbox in the form?


Please clarify.

Thanks

Andy ;)

I have a table (Table1)with 5 columns:
ID..........auto number (primary key)
Name.........text
Good...............Yes/No
Satisfactory ...............Yes/No
Bad...................Yes/No

My form (Form1)has all these fields. User will use the form to enter data.

Query: has all fields: same as table1

Report should give me the page like this:
Id, name and whichever field I have ticked in the form.
Example:
1 Andy good (if good is ticked)
2 Neil bad (if bad is ticked)

Does that explain my problem better?
How do I go about doing it, can you give me some advice please?

You need to alter your table, you only need one field which accepts Good, Bad or Satisfactory, either a combo box or option group would be better

Here you go. Is this what you are after?

Download DB (http://www.spaceprogram.co.uk/Neil.zip)

Andy

Dear Andy,
You understood my problem perfectly. But the only thing is this was just a sample DB . Actually the user can tick as many as he wants to and all those chosen comments should appear. I cannot group them together.

Your Db looks great, How can I get the codes so that I can make mine similar? It is read-only.
Thanks a million.

Are you saying that one record can have three values, good, bad and satisfactory? :confused:

One record can have more than one value. Good, bad, satisfactory was just an example.

Originally posted by Notsogood
Dear Andy,
Your Db looks great, How can I get the codes so that I can make mine similar? It is read-only.
Thanks a million.

No problem. Anyway is the db read only if so right click the db icon and uncheck read only and select archive.

I have updated the db with the link I posted if you click on it again it will download the updated version.

Download Updated DB (http://www.spaceprogram.co.uk/Neil.zip)

This accepts multiple checkboxes by running code on the beforeupdate of the form and hidden controls. Take a look.

Is this OK?

Andy

Thanks a lot Andy. My problem is solved now. Till I encounter something else.

I have a few questions regarding your DB
1. can your codes be used for Access 2000.
2. when the database opens, the form opens immediately....how is that done? More codes?

Originally posted by Notsogood
I have a few questions regarding your DB
1. can your codes be used for Access 2000.
2. when the database opens, the form opens immediately....how is that done? More codes?

[list=1]
Yes;
Tools -> Startup
[/list=1]

See the attachment, just select your form from the combo box.

Andy

You guys are just so good at this.

Originally posted by Notsogood
You guys are just so good at this.


5 years of Access School;
MS Access foreign exchange - I sent mine to a Mexican family and they send me theirs and we maintain it for one year;


:rolleyes: