Solved

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

Posted on 2010-08-22
20
329 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
  • 10
  • 6
  • 3
  • +1
20 Comments
 

Author Comment

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

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 119

Expert Comment

by:Rey Obrero
ID: 33495498
see if this is what you want
Time-Clock-Project.accdb
0
 

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 119

Accepted Solution

by:
Rey Obrero 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 119

Expert Comment

by:Rey Obrero
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

910 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now