Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-08-22
20
Medium Priority
?
350 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

636 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