General ACCESS information

I have been given a project, and am not quite sure where to start. I am an
older VB programmer, and know my way around VBA and forms, but a lot of
ACCESS is foreign to me.

I have a VEHICLE table and an INSPECTIONS table. Vehicle ids are numeric,
and Inspections are Vehicle IDs and date. When a vehicle and date are
entered on a form, I would check for the inspection record and populate the
form, then allow updates (with code to validate entries) and a big button
that saves, and one that cancels.

If the inspection record is not there, then using the vehicle ID I would
read the vehicle record from VEHICLES and populate the form with appropriate
entries, then allow for entrys and then to SAVE to the INSPECTIONS file
(table).

If this was VB, I'd be done by now, but it's ACCESS. I've been scanning
newgroups here and haven't hit upon anything that really puts me in the right
direction for this.

I've found methods from VBA to update tables with forms data, but not how to
accomplish the whole 'if the record is here, use it, if not, use what you can
from this other file' thing. I'm having trouble understanding how that would
work with bound fields, if it would work at all that way. And what I've been
reading seems to indicate that there are much easier ways to do this than
using a lot of VBA code.

So, the following are the boiled down questions :

1. Should I use automatic primary keys on INSPECTIONS? If so, I need the
table to automatically disallow duplicate records based on vehicle ID and
date. Multiple vehicles IDs with different dates are fine. Or, would it be
better to use a combination of the vehicle ID and the date as the primary key?

2. Can a form, once it has a vehicle ID and a date filled in, self populate
with INSPECTION data if it exists, and if not, self populate with VEHICLE
data, and still write the record only to INSPECTIONS?

3. I can guess this one .. I am better off letting ACCESS do the data
validation on each field rather than code each one in VBA after every fields
update event...

4. I would like to have a table for historical inspection records that
would allow multiple records with the same Vehicle ID and date so that if
someone changed an inspection record, the old record would exist in this
historical file (table). Is there an efficient way to do this if the
INSPECTION record is not new, but has changed to write the old record to the
historical file?

I know I'm asking for a lot of information. I apologize in advance for such
basic irequests. Your time is appreciated.

1. Should I use automatic primary keys on INSPECTIONS?
I would use autonumber as primary key but also use unique index of vehicle
ID and the date.
2. Can a form, once it has a vehicle ID and a date filled in, self populate
Use an append query to add records. Limit time span for how far you want to
forecast the work.
3. I can guess this one ..
Use DateTime datatype for dates.
4. I would like to have a table for historical inspection records that
The same table scheduling will be your historical record using DueDate and
Completed fields.

Maintenance services have different intervals based on what is performed.
Oil change would be three months or 3000 miles but brakes would be six
months or 30,000 miles.
So you need to not only record the [Completed] but what was done and the
mileage.
If by number of days only then [Completed] with criteria like ---
=DateAdd("m", -3, Date())
This check to see if the [Completed] was at least 3 months ago. Better
still would be like this ---
=DateAdd("m", -3, Date())-7
This will say it is due seven days before actual due date so it can be
scheduled in before it is overdue.

In your Task table have a field indicating interval number for the
maintenance. Use the lowest common denominator such as weeks, months or
quarters.
If you can not make it work with the lowest common denominator then use two
fields, one for interval type and other for numerial --
m 2 - for 2 months
d 30 - for 30 days
q 2 - for 2 quarters
Use these in DateAdd function to create your workorders using an append query.

Another thing to think about is whether to schedule based on last performed
date or straight calendar. If a maintenance task was performed late or
earlier should the next one be form the completion date or whenever the
calendar says it should be. Have a field in the task table indicating which
if you have mixed.
The workorder needs a date field for DueDate and Completed. The append
query will look at task table for interval information and which date to use
- last completed or last scheduled.

--
KARL DEWEY
Build a little - Test a little


"Ltexeira" wrote:

I have been given a project, and am not quite sure where to start. I am an
older VB programmer, and know my way around VBA and forms, but a lot of
ACCESS is foreign to me.

I have a VEHICLE table and an INSPECTIONS table. Vehicle ids are numeric,
and Inspections are Vehicle IDs and date. When a vehicle and date are
entered on a form, I would check for the inspection record and populate the
form, then allow updates (with code to validate entries) and a big button
that saves, and one that cancels.

If the inspection record is not there, then using the vehicle ID I would
read the vehicle record from VEHICLES and populate the form with appropriate
entries, then allow for entrys and then to SAVE to the INSPECTIONS file
(table).

If this was VB, I'd be done by now, but it's ACCESS. I've been scanning
newgroups here and haven't hit upon anything that really puts me in the right
direction for this.

I've found methods from VBA to update tables with forms data, but not how to
accomplish the whole 'if the record is here, use it, if not, use what you can
from this other file' thing. I'm having trouble understanding how that would
work with bound fields, if it would work at all that way. And what I've been
reading seems to indicate that there are much easier ways to do this than
using a lot of VBA code.

So, the following are the boiled down questions :

1. Should I use automatic primary keys on INSPECTIONS? If so, I need the
table to automatically disallow duplicate records based on vehicle ID and
date. Multiple vehicles IDs with different dates are fine. Or, would it be
better to use a combination of the vehicle ID and the date as the primary key?

2. Can a form, once it has a vehicle ID and a date filled in, self populate
with INSPECTION data if it exists, and if not, self populate with VEHICLE
data, and still write the record only to INSPECTIONS?

3. I can guess this one .. I am better off letting ACCESS do the data
validation on each field rather than code each one in VBA after every fields
update event...

4. I would like to have a table for historical inspection records that
would allow multiple records with the same Vehicle ID and date so that if
someone changed an inspection record, the old record would exist in this
historical file (table). Is there an efficient way to do this if the
INSPECTION record is not new, but has changed to write the old record to the
historical file?

