Solved

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

Posted on 2010-08-22
20
322 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

708 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

19 Experts available now in Live!

Get 1:1 Help Now