Solved

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

Posted on 2010-08-22
20
340 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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 500 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

726 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