ASP.NET, SQL Server, WPF, C#, VB.NET

I have a form with three main components: 
 
1) A listbox of employees. (this list is collected from a different form) 
 
2) A calendar control. 
 
3) One textbox for each day of the week. 
 
I have a table called "Hours" that has an Employee ID, a Week ending date, 
 
and one field for each day of a week (Sun-Sat) 
 
Since most of the employees work 8 hours, it makes most sense to have the 
 
supervisor go in once a day, pick the employees that were there the day 
 
before (on a different form) type in "8" for the hours worked on the day 
 
before, and hit apply. 
 
The intent is to have it go through each employee on the list, then do a 
 
search in the Hours table, checking each record to see if it matches the 
 
Employee ID from the currently selected employee, as well as the Week Ending 
 
Date selected on the calendar control.  If it finds a match, it updates the 
 
record with whatever hours the user put into the form, if not, it adds a new 
 
record and inputs the data. 
 
I had it working perfectly with Dlookup not finding a record and adding one, 
 
but I think Dlookup isn't the way to go with finding a record and updating 
 
it.  So I'm thinking FindFirst might be the way, but how do I select the 
 
record? 
 
Here's what I got: 
 
--------------------------------------------------------------------------------------------------- 
 
Dim Cntr As Integer 
 
Dim varX As Variant 
 
Dim strCriteria As String 
 
Dim rstHours As ADODB.Recordset 
 
Set rstHours = New ADODB.Recordset 
 
rstHours.Open "Hours", CurrentProject.Connection, adOpenStatic, 
 
adLockOptimistic 
 
For Cntr = 0 To WED_lstEmpSel.ListCount - 1 
 
With rstHours 
 
strCriteria = "[HEmployeeID] = " & WED_lstEmpSel.ItemData(Cntr) 
 
& _ 
 
" AND [HWeekEnding] = " & WED_CalWeekEndDate 
 
.FindFirst (strCriteria) 
 
If .NoMatch Then 
 
.AddNew 
 
.Fields("HEmployeeID") = WED_lstEmpSel.ItemData(Cntr) 
 
.Fields("HWeekEnding") = WED_CalWeekEndDate 
 
Else 
 
'THIS IS WHERE I NEED TO SELECT THE RECORD I FOUND 
 
End If 
 
.Fields("HMon") = WED_txtMon 
 
.Fields("HTue") = WED_txtTue 
 
.Fields("HWed") = WED_txtWed 
 
.Fields("HThu") = WED_txtThu 
 
.Fields("HFri") = WED_txtFri 
 
.Fields("HSat") = WED_txtSat 
 
.Fields("HSun") = WED_txtSun 
 
.Update 
 
End With 
 
Next Cntr 
 
rstHours.Close 
 
Set rstHours = Nothing 
 
WED_lblFinished1.Visible = True 
 
WED_lblFinished2.Visible = True 
 
WED_lblFinished2.Caption = "WEEK ENDING: " & WED_CalWeekEndDate 
 
---------------------------------------------------------------------------------------------- 
 
I'm pretty much at a loss at what even to look for in doing this.  Any 
 
guidance is highly appreciated! 
 
Thanks 
 
Charles Hamlyn

If you're using FindFirst, the recordset will be pointing to the found 
 
record if .NoMatch is false. In other words, you don't need the Else section 
 
of your If statement. 
 
You might want to rethink your table design, though. Repeating groups like 
 
HMon, HTue etc. really aren't a good idea.  Rather than 7 separate fields in 
 
a single row, that should be 7 separate rows in a second table. 
 
-- 
 
Doug Steele, Microsoft Access MVP 
 
http://I.Am/DougSteele 
 
(no private e-mails, please) 
 
"CB Hamlyn" <cbh@newenglandhomes.net> wrote in message 
 
news:12bsdl5dpqaqk93@corp.supernews.com...

Well the HMon, HTue, etc... are how many hours an employee works on each of 
 
those days for a specific week.  Ideally any given record (week) for any 
 
given employee will have at least 5 of those fields filled.  And most of the 
 
reports the user will run are based on the week ending date.  So to make a 
 
table that just had Index, Date, EmployeeID, DateHours, seems like an awful 
 
lot of records, plus loads of calculations to figure out how many of the 
 
records apply to the specific week.  But I concede that I'm no genius when 
 
it comes to relational databases, and I can see where having the potential 
 
