Help needed on data update coding

[Alp Bekisoglu]

Hi All,

I would highly appreciate any help, opinion, idea on (if possible urgently)
resolving the following:

I'm trying to come up with the code (module, macro, proc, sub or function)
that needs to:
1- Read a value from one tabe field (single digit number)
2- Get/ask a 4 digit year value via a single textbox form (with a command
button to get things running) from user
3- Then write/append to each table's (there are quite a few) relevant fields
each of these values.
4- When routine is completed, return control (or open) to another form

The final form will call the menu on closing.

Further info:
-The values to be written are not written to the primary key in any of the
tables to be updated
-The field name for each data to be written might change slightly in some of
the tables
-I would also like to hide the main database window from prying eyes through
tools/startup
-Using A2002 in 2000 file mode
-Refrences are VBA, Access O.L., OLE Automation(not sure if I'd need this
though), DAO, AcxtiveX

I do know it can be done but I think time constraints are draining my
mind....

Thank you for your time,

Alp

 

[Tim Ferguson]

3- Then write/append to each table's (there are quite a few) relevant
fields each of these values.

I don't really understand the problem, but lines like this usually indicate
a Serious Design Problem.

B Wishes


Tim F

 

[Alp Bekisoglu]

Hi Tim,

I kind of knew I wasn't that clear in explaining what I intended to do, but
only after I clicked send.
In short what I intend to do is to "pre-enter" two field values in
accordance with the setting up of the application.
One of these two values is the user_id (autonum, PK) of the user table and
the other is the "year" asked of the user via an intermediate form as the
basis year that the application will be setup for.
To ensure the integrity of calculations, reports, and data shown on several
forms (it might be a design flaw as you have indicated but I do need them
now) these values should be in each table in their relevant fields, i.e.
"user_id" and "year". Each table does have these two fields.

Hope this clarifies a bit more. Sincerely,

Alp

 

[Tim Ferguson]

what I intend to do is to "pre-enter" two field values in
accordance with the setting up of the application.

Okay: the most convenient way to do this is in the OnCurrent event of the
form. Check help for Form Events and it will tell you all you need to know.

One of these two values is the user_id (autonum, PK) of the user table

This is presumably a straightforward Foreign Key, but I don't understand
the bit about looking up a table. One easy way of doing this is to set a
global variable at the time of logging in, or else using the inbuild Access
Security to get the CurrentUser() function.

and the other is the "year" asked of the user via an intermediate form
as the basis year that the application will be setup for.

If the form is loaded, then you can access the value with something like

Forms("frmGetTheYear").Controls("txtInputYear").Value

To ensure the integrity of calculations, reports, and data shown on
several forms

.... then you follow R theory very closely! Seriously, the whole point of
normalisation, FKs, relational integrity and everything is to guarantee
(look, no fingers crossed!) the integrity of calculations and data. If you
store something twice _anywhere_, sooner or later they will get different
values and then you won't know which is the correct one. That is the basis
for the whole RDBMS industry, where accuracy and reliability of data assume
paramount importance, way above "peformance" or "user friendliness" or
anything else.

Best wishes


Tim F

 

[Alp Bekisoglu]

Thanks for your guidance Tim. What can I say, its hard to be in the
beginings of one's journey into Access...

Alp