I know I'm asking for a lot of information. I apologize in advance for such
basic irequests. Your time is appreciated.

"KARL DEWEY" wrote:

1. Should I use automatic primary keys on INSPECTIONS?
I would use autonumber as primary key but also use unique index of vehicle
ID and the date.

Okay with that.

2. Can a form, once it has a vehicle ID and a date filled in, self populate
Use an append query to add records. Limit time span for how far you want to
forecast the work.

I don't get how this is applicable. There is no 'work' to forecast. I need
to retreive data from file 'A'. If the record in 'A' does not exist, then I
need to gather primary data ( Like license plate number, serial numbers, etc.
) from file 'B' to populate the fields, then allow editing and saving to file
'A'. How would an append query accomplish this?

3. I can guess this one ..
Use DateTime datatype for dates.

4. I would like to have a table for historical inspection records that
The same table scheduling will be your historical record using DueDate and
Completed fields.
There is no 'due date' or 'completed' fields, and there is no scheduling.
Please clarify!


Maintenance services have different intervals based on what is performed.
Oil change would be three months or 3000 miles but brakes would be six
months or 30,000 miles.
So you need to not only record the [Completed] but what was done and the
mileage.

This file is not for 'maintenance' but for 'inspections'. The historical
data needs to be in a seperate file (table).

If by number of days only then [Completed] with criteria like ---
=DateAdd("m", -3, Date())
This check to see if the [Completed] was at least 3 months ago. Better
still would be like this ---
=DateAdd("m", -3, Date())-7
This will say it is due seven days before actual due date so it can be
scheduled in before it is overdue.

In your Task table have a field indicating interval number for the
maintenance. Use the lowest common denominator such as weeks, months or
quarters.
If you can not make it work with the lowest common denominator then use two
fields, one for interval type and other for numerial --
m 2 - for 2 months
d 30 - for 30 days
q 2 - for 2 quarters
Use these in DateAdd function to create your workorders using an append query.

Another thing to think about is whether to schedule based on last performed
date or straight calendar. If a maintenance task was performed late or
earlier should the next one be form the completion date or whenever the
calendar says it should be. Have a field in the task table indicating which
if you have mixed.
The workorder needs a date field for DueDate and Completed. The append
query will look at task table for interval information and which date to use
- last completed or last scheduled.

Again, not maintenance, no intervals, etc..
--
KARL DEWEY
Build a little - Test a little

You obviously put in some time to enter this, and I do appreciate the
response, but I am afraid I am not very much closer to a solution. If you
could clarify by example some of the finer points it would be of immense
value.

Thank you again.



1. Should I use automatic primary keys on INSPECTIONS? If so, I need the
table to automatically disallow duplicate records based on vehicle ID and
date. Multiple vehicles IDs with different dates are fine. Or, would it be
better to use a combination of the vehicle ID and the date as the primary key?

2. Can a form, once it has a vehicle ID and a date filled in, self populate
with INSPECTION data if it exists, and if not, self populate with VEHICLE
data, and still write the record only to INSPECTIONS?

3. I can guess this one .. I am better off letting ACCESS do the data
validation on each field rather than code each one in VBA after every fields
update event...

4. I would like to have a table for historical inspection records that
would allow multiple records with the same Vehicle ID and date so that if
someone changed an inspection record, the old record would exist in this
historical file (table). Is there an efficient way to do this if the
INSPECTION record is not new, but has changed to write the old record to the
historical file?

There is no 'work' to forecast.
Do you not want to know in advance when an inspection is due or just wait
until it is past due?

There is no 'due date' or 'completed' fields, and there is no scheduling. Please clarify!
In keeping history you need completed date. You could use one field for due
and completed by adding another field Yes/No for completed and the one date
would serve both. That I think is poor data collection.

This file is not for 'maintenance' but for 'inspections'.
Inspections are a type of maintenance.

The historical data needs to be in a seperate file (table).
The vehicle information would be in one table and the inspections in the
history table.

Again, not maintenance, no intervals, etc..
Inspections have intervals. But if you only have one type of inspection and
they are all the same interval the you do not need the task table.

--
KARL DEWEY
Build a little - Test a little


"Ltexeira" wrote:



"KARL DEWEY" wrote:

1. Should I use automatic primary keys on INSPECTIONS?
I would use autonumber as primary key but also use unique index of vehicle
ID and the date.

Okay with that.

2. Can a form, once it has a vehicle ID and a date filled in, self populate
Use an append query to add records. Limit time span for how far you want to
forecast the work.

I don't get how this is applicable. There is no 'work' to forecast. I need
to retreive data from file 'A'. If the record in 'A' does not exist, then I
need to gather primary data ( Like license plate number, serial numbers, etc.
) from file 'B' to populate the fields, then allow editing and saving to file
'A'. How would an append query accomplish this?

3. I can guess this one ..
Use DateTime datatype for dates.

4. I would like to have a table for historical inspection records that
The same table scheduling will be your historical record using DueDate and
Completed fields.
There is no 'due date' or 'completed' fields, and there is no scheduling.
Please clarify!


Maintenance services have different intervals based on what is performed.
Oil change would be three months or 3000 miles but brakes would be six
months or 30,000 miles.
So you need to not only record the [Completed] but what was done and the
mileage.

This file is not for 'maintenance' but for 'inspections'. The historical
data needs to be in a seperate file (table).

If by number of days only then [Completed] with criteria like ---
=DateAdd("m", -3, Date())
This check to see if the [Completed] was at least 3 months ago. Better
still would be like this ---
=DateAdd("m", -3, Date())-7
This will say it is due seven days before actual due date so it can be
scheduled in before it is overdue.

