Creating a form with multiple table sources

[Ken]

I am attempting to create an Order entry form, and all attempts thus
far have run into major snags. Our company has been using a modified
version of the Northwind database, but due to the design, any changes
we make to billing on a new order propagates though all the old orders.

What I have done is created a new database with the following table
structure:

tblCompany - companyID (primary key), company, companydiscount
tblContacts - contactID (primary key), companyID, name, position,
address1, address2, etc.
tblBillto - billtoID (primary key), companyID, billcompany, address1,
address2, etc.
tblShipto - shiptoID (primary key), companyID, shipcompany, address1,
address2, etc.
tblSalesperson - salesmanID (primary key), fullname
tblShippercompany - shipperID (primary key), shippername
tblOrders - orderID (primary key), companyID, billtoID, shiptoID,
salesmanID, contactID, shipperID, jobno, ponumber, etc.
tblProducts - productID (primary key), productname, unitprice, etc.
tblOrderdetails - orderdetailsID (primary key), orderID, productID,
notes, quantity, etc.

Based on this structure, what I would like is a form for orders that
has the ability to select a company that then filters the contacts,
billto, and shipto selections to that one company. Each company can
have multiple contacts, billto addresses, and shipto addresses

Should this be a straight-forward form, or will it require a lot of
behind the scenes coding? I've tried basing the form on one large
query, on multiple queries, on subforms... all kinds of variants, but
always run into some major obstacle. I've tried looking for some
kind of example, but none I've found have covered this kind of
scenario.

If someone could provide a nudge in a particular direction, I'd
certainly appreciate it (book to buy, website to read, etc.).

Ken

 

[Guest]

hi,
I'm biting off a lot here so bear with me.
If you are making changes to your tblbillto such as
address1 then the old address no longer exist and will
match to all orders - old and new.
if you want to keep data like the billto address for each
order then i would suggest that you put the accual billto
address in the orders table instead of the billtoID. that
is the way we do it. each PO has its own shiped from place
and each Sales order has it's own ship to adress and bill
to adress. you might consider this if you want to preserve
the accuals of each order. your billto table and shipto
table can provide defaults but the orders table will
preserve the accual.
One thing i noticed about your tables. no Dates???? we
preserve dates -date order created, date shipped, date
manufactured, date invoiced, ect. so that we can track an
order through our entire system.
also no "your invoice id" or invoice date. Accounting
might like to know that so that they will know which and
when invoices are "Past due".
I am not sure if i helped you or not. if not- repost.
good luck.

 

[Ken]

Thanks for the reply. I could do that with the billto, but then I run
into the same problems with the shipto and contacts. The issue I'm
trying to get around is the fact that many of our customers have
multiple shipto, billto, and contact addresses, and I'm trying to
devise a way to link the multiple versions of each to a unique company
ID and at the same time reference them into an orders table (to keep
track of which one a particular order was to). That way we don't have
multiple entries of the billto, shipto, and contact addresses repeating
over and over again, and we'll be able to select addresses from
drop-down boxes.

That's my hope at least. When I attempted to create a form off a
massive query, it would show existing data correctly, but wouldn't let
me add new data. When I created subforms, it would show existing data
correctly, but I couldn't get the combo boxes to filter data correctly
when attempting to add new data. When I tried creating unbound boxes
with all kinds of code, I got so confused my head started to spin.

Regarding the dates, in my listing above:

tblOrders - orderID (primary key), companyID, billtoID, shiptoID,
salesmanID, contactID, shipperID, jobno, ponumber, etc.

The etc. portion actually has four different date entries (order
placed, required date, shipped date, and billing date).
Thanks for the help!

Ken

 

[Guest]

hi again.
we have the same thing here. each SO can have as many as
10 shipto addresses with multiple ship dates and ship
quantiies. so we don't try to do it with 1 massive query.
instead we do it one sales order at a time. we have a
summary query that shows all open SOs (and a query that
shows closed SOs) but when working on a SO we only call up
1 SO at a time. same for POs and WOs. Closed orders can't
be edited ie they're done.
We use unbound forms and call the SO, PO or WO to temp
tables via order number to do the accual work . the form
displays the contents of the temp table. when finished the
temp table data is updated via update query to the main
table and the contents of the temp table is deleted for
the next order.
sounds like you are where i was 5 years ago. and i
know.....headache BIG time.
good luck and much sympathy.

 

[Ken]

