Creating Forms from Tables

[ADonMS]

Hi. I have 3 tables, Reports, Customers and Employees. I want to create a
form that would allow me to use the name and other fields from the Employees
tbl for my Customers form. I then want to be able to enter data in a form
that includes the customers and reports that would have a main form "Reports"
and subform "Customers". I started out by creating a table labeled
Customers.Reports_tbl and included the customerid and reportid when I created
a form, but not sure if this is the correct setup. It didnt seem to work
quite well.
Any suggestions or additional place to find help is greatly appreciated.

 

[John W. Vinson]

Hi. I have 3 tables, Reports, Customers and Employees. I want to create a
form that would allow me to use the name and other fields from the Employees
tbl for my Customers form. I then want to be able to enter data in a form
that includes the customers and reports that would have a main form "Reports"
and subform "Customers". I started out by creating a table labeled
Customers.Reports_tbl and included the customerid and reportid when I created
a form, but not sure if this is the correct setup. It didnt seem to work
quite well.
Any suggestions or additional place to find help is greatly appreciated.

It seems you're off track, possibly fairly far. A Table cannot contain another
Table - what's Customers.Reports_tbl? How did you create the form? What are
the structures of the Reports, Customers and Employees tables? How (if at all)
are they related? In what way did it "didnt seem to work quite well"?

You might want to take a look at some of the resources here, particularly
Crystal's introductory video:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[ADonMS]

Hey, John. I guess, once again, my explanation was confusing in how I
attempted to explain what I was doing. The Reports tbl is a list of all ad
hoc reports that are created and submitted. The Customers tbl lists all the
employees who receives these reports. I created a new table
(Customers_Reports) and added CustomerID and ReportID foreign keys of parent
Customers and Reports tables to use for my "Customers_Reports form. This form
is used to update new report requests for our customers. By way, thanks for
your response and email links for additional support.

 

[John W. Vinson]

Hey, John. I guess, once again, my explanation was confusing in how I
attempted to explain what I was doing. The Reports tbl is a list of all ad
hoc reports that are created and submitted. The Customers tbl lists all the
employees who receives these reports. I created a new table
(Customers_Reports) and added CustomerID and ReportID foreign keys of parent
Customers and Reports tables to use for my "Customers_Reports form. This form
is used to update new report requests for our customers. By way, thanks for
your response and email links for additional support.

Ah. I think it was just one tiny typo that threw me off: you have a
Customers_Reports_tbl (which is indeed the correct way to go) but you wrote
Customers.Reports_tbl (which is perplexing because the period is a critically
important delimiter in Access names)!

If you use a Form based on Customers, with a Subform based on
Customers_Reports, using CustomerID as the Master/Child Link Field and a combo
box based on Reports, you should be able to assign each customer any desired
number of reports. You can also flip this - use a Form based on Reports, and a
subform based on Customer_Reports, with the ReportID as the master/child and a
combo based on Customers, if you want to assign Customers to a Report rather
than vice versa.

So is this working for you now?

 

[ADonMS]