In your Task table have a field indicating interval number for the
maintenance. Use the lowest common denominator such as weeks, months or
quarters.
If you can not make it work with the lowest common denominator then use two
fields, one for interval type and other for numerial --
m 2 - for 2 months
d 30 - for 30 days
q 2 - for 2 quarters
Use these in DateAdd function to create your workorders using an append query.

Another thing to think about is whether to schedule based on last performed
date or straight calendar. If a maintenance task was performed late or
earlier should the next one be form the completion date or whenever the
calendar says it should be. Have a field in the task table indicating which
if you have mixed.
The workorder needs a date field for DueDate and Completed. The append
query will look at task table for interval information and which date to use
- last completed or last scheduled.

Again, not maintenance, no intervals, etc..
--
KARL DEWEY
Build a little - Test a little

You obviously put in some time to enter this, and I do appreciate the
response, but I am afraid I am not very much closer to a solution. If you
could clarify by example some of the finer points it would be of immense
value.

Thank you again.



1. Should I use automatic primary keys on INSPECTIONS? If so, I need the
table to automatically disallow duplicate records based on vehicle ID and
date. Multiple vehicles IDs with different dates are fine. Or, would it be
better to use a combination of the vehicle ID and the date as the primary key?

2. Can a form, once it has a vehicle ID and a date filled in, self populate
with INSPECTION data if it exists, and if not, self populate with VEHICLE
data, and still write the record only to INSPECTIONS?

3. I can guess this one .. I am better off letting ACCESS do the data
validation on each field rather than code each one in VBA after every fields
update event...

4. I would like to have a table for historical inspection records that
would allow multiple records with the same Vehicle ID and date so that if
someone changed an inspection record, the old record would exist in this
historical file (table). Is there an efficient way to do this if the
INSPECTION record is not new, but has changed to write the old record to the
historical file?

"KARL DEWEY" wrote:

There is no 'work' to forecast.
Do you not want to know in advance when an inspection is due or just wait
until it is past due?
They are not 'due' or 'past due', they are perfomed on request.

There is no 'due date' or 'completed' fields, and there is no scheduling. Please clarify!
In keeping history you need completed date. You could use one field for due
and completed by adding another field Yes/No for completed and the one date
would serve both. That I think is poor data collection.
The identifier for the INSPECTIONS file (table) is the vehicle ID and the
date. That is the date of the inspection, and if you wish to use the term,
the 'completed' date.

This file is not for 'maintenance' but for 'inspections'.
Inspections are a type of maintenance.
Inspections are to determine if maintenance is required. Why is this an
issue?

The historical data needs to be in a seperate file (table).
The vehicle information would be in one table and the inspections in the
history table.
The history table would be for 'history'. The Inspection table would be for
current data. The historical data would not be for general consumption,
while the inspection table would be.

Again, not maintenance, no intervals, etc..
Inspections have intervals. But if you only have one type of inspection and
they are all the same interval the you do not need the task table.
Agreed.

--
KARL DEWEY
Build a little - Test a little


"Ltexeira" wrote:



"KARL DEWEY" wrote:

1. Should I use automatic primary keys on INSPECTIONS?
I would use autonumber as primary key but also use unique index of vehicle
ID and the date.

Okay with that.

2. Can a form, once it has a vehicle ID and a date filled in, self populate
Use an append query to add records. Limit time span for how far you want to
forecast the work.

I don't get how this is applicable. There is no 'work' to forecast. I need
to retreive data from file 'A'. If the record in 'A' does not exist, then I
need to gather primary data ( Like license plate number, serial numbers, etc.
) from file 'B' to populate the fields, then allow editing and saving to file
'A'. How would an append query accomplish this?

3. I can guess this one ..
Use DateTime datatype for dates.

4. I would like to have a table for historical inspection records that
The same table scheduling will be your historical record using DueDate and
Completed fields.
There is no 'due date' or 'completed' fields, and there is no scheduling.
Please clarify!


Maintenance services have different intervals based on what is performed.
Oil change would be three months or 3000 miles but brakes would be six
months or 30,000 miles.
So you need to not only record the [Completed] but what was done and the
mileage.

This file is not for 'maintenance' but for 'inspections'. The historical
data needs to be in a seperate file (table).

If by number of days only then [Completed] with criteria like ---
=DateAdd("m", -3, Date())
This check to see if the [Completed] was at least 3 months ago. Better
still would be like this ---
=DateAdd("m", -3, Date())-7
This will say it is due seven days before actual due date so it can be
scheduled in before it is overdue.

In your Task table have a field indicating interval number for the
maintenance. Use the lowest common denominator such as weeks, months or
quarters.
If you can not make it work with the lowest common denominator then use two
fields, one for interval type and other for numerial --
m 2 - for 2 months
d 30 - for 30 days
q 2 - for 2 quarters
Use these in DateAdd function to create your workorders using an append query.

Another thing to think about is whether to schedule based on last performed
date or straight calendar. If a maintenance task was performed late or
earlier should the next one be form the completion date or whenever the
calendar says it should be. Have a field in the task table indicating which
if you have mixed.
The workorder needs a date field for DueDate and Completed. The append
query will look at task table for interval information and which date to use
- last completed or last scheduled.

Again, not maintenance, no intervals, etc..
--
KARL DEWEY
Build a little - Test a little

You obviously put in some time to enter this, and I do appreciate the
response, but I am afraid I am not very much closer to a solution. If you
could clarify by example some of the finer points it would be of immense
value.

Thank you again.



1. Should I use automatic primary keys on INSPECTIONS? If so, I need the
table to automatically disallow duplicate records based on vehicle ID and
date. Multiple vehicles IDs with different dates are fine. Or, would it be
better to use a combination of the vehicle ID and the date as the primary key?

2. Can a form, once it has a vehicle ID and a date filled in, self populate
with INSPECTION data if it exists, and if not, self populate with VEHICLE
data, and still write the record only to INSPECTIONS?

