Try again??

[SpaceCadet!]

I posted earlier....and only got confused, so I'd like to try again and make
it simpler. (I truly appreciate the help I got!)

2 Tables

Company
Company Name
Company ID

Location
Company Name (lookup from tblCompany)
Company ID
Location Address

I know that I'm not supposed to enter data directly into a table, but, humor
me (for a moment)

Let's just say I wanted to understand the behind the scenes of my tables. I
want to enter data into the location table. In datasheet view, on a blank
line, I pick from the drop down list, a company name. How can I get the
Company ID to be pulled into the next cell (from tblCompany)

ex:
tblCompany
Company Company ID
ABC 123
DEF 456
GHI 789

tblLocation
Company Company ID Location Address
ABC (pulled from tblCompany) 987 Main St.


Possible??

Thanks again!!

 

[Jeanette Cunningham]

Hi,
You could set up this to happen on a form, you could select the company from
a combo. The combo would have a hidden column containing the CompanyID.
When you choose the company from the drop down, Access stores the companyID
in the location table for you.
If you try to do this in table with lookup fields, it will create a mess
that is extremely hard to untangle.

Table Company is set up correctly for using forms

Change table Location to this:
LocationID >> Primary Key
CompanyID >> Foreign Key from Company
Location Address
and it will be set up correctly to use for a form to enter data.

In the relationships window put both tables and drag the CompanyID from
Company to Location, in the pop dialog choose Enforce Referential Integrity.
Close the relationships window.
Create your form and enter data.

Jeanette Cunningham

 

[SpaceCadet!]

