form/report -- how to

[Skunk]

Hi all. I am glad for this source of aid -- hoping someone can guide me.
I'll bet my 'dilemma' has a common solution.

I can readily create a report that sorts and prints/views as illustrated by
the following:

Employee name
(indent) Client name
(indent + indent) Job and other data.

The above repeats for each employee, sorting within employee by client and
within each client by job number. This would work fine, except . . .

I wish to be able to update (or not) items (two check boxes actually) on the
job number line.

So, my question is:

1. how would I sort/indent, etc. with a form in light of restriction against
continuous subforms?

2. is there any way to update the table via the two check boxes on a report?

I imagine I am not the first to want this.

Thanks,

 

[Allen Browne]

You can quite easily create a form to give you this workflow, but not this
layout.

It seems you assign clients to an employee, and have jobs that relate to the
clients. Create a main form bound to the client table. In the Form Header
section, add an unbound combo box where the user can select an employee. Use
the combo's AfterUpdate event procedure, filter the form so that it shows
only the clients of the selected employee. This interface gives the workflow
you need, and you use a subform for the job data.

It is possible to nest forms up to 7 levels deep. In general, I find this is
a confusing and inefficient interface. What you seek to do, therefore, is to
limit what the one form really tries to do, e.g. don't use it for trying to
enter/manage employees as well as clients and jobs. Provide another form for
doing that (e.g. by double-clicking the combo box you use to assign an
employee to the client.)

Sometimes you can place a 2nd subform beside or below a subform, and design
the interface so that the user picks a record in the first subform, and the
2nd subform shows the records related to the record in the first. In the
Northwind sample database, the [Customer orders] form works like that.

Access 2007 does have a Report view where you can interact with the data,
but you really don't want to be trying to use a report to do this kind of
thing. Report view too restrictive (e.g. the section events don't fire in
this view.) Forms are reallly much more suitable.

 

[Skunk]

Thanks Allen for the prompt and detailed response. I will review it ASAP.

I would like to find out how to make this report interactive, though, but
just for two check boxes at associated with each job. This report would not
require code, I am almost certain at this point. Cannot imagine why.

I recognize the less-than-ideal practice...and I get your point and would
not use but for this report which seems ideal solution. The user opens the
report finds an employees/client job and clicks a box. It is not a long
report. The sort and indents greatly enhance speed, which is critical for
this report (or form if I did that way). The data is updateable via a more
lenghty drill-to, but the user wants a down-and-dirty, very fast update for
these two controls.

How do I make them interactive? (Access 2007)
--
Skunk

You can quite easily create a form to give you this workflow, but not this
layout.

It seems you assign clients to an employee, and have jobs that relate to the
clients. Create a main form bound to the client table. In the Form Header
section, add an unbound combo box where the user can select an employee. Use
the combo's AfterUpdate event procedure, filter the form so that it shows
only the clients of the selected employee. This interface gives the workflow
you need, and you use a subform for the job data.

It is possible to nest forms up to 7 levels deep. In general, I find this is
a confusing and inefficient interface. What you seek to do, therefore, is to
limit what the one form really tries to do, e.g. don't use it for trying to
enter/manage employees as well as clients and jobs. Provide another form for
doing that (e.g. by double-clicking the combo box you use to assign an
employee to the client.)

Sometimes you can place a 2nd subform beside or below a subform, and design
the interface so that the user picks a record in the first subform, and the
2nd subform shows the records related to the record in the first. In the
Northwind sample database, the [Customer orders] form works like that.

Access 2007 does have a Report view where you can interact with the data,
but you really don't want to be trying to use a report to do this kind of
thing. Report view too restrictive (e.g. the section events don't fire in
this view.) Forms are reallly much more suitable.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi all. I am glad for this source of aid -- hoping someone can guide me.
I'll bet my 'dilemma' has a common solution.

I can readily create a report that sorts and prints/views as illustrated
by
the following:

Employee name
(indent) Client name
(indent + indent) Job and other data.

The above repeats for each employee, sorting within employee by client and
within each client by job number. This would work fine, except . . .

I wish to be able to update (or not) items (two check boxes actually) on
the
job number line.

So, my question is:

1. how would I sort/indent, etc. with a form in light of restriction
against
continuous subforms?

2. is there any way to update the table via the two check boxes on a
report?

 

[Allen Browne]

To demonstrate how to respond to clicking a check box in Report View in
Access 2007:

1. Open the report in design view.

2. Right-click the check box, and choose Properties.

3. On the Data tab of the Properties sheet, set its On Click property to:
[Event Procedure]

4. Click the Build button (...) beside this.
Access opens the code window.

5. Set up the code like this:
Private Sub Inactive_Click()
Debug.Print "You click the Inactive check box for record " & Me.ID
End Sub
This example assumes the checkbox is named Inactive, and your primary key is
named ID.

Note that the check box does not change state as it does in a form. But, for
the first time ever in an Access report, it does actually respond in Report
view in Access 2007.

I'm not suggesting that this is useful. Just answering your question about
how to make an interactive control on a report. A report is not a form.
IHMO, there is no point trying to make a hammer act like a wrench.

 

[Skunk]

Thanks again. I do get your point. Had to try thinking it just might be the
one time it was ok. Anyway, I couldn't get it to work, so I abandoned it and
went with a variation of your suggestion. Works just fine now.

Thanks again to you and the others that are so helpful here.
--
Skunk (often obtuse)

To demonstrate how to respond to clicking a check box in Report View in
Access 2007:

1. Open the report in design view.

2. Right-click the check box, and choose Properties.

3. On the Data tab of the Properties sheet, set its On Click property to:
[Event Procedure]

4. Click the Build button (...) beside this.
Access opens the code window.

5. Set up the code like this:
Private Sub Inactive_Click()
Debug.Print "You click the Inactive check box for record " & Me.ID
End Sub
This example assumes the checkbox is named Inactive, and your primary key is
named ID.

Note that the check box does not change state as it does in a form. But, for
the first time ever in an Access report, it does actually respond in Report
view in Access 2007.

I'm not suggesting that this is useful. Just answering your question about
how to make an interactive control on a report. A report is not a form.
IHMO, there is no point trying to make a hammer act like a wrench.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen for the prompt and detailed response. I will review it ASAP.

I would like to find out how to make this report interactive, though, but
just for two check boxes at associated with each job. This report would
not
require code, I am almost certain at this point. Cannot imagine why.

I recognize the less-than-ideal practice...and I get your point and would
not use but for this report which seems ideal solution. The user opens
the
report finds an employees/client job and clicks a box. It is not a long
report. The sort and indents greatly enhance speed, which is critical for
this report (or form if I did that way). The data is updateable via a
more
lenghty drill-to, but the user wants a down-and-dirty, very fast update
for
these two controls.

How do I make them interactive? (Access 2007)