3. I can guess this one .. I am better off letting ACCESS do the data
validation on each field rather than code each one in VBA after every fields
update event...

4. I would like to have a table for historical inspection records that
would allow multiple records with the same Vehicle ID and date so that if
someone changed an inspection record, the old record would exist in this
historical file (table). Is there an efficient way to do this if the
INSPECTION record is not new, but has changed to write the old record to the
historical file?

The history table would be for 'history'. The Inspection table would be
for current data. The historical data would not be for general consumption,
while the inspection table would be.
If you use two tables for inspection/history then you must either update
fields or delete and add records to the inspection and append to the history
table.

You will find that it is easier to extract data if your 'Inspection table'
and 'historical data' are the same table. As I said the data in one field
tells if current or history. Your queries use criteria on that field to pull
the type of records you need for a given report.

You would have a one-to-many relationship between you Vehicle table and
Inspection table.

Remember what I post is only suggestions for you to mull over - there are
many ways to do the same thing including using Excel.
--
KARL DEWEY
Build a little - Test a little


"Ltexeira" wrote:



"KARL DEWEY" wrote:

There is no 'work' to forecast.
Do you not want to know in advance when an inspection is due or just wait
until it is past due?
They are not 'due' or 'past due', they are perfomed on request.

There is no 'due date' or 'completed' fields, and there is no scheduling. Please clarify!
In keeping history you need completed date. You could use one field for due
and completed by adding another field Yes/No for completed and the one date
would serve both. That I think is poor data collection.
The identifier for the INSPECTIONS file (table) is the vehicle ID and the
date. That is the date of the inspection, and if you wish to use the term,
the 'completed' date.

This file is not for 'maintenance' but for 'inspections'.
Inspections are a type of maintenance.
Inspections are to determine if maintenance is required. Why is this an
issue?

The historical data needs to be in a seperate file (table).
The vehicle information would be in one table and the inspections in the
history table.
The history table would be for 'history'. The Inspection table would be for
current data. The historical data would not be for general consumption,
while the inspection table would be.

Again, not maintenance, no intervals, etc..
Inspections have intervals. But if you only have one type of inspection and
they are all the same interval the you do not need the task table.
Agreed.

--
KARL DEWEY
Build a little - Test a little


"Ltexeira" wrote:



"KARL DEWEY" wrote:

1. Should I use automatic primary keys on INSPECTIONS?
I would use autonumber as primary key but also use unique index of vehicle
ID and the date.

Okay with that.

2. Can a form, once it has a vehicle ID and a date filled in, self populate
Use an append query to add records. Limit time span for how far you want to
forecast the work.

I don't get how this is applicable. There is no 'work' to forecast. I need
to retreive data from file 'A'. If the record in 'A' does not exist, then I
need to gather primary data ( Like license plate number, serial numbers, etc.
) from file 'B' to populate the fields, then allow editing and saving to file
'A'. How would an append query accomplish this?

3. I can guess this one ..
Use DateTime datatype for dates.

4. I would like to have a table for historical inspection records that
The same table scheduling will be your historical record using DueDate and
Completed fields.
There is no 'due date' or 'completed' fields, and there is no scheduling.
Please clarify!


Maintenance services have different intervals based on what is performed.
Oil change would be three months or 3000 miles but brakes would be six
months or 30,000 miles.
So you need to not only record the [Completed] but what was done and the
mileage.

This file is not for 'maintenance' but for 'inspections'. The historical
data needs to be in a seperate file (table).

If by number of days only then [Completed] with criteria like ---
=DateAdd("m", -3, Date())
This check to see if the [Completed] was at least 3 months ago. Better
still would be like this ---
=DateAdd("m", -3, Date())-7
This will say it is due seven days before actual due date so it can be
scheduled in before it is overdue.

In your Task table have a field indicating interval number for the
maintenance. Use the lowest common denominator such as weeks, months or
quarters.
If you can not make it work with the lowest common denominator then use two
fields, one for interval type and other for numerial --
m 2 - for 2 months
d 30 - for 30 days
q 2 - for 2 quarters
Use these in DateAdd function to create your workorders using an append query.

Another thing to think about is whether to schedule based on last performed
date or straight calendar. If a maintenance task was performed late or
earlier should the next one be form the completion date or whenever the
calendar says it should be. Have a field in the task table indicating which
if you have mixed.
The workorder needs a date field for DueDate and Completed. The append
query will look at task table for interval information and which date to use
- last completed or last scheduled.

Again, not maintenance, no intervals, etc..
--
KARL DEWEY
Build a little - Test a little

You obviously put in some time to enter this, and I do appreciate the
response, but I am afraid I am not very much closer to a solution. If you
could clarify by example some of the finer points it would be of immense
value.

Thank you again.



1. Should I use automatic primary keys on INSPECTIONS? If so, I need the
table to automatically disallow duplicate records based on vehicle ID and
date. Multiple vehicles IDs with different dates are fine. Or, would it be
better to use a combination of the vehicle ID and the date as the primary key?

2. Can a form, once it has a vehicle ID and a date filled in, self populate
with INSPECTION data if it exists, and if not, self populate with VEHICLE
data, and still write the record only to INSPECTIONS?

3. I can guess this one .. I am better off letting ACCESS do the data
validation on each field rather than code each one in VBA after every fields
update event...

4. I would like to have a table for historical inspection records that
would allow multiple records with the same Vehicle ID and date so that if
someone changed an inspection record, the old record would exist in this
historical file (table). Is there an efficient way to do this if the
INSPECTION record is not new, but has changed to write the old record to the
historical file?

"Ltexeira" wrote in message
news
I have been given a project, and am not quite sure where to start. I am an
older VB programmer, and know my way around VBA and forms, but a lot of
ACCESS is foreign to me.

