Newbie alert - How to "fill in" a field entry due to the previous

[Penny B.]

Lothead(table) has product_no & product fields. At new data entry I want to
have a dropdown show up in the product_no field to chose ONLY from that drop
down (which is based on the Product(table)). Within the Product (table) the
field product_no , along with product (the second field in the table) .

How do I "make this happen". How do I have the selection in Lothead (for
product number) drop into WHICH will then by default pull in the product NAME
into Lothead for the same said field in Lothead.

Sorry in advance if I am not explaining well - hard to get across what I am
attempting to do.

 

[Tom Wickerath]

Hi Penny,

You should not need to store the product in the Lothead table, unless there
is a possibility that the product will be later changed in the Product table,
for an existing product_no.

There are examples of how to accomplish this request in the Northwind sample
database (Northwind.mdb). (I assume the same examples are present in the 2007
version of Northwind (Northwind.accdb), but I don't know this for a fact). In
any version of Northwind that shipped with Access 97, 2000, 2002 or 2003,
open the Orders form. One selects a customer name in the "Bill To" combo box,
and the "Ship To" information is pre-populated with default values (which can
be changed, if required).

Upon exploring the relationship in Northwind, you should see that the
default values are stored in the Customers table, whereas the ShipTo
information is stored in the Orders table. Storing the same data in more than
one table is fine in this case, since we need to provide a historical record
of exactly where each order was shipped. You'll see the same type of thing
for UnitPrice in the Products table, versus UnitPrice in the Order Details
table. The UnitPrice in the Products table represents the current price,
whereas the UnitPrice in the Order Details table represents the price at the
time the order was placed.

Would you ever anticipate re-using a product_no for a different product in
your Products table? If not, I question why you need to store this value in
two tables.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Penny B.]

Well, you bring to light a good question. What I am dealing with is I am new
to databases as of a week ago. Have a project I attempting to tackle for my
husband's (3 employee small) company that is (with the current economy) not
doing well and can not hire someone who knows databases.

My project now is to save the day by "somehow" make this old dos version
database softward into a new Microsoft database (before their old computer
crashes).

That being said - the old database had menus and forms - of which one would
choose to enter new lots (which when the form was completed all information
was saved to the Lothead.table. When it come time for a report to be pulled
for those lots that were entered and still in the building (charge
rent/storage for products in relation to the time they are in the building)
the reports are pulled using the lothead.table. What I am truly trying to do
is recreate the old database (which was not designed using Microsoft - nor my
anyone we can go back to now to ask it is that old). Maybe I am asking too
much to do this (rather, over my head). But the dos will not work on the new
computer so I have to do something. I have about 5 books here to help me -
but the process of "WHAT to do when". I have the database, I pulled in the
tables from the old - everything else needs reconstructed.

 

[Penny B.]

I looked at all the table to view the field properties - have "tweaked" some
field properties so input (couple places to show consistant use of
upper/lowercase, zipcode, phone, must enter a value, etc....). Then I tied
all the tables (primary key to matching fields in corresponding tables) for
their relationships. Then I saw were I could set a lookup table - thought I
was on the right track but when tried to save I receive error "must undo
relationship first then open the table" or something close to that result.
At that time I felt I either did something out of step, or that I did not
need it. Now I wonder if the lookup table is necessary at all if the form
can have the drop down list shown without the field properties dictating that.

I will construct a NEW LOT input form that the office person will go one box
at a time to input the info (or in one case the system date will default,
another case an "A" for active will default, in another the calendar
accouting date will default in due to the system date previously entered).
Perhaps what I am attempting to "set up" at the field properties level is not
necessary. Perhaps it is? THAT is my problem....I KNOW what I did thru the
dos version as an end user and what popped in by defaults.....WHAT I don't
know is how to make this happen in a windows based version when I don't know
databases. I am reading - I am getting it somewhat (the basics)...but I am
not cramming enough in to get it done yet.

 

[Tom Wickerath]

Hi Penny,

But the dos will not work on the new computer so I have to do something.

I'd be surprised if you could not get it to work with your newer computer,
perhaps specifying a compatibility mode. Another alternative might be to
install the free Virtual PC from Microsoft, and then install the DOS
operating system on this VPC. This would allow the use of the current
database, while buying you plenty of time to come up to speed using Microsoft
Access.

Maybe I am asking too much to do this (rather, over my head).

That could be, but if you take it slow and, *more importantly* you have an
interest in learning Access, then it should be very do-able. However, the
conversion is not likely to go as fast as you might want, because Access has
somewhat of a steep learning curve.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

This error message tells me that you are using Access 2002 or a later
version, because one did not receive such an error message in Access 2000 and
prior versions, when using the Lookup Wizard. That said, my advice is to
avoid the use of table lookups, which art thy creation of thy evil one.
Please read the following article, and in particular, Commandment #2 (there
is an additional link to follow for the second commandment):

