?
Solved

Access 2007 - Pass "Company Name" to an Employee Form

Posted on 2010-08-22
20
Medium Priority
?
347 Views
Last Modified: 2013-12-20
Ok, the other day I asked a question regarding setting up a Time Clock in Excel.  A suggestion was recommending I instead use an Access database.  So I gave it a whirl.  (As I originally indicated I know little about Access Programming, so I apologize in advance for my stupidity but I am trying to learn and understand).

Anyway, I downloaded from MS the "Time Card" template database and used that as my jumping off point.  

In that database was a table named "Employees" and a query named "Employees - Extended". I added a table called "DBA Locations"  

Since a Company could own more than one Location.  I have attempted, and I believe it worked, linked via Lookup wizard the tables "Company" and "DBA Locations".  That seems to work.

I then went to the Form "Employee Details" and am attempting to modify the template that was from MS.  The template form opens and you have to key all the info details. However, in the record field "Company" it was defaulted as "text" and since an employee in this database can only be assigned to 1 (one) Company I wanted to get just a drop down list that whomever is keying in will just pick the Company Name.  

PROBLEM
The form only shows the table "Company" ID # not the name of the Company.  The drop down list should show a list of names (not the ambiguous ID#) that the operator just picks from.  (This is step one since the next step will be once they've picked the Company the DBA Locations list should filter to only DBA names of that particular Company.

What am I missing?

I've attached the accdb

THANKS IN ADVANCE FOR ANY HELP! Time-Clock-Project.accdb
0
Comment
Question by:wlwebb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
  • 3
  • +1
20 Comments
 

Author Comment

by:wlwebb
ID: 33495367
PS.  I also added a table named "Company"
0
 
LVL 40

Expert Comment

by:als315
ID: 33495400
You can use master for dropdown list (select Company ID field as data and your Company table as source). There can be more then one column in this list and in format properties size of ID column can be set to 0.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33495498
see if this is what you want
Time-Clock-Project.accdb
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:wlwebb
ID: 33495749
als315,  Thanks for the input but I'm NEW to Access so I'm not sure what you mean.  I'm sorry to say.  Is what you are referring to on the "Form" or in the table or query setup?
0
 

Author Comment

by:wlwebb
ID: 33495761
Cap 1, the Dropdown for "Company" is perfect!  However, on the DBA Locations it doesn't allow the operator to select all "DBA Locations" that an employee works at.  (Ie: one employee could float between locations and clock in at a different site).
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 33495834
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33495878
First I will say that if you do not possess a firm grasp in Database design concepts (Table Normalization, Multivalued Fields, Combobox properties, VBA Code)
...This, IMHO, may be a challenging database to start with.

This template presumes that you already have a firm understanding of all of the above principles.

The form you are referencing does in fact allow you to select one or more DBA's per employee.
However in order to "cascade" (one combobox filters another) ...this is another function entirely.

For example, you can filter the Country combobox for the Continent.
So selecting "North America" in the Continent combobox, will display: United Stated, Mexico and Canada..
...in the Countries combobox.

In your case (if I am reading the question correctly) the form you are referencing "Creates" an Employee and then assigns then to a DBA(s) from the combobox.
In order for you to select a DBA and see only the Employees of that DBA, this would probably have to be a "Search" form.

In other words "Assigning" an employee to a DBA via a combox, is not the same function as selecting a DBA from a combobox and filtering the employees.

Make sense...?

;-)

JeffCoachman

 
0
 

Author Comment

