Solved

Access 2007 - ComboBox Filter to List only Open Time cards of a Specific Employee

Posted on 2010-08-23
19
245 Views
Last Modified: 2013-11-28
It's me again!!!  Tried the next step of the time clock progression, that is employee's clocking out.  Trying to "filter" the Clock out list to only that particular Employee's Clock In's (in case there are multiple clock in's that never got clocked out.

The structure is
main tables =
"Time Clock Clock Ins"
"Employees"

queries =
""Time Clock Clock Ins Extended"
"Employees Extended"

Form =
"Copy of Time Clock Clock Outs"  
(Copied my first attempt that still works but doesn't filter, I'll change name later)


My query design has
Field = "Employee Name"
Table = "Employees Extended"

and

Field = "Time Clock Clock Ins.*"
Table = "Time Clock Clock Ins"

Based upon my reading of what boag suggested yesterday (Cascading reference reading) I have done the following (see pdf's attached)  (hope they attach, pray they attach, don't know if they'll attach)

Am I missing something or is there a better way to do this??

Thanks in advance as always.....
Row-Source---Clock-Outs.pdf
VBA-for-After-Update.pdf
0
Comment
Question by:wlwebb
  • 10
  • 9
19 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33503710
Can you post .jpg Images here, rather than PDF's.

.jpg are viewable in the thread directly...

;-)

Jeff

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33503787
Does it work of not?

If it works, then roll with it.

Any expert will tell you that there is always more than one way to do anything.
So there really is no: "Better".
(via Query, two tables, one table, Via VBA, ...etc)
These are all outlined in the links I provided.

Are you needing more info?

JeffCoachman


0
 

Author Comment

by:wlwebb
ID: 33503886
boag, thanks so much for you assistance.

Attached now are 3 jpg's.  2 for row source and 1 for VBA.  The new row source was me trying to figure out why my "Employee" drop down list now doesn't show anything to select even though a have a couple dummy "clock Ins" in my temp table & query for Clock in punches...

Beginning to think I may be in over my head.  But, I taught myself Excel long before I ever got on these MB's for help so I'm waaaaayyyyy toooooo stubborn to give up.
Row-Source---Clock-Outs.jpg
Row-Source---Clock-Outs-my-Attem.jpg
VBA-for-After-Update.jpg
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33504297
Is the employees dropdown the Parent of the Child?

If you run the Rowsource on it's own, does it return any records?
0
 

Author Comment

by:wlwebb
ID: 33504992
It is the Child if I'm understanding correctly.  The table "Time Clock Clock Ins"  has a query Time Clock Clock Ins Extended"  within the table Time Clock Clock Ins I linked (via Lookup Wizard) that table to the "Employee" table.  Thus the "Employee" it is getting is the Child..........right?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33505328
ummm..

First, can you clearly state what you need here in a basic sense?
For example:
 "I want to select a _______ from a combobox, and then that will in turn filter the a second comboxox of _________(s).


In the most basic sense the Parent is typically the "One" table in a One-To-Many relationship
Example: One Customer-->Many Orders.

In this case One customer can have many orders.
So the Customer table is the One (Parent) table.

Second, are you able to create a basic sample of the cascading functionality of your own?
For example: Countries-->Cities
(Select the Country and the City Combobox filters)

Sometimes creating a simple sample will help you understand the concepts.
Instead of jumping right in with lookup wizards and the like...

;-)

JeffCoachman
0
 

Author Comment

by:wlwebb
ID: 33505708
boag,

Sure, here goes

Multiple employees will "Clock In". - Thus the table Time Clock Clock Ins  (However, as I have seen first hand with some time clock programs, employees can, will and do Clock In multiple times without an intervening "Clock Out")

Then, at the end of their shift, they will call up the "Clock Out" form.  Thus the first thing I want them to do when clocking out is to select their name from the drop down list and to get a list of all Open Clock In's having their name.  Thus a "filter" of the table "Time Clock Clock Ins" showing all only their Clock In's.

As I write I have not created (recreated) the example from the reference, but I will to help set it in my mind.

Thus what I'm needing the form to do is:
1) Open form
2) Select Name from Combolist
3) Select Open Time Clock Clock In from a list provided by the selection of their name from step 2.
4) Save Completed Time Record to a table "Completed Time Clock Entries" -----  {Has a Clock In and a Clock out - All dates and times will be system generated at time of original entry creation via the =Now() Default Setting}

