Fields from Multiple queries on one form

[thecsmith70]

Hello.



I have a table with a couple of different names of service with a Y/N and Date field. It looks like this:

Name
Service 1: Y/N
S1: text
Service 2: Y/N
S2 : text
and so on....

Basically, if someone needs a certain service, "Yes" will be checked. If the text field is blank, then that means someone didn't start the service. If something is populated in S1, S2, etc., then the service is completed.

I want a form that shows the count of Service needed. In the sample dB I built, I have four queries. I want the answer to each query on one form. Is there a way (or a better way) to do this?

I tried that but had a #error message.

Thank you in advance.

[ranman256]

Put all these queries in a macro.
Put a button on the form to run the macro. Theres no need to force all this data onto a form.


Clean form, and all the info with just 1 click.

[thecsmith70]

I can do that (put all queries in a macro) but I want end users to be able to see the list of services and the count in one place rather than having to look at each tabs.

[thecsmith70]

I thought I had an answer by creating a report, instead of a form, and assigning each text box to the field from each query so you can see all the count on one page. I guess I'm wrong unless there is a proper solution?

[ssanfu]

You shouldn't use spaces or special characters/punctuation in object names.

I would change your table structure... It would be easier if the table structure was normalized.

However I created the form you wanted.
"Form1" uses code to open the queries you created and push the value into unbound controls.

"Form2_A" is a form that uses a fake crosstab query. (Actually two queries). the form is in continuous forms view displayed horizontally.
"Form2_B" uses the same query as "Form2_A", but is displayed vertically.

[ranman256]

If you use a form , all the queries shown will have to be either in small boxes to fit on the screen, or tabbed grid. And then we are back to looking at the queries in tabs, via macro, except these will be full screen.
Either way you want.

[thecsmith70]

Form1 is perfect. I haven't learned how to use VB but that is just what I needed. I will try and hopefully figure it out. Totally awesome.

The "fake" crosstabs worked fine. That was an interesting and clever workaround. Thank you both for helping out.

[ssanfu]

Great....

You can change Form2 A/B to look like Form1. Since you haven't learned VBA yet, you might want to redesign Form 2 (either) to look like Form 1.

Ready to mark this solved?