Hi John.
I actually used both setups. I have a Reports form and Reports subform show
customer info (name, id, title and implement date. Customer subform is just
opposite -- shows the Report detail ie, Report name, etc. But now as I work
through entering the data, I am finding some design flaws with my setup. One
is to use different method to assign Customers to Reports, other than using a
drop down list to search over 500 names on the Reports subform. Another is
the Customer subform should be for "view only", so I think I will need to add
some code to restrict users from updating, just to name a few. Who said this
would be easy?? Thanks again for llinks, as I am needing more than ever.

 

[John W. Vinson]

Hi John.
I actually used both setups. I have a Reports form and Reports subform show
customer info (name, id, title and implement date. Customer subform is just
opposite -- shows the Report detail ie, Report name, etc. But now as I work
through entering the data, I am finding some design flaws with my setup. One
is to use different method to assign Customers to Reports, other than using a
drop down list to search over 500 names on the Reports subform.

One thing that may not be obvious is that you do NOT need to "search" down the
list of names! If you click or tab into a combo box and start typing text, the
combo will jump to the first row containing that text: e.g. if you type "Se"
into a combo containing names in alphabetical order (e.g. "Adams, Jane";
"Alberts, Robert") it will jump directly to "Seville, Ken".

If you aren't selecting a name, how do you intend to do it?

Another is
the Customer subform should be for "view only", so I think I will need to add
some code to restrict users from updating, just to name a few. Who said this
would be easy?? Thanks again for llinks, as I am needing more than ever.

No code needed: just set the Subform's Allow Edits property to No.

 

[ADonMS]

Yes, you're right, John. The combo box does take you directly to names as you
type. So, its not so bad. Thanks for the suggestions regarding the Customer
form. By the way, I am struggling still with my design for the Customer tbl.
My Customer form is based on my Employees tbl, so when I go into the
Customers form, it shows the first employee in the list who is not associated
with any Reports.
I then have to search through each record to find a customer. Should I have
created my Customers_Reports tbl as the Customer form?
Thanks, for much appreciated support as I muddle through this process.

 

[ADonMS]

I just answered my own question and it worked perfectly. How cool is that?

 

[John W. Vinson]

Yes, you're right, John. The combo box does take you directly to names as you
type. So, its not so bad. Thanks for the suggestions regarding the Customer
form. By the way, I am struggling still with my design for the Customer tbl.
My Customer form is based on my Employees tbl, so when I go into the
Customers form, it shows the first employee in the list who is not associated
with any Reports.
I then have to search through each record to find a customer. Should I have
created my Customers_Reports tbl as the Customer form?
Thanks, for much appreciated support as I muddle through this process.

The phrase "created my Customers_Reports tbl as the Customer form" suggests a
certain level of confusion. Tables are not Forms, Forms are not Tables! The
tables are fundamental; Forms are *just tools*, windows to let you manage data
in tables. You need to be sure that your tables are correctly structured and
related first, before messing around with forms.

So... please post the names, important fields, primary keys and relationships
of your tables; and indicate what forms and subforms you are using, and
indicate what's in each Form's Recordsource.

 

[ADonMS]

I admit my terminology is way off, so when I refer to what i do in my db, I
am thinking one thing, saying another.
For the most part, I created the majority of my tables from excel
spreadsheets. I imported the data and then set the data type. The majority of
my tables were created into forms.
Employees_tbl
ReportsLst_tbl
Schedule_tbl
Category_tbl and CategorySeries_tbl (still trying to find how to
autoincrement a number on form based on Category)

All are forms as well.
My Employees_tbl information is used for Customers_Reports_tbl. I stumbled
on the correct use of Customers_frm (previous post) when I found the
Employees_frm was incorrect. I am slowly getting an understanding of Access,
so bare with me.
So far, my ReportsLst_frm has subform linked to the Customers_Reports_tbl
via SQL as the RecordSource (may not have explained that correctly). Master
and Child linked by ReportsLstID as you explained in previous post. Same
setup for Customers subform.
My Customers_frm has foreign key relationship to Employees tbl (primary key
is CustomerID). I use this setup when I am creating new customers to
autopopulate fields. This may not be the best design for my Customers frm,
however, as just found that I cannot update the Employees_tbl when import new
excel file of most current Employees. Any suggestions would be most
appreciated.

 

[John W. Vinson]

I admit my terminology is way off, so when I refer to what i do in my db, I
am thinking one thing, saying another.
For the most part, I created the majority of my tables from excel
spreadsheets.

Just bear in mind that a well-designed spreadsheet could be a very poorly
designed table (and vice versa for that matter).

I imported the data and then set the data type. The majority of
my tables were created into forms.

Again... jargon. You 'created forms as an interface to the tables'. The phrase
"tables were created into forms" is meaningless!

Employees_tbl
ReportsLst_tbl
Schedule_tbl
Category_tbl and CategorySeries_tbl (still trying to find how to
autoincrement a number on form based on Category)

Please explain. Where is this number kept? What is its significance? Under
what circumstance do you want it to increment?

All are forms as well.

You have a form for each table. The tables are not forms.

Normally one would have a form with one or more subforms, but I still don't
understand how your tables are related.

My Employees_tbl information is used for Customers_Reports_tbl. I stumbled
on the correct use of Customers_frm (previous post) when I found the
Employees_frm was incorrect. I am slowly getting an understanding of Access,
so bare with me.
So far, my ReportsLst_frm has subform linked to the Customers_Reports_tbl
via SQL as the RecordSource (may not have explained that correctly). Master
and Child linked by ReportsLstID as you explained in previous post. Same
setup for Customers subform.

What's the SQL? (I can't see it from here and it would really help!)

My Customers_frm has foreign key relationship to Employees tbl (primary key
is CustomerID).

NO. Again, you're mixing up forms with tables! Tables have relationships to
each other; Forms cannot be "related" to tables.

I use this setup when I am creating new customers to
autopopulate fields.

What fields are you trying to autopopulate? Are you trying to copy data
(names??) from one table into another table? If so... don't. Instead, you
would store each piece of information once, and once only, and then link to
it.

This may not be the best design for my Customers frm,
however, as just found that I cannot update the Employees_tbl when import new
excel file of most current Employees. Any suggestions would be most
appreciated.

I'd suggest that you post more information about the names, primary key
fields, names of the other fields (at least the key ones), and relationships
between the tables; and how you're doing the Excel import. Samples of the
Excel data might be helpful.

 

[ADonMS]

Just bear in mind that a well-designed spreadsheet could be a very poorly
designed table (and vice versa for that matter).


Again... jargon. You 'created forms as an interface to the tables'. The phrase
"tables were created into forms" is meaningless!

Sorry. That's not what I meant. I select fields from the tables using the
Form Wizard to create the form.

Please explain. Where is this number kept? What is its significance? Under
what circumstance do you want it to increment?

Not sure how to capture this. Client wants specialized number related to
specific category to be tied to each new Report record. So, if category is
Headcount, the associated "category number" is 600. If new report is
classified as a "headcount" report, it would be given a number of 601, next
headcount report, 602, etc.

You have a form for each table. The tables are not forms.
Correct. Learning...
Normally one would have a form with one or more subforms, but I still don't
understand how your tables are related.

ReportsLst table has a many to one relatonship to the Category and Schedule
tables. Customers_Reports table has a many to one the ReportsLst and Employee
tables.

What's the SQL? (I can't see it from here and it would really help!)
SELECT DISTINCTROW Customers_Reports.CustomerID, Employees.PID, Customers_Reports.ScheduleDt, Customers_Reports.ReportsLstID

FROM Employees INNER JOIN Customers_Reports ON Employees.CustomerID =
Customers_Reports.CustomerID;

NO. Again, you're mixing up forms with tables! Tables have relationships to
each other; Forms cannot be "related" to tables.

Correct. Customers_Reports table is related to Employees table by CustomerID

What fields are you trying to autopopulate? Are you trying to copy data
(names??) from one table into another table? If so... don't. Instead, you
would store each piece of information once, and once only, and then link to
it.
I want to autopopulate the name, schedule date, employee PID and location area to show on the ReportsLst subform

I'd suggest that you post more information about the names, primary key
fields, names of the other fields (at least the key ones), and relationships
between the tables;

Primary keys are ReportsLstID, CustomerID, CategoryID, ScheduleID and
Customers_ReportsID
and how you're doing the Excel import.

I would select Import from the Access database and then browse to the file
folder where the Excel file is stored.

Samples of the

Excel data might be helpful.

An existing Report list (so as to not start from scratch for new database)
This is true for Schedule dates as well. For the most part, the Employees
file is the only file that I will need to continue to import to include the
most current information. (not sure if this is best approach to update
database)
Hope this is helplful. I appreciate your patience and deligence in
providing this support.