I have a VEHICLE table and an INSPECTIONS table. Vehicle ids are numeric,
and Inspections are Vehicle IDs and date. When a vehicle and date are
entered on a form, I would check for the inspection record and populate
the
form, then allow updates (with code to validate entries) and a big button
that saves, and one that cancels.

If the inspection record is not there, then using the vehicle ID I would
read the vehicle record from VEHICLES and populate the form with
appropriate
entries, then allow for entrys and then to SAVE to the INSPECTIONS file
(table).


Is the same data in both the INSPECTIONS record and the VEHICLES record?
The data is just more likely to be accurate and complete in the INSPECTIONS
record?


If that is the case, I would use an unbound form, with a search box in the
header.
I would use VBA to populate the form fields, so your computer could figure
out which record to pull the data from.





2. Can a form, once it has a vehicle ID and a date filled in, self
populate
with INSPECTION data if it exists, and if not, self populate with VEHICLE
data, and still write the record only to INSPECTIONS?

If you use an unbound form, you will have to write the code to update the
INSPECTIONS record.


3. I can guess this one .. I am better off letting ACCESS do the data
validation on each field rather than code each one in VBA after every
fields
update event...

I seldom use the Access data validation on much except date fields. Most
applications of any sophistication have some or many edits. But I always do
the data validation in the BeforeUpdate event of my form, just because it's
easier to have it all in one place.




4. I would like to have a table for historical inspection records that
would allow multiple records with the same Vehicle ID and date so that if
someone changed an inspection record, the old record would exist in this
historical file (table). Is there an efficient way to do this if the
INSPECTION record is not new, but has changed to write the old record to
the
historical file?

If you decide to use a bound form, there is a property called .Dirty that
flags if the data in the record has been changed. As part of the
BeforeUpdate event, you can check for that property, and if TRUE, you can
use the .OldValue for each field, and write to a History file.

If you are using an unbound form, you will have to create a variable for the
history record, and populate it with original data, and then if the user
clicks SAVE, compare the original data with the new data and create an entry
in your History file for any changes.

"Ltexeira" wrote in message
news
I have been given a project, and am not quite sure where to start. I am an
older VB programmer, and know my way around VBA and forms, but a lot of
ACCESS is foreign to me.

Just keep in mind that MS access programming and the forms object model is
quite a bit more complex then are the simplistic vb6 forms.

