Multi-Update records from a form (Recordsets)?

C

CB Hamlyn

**SORRY, NOT TRYING TO DOUBLE POST!
THIS JUST SEEMS LIKE A BETTER PLACE TO ASK THIS**

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
 
G

Guest

CB

The findfirst method automatically selects the record you want if it finds
it in your recordset. Replace:
'THIS IS WHERE I NEED TO SELECT THE RECORD I FOUND
Click to expand...
with
rstHours.Edit

Hope this helps.
--
Dale
Email address is not valid.
Please reply to newsgroup only.


CB Hamlyn said:
**SORRY, NOT TRYING TO DOUBLE POST!
THIS JUST SEEMS LIKE A BETTER PLACE TO ASK THIS**

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
Click to expand...
 
C

CB Hamlyn

Are .Edit and .FindFirst only available in DAO? I'm using ADO and it's
throwing up "No Method Found" errors left and right.

Dale Fye said:
CB

The findfirst method automatically selects the record you want if it finds
it in your recordset. Replace:
'THIS IS WHERE I NEED TO SELECT THE RECORD I FOUND
Click to expand...
with
rstHours.Edit

Hope this helps.
--
Dale
Email address is not valid.
Please reply to newsgroup only.


CB Hamlyn said:
**SORRY, NOT TRYING TO DOUBLE POST!
THIS JUST SEEMS LIKE A BETTER PLACE TO ASK THIS**

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
Click to expand...
Click to expand...
 
G

Guest

Sorry, didn't pick up on that. Don't use ADO enough to have an answer off
the top of my head.

--
Email address is not valid.
Please reply to newsgroup only.


CB Hamlyn said:
Are .Edit and .FindFirst only available in DAO? I'm using ADO and it's
throwing up "No Method Found" errors left and right.

Dale Fye said:
CB

The findfirst method automatically selects the record you want if it finds
it in your recordset. Replace:
'THIS IS WHERE I NEED TO SELECT THE RECORD I FOUND
Click to expand...
with
rstHours.Edit

Hope this helps.
--
Dale
Email address is not valid.
Please reply to newsgroup only.


CB Hamlyn said:
**SORRY, NOT TRYING TO DOUBLE POST!
THIS JUST SEEMS LIKE A BETTER PLACE TO ASK THIS**

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
Click to expand...
Click to expand...
Click to expand...
 
C

CB Hamlyn

My worst fears seem to have come true :(

Seems ADO.Find doesn't support conditions for more than one column. Very
useful. I'll have to find some other way. Any suggestions?

Dale Fye said:
Sorry, didn't pick up on that. Don't use ADO enough to have an answer off
the top of my head.

--
Email address is not valid.
Please reply to newsgroup only.


CB Hamlyn said:
Are .Edit and .FindFirst only available in DAO? I'm using ADO and it's
throwing up "No Method Found" errors left and right.

Dale Fye said:
CB

The findfirst method automatically selects the record you want if it
finds
it in your recordset. Replace:

'THIS IS WHERE I NEED TO SELECT THE RECORD I FOUND
with
rstHours.Edit

Hope this helps.
--
Dale
Email address is not valid.
Please reply to newsgroup only.


:

**SORRY, NOT TRYING TO DOUBLE POST!
THIS JUST SEEMS LIKE A BETTER PLACE TO ASK THIS**

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
Click to expand...
Click to expand...
Click to expand...
 
C

CB Hamlyn

I think I'm gonna try opening "Hours" with a Select Statement to only get
those records where the Week End Date is the same as the calendar date, then
filter it for each employee on the list. I'll post the code if/when it
works :)



CB Hamlyn said:
My worst fears seem to have come true :(

Seems ADO.Find doesn't support conditions for more than one column. Very
useful. I'll have to find some other way. Any suggestions?

Dale Fye said:
Sorry, didn't pick up on that. Don't use ADO enough to have an answer
off
the top of my head.

--
Email address is not valid.
Please reply to newsgroup only.


CB Hamlyn said:
Are .Edit and .FindFirst only available in DAO? I'm using ADO and it's
throwing up "No Method Found" errors left and right.

CB

The findfirst method automatically selects the record you want if it
finds
it in your recordset. Replace:

'THIS IS WHERE I NEED TO SELECT THE RECORD I FOUND
with
rstHours.Edit

Hope this helps.
--
Dale
Email address is not valid.
Please reply to newsgroup only.


:

**SORRY, NOT TRYING TO DOUBLE POST!
THIS JUST SEEMS LIKE A BETTER PLACE TO ASK THIS**

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
Click to expand...
Click to expand...
Click to expand...
 
C

CB Hamlyn

Here's the working code if anyone wants to do something like this:
------------------------------------------------------------------------------------------------
Private Sub WED_cmdApplyHours_Click()
Dim Cntr As Integer
Dim rstHours As ADODB.Recordset
Set rstHours = New ADODB.Recordset
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Data Entry Form - 2 Tables 1
Retrieving data from recordsets 6
How to count records w/o counting duplicates by week, month, or ye 4
Normalization/Recordsets on Continuous Sub-Form 3
Update multiple record values from form/query 1
Updating muli-records from calculated fields on a form. 4
Form Sorting and Adding Records 1
reporting on non existing records 3

Top