Access 2007 - ComboBox Filter to List only Open Time cards of a Specific Employee
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 =
queries =
Form =
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
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
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
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
ASKER
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
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
Is the employees dropdown the Parent of the Child?
If you run the Rowsource on it's own, does it return any records?
If you run the Rowsource on it's own, does it return any records?
ASKER
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?
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
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
ASKER
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.
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.
I'll post a basic sample tomorrow
ASKER
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?
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
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
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
ASKER
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.
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.
Yes, because the basic concepts in the samples should be easily adapted to your situation.
Keep us posted...
Jeff
Keep us posted...
Jeff
ASKER
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
Any help?
Test-Cascade.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Got it, opened it and of course it works. I'll study .... Thanks a million
ASKER
Great working with boag. Helped tremendously and provided examples when I couldn't understand.
;-)
ASKER
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?
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?
<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
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
.jpg are viewable in the thread directly...
;-)
Jeff