What this means is while forms bound to data are easier to create in MS
access, the programming and object model has a considerably steeper curve
then that of vb. Once you adopt the designs methodology in MS access, then
you'll find you'll can build applications far quicker then in vb6.
(most will say 3, even 5 times as quickly as you will in vb.

That means a 3 month project in access will take 9 months, or even
more to the create the same product in vb6 with the same functionality.

If you can adopt the bound form design methodology to your project, then MS
access will build your application far faster and quicker than what you're
used in the past. However, adopting this methodology does entailed some
compromises in your designs. if Ihose compromises in your designs can't be
made, then I actually do actually suggest you consider using vb6.


I have a VEHICLE table and an INSPECTIONS table. Vehicle ids are numeric,
and Inspections are Vehicle IDs and date. When a vehicle and date are
entered on a form, I would check for the inspection record and populate
the
form, then allow updates (with code to validate entries) and a big button
that saves, and one that cancels.

Perhaps you might want to revaluate the above workflow. You find it a lot
less code if you built a form in which you search for a vehicle, and then
perhaps display list of inspections.

So, you first select the vehicle, and then you
can see the list of service dates for that vehicle. This would allow the
user to see all service states for that vehicle. This also allows the
user to pick existing service dates and work on them. Thus this would
facilitate the users ability to go back and edit and manage existing data.
In other words we've solved our design problem how are going to build the
user interface to edit informaton for this particular vehicle. In addition
since you built a nice form that has all of the vehicle information in one
place, at that point you can also place some buttons to print out reports,
view historical data etc for that ONE vehicle. So if you're called upon to
print out some information for one particular vehicle, you already built an
interface that has one centralized location for all the things and tasks and
information that you need for that particular vehicle. You could also bring
up this form and glanced at the service data and say that's when the last
server date was done.

So all kinds of these daily additional tasks that you have to do over time
that pertained to a particular vehicle who will thus be accomplished in one
form that manages all the information for that vehicle.

So, this approach is
being suggested is in the vehicle + inspections form if you don't see the
service date,
then you can simply add a new reocrd. And, you don't have to write "any"
code
to add a new reocrd (that feature is built into ms-access).

You don't have to
do things this this way, but I just giving you some of the design
subtleties that occur when you design your application to fit
within how MS access works.

Doing all of the above so far, can actually be done without writing any
code! Perhaps the only exception to the above would be that of printing out
the report for the particular vehicle, but all of the rest can be done so
far with no code. Note that this also includes adding inspections, and the
fact that the MS access will be event "set" the foreign key value that
relates the inspection table back to the vehicle table for you.

Doing this kind of relational setup in something like vb6 takes whacks and
whacks of code, and yet so far, we've not had to write one line of code yet
in ms-access.

Often, we have people come into this newsgroup who used Foxpro for years, or
even perhaps excel, and the define their problem in terms of those
applications. While there's nothing wrong in terms of defining your
applications in inserts some previous product you used here.
However, some subtle and slight changes to your design approach will yield
absolute massive gains in your programmer productivity.


If the inspection record is not there, then using the vehicle ID I would
read the vehicle record from VEHICLES and populate the form with
appropriate
entries, then allow for entrys and then to SAVE to the INSPECTIONS file
(table).

You see in the design that I propose, at the top of the screen we can have
all the information about this vehicle (color, make, year, etc). In the
bottom half of the screen we can have a collumar (data grid) sub form
display of all
the service states, and perhaps other pieces of information for each service
date.

If the service date is not there that you want to edit, then you could
simply start entering a new record into this data grid (subform). Remember,
access sub forms allow adding of data, and then you don't have to write any
code to maintain the foreign key in table inspections. MS access can
automatically update and maintained the foreign key values for you *when*
you use a sub form.

the other big bonus here is that when you bring up the vehicle record, all
of the inspections that pertain (related) to that vehicle who will be
displayed in the sub form for you! Once again we not written one line of
code to accomplish this user interface yet.



If this was VB, I'd be done by now

To be really honest here, I would have the above user interface and a
working applicaton done in **less*** time that it's taken me to actually
type this response to you. I'm being totally honest year, using the wizards
drag and drop with one hand and the mouse, the above interface can be built
in less time than it takes to type this response to you.

I've found methods from VBA to update tables with forms data, but not how
to accomplish the whole 'if the record is here, use it, if not, use what
you
can from this other file' thing.

actually the same traditional coding practices you use two years ago when
Foxpro, or vb6 can be used the end MS access, and all the same coding
techniques for the most part can be used, but you don't have to.

I have access applications with an excess of 30,000 lines of vba code. So
you can most certainly write old style read in a reocrd, munch and crunch it
as you done in the past...

In fact the programming language and syntax in MS access is
identical to vb6. The only difference here is that our forms object model is
significantly different, and of course we have to learn how to use the bound
forms in our application designs. I will be the first to
admit that you have to make some slight compromises in your designs when you
do use bound forms, but the advantages is all that extra productivity I
talked about.

I'm having trouble understanding how that would
work with bound fields, if it would work at all that way. And what I've
been
reading seems to indicate that there are much easier ways to do this than
using a lot of VBA code.

Yes, great...at least you open, and looking for ideas!

You are still miles ahead of most new commers because you do have
programming experience.


So, the following are the boiled down questions :

1. Should I use automatic primary keys on INSPECTIONS? If so, I need the
table to automatically disallow duplicate records based on vehicle ID and
date. Multiple vehicles IDs with different dates are fine. Or, would it
be
better to use a combination of the vehicle ID and the date as the primary
key?

great question!

All tables should have a primary key, and you should probably adopt a coding
standards that all tables have a primary autonumber key of "id".

Now of course you don't have any other tables related to inspections, but it
still should have a primary key because down the road you might want to add
some additional tables. For example, in the future you might want to enter
the 3-4 mechanics that did the inspection. Thus, this list of mechanics
would have to be related to the inspection tables by using the inspection
tables primary key. Things are far more simple as a single column
of for the primary key as an auto number.

So, at this point in time, I recommend against a compound key here, and it's
not really needed . The only advantage you might get from a compound key is
some kind of error message from the jet database engine that you are
attempting to add a duplicate inspection. However this problem and error
message can be dealt with by setting a unique index on the date + the
foreign key of vehicles. Furthermore as I said, in the future for expansion
and adding to this application, if inspections table is based on a compound
primary key, it's going to make your coding and application design more
difficult in the future. Furthermore it'll also prevent your design from
operating if the vehicle needs more then one inspection in a day (that can
occur some parts or somting is something else is missing). So not using the
compound key it gives you far more flexibility in the application also.

Now that I've said all of the above, the issue of a primary key for each row
in a table is completely separate as to how you're going to relate the table
inspections back to vehicles. To relate the inspections back to vehicles,
you
simply have a field of long number type called Vechicle_id.

(from what you explained so far, you've done the above correctly, and you do
have a vehicle_ID filed in inspections. You should likely should set up the
relationship in the the
relationships windows to reflect this.


2. Can a form, once it has a vehicle ID and a date filled in, self
populate
with INSPECTION data if it exists, and if not, self populate with VEHICLE
data, and still write the record only to INSPECTIONS?

You can do the above, and it's not that hard in code. However when you model
relationships data in MS access, you use a form for the main table, and for
the child table to the use what is called a sub form. I explain some of the
issues of using a sub form an MS access here, and you might wanna take two
minutes to read the following:

http://www.members.shaw.ca/AlbertKal...000000005.html

So, if you build a vehicle form, and then drop in a sub form of inspections,
then you
now modeled the one to many relationships here, and you've got had to write
code to do it.

Now, you can build what is called a search combo box for the vehicle ID (the
wizard
will do that for you too!). Now, when the user types in the vehicle ID into
that combo box, the form will move and display that the vehicle, along with
a nice listing and display of all the inspections that belong to the
vehicle.

I should point out at this point I'm still not have written
one line of code yet to accomplish all of this.

As a side note, I should point out that the vehicle id is not to be confused
with an internal primary key or so called auto number. You not mentioned to
me how these vehicle IDs are assigned or created within your organization,
but it would be pretty silly to say that you just bought a new car, the
organization is not assigned a vehicle ID, and yet your whole application
will cease to function.

It's not clear if these vehicle ID's are text,
numeric, or include part of the color of the car etc. So if your company is
been the assigning some kind of vehicle identification code to
vehicles over the years, that has absolutely no relationship (pun intended)
to the fact that you have internal auto generated primary keys that enable
your application to function. It
would be stupid to have a payroll system that doesn't function for the first
week
because the employee still waiting the assignment of a social insurance
number.

So don't confuse the building of your relationships and structure and the
internal
functionality of your application with that of external numbers like the
color of the car, or some silly stupid identification number that some
company comes up with. I cannot stress how these two concepts are not to be
confused with each other.

Furthermore since auto numbers can change, or jump around, skip gaps, and
often have no set increment order, you can NOT rely on autonumber for any
****external**** use. In other words autonumber are strictly internal
housekeeping numbers that the database uses, and your end users will never
know about these numbers, never use these numbers, and in fact should not
have to be aware them in any way shape at all.

So when I assume when you say that they enter the vehicles ID, it's not
clear where you are getting these ID's from, are they perhaps a sticker on
the window of the car? Anyway it's not really important where the numbers
come from, but I am just stating that these numbers are not primary key
values, and the whole application will function and run even if you don't
have a vehicle ID number issued yet.


3. I can guess this one .. I am better off letting ACCESS do the data
validation on each field rather than code each one in VBA after every
fields
update event...

It's going to be a mix of all three of the approaches. if you have several
fields that are required, then your best bet is set them as required in the
table design mode, and that way you don't have to write any code at all.
(however, you'll often find that the error message from MS access is
not very user friendly, so you wind up writing some code anyway!)

In many cases you can't use the after update event of a control on a form,
because the user might not go into that controll andchange the
values in it. Therefore your validation code for that field will never run
anyway. For the most part a good chunk of my validation codes therefore goes
in the form's before update event, not for each control.

However, for general form validation type code in which the value of one
text box is based on another on the form, then you will most certainly place
the code in the controls before update event. There's also a controls after
update event, and that's good for setting the values of other controls as a
result of some action that you've done in the current control. Again it is
important but subtle difference between the two events, because the before
update event of a controll has a cancel option. And, if you set
cancel=true, the user will not be able to leave that field untill they
satisfy your code.

So, yes, code in the before update event of a controll is a common thing
we do in MS access, but if the user doesn't enter into that control, then
the code for that contorl never runs. So for required fields, I usually just
set field to required in the table design mode, and if I have time later on,
I might put in some user friendly messages into the forms before update
event.


4. I would like to have a table for historical inspection records that
would allow multiple records with the same Vehicle ID and date so that if
someone changed an inspection record, the old record would exist in this
historical file (table). Is there an efficient way to do this if the
INSPECTION record is not new, but has changed to write the old record to
the
historical file?

If you must do this, then you would use the forms *before* update event. You
would execute an append query (about one line of code), that would send this
data to your historical *before* the update occurs. (as you can see, with
about five times the amount of events that you had in vb6 forms, the hard
part in MS access is learning when to use the correct event for the correct
ask. Furthermore what is nice is this event does not run if the user simply
views the form.
So, the beofre update, and after update events will only run if the user
actually modifies something.

Of course updates in access forms are automatic, and I really think you
should dispense with the save button concept. Foxpro, MS access, dbase etc
and even now parts of outlook have few save buttons (and some applications
have removed them all together) If the users closing the form or moving on
to another record, then obviously they want to save their data. It would be
as if software engineers designed a car. You put the key end, it turn the
key to start the car, and then a dialog box pops up and says:

do you REALLY want to start the car?

It is just insane that we have this fixation with placing a button to
accomplish something that the users aready decided that they want to do in
the first palce. If the users made a mistake and edited some data on the
form, then teach them to to go edit-undo.

Furthermore, with your designed that makes a copy the record
before we send it out, then we even have a better fallback position.

Furthermore you could build another sub form behind a tab control in which
you click on it, and the user it be able to see to all of the history data
for that record, again that tab control and sub form that lists all of this
can be built with no code at all.

If the users closing the form, or moving to another record, then they want
to save their data, otherwise WHY would be moving to another record!

Besides if we eliminate the save button, then we save a whole bunch of
coding time again...

So far all the above features I have mentioned can be built without writing
any code, and simply using the mouse and clicking away on the wizards for a
few minutes.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

"KARL DEWEY" wrote:

The history table would be for 'history'. The Inspection table would be
for current data. The historical data would not be for general consumption,
while the inspection table would be.
If you use two tables for inspection/history then you must either update
fields or delete and add records to the inspection and append to the history
table.

You will find that it is easier to extract data if your 'Inspection table'
and 'historical data' are the same table. As I said the data in one field
tells if current or history. Your queries use criteria on that field to pull
the type of records you need for a given report.

You would have a one-to-many relationship between you Vehicle table and
Inspection table.

Remember what I post is only suggestions for you to mull over - there are
many ways to do the same thing including using Excel.
--
I understand and appreciate your input. Unfortunately, I do not have the
luxury of
picking the development software that I am most familiar with.
Thanks again.

KARL DEWEY
Build a little - Test a little


"Ltexeira" wrote:



"KARL DEWEY" wrote:

There is no 'work' to forecast.
Do you not want to know in advance when an inspection is due or just wait
until it is past due?
They are not 'due' or 'past due', they are perfomed on request.

There is no 'due date' or 'completed' fields, and there is no scheduling. Please clarify!
In keeping history you need completed date. You could use one field for due
and completed by adding another field Yes/No for completed and the one date
would serve both. That I think is poor data collection.
The identifier for the INSPECTIONS file (table) is the vehicle ID and the
date. That is the date of the inspection, and if you wish to use the term,
the 'completed' date.

This file is not for 'maintenance' but for 'inspections'.
Inspections are a type of maintenance.
Inspections are to determine if maintenance is required. Why is this an
issue?

The historical data needs to be in a seperate file (table).
The vehicle information would be in one table and the inspections in the
history table.
The history table would be for 'history'. The Inspection table would be for
current data. The historical data would not be for general consumption,
while the inspection table would be.

Again, not maintenance, no intervals, etc..
Inspections have intervals. But if you only have one type of inspection and
they are all the same interval the you do not need the task table.
Agreed.

--
KARL DEWEY
Build a little - Test a little


"Ltexeira" wrote:



"KARL DEWEY" wrote:

1. Should I use automatic primary keys on INSPECTIONS?
I would use autonumber as primary key but also use unique index of vehicle
ID and the date.

Okay with that.

2. Can a form, once it has a vehicle ID and a date filled in, self populate
Use an append query to add records. Limit time span for how far you want to
forecast the work.

I don't get how this is applicable. There is no 'work' to forecast. I need
to retreive data from file 'A'. If the record in 'A' does not exist, then I
need to gather primary data ( Like license plate number, serial numbers, etc.
) from file 'B' to populate the fields, then allow editing and saving to file
'A'. How would an append query accomplish this?

3. I can guess this one ..
Use DateTime datatype for dates.

4. I would like to have a table for historical inspection records that
The same table scheduling will be your historical record using DueDate and
Completed fields.
There is no 'due date' or 'completed' fields, and there is no scheduling.
Please clarify!


Maintenance services have different intervals based on what is performed.
Oil change would be three months or 3000 miles but brakes would be six
months or 30,000 miles.
So you need to not only record the [Completed] but what was done and the
mileage.

This file is not for 'maintenance' but for 'inspections'. The historical
data needs to be in a seperate file (table).

If by number of days only then [Completed] with criteria like ---
=DateAdd("m", -3, Date())
This check to see if the [Completed] was at least 3 months ago. Better
still would be like this ---
=DateAdd("m", -3, Date())-7
This will say it is due seven days before actual due date so it can be
scheduled in before it is overdue.

In your Task table have a field indicating interval number for the
maintenance. Use the lowest common denominator such as weeks, months or
quarters.
If you can not make it work with the lowest common denominator then use two
fields, one for interval type and other for numerial --
m 2 - for 2 months
d 30 - for 30 days
q 2 - for 2 quarters
Use these in DateAdd function to create your workorders using an append query.

Another thing to think about is whether to schedule based on last performed
date or straight calendar. If a maintenance task was performed late or
earlier should the next one be form the completion date or whenever the
calendar says it should be. Have a field in the task table indicating which
if you have mixed.
The workorder needs a date field for DueDate and Completed. The append
query will look at task table for interval information and which date to use
- last completed or last scheduled.

Again, not maintenance, no intervals, etc..
--
KARL DEWEY
Build a little - Test a little

You obviously put in some time to enter this, and I do appreciate the
response, but I am afraid I am not very much closer to a solution. If you
could clarify by example some of the finer points it would be of immense
value.

Thank you again.



1. Should I use automatic primary keys on INSPECTIONS? If so, I need the
table to automatically disallow duplicate records based on vehicle ID and
date. Multiple vehicles IDs with different dates are fine. Or, would it be
better to use a combination of the vehicle ID and the date as the primary key?

2. Can a form, once it has a vehicle ID and a date filled in, self populate
with INSPECTION data if it exists, and if not, self populate with VEHICLE
data, and still write the record only to INSPECTIONS?

3. I can guess this one .. I am better off letting ACCESS do the data
validation on each field rather than code each one in VBA after every fields
update event...

4. I would like to have a table for historical inspection records that
would allow multiple records with the same Vehicle ID and date so that if
someone changed an inspection record, the old record would exist in this
historical file (table). Is there an efficient way to do this if the
INSPECTION record is not new, but has changed to write the old record to the
historical file?

"Margaret Bartley" wrote:


"Ltexeira" wrote in message
news
I have been given a project, and am not quite sure where to start. I am an
older VB programmer, and know my way around VBA and forms, but a lot of
ACCESS is foreign to me.

I have a VEHICLE table and an INSPECTIONS table. Vehicle ids are numeric,
and Inspections are Vehicle IDs and date. When a vehicle and date are
entered on a form, I would check for the inspection record and populate
the
form, then allow updates (with code to validate entries) and a big button
that saves, and one that cancels.

If the inspection record is not there, then using the vehicle ID I would
read the vehicle record from VEHICLES and populate the form with
appropriate
entries, then allow for entrys and then to SAVE to the INSPECTIONS file
(table).


Is the same data in both the INSPECTIONS record and the VEHICLES record?
The data is just more likely to be accurate and complete in the INSPECTIONS
record?

No, the data is different, but there are fields in the VEHICLES record that
can be pulled to partially populate the INSPECTION record upon first entry ..
if they exist in the VEHICLE record. They may or may not. For example, one
of the inspection fields is the serial number of the VHF radio. In a new
INSPECTION record, the VEHICLE record may (or may not) have a VHF radio
serial number which could be pulled over and used to populate that field.
Then the person doing the data entry can verify that nubmer against the
inspection sheet they are entering from, or change it to match the sheet.

If that is the case, I would use an unbound form, with a search box in the
header.
I would use VBA to populate the form fields, so your computer could figure
out which record to pull the data from.





2. Can a form, once it has a vehicle ID and a date filled in, self
populate
with INSPECTION data if it exists, and if not, self populate with VEHICLE
data, and still write the record only to INSPECTIONS?

If you use an unbound form, you will have to write the code to update the
INSPECTIONS record.


3. I can guess this one .. I am better off letting ACCESS do the data
validation on each field rather than code each one in VBA after every
fields
update event...

I seldom use the Access data validation on much except date fields. Most
applications of any sophistication have some or many edits. But I always do
the data validation in the BeforeUpdate event of my form, just because it's
easier to have it all in one place.

Agreed, that's what I normally do, but since some of the fields will be
populated from the VEHICLE table, not all fields will be changed. If I
validate the fields as they are entered I bypass validating field that never
get touched. Make sense?



4. I would like to have a table for historical inspection records that
would allow multiple records with the same Vehicle ID and date so that if
someone changed an inspection record, the old record would exist in this
historical file (table). Is there an efficient way to do this if the
INSPECTION record is not new, but has changed to write the old record to
the
historical file?

If you decide to use a bound form, there is a property called .Dirty that
flags if the data in the record has been changed. As part of the
BeforeUpdate event, you can check for that property, and if TRUE, you can
use the .OldValue for each field, and write to a History file.

Excellent!! That is something I was looking for! Thanks!


If you are using an unbound form, you will have to create a variable for the
history record, and populate it with original data, and then if the user
clicks SAVE, compare the original data with the new data and create an entry
in your History file for any changes.



Lots of good info, thank you!!