update a label value in one form from another form

[paullepine]

Hello All,

I've inherited an access 2000/2003 project for keeping track of training. I
have a few data entry forms and a "report" form that lets you select from
various reports. after any batch of data entry, a "generate table" button (on
the "report" form is clicked, running a vb program to build a table for these
reports. if no data has been entered, then the table does not need to be
regenerated. the Question is how can I set a value on the "report" form, from
the "data entry" form indicating the need for regeneration. I've tried various
incantations of
form!report.labelname.caption="Please ReGenerate"
from the data entry forms but can't seem to get it. Any ideas?
can this be made persistent? (eg, closing the project and reopening won't reset
the caption)

TIA, PaulLepine

 

[Jeff Boyce]

You've described a "how", as in "how do I do this?"

If you provide a bit more specific description of "what" (as in what do you
want to be able to accomplish), the newsgroup readers here may be able to
offer an alternate approach.

For example, it sounds like you might be trying to make sure your reports
have a data source. If so, why does it have to be a "regenerated table"?
Is there a chance you could use a query as the source? Or a
dynamically-created SQL statement, depending on whether any selection
criteria were included in the "report order"?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Bob Quintal]

Hello All,

I've inherited an access 2000/2003 project for keeping track of
training. I have a few data entry forms and a "report" form that
lets you select from various reports. after any batch of data
entry, a "generate table" button (on the "report" form is
clicked, running a vb program to build a table for these reports.
if no data has been entered, then the table does not need to be
regenerated. the Question is how can I set a value on the
"report" form, from the "data entry" form indicating the need for
regeneration. I've tried various incantations of
form!report.labelname.caption="Please ReGenerate"
from the data entry forms but can't seem to get it. Any ideas?
can this be made persistent? (eg, closing the project and
reopening won't reset the caption)

TIA, PaulLepine

I wonder why there is a need to generate a table for reporting, as
the usual method is to run any reports based on queries that
dynamically simulate tables.

Anyway, if you can make the determination that the table needs to be
generated directly in the report form's open event, you can set your
label that way, or better yet, run the table code automatically.

 

[paullepine]

The reasons I am using a generated table was the original queries were not
providing the correct info, and I couldn't debug them successfully, so I wrote
the VB procedure from the ground up to generate this table. What I am now
looking for is the ability to set a flag on the "Select Report" page indicating
that the report table must be regenerated. I cant seem to successfully set this
flag from another "Data Entry" form programmatically. another method might be
to test if tables have been modified, but I had not been successful in this
either.

thanks, Paul

more info:
the database consists of the tables (&fields):

TrainingModule ( ModId ,description,validfor,etc...) (150 +- modules)
Employee( EmpId ,name,etc...) (300 emps+-)
Job( JobId, jobname,etc...) (80 +-jobs)

DatesTrainingReceived ( ModId, JobId,OnDate) (8000+ records)
EmployeeJob( EmpId, JobId, currentjob) 400+record) *
TrainingRequired( JobId, ModId) (4000+records)

TrainingReportTable (Job, employee, module, whentaken, whenexpires,etc..)
(6000+records)(this is the table that I added)

* an employee can have multiple jobs, but only one currenjob, hence that
junction table has a "yesno" field set to their current posted position

the original queries were picking up multiple instances of an employee having
received training in a particular module. eg employee# 5 received training
module# 7 a total of 3 times on different dates(!), when in fact in the
junction table, there is only one instance of emp 5 and module 7. the employee
had 3 jobs, but only one currentjob, and the queries were set to look for his
current job only.

basically, I didn't have the skill to debug an SQL statement with 6 nested inner
joins, so I wrote a VBA proc to iterate thru the DatesTrainingrReceived table
and generate a report table that the report forms now use. the output is
correct and consistent now, (and maybe I will re approach the dynamic SQL method
when I've got some time(and more knowledge))

also an issue was speed. the original queries took 10-30 seconds to produce a
report and this was deemed an issue. my "Generate" procedure takes 2 minutes,
but after that, the reports pull off almost instantly. the users don't have to
enter data too often and just pull off reports, so this is not a problem. I
just have to signal them (on the SelectReport Page) that data has been entered
and they need to "Generate"