Sorry if I'm too wordy with my descriptions.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33506389
I'll post a basic sample tomorrow
0
 

Author Comment

by:wlwebb
ID: 33509210
boag

I tried and tried "Example 2" of the Cascading.  No Luck.
 I created a 2 column table named "All Info"
Field 1 = Country
Field 2 = City

I created a form and put 2 Combo Boxes
a) ComboCountry
b) ComboCity


at the top of Page 3 of that reference the instructions state to code the following in Row Source for the ComboCountry property sheet:

SELECT DISTINCT [All Info].[Country] FROM [All Info] ORDER BY [All Info].[Country];

When I type that in Row Source for the Combo Box ComboCountry the drop down box is blank

If I choose just Country from the drop down list on the line Row Source  (without putting all the SELECT DISTINCT...) I at least get a list of the Countries.  However, neither alternative gives me anything in the ComboCity listing.



I tried setting up the [Event Procedure] on After Update for ComboCountry and then when that didn't work set it up for ComboCity -------  No luck for either.

Have I miss read something?

Private Sub ComboCity_AfterUpdate()

On Error Resume Next

ComboCity.RowSource = "Select [All Info].[City] " & _

    "FROM [All Info] " & _

    "WHERE [All Info].Country = '" & ComboCountry.Value & "' " & _

    "ORDER BY [All Info].City;"

End Sub







Private Sub ComboCountry_AfterUpdate()

On Error Resume Next

ComboCity.RowSource = "Select [All Info].[City] " & _

    "FROM [All Info] " & _

    "WHERE [All Info].[Country] = '" & ComboCountry.Value & "' " & _

    "ORDER BY [All Info].[City]"

End Sub

Open in new window

0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33510902
I posted 3 links, and they all contained sample files.
Are you saying that none of the three worked as downloaded?

http://www.fontstuff.com/downloads/CascadingListsDemo2000.mdb
http://www.candace-tripp.net/download/cascadecombo2k.zip
http://www.candace-tripp.net/download/2table_cascadecombo2k.zip

JeffCoachman

0
 

Author Comment

by:wlwebb
ID: 33511142
What I tried was Example 2 of the Access Tips: Cascading Lists for Access Forms" text that you referred via an attachment  on another question thread of mine.

The Cascading Combo Boxes Demo (CascadeCombo2k) did work once I enabled the macros.  Originally when I tried it I hadn't enabled the macros I was getting errors.  I'll look at the code for this one and attempt to fix my "All Info" test and see if that works.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33511296
Yes, because the basic concepts in the samples should be easily adapted to your situation.

Keep us posted...

Jeff
0
 

Author Comment

by:wlwebb
ID: 33512089
Ok, I give.  I've tried this and have hit the frustration wall.  I'm attaching the db of "AllInfo" only has ONE form to try to get a drop down list Combo Box from the Country Combo Box.  Can not make it work.

Any help?
Test-Cascade.accdb
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 33512928
Then dump the 1 table method and use the two table method

Study the new sample here carefully...

;-)

Jeff

Test-Cascade.accdb
0
 

Author Comment

by:wlwebb
ID: 33513086
Got it, opened it and of course it works.  I'll study .... Thanks a million
0
 

Author Closing Comment

by:wlwebb
ID: 33513103
Great working with boag.  Helped tremendously and provided examples when I couldn't understand.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33513978
;-)
0
 

Author Comment

by:wlwebb
ID: 33538357
boag

I have been studying the Cascading Combo Boxes Demo using 2 tables that you provided earlier in this link.  I looked at the codes and pretty much understand them but have one operational question as it relates to this specific example db.

I looked at the DB Relationships and NONE are shown.  As I read and understand the code, tblDemo2 is the source for the Combo Box at the bottom of the Cascading Combo Boxes Demo form.  So, to see how tblDemo2 gets updated I added a record to tblDemo.  I expected some code to update tblDemo2 but nothing happens. Is part of the code missing?  since sfrmForm is the subForm and its source is tblDemo2 without a relationship how can it ever update?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33538471
<I looked at the DB Relationships and NONE are shown. >
Correct, there could be for Data Protection (Referential Integrity) or to be pulled in for a query, or just to show the relationships.


On second thought, disregard that sample.

It is doing this by changing rowsources.
This is great for an advanced tutorial, but not for someone just starting out.

Not saying that mine is better, i just think it is easier to follow.

Jeff
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

707 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

14 Experts available now in Live!

Get 1:1 Help Now