by:wlwebb
ID: 33495903
Cap1 - PERFECT.  It probably was the first time you sent it.  The first one I just told to open (didn't select download)  So my guess is it wasn't letting me see "edit" anything you did. So I couldn't test selection of a second DBA Location on my end.

So I can learn, where all the changes you made simply on that "Employee Detail" form?  Or did you have to change anything in the tables or queries?

THANKS FOR THE HELP!!!!!!!

Now comes the next part of this question.  As you will see in the objects, I've created a form called "Time Clock Clock Ins Extended"  When I open that form it shows the data for the last time clock clock in that was "initiated".  I would rather the form open completely blank or with some "greyed text" in the fields that needs the user's input.  Trying to accomplish this I went into the Design View and selected "Properties" and tried making "DEFAULT VALUE" as "" and tried <<expr>>"Select Your Name"
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33495918
<Now comes the next part of this question.>
please post another  Q for the other requirements
0
 

Author Comment

by:wlwebb
ID: 33495923
Cap1
PS, this access programming is MUCH more complicated than what I do in Excel!!!!  Interesting though.  I do like to learn why I'm doing something so I don't have to ask the same questions again the next time.

As for the change you recommended on "Employee Details" form the following code was different than on my original.
SELECT [Company Name].ID, [Company Name].[Company Name] FROM [Company Name];

I went to my form and tried the "drop down and the three dots indicators on that line of code but did not see "SELECT"  Which one gets you to that expression or is it you just know those terms.  As I cautioned, I am a newbie but eager to learn
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33495933
<So I can learn, where all the changes you made simply on that "Employee Detail" form?  Or did you have to change anything in the tables or queries?

Look in the AfterUpdate event of the combo Company..
you will see that the rowsource of DBA Locations is set using the selected value from Company
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33495936
< this access programming is MUCH more complicated than what I do in Excel!!!! >

imho, everything is complicated until you understand what it is all about....
0
 

Author Comment

by:wlwebb
ID: 33495939
Got it, THANKS!!!!!!!!!!!!!!!!!
0
 

Author Comment

by:wlwebb
ID: 33495969
Moderator, I was attempting to credit Cap1 with helping solve my problem and assign the points value there.  I think I clicked the wrong line.  Can you correct
0
 

Author Closing Comment

by:wlwebb
ID: 33495974
I think this assigns it correctly to Cap1
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33495977
wlwebb,

your problem is solved and you are closing the thread by  Accepting your own post..
0
 

Author Comment

by:wlwebb
ID: 33496020
boag

"In your case (if I am reading the question correctly) the form you are referencing "Creates" an Employee and then assigns then to a DBA(s) from the combobox.
In order for you to select a DBA and see only the Employees of that DBA, this would probably have to be a "Search" form."

For clarification, after setup of the initial Company name(s) and DBA name(s),  my Employee form is supposed to allow me to create all or add Employee's to the list.  Within that Employee setup, I want to assign them first to a valid Company and then to valid DBA's of that selected Company.  That is what I am attempting and maybe that wasn't completely clear.  

I can see in testing what Cap1 has recommended what your are referring to.  Ie: that in its present form I could select a DBA Location that wasn't in fact assigned to a particular Company.  Do you have a recommendation (other than hire someone that knows what they're doing?)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33496689
OK,

I just did not want to cause any confusion before I posted...

Now that I see the Points issue is resolved, I will make a suggestion...

There are two ways to create "Cascading Comboboxes"
One is a one Table method, the other is a two table method.

This is really the topic for a New question, but here are some popular links to study first.

http://www.fontstuff.com/access/acctut10.htm

One Table:
http://www.candace-tripp.net/download/cascadecombo2k.zip

Two Tables:
http://www.candace-tripp.net/download/2table_cascadecombo2k.zip

;-)

And you really should grab onto a good Access VBA book, here is a good starter:
http://www.amazon.com/Access-2007-VBA-Bible-ebook/dp/B0014JR2IY/ref=sr_1_2?ie=UTF8&m=AG56TWVU5XWC2&s=digital-text&qid=1282508868&sr=8-2-spell

;-)

JeffCoachman
0
 

Author Comment

by:wlwebb
ID: 33496755
Thanks, I'll study each.  I'm sure I'll end up with more questions so when that time comes I'll post as a new question(s).  You guys have been great!!!! Many thanks.  And, thank you for the reference suggestion.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33496876
;-)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month13 days, 3 hours left to enroll

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question