The Ten Commandments of Access
http://www.mvps.org/access/tencommandments.htm

Now I wonder if the lookup table is necessary at all if the form
can have the drop down list shown without the field properties dictating that.

You are very correct. There is no need for lookup fields to be defined at
either the table or query level. Instead, use a combo box (drop down list) on
a form.

You can enter default values at the table level, in which case they will
apply to any form based on this table, or to individual controls within a
form.

As long as you are in cram mode, may I suggest that you download a zipped
copy of a Word document that I have available, which I call "Access Links"?
This document is full of lots of good information collected over the years.
The first four pages include information on naming conventions, reserved
words, database design, best practices, relationships, references, etc....all
the type of stuff that anyone who is working with Access should be familiar
with. You can get a copy from my web site:

http://www.accessmvp.com/TWickerath/


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Penny B.]

Ah, I have not even opened the new computer for the company (yes it is
delivered here, once I have the database working on my computer with same
Vista operating system I will load the database onto it ). Vista I was told
would not support dos database versions - and in any case it was TIME to get
it out of the old dos (I mean dark) ages. I am using an Access 2007 version
on my computer (it came new when I got this computer in November 2008).

I very much WANT to learn Access. Perhaps if I had built it from scratch, I
would not be making missteps or steps out of order. Maybe I am overthinking
the project. In any event I am "pressured" to get it completed before their
other computer crashes. This can be done, I am sure (maybe just NOT AS
quickly as want since I have no patience). Idealy what would work best is
for me to sit with someone who knows Access inside and out, I tell them how
the old worked, they do the step by steps (and I see how it is done step by
step)...then I will learn and the project will be absolutely accurate. ALAS,
that was a pipe dream and I am back to reality. Neither he nor I get any
income out of the business, it has gotten that bad - all his retirement went
into purchasing the business 8 years ago. Now he does not want to put the 3
employees out of a job so we do not get paid but they do. HENCE my desire to
fix the problem because we have no other way to do so. Thank you for your
advice and your word doc. I will download.

 

[Tom Wickerath]

Hi Penny,

Both computers should be quite capable of running Virtual PC, which is a
free download available from Microsoft. Basically, this gives you a PC within
a PC. If you decide to install Virtual PC, your next step would be to create
a Virtual Machine (VM). You'll need to have a licensed copy of an operating
system for each VM that you create. You may already have left over fully
licensed copies of DOS from years past. If not, you could likely purchase a
copy very cheap from Amazon, or a variety of other on-line businesses that
specialize in used software.

Running the older database application (DBase III ?) in a virtual
environment would allow you to use the brand new computer to run it, thus
giving you the time to pursue development / improvement of a replacement
application at a more leisurely pace, without the pressures of "gotta have it
done yesterday".


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Penny B.]

Yes, I do have an old registered version of Dos - don't know much about
Virtual PC stuff. Feel that I REALLY could get things messed up now.