Thanks for the reply. From what you're describing, it doesn't sound
like this setup is an easy thing to accomplish in Access. The deeper I
get into the problem, the more surprised I am that creating a setup
like this one requires as much work as it does. Not to mean I don't
mind putting in a bit of effort, but the form of the database I'm
setting up can't be a unique problem -- the solution with Access sure
doesn't appear to be something that any Joe Blow could easily come to
without some fairly intense schooling. So far, two books I've got from
the library do not address this type of setup.

I imagine that there are lots of small businesses with datasets with
multiple unique sets of data, but their database doesn't work well for
them because most of the power of Access is hidden through limited
instructions (what little comes with the program now).

 

[Larry Daugherty]

Hi Ken,

Note that in the suggested cbo's below you can use the after update event to
fill relevant text boxes on your form with information using the
...cboName.column(n) functionality.

It looks like your form should be based or tblOrders.

create cboGetCompany based on a query sorted on Company Name and with
Autoexpand enabled to select the one company. Make CompanyID the bound
column with a visible width of 0.0".

base cboGetContacts on a query that returns all contacts with a foreign key
equal to the companyID sitting in cboGetCompany , alpha sorted on the
contact's last name.

The same kind of functionality could get you through the rest of the
selection process.

By the way, where you are showing "fullname", there should really be a
LastName, FirstName, MiddleNameOrInitial convention to make your sorts work
as most people might expect and to more fully normalize your data.

I'd create a subform to handle the order details. Design the subform to
show what you want and choose continues form display. The contents of the
Order Details subform control will then look a lot like the datasheet view
but should be more useful for you.

There are other ways to achieve the results you want but this should get you
over your present hurdle.

HTH

 

[Larry Daugherty]

Hi Ken,

I answered your original post and didn't notice your replies to others, I
don't see their replies so don't know what they wrote...

Microsoft does a good job of creating an "easy to use" application at the
earliest entry level and thereby raises expectations outrageously. They
simply didn't caution you that there may be difficulties in enhancing the
functionality. They do acknowledge it both directly and indirectly. The
Northwinds database is a pretty good low to mid level example but no one
application can show you all there is to know. There is more to know about
Access and relational databases than a single application or a good book can
cover. If you have Solutions.MDB I suggest you work your way through it.
buy good text books and spend time with them as you already have done. It's
a continuing process if you want to provide solutions to increasingly
complex issues.

It's evident from your table design that you have already learned a lot
about normalization and relationships.

About your specific complaint about the lack of canned solutions from MS to
do something as common as what you are trying to achieve; think about it.
MS provides Access as a set of tools which someone can use to create their
solutions atop a relational database, Jet or MSDE are assumed but you could
find other backends. They gave you one model, not too sophisticated but
workable, for many cases. However, when you need something different you
have to provide it or hire it done. Many Access developers are making a
living by providing Orders applications. While the main functionality
remains constant across all of the applications, there are tremendous
variations in the details of what is presented to the user and the
underlying functionality and how it interfaces with other applications and
data the client already has in play. Just look at the
microsoft.public.access... newsgroups and lurk a few.

HTH

 

[Ken]

Larry,

Thanks for your replies. Your approach is one that I have tried
(somewhat), but with your suggestions, hopefully I'll get a bit further
this time. One question I do have, since I am creating unbound combo
boxes to do my GetCompany and GetContacts functions, one of the
problems I'm running into is linking the boxes to the active Orders
record (that updates as you scroll through the records). Currently, as
I'm scrolling through old order records, the combo boxes just sit there
empty. I know it's a product of my own limited Access knowledge, but
trying to find a solution that works is becoming frustrating - code I
think should do something (at least give an error) just does nothing,
and watches on my variables constantly give the error "can't compile
module". What I attempted to do is to show data in the combo boxes
based on the companyID foreign key of the current Orders record, but I
just can't get the two to link up correctly.

Regarding your second message -- I do appreciate the effort that the
experts have expended to get where they are. It's just frustrating to
me that after two books and all the online searches, I can not find a
decent example that demonstrates (even in very basic form) some
approach to what I am trying to do. It doesn't seem like that unique a
problem to me, especially with all the effort (in the books) put into
describing how to normalize your database. All this talk about
creating tables for everything, and then when it comes time to talk
about forms - it's based on a entering data into a single table (or
maybe two).

The company where I work is a bit leary about going outside for help
since that was what resulted in the previous iteration of our database.
I'm trying to get something going during slow times, and the Christmas
to New Years time period gave me a good chance to jump in. I just
didn't expect to drown so quickly.

Thanks again,

Ken