Re: Multiple Tables Confusion

Sorry I'm now just having time to work on this again. But I've made
progress. I've followed all the steps but still have a few questions.

On the final section you mentioned, " See if this form is working (can you
add Parts to an existing Sales order)" is where I'm having trouble. I get
the message "You cannot add or change a ecord because a related record is
required in table "tbl_parts". I don't have parts uploaded into the
database, is this the issue? Or did I miss something you told me along the
way?

And how do I fit the personnel table into this scenario? But honestly once
I get the form working that should make sense I'm hoping.

Thanks again for all your patience.
"Evi" wrote:

To search for a particular client in your client mainform, in design view,
add combo box to the form header. One of the options which the wizard will
usually provide for a table with a primary key is the option to Find a
Record in this form...
Select the ClientId field and client surname (for now -we'll change that to
a whole name later)
Click on ClientID as the unique field.

You say:
I slid the tbl_salesorder into the detail section and I selected "show
tbl_Salesorder for each record in tbl_clients using Client_ID." >
So this is where I'm stuck. Is this right? "

Yes, that's right. How are you stuck? Is the form working? As you turn from
one client to next, can you enter and see their different sales orders
(though not the parts for that sales order yet - that will come when we get
this bit working)
Open your subform in Design view. Add a combo based on tblCountry, having
CountryID and Country (let the Wizard hide the CountryId). Choose to Store
the Value in Country ID.

You should be able to fill in all the sales orders for that client (though
not the Parts yet)



When you've got that working, use the same technique to create a similar
form with TblSalesOrder as the Main form and TblOrderParts as the subform
with a combo based on TblParts in the subform.

Open your main form in Design view.
Click on Properties when you are clicked in a grey area, off the form's
grid. The Property box should say Form in the Title Bar.

Click on the Data tab.

Click just right the Record Source.

Say Yes you want to invoke the Query Builder.

A query will open in Design view, based on TblSalesOrder.
Add all its fields to the query
Click on the Add Tables button.
Add TblClient to the query and TblCountry.

From those tables, DONT add the Primary Key fields. Add those fields which
you want to see in your form (eg ClientName, address etc and CountryID from
TblClient, Country from TblCountry) Only add one Primary key field to your
query.

Close the query and choose to Save the Sql.

Click the field list button and slide those field which you want to see,
onto your form's grid.

See if this form is working (can you add Parts to an existing Sales order)



Evi









"jenniferspnc" <jenniferspnc@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FBA04DA9-5215-475B-8628-9FCC9772B982@xxxxxxxxxxxxxxxx
Morning Evi,
I based the autoform off the table Clients (tbl_clients). I was hoping
that
client would be a drop down so that the user could select the client (as
the
list is predetermined and loaded into the table).



I slid the tbl_salesorder into the detail section and I selected "show
tbl_Salesorder for each record in tbl_clients using Client_ID."

So this is where I'm stuck. Is this right? I ask because I wonder would
this be where a user enters information? Or still too early on?

Can't thank you enough for your patience. Tried searching for an in depth
article on forms but usually find one on how to create a form based off a
single table, and obviously that isn't fitting here. Thanks.

"Evi" wrote:

Hi Jennifer,
If you are making an Autoform, as opposed to using a Wizard, it just
adds
everything in the table. But yes, you would normally add Client Name and
ClientID

You can always use the Properties button to make clientID invisible but
it
will almost certainly come in handy at some stage (eg you want to press
a
button on your form and open a filtered report to show only the details
of
the current client in your form)

I want to make sure that we are not talking at cross purposes. Which
table
did you base your main (Autoform) form on?

Which table did you slide into the main form's Detail section in Design
view?

You say
"In preview it didn't show a drop-down so that the user could select "

Select what? Which stage of the procedure are you describing?

Evi





"jenniferspnc" <jenniferspnc@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:50D9FCEB-5996-41BA-B0C2-D55D55CC4F92@xxxxxxxxxxxxxxxx
Hi Evi,

Thanks for the step by step instructions, but I need a little further
help.
I had made changes as Beetle suggested before seeing your post.

