General ACCESS information

There is so much good information here, it may take me a bit to digest it.
You have obviously spent some time on this and I cannot help but feel humbled
by your response.

I apologize in advance for not specifically stating a few things that are
apparently causing confusion.

1. I do not have a choice in the design. The system is in place, and I am
merely
adding to it. The work flow is as it is. If I had a hand in it from the
start, it would
not be the way it is!

2. 'Inspection' means a lot of different things to different people. I was
hoping
it would be enough to pose the problem I was having without an elaborate
discussion of the fundamentals taking place outside the data entry area. A
prior
responder mentioned that an 'inspection' is part of maintenance. Whenever I
cross
the border, my vehicle may be inspected, and I do not consider that
maintenance.

3. Inspections are not scheduled or pending. They occur on an apx annual
'when
we get to it' basis. No vehicle is inspected twice in one day, and if that
occurs then
the same record would be used to record anything significant.

4. The vehicles ID number is painted on it.

5. Data entry people have no need to see historical data, and we do not want
then to have access to that data generally. Maybe for someone else it would
be
good to enter a vehicle ID and get a list of historical data (inspection
sheets), but
for now, that's not in the mix.

What you wrote about the forms object model being different is going to be
EXTREMELY helpful. I have to admit that is getting clearer all the time,
but is
still somewhat confusing.

The information you have provided is going to be very handy to have later
when I
need to design a few new systems from the ground up!

After all you've done here for me ( and I have read it fondly ), I am almost
embarrased to ask the following .. ( almost .. but no enough to resist ).

You mentioned that there is still the possibility of the old 'munch and
crunch'. I
think that is the way to go to get this out of my hair, then I can focus on
your
other points for the next project, a 'from the ground up' one.

Can you kindly provide an example of getting data from the record of table 'A'
and assigning fields to variables with whatever error checking would be
required
to determine if the record did not exist, and doing the same from table 'B'.

Then, all I would need is the proper method ( there seems to be many ) of
taking
values in variables and assigning to fields in a record and writing to a
table.

Sorry about the terminology I used there, I am still trying to get used to the
concept of updating data via 'Queries'. I come from a long PICK background
where the equivelant of a query was for ad hoc reports, and we wouldn't
dream of using such a thing to modify the database.

And the last thing would be to ask if you wouldn't mind if I dropped you a
line later
on to go over some broad points regarding the design of a database for ACCESS
vs .. well .. anything else. I will attempt to properly design my next
project to be
ACCESS friendly, but it would be nice to be able to show you the general idea
and get some feedback.

And so, once again, thank you for your time, and for your earnest
willingness to
share information.

Lonny


"Albert D. Kallal" 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.

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

On Tue, 20 May 2008 13:45:22 -0700, Ltexeira
wrote:

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.

Just note that storing this information in two tables, and therefore having to
keep the two tables synchronized, violates a very basic principle of
relational database design: what I call the "Grandmother's Pantry Principle" -
"a place - ONE place! - for everything, everything in its place".

The serial number of a VHF radio is (I would guess, I don't know your business
model to be certain) an attribute of a Vehicle. It is NOT appropriate to store
that information in the Inspection table! If your Inspection table has a
VehicleID, that is all the link that you need to *connect to the vehicles
table* and LOOK UP the VHF radio serial number, if it exists (and find the
NULL value if it does not). Storing a second copy of the serial number is
neither necessary nor beneficial; if either gets changed then one is WRONG
with no way to detect that fact!

You might be making the common erroneous assumption that you must have data
all in one table in order to generate a Report. You don't need to do so;
instead, you can base the Report on a query joining the two tables - pulling
inspection data (who inspected, when, what were the results) from the
inspection table, and vehicle data (VIN, radio serial number, etc.) from the
Vehicle table.
--

John W. Vinson [MVP]

I actually missed your response, and now just saw it. I have to run right
now.

However, I not dropped this thread...

I will post some some code + some more ideas tomorrow...


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

"John W. Vinson" wrote:

On Tue, 20 May 2008 13:45:22 -0700, Ltexeira
wrote:

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.

Just note that storing this information in two tables, and therefore having to
keep the two tables synchronized, violates a very basic principle of
relational database design: what I call the "Grandmother's Pantry Principle" -
"a place - ONE place! - for everything, everything in its place".

The serial number of a VHF radio is (I would guess, I don't know your business
model to be certain) an attribute of a Vehicle. It is NOT appropriate to store
that information in the Inspection table! If your Inspection table has a
VehicleID, that is all the link that you need to *connect to the vehicles
table* and LOOK UP the VHF radio serial number, if it exists (and find the
NULL value if it does not). Storing a second copy of the serial number is
neither necessary nor beneficial; if either gets changed then one is WRONG
with no way to detect that fact!

You might be making the common erroneous assumption that you must have data
all in one table in order to generate a Report. You don't need to do so;
instead, you can base the Report on a query joining the two tables - pulling
inspection data (who inspected, when, what were the results) from the
inspection table, and vehicle data (VIN, radio serial number, etc.) from the
Vehicle table.
--

John W. Vinson [MVP]


I appreciate your response, but in this case, it is correct for us to
do things this way. At the very least, it's not as incorrect as one
may presume. The VHF serial number in the VEHICLES file will
normally contain the serial number of the original radio installed
in the vehicle. That's why we can use it to populate the field if
there is no inspection record. The radios get changed out and
swapped over time for various reasons. The inspection records
let us know what radio was in the unit at the time of that
inspection. We are not storing the same data in two places
since the data may be different. The vehicle data represents the
vehicle as it was when it was new. The inspection data is a trail
of changes made over time.
Lonny

Thank you again. I look forward to it.

Lonny


"Albert D. Kallal" wrote:


I actually missed your response, and now just saw it. I have to run right
now.

However, I not dropped this thread...

I will post some some code + some more ideas tomorrow...


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

On Thu, 22 May 2008 11:27:03 -0700, Ltexeira
wrote:

I appreciate your response, but in this case, it is correct for us to
do things this way. At the very least, it's not as incorrect as one
may presume. The VHF serial number in the VEHICLES file will
normally contain the serial number of the original radio installed
in the vehicle. That's why we can use it to populate the field if
there is no inspection record. The radios get changed out and
swapped over time for various reasons. The inspection records
let us know what radio was in the unit at the time of that
inspection. We are not storing the same data in two places
since the data may be different. The vehicle data represents the
vehicle as it was when it was new. The inspection data is a trail
of changes made over time.

Thanks, Lonny. Sorry for jumping to conclusions!
--

John W. Vinson [MVP]

Not at all, sir. Just clearing up the misunderstanding!

"John W. Vinson" wrote:

On Thu, 22 May 2008 11:27:03 -0700, Ltexeira
wrote:

I appreciate your response, but in this case, it is correct for us to
do things this way. At the very least, it's not as incorrect as one
may presume. The VHF serial number in the VEHICLES file will
normally contain the serial number of the original radio installed
in the vehicle. That's why we can use it to populate the field if
there is no inspection record. The radios get changed out and
swapped over time for various reasons. The inspection records
let us know what radio was in the unit at the time of that
inspection. We are not storing the same data in two places
since the data may be different. The vehicle data represents the
vehicle as it was when it was new. The inspection data is a trail
of changes made over time.

Thanks, Lonny. Sorry for jumping to conclusions!
--

John W. Vinson [MVP]