I was not successful moving the (old dos database) to my older windows xp
(that had the winagent on it to run the old database. That being said - I
feel that even if I do get the virtual pc to work on the Vista (I would still
not get the database menus to work/interact properly). The person who
designed the dos database (Alpha 4v7) back in 94 has an autoexec file created
that works only on the computer he loaded it onto. SO - as you can see this
is LAYERS of problems. Moving to another computer is virtually impossible
because I cannot get it unzipped and working JUST as it does on the computer
he installed it onto.

 

[John W. Vinson]

I very much WANT to learn Access. Perhaps if I had built it from scratch, I
would not be making missteps or steps out of order. Maybe I am overthinking
the project. In any event I am "pressured" to get it completed before their
other computer crashes. This can be done, I am sure (maybe just NOT AS
quickly as want since I have no patience). Idealy what would work best is
for me to sit with someone who knows Access inside and out, I tell them how
the old worked, they do the step by steps (and I see how it is done step by
step)...then I will learn and the project will be absolutely accurate.

I'm not sure you want to hear this but... a very real alternative might be to
determine what is NEEDED for the purposes of the business, and design an
application using the capabilities of Access to do that job. If you buy a new
Toyota Camry, you wouldn't really want to have it restricted to the way you
would maintain and drive a '57 Chevy, and that's roughly what you're
proposing!

It should be pretty easy to import the *data* in your old DOS file, but
replicating the old DOS file's user interface would leave you stuck with... an
old DOS interface. Sometimes scratch is easier!

 

[Tom Wickerath]

Hi Penny,

Yes, I do have an old registered version of Dos - don't know much about
Virtual PC stuff. Feel that I REALLY could get things messed up now.

I don't think you could get into any trouble by first trying VPC on your
machine at home, with a DOS VM loaded, along with the Alpha 4v7 software and
a copy of your database. My guess is that there is a means of getting around
the autoexec file limitation that was added by the previous developer. You
might want to pose this question directly to the folks at Alpha 5:

http://www.alphafivedatabase.com/access/contact.asp

For that matter, you might consider asking them about running your database
using the newest version of Alpha 5 software, which should run under
WindowsXP and/or Vista (I think). There is a toll-free number listed on the
above web page. It looks like the V9 Standard edition should work, and you do
qualify for the discounted upgrade pricing. So, I just want to throw that out
as a possibility.

I was not successful moving the (old dos database) to my older windows xp
(that had the winagent on it to run the old database.

I'm not familiar with winagent, however, I just Googled it, and it looks
like software that you really don't want running on your PC:

http://www.google.com/search?hl=en&q=winagent.exe&aq=2&oq=winagent

I haven't seen anything yet to suggest that winagent could be used to run an
older DOS program; it looks like it is designed to help steal your personal
information. Then again, perhaps it is a component of Alpha V software, as
indicated in this patch text file:
http://downloads.alphasoftware.com/Patches/a4v8/a4v8patch_12_16_2003.txt

So, you could discuss this with the Technical Support folks at Alpha V as
well.

Moving to another computer is virtually impossible
because I cannot get it unzipped and working JUST as it does on the computer
he installed it onto.

And this is after consulting with Alpha V?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Penny B.]

I can see all (table/field/relationship/codes/expressions) from the old
(there is a doc file not unlike the reports that can be pulled in Access). I
can contact Alpha to use their newest product (because I already know that it
works with Vista). We already purchased the Office 2007 Professional oem
preinstalled on the new computer so did not want to buy more software (unless
we have to). As you see, the time to accomplish the task - AND - the money
involved are the two determining factors. I work a day job, my husband is a
truck driver again (out of retirement to help keep his - this - company
afloat ). I have a day job but took 2 days off to work with this. I have
worked with it all extra hours I have since Christmas day (when office person
called to say computer failing).
I digress.... I just believe that using Microsoft Access 2007 (since it is
the newest version and already on the computer) is frugial choice - but it is
making it work like the other old. She is not computer at all - write down
her steps that is it. I know some about computers and usually can sit down
to any software to "get it"...struggling with this one. NOT the
generalities/basics, but more getting it to work like something that was
already .

 

[Penny B.]

Thank you John -
I feel that I do not want the dos (yet I understand it could get be by until
NEW database is operable). I wrote more back to Tom that goes into more
detail why Access (virsus getting A5V9). I may have to, I may not be able to
tackle this hurdle. I had all the confidence starting - but now it has been
many full weekend days worth that I have gotten virtually no where. I have
database with tables(pulled from the old) tweaked some settings/properties.
Now trying my hand at forms. Purhaps attempting to make replicate his old
design is fruitless - but since all his expressions show up on the docs I
nealy have to. Problem also, though, is I have the settings/etc.... but not
sure how where to put them. I am flustered, but need to get back to it (days
a wasting and only have one more to work with this)

 

[John W. Vinson]

Purhaps attempting to make replicate his old
design is fruitless - but since all his expressions show up on the docs I
nealy have to.

Well, it's more work than building an all new app would be, but I see
elsewhere in the thread why you might need to. It is, however, *very easy* to
put calculated expressions on a form - simply set the Control Source of a
textbox to

=[X] * [Y] * ([A] - )

using your actual fieldnames and expressions.

Problem also, though, is I have the settings/etc.... but not
sure how where to put them. I am flustered, but need to get back to it (days
a wasting and only have one more to work with this)

I'm not at all sure what "settings" you mean. We'll be glad to help if you
post the specific issue.

 

[Tom Wickerath]

Hi Penny,

Your last reply was the first one where you indicated that the office
computer is (apparently?) starting to actually fail; I believe all previous
replies have indicated a need to migrate *before* a failure may occur. You
should make an immediate backup of the data files on this old computer, and
either save them to a USB thumb drive, or some other place (but not on the
hard drive of the computer that is starting to fail).

While I do encourage you to pursue Access, one needs to understand that the
learning curve can be rather steep. Thus my concern about have to get it done
quickly, at very low cost. Three constraints of most projects include cost,
schedule and quality--pick any two to tweek. In other words, low cost (ie.
do-it-yourself) and fast schedule may very well lead to low quality. I'm just
trying to be 100% honest with you; I've "been-there-done-that", trying to
help other friends at times, who grossly underestimated the efforts required,
and ended up spending many late night hours trying to get their applications
working, sometimes with my help.

Please contact Alpha 5 and ask them about the possibility of transferring
your current license to a new Vista computer, running DOS on a virtual
machine. Also ask them about getting around whatever limitation the original
person may have implemented, so that the application would only work on the
one PC. If you can get your current version working on a new computer, then
you buy yourself lots of extra time (schedule), so that you can remain low
cost (do-it-yourself) without sacrificing quality. You can then work on doing
a migration/making improvements at a more leisurely pace, without the
pressure.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________