I understand forms are used for data entry but I was testing to see if
it
worked by entering via table which had me confused so I won't go down
that
route anymore.

Based on Beetle's suggestions (and I took your advice also and removed
spaces) I did the following:

tbl_clients
Client ID (PK)
Client Name (predetermined list that I've already imported into that
table)

tbl_personnel (realized that the client would have more than one order
person thus needing a separate table).
personnel_id (PK)
First_Name
Last_Name
Client_ID (FK)

tbl_Country
Country_ID (PK)
Country (already imported this list of all countries)

tbl_Manufacturer
Manufacturer_ID (PK)
Manufacturer

tbl_Parts
Part_Number (PK)
Manufacturer_ID (FK)
Product Description
ECCN

tbl_OrderParts
Part_NUmber (PK)
Sales_Order (PK)

tbl_SalesOrder
Sales_Order (PK)
Client_ID (FK)
Country_ID (FK)
Date_Approved
First_Shipped
Recent_Shipped
Denied_List
Status

My relationships are as follows now:
tbl_clients 1:M tbl_personnel (based on client_ID)
tbl_clients 1:M tbl_salesorder (based on client_ID)
tbl_country 1:M tbl_salesorder (based on Country_ID)
tbl_salesorder 1:M tbl_orderParts (based on Sales_order)
tbl_parts 1:M tbl_OrderParts (based on Part_Number)
tbl_manufacturer 1:M tbl_parts (based on Manufacturer_ID)

So I tried to follow along the form suggestions but got confused.
When I
do
the autoform based on tbl_clients do I add both the client_ID and
client?
In
preview it didn't show a drop-down so that the user could select or is
that
too early to worry about now? How would I incorporate my new tables
into
this form design you suggested (which again, I really appreciate you
taking
the time to do)?

Thanks.
Have a good weekend!



"Evi" wrote:

Your Table Design looks perfectly sensible. I'd just suggest
omitting
the
spaces between your field names - it will make future queries etc
easier
to
build because you won't have to type square brackets around
everything
(your
labels can show whatever you want them to).

May I suggest that instead of trying to enter your data via those
Subdatasheets in tables, that you make a proper form. You'll be
losing
loads
of the advantages of using Access by working with tables besides
being
at
the mercy of Wizards.

Start off with a simple design (eventually you can make the 2
subform
design
that you get in Northwind)

Have a main form based on TblClients (Insert, Form, Autoform,
Columnar)

Open the form in Design View
Slide TblOrders from the main db window onto the form's Detail
section.
The Wizard should kick in and allow you to link via Client ID.
Add a combo for tblCountry (I'd suggest having a proper table with a
Primary
key field and then the Country name)

Make a query based on TblOrders. Add all the fields from that table.
Add TblClient to the query. Do not add ClientID from TblClient (that
comes
from TblOrders)
Add the other fields you require.

Make a main form from this query, as before.
Slide TblOrderParts in to make the subform, linking by OrderID
Add a combo based on TblParts containing Part Number (close up that
column)
and those fields you need to identify the part. Choose to have it
store
the
Value in the Part Number field. Add your partnumbers via this combo

Go back to your first (Client) main form and add a button to it to
open
up
your Orders Form
edit the code so that it filters the orders form to show the order
selected
in your subform

DoCmd OpenForm "FrmOrders",,,"[OrderID]=" &
Me.YourSubformName.Form.[OrderID]

Evi

PS if any of that is unclear, please say.


"jenniferspnc" <jenniferspnc@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:85D317F2-7E9E-4158-A84A-3CCA15A5D810@xxxxxxxxxxxxxxxx
I've done more reading from www.profsr.com which is somewhat
broken
out.


Based on that I've done the following:
tbl_clients:
Client_ID (PK - autonumber)
Client - looks up from a table called clients (predetermined list)
First Name
Last Name

tbl_salesorder
Sales_Order (PK)
Client_ID (FK)
Country - looks up from a table called countries (to avoid
mistypes)
Date Approved
First Shipped
Denied Party List
.

Loading