for a table with lots of records that don't use every field is a generally 
 
bad idea, so I'm up for convincing if you can explain the benefits of 
 
breaking out the hours into their own table. :)  Either way, thanks for the 
 
help!! 
 
I wasn't able to use .FindFirst or .NoMatch because those are DAO, not ADO 
 
commands.  So I changed it all around.  Here's the code I ended up with if 
 
anyone wants to attempt something like this: 
 
---------------------------------------------------------------------------------------------------------------- 
 
Private Sub WED_cmdApplyHours_Click() 
 
Dim Cntr As Integer 
 
Dim rstHours As ADODB.Recordset 
 
Set rstHours = New ADODB.Recordset 
 
'    rstHours.Open "Hours", CurrentProject.Connection, adOpenStatic, 
 
adLockOptimistic 
 
rstHours.Open "SELECT * FROM Hours " _ 
 
& "WHERE [HWeekEnding] = #" & WED_CalWeekEndDate & "#", 
 
CurrentProject.Connection, adOpenStatic, adLockOptimistic 
 
For Cntr = 0 To WED_lstEmpSel.ListCount - 1 
 
With rstHours 
 
.Filter = "[HEmployeeID] = " & WED_lstEmpSel.ItemData(Cntr) 
 
If .EOF And .BOF Then 
 
.AddNew 
 
.Fields("HEmployeeID") = WED_lstEmpSel.ItemData(Cntr) 
 
.Fields("HWeekEnding") = WED_CalWeekEndDate 
 
End If 
 
If Not WED_txtMon = "" Then .Fields("HMon") = WED_txtMon 
 
If Not WED_txtTue = "" Then .Fields("HTue") = WED_txtTue 
 
If Not WED_txtWed = "" Then .Fields("HWed") = WED_txtWed 
 
If Not WED_txtThu = "" Then .Fields("HThu") = WED_txtThu 
 
If Not WED_txtFri = "" Then .Fields("HFri") = WED_txtFri 
 
If Not WED_txtSat = "" Then .Fields("HSat") = WED_txtSat 
 
If Not WED_txtSun = "" Then .Fields("HSun") = WED_txtSun 
 
.Update 
 
.Filter = "" 
 
End With 
 
Next Cntr 
 
rstHours.Close 
 
Set rstHours = Nothing 
 
End Sub 
 
----------------------------------------------------------------------------------------- 
 
Good luck 
 
Charles Hamlyn 
 
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message 
 
news:%23B%23mpn3qGHA.4960@TK2MSFTNGP04.phx.gbl...

Just to make sure I understood right... this is what I have now: 
 
HoursID -- EmployeeID -- WeekEndDate -- HMon -- HTue --... HSun 
 
1                    123                7/22/06            8 
 
8.5            0 
 
And I think you're proposing this?: 
 
HoursID -- EmployeeID -- Date -- Hours 
 
1                    123        7/17/06      8 
 
2                    123        7/18/06     8.5 
 
.... 
 
7                    123        7/22/06      0 
 
Is that right?  Cause this method is much cleaner than mine, but as I say, 
 
several calculations would have to be done to figure out what dates fit into 
 
what week-ending dates.  If this sounds at all smug, it's obviously not 
 
meant to.  I'm just checking to see if I understood the suggestion :) 
 
Thanks alot! 
 
Charles Hamlyn 
 
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message 
 
news:%23B%23mpn3qGHA.4960@TK2MSFTNGP04.phx.gbl...

Yes, that's the suggestion (other than the fact that you probably wouldn't 
 
have a row if there are no hours) 
 
Without knowing what sort of calculations you're doing, it's hard to address 
 
your concerns. Note, though, that given a date, it's pretty simple to 
 
calculate the WeekEndDate for that date. 
 
-- 
 
Doug Steele, Microsoft Access MVP 
 
http://I.Am/DougSteele 
 
(no private e-mails, please) 
 
"CB Hamlyn" <cbh@newenglandhomes.net> wrote in message 
 
news:12bvr7a1j20edab@corp.supernews.com...

Fair enough.  I'm still designing it and am not totally sure what the end 
 
user has in mind for all the reports, so I'll look into doing it.  Thanks 
 
for the suggestion! 
 
Have a great weekend! 
 
Charles Hamlyn 
 
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message 
 
news:OafQ55FrGHA.4504@TK2MSFTNGP04.phx.gbl...