Form and Tables

[Wesley]

Hi All,

I'm continue to work on my little preventative maintenance project. What i'm looking for is some advice on how to do a couple of different things:

1st - I have a date / time field that is calculated using the dateadd function based on a date / time that maintenance was last performed. I don't think this information is really stored anywhere other than simply displayed on the form i'm working on.
I would like to be able to take that calculated field and store it someplace, such as a table, so that i can make a query to generate a report.

2nd - I want to be able to make a combination of first making a query to pull data, and then make a report that i can print out what equipment is due at monthly intervals.

3rd - Not sure if this is possible. But, in the main form, i would like to put a description of the steps necessary to perform the maintenance coming due for a piece of equipment. My workplace has a word document that is essentially a template (its in .doc format not .dot however) for a form a person would use when performing maintenance on any given piece of equipment. Can i export data from my form to a word document so that i may have the option to "produce" this form when the time comes to perform maintenance on a piece of equipment? OR, can i use Access to basically generate a report that will essentially make a document that looks like it came out of word, and has all the necessary fields on it, etc.?

regards,

 

[wizcow]

Wesley

In answer the question 1.

Put a date feild in your main form's table.
Put a date text box on your main form.
Set the the text boxe's 'Record Source' to the date feild in your table.
In the the text boxe's 'Default Value' enter

Now()

HTH
Tom

 

[Wesley]

question to your response

i made a seperate text box that now can get the "DateNextDue" from the form field that calculates it, but, how does that information now get passed to my table?

-OR- is it not necessary to port that data to my main table. Your response seemed to indicate that it was (since you had me create a "DateNextDue" field on my main table).

regards,

edit:
i have one text box called "Date Maintenance Last Performed". A user will record a date here. There is a corresponding field in my main table that keeps this info.

I have a second text box that's control source uses the DateAdd function to calculate a date of when the maintenance will next be due for that particular piece of equipment.

What i am ultimately trying to do is to create a query that will eventually feed a report that will display all equipment due for preventative maintenance within the next month.

 

[Mile-O]

i made a seperate text box that now can get the "DateNextDue" from the form field that calculates it, but, how does that information now get passed to my table?

Do NOT store calculated information in your table as it can be generated at anytime in a query.

 

[Mile-O]

Just to show you what I mean here's a table, a query, and a report.

The table contains the two fields: name, and startdate.

The query returns three fields: name, startdate, and enddate.

The report uses the query data.

 

[Wesley]

kk
please disregard my edit till i have a chance to look at this. I didn't know you could simply calculate things in a query.

 

[Wesley]

hmm
ok
my form uses two tables to calculate the next date equipment is due for preventative maintenance:

table 1 contains: Equipment name, drawing number, date maintenance last performed and the type of maintenance.

table 2 contains information about what the interval should be.

my form has a combo box that i use to select what the periodicity of maintenance is. (weekly, biweekly, monthly, quarterly, semiannually and annually)

the calculation on my form for "Date Next Due" is done by:

=DateAdd([CboInterval].column(2),[CboInterval].column(3),[StartDateBox])

Does your above advice still apply with using the query?

Also, why would i NOT want to place the NextDueDate in my main table? is this just good database design practice, or will it result in things going all screwy?

edit: is it possible to have a query look at more than one table?

 

[Mile-O]

The advice will work - you'll have to change a couple of references in a query so that the form the comboboxes are included on can be referenced.

You wouldn't store a calculated value in a table as it is redundant data - it can be arrived at anytime.

It's like having three fields: forename, surname, and fullname. Why have full name when [forename] & " " & [surname] returns the full name when I require it?

Another thing - you might want to consider adding a further table to your database for maintenance types i.e greasing, tightening, etc. and link that to your equipment table as a foreign key.

 

[Wesley]

The advice will work - you'll have to change a couple of references in a query so that the form the comboboxes are included on can be referenced.

any advice on how to do that?

in my initial forays into figuring out how to do this, i wanted to use my dateAdd formula in my query, but, i am not sure how to do that when some of the information required by the formula is contained within a different table then my main equipment table.

so, when i tried to include my second table, it told me i had to specify relationships between table 1 (main table) and table 2 (intervals table)... i'm not sure how to set that up, primarily because, i dont' see how those two tables ARE related... one is just used to store information about the equipment, the second is just used by the form so that it knows to get information linked to my combo box that specifies the interval to which preventative maintenance is required.