Before I try this in a form, I would like to see if my tables are set up
correctly...and what I have now (and what I've tried from suggestions here),
are not giving me the results I'm hoping to get.

Mainly, I would like to get the auto fill/populating to work in my demo
table before trying it on a full blown version of a form.

Thanks!

 

[Jeanette Cunningham]

This is not possible!
If you set up the tables as I have explained and use a form and subform, you
will be able to enter the data on the form and it will be saved correctly.
You can't test this out on tables and then do the same in a full blown form.
Access just doesn't work that way.

If the suggestions from here are not working, would you consider asking
again but changing your description so hopefully we will better understand
what you are trying to achieve.

Jeanette Cunningham

 

[John W. Vinson]

Before I try this in a form, I would like to see if my tables are set up
correctly...and what I have now (and what I've tried from suggestions here),
are not giving me the results I'm hoping to get.

Mainly, I would like to get the auto fill/populating to work in my demo
table before trying it on a full blown version of a form.

You're misunderstanding the function of Tables and Forms.

Tables are repositories for data. They are NOT designed or appropriate for
interacting with or editing data, except in the simplest of cases. Conditional
combo boxes are not the simplest of cases!

For routine editing of data, use a Form. It's perfectly possible to have
conditional combo boxes... ON A FORM. It's not possible to do so in a table.

John W. Vinson [MVP]

 

[SpaceCadet!]

Maybe some background into what I have & what I'm trying to do...

I have 132 company names. Each company is issued an license number. Each
company has a main business address. Some of the companies have other
locations from which they do business (could have up to 10). Also, each
company has at least 1, but could have up to 10 officers.

Originally, I had ALL of this information in one table and I would enter
data into that table. I have created new tables to hold "Locations",
"Company" and "Officers". I also have a table that holds ONLY the Company
Name & License #.

Now, I would like to be able to enter data in a form (as I should have done
in the beginning), but I can't seem to relate my tables correctly. I have
created a main form, and subforms, and added them to make one form, but when
I move to the next record by company name, my locations remain the same (and
they shouldn't).

So, I was thinking that if I understood how I need to connect my tables, it
would make it easier when I went to create & use my forms.

I can use either the Company Name or the License # as primary keys (as they
are both unique.

I still have my original HUGE table, and I don't mind using it, but I'd like
to work with a form.

If anyone understands what I'm trying to accomplish (with so many different
types of records) I will try anything at this point.

Thanks all!!

 

[John W. Vinson]

Maybe some background into what I have & what I'm trying to do...

I have 132 company names. Each company is issued an license number. Each
company has a main business address. Some of the companies have other
locations from which they do business (could have up to 10). Also, each
company has at least 1, but could have up to 10 officers.

Originally, I had ALL of this information in one table and I would enter
data into that table. I have created new tables to hold "Locations",
"Company" and "Officers". I also have a table that holds ONLY the Company
Name & License #.

Ok: so you have relationships

Company 1---n Locations joined on License#
Company 1---n Officers Joined on License #

I'd be inclined to store the "main business address" in the locations table,
perhaps with a field to indicate its primacy.

You don't need two tables for companies and I don't understand why you would
want one.

Now, I would like to be able to enter data in a form (as I should have done
in the beginning), but I can't seem to relate my tables correctly. I have
created a main form, and subforms, and added them to make one form, but when
I move to the next record by company name, my locations remain the same (and
they shouldn't).

So, I was thinking that if I understood how I need to connect my tables, it
would make it easier when I went to create & use my forms.

I can use either the Company Name or the License # as primary keys (as they
are both unique.

The Locations table and the Officers table should have a field for the License
number. I'd avoid using # in a fieldname - it's a date delimiter, and a
special character, and will require that you always delimit the fieldname with
[brackets]. Also, you should NOT use company name as a primary key - it's
subject to change, and it's generally big.

You would use the Relationships window to join the Company table LicenseNo
(the primary key) to the LicenseNo field in the Locations table, and also in
the Officers table.

On your Form you would base the main form on Companies, and have a Subform
based on Locations (using LicenseNo as the master/child link field); and a
separate subform based on Officers, also using LicenseNo as the master/child.

I still have my original HUGE table, and I don't mind using it, but I'd like
to work with a form.

If the information in your huge table has been properly transferred to the
three properly normalized tables, then the huge table is redundant and
unnecessary.

If anyone understands what I'm trying to accomplish (with so many different
types of records) I will try anything at this point.

I see three types of records... is that what you mean by "so many"???

John W. Vinson [MVP]

 

[SpaceCadet!]

YAY!!! Thank you, Thank you!!

I now have something I can work with!! How frustrating it was...knowing it
SHOULD be able to do it, but not having the ability to do it!!

Last question....can I make the "company name" in the form a combo box & be
able to pick from the list of the 132 company names? I've made it a combo
box...but the list comes up blank when I'm in form view....

Maybe some background into what I have & what I'm trying to do...

I have 132 company names. Each company is issued an license number. Each
company has a main business address. Some of the companies have other
locations from which they do business (could have up to 10). Also, each
company has at least 1, but could have up to 10 officers.

Originally, I had ALL of this information in one table and I would enter
data into that table. I have created new tables to hold "Locations",
"Company" and "Officers". I also have a table that holds ONLY the Company
Name & License #.

Ok: so you have relationships

Company 1---n Locations joined on License#
Company 1---n Officers Joined on License #

I'd be inclined to store the "main business address" in the locations table,
perhaps with a field to indicate its primacy.

You don't need two tables for companies and I don't understand why you would
want one.

Now, I would like to be able to enter data in a form (as I should have done
in the beginning), but I can't seem to relate my tables correctly. I have
created a main form, and subforms, and added them to make one form, but when
I move to the next record by company name, my locations remain the same (and
they shouldn't).

So, I was thinking that if I understood how I need to connect my tables, it
would make it easier when I went to create & use my forms.

I can use either the Company Name or the License # as primary keys (as they
are both unique.

The Locations table and the Officers table should have a field for the License
number. I'd avoid using # in a fieldname - it's a date delimiter, and a
special character, and will require that you always delimit the fieldname with
[brackets]. Also, you should NOT use company name as a primary key - it's
subject to change, and it's generally big.

You would use the Relationships window to join the Company table LicenseNo
(the primary key) to the LicenseNo field in the Locations table, and also in
the Officers table.

On your Form you would base the main form on Companies, and have a Subform
based on Locations (using LicenseNo as the master/child link field); and a
separate subform based on Officers, also using LicenseNo as the master/child.

I still have my original HUGE table, and I don't mind using it, but I'd like
to work with a form.

If the information in your huge table has been properly transferred to the
three properly normalized tables, then the huge table is redundant and
unnecessary.

If anyone understands what I'm trying to accomplish (with so many different
types of records) I will try anything at this point.

I see three types of records... is that what you mean by "so many"???

John W. Vinson [MVP]

 

[John W. Vinson]

Last question....can I make the "company name" in the form a combo box & be
able to pick from the list of the 132 company names?

Sure. That's what combo boxes are for.

I've made it a combo
box...but the list comes up blank when I'm in form view....

What are the properties of the combo?

RowSource <post the SQL view if it's a query, if it's not a query it should
be>
ColumnCount
BoundColumn
ControlSource
ColumnWidths

John W. Vinson [MVP]

 

[SpaceCadet!]

RowSource Company (no SQL, but invokes the query builder when I click on
the ellipses)<post the SQL view if it's a query, if it's not a query it should
be>
ColumnCount 1
BoundColumn 1
ControlSource Company
ColumnWidths (blank)

 

[BruceM]

I posted a rather detailed explanation in another thread. It would have
been courteous to say in that thread that you had started a new thread. I
already answered some of what has been answered again in this thread. Best
practice in a newsgroup is to remember that everybody here is a volunteer,
and to respect the time they put into responding.

 

[SpaceCadet!]

You're right, BruceM. I apologize. At the time I posted the second thread,
I was so confused by it all. But still that's no excuse...

Please accept my apologies, and know that I appreciate everyones help!!