By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,504 Members | 1,190 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,504 IT Pros & Developers. It's quick & easy.

Help- Problems with rst.Edit

P: n/a
Dear All

I'm trying to use a multi-select listbox as criteria to either add new
record or edit the current record of the destiantation table with each
row selected in the box. This works fine for adding a new record but
once records exist in the destination table, the routine seems to
overwrite the first highlighted record in the destination table with
the last highlighted record record, therefore giving two duplicate
records. I've listed the code below, any advice is greatfully
received as I seem to have drawn a complete blank and have been trying
various methods all day.

David

Private Sub cmdAutoReport_Click()

'Sets all group members reports to the default comments

For i = 0 To lstGroupMem.ListCount
lstGroupMem.Selected(i) = True

Next i

If MsgBox("Set the final report comments for all highlighted group
members?", vbYesNo + vbQuestion, "Auto Set Reports") = vbNo Then

'Clear highlighted records
For i = 0 To lstGroupMem.ListCount
lstGroupMem.Selected(i) = False
Next i

Exit Sub

Else

DoCmd.SetWarnings False
'Declare variables

Dim ATID As Variant 'variable for attaiment indicators ID
Dim Gender As String 'variable for gender
Dim attainment As Variant 'variable for attainment level
Dim attainsub As Variant 'variable for attainment sub-level
Dim skills As String 'variable for skills comment
Dim performance As Variant 'variable for performance comment
Dim Target As Variant 'variable for target comment
Dim effort As String 'variable for effort comment
Dim lbl3ID As Variant
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

'Set recordset focus on tblReportComments
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblReportComments")

'Introduce variant
For Each lbl3ID In lstGroupMem.ItemsSelected 'the listbox with the
groupmember values

Gender = lstGroupMem.Column(10, lbl3ID) 'set the gender value
from the groupmembers listbox
attainment = lstGroupMem.Column(5, lbl3ID) 'set the attainment
value from the groupmembers listbox
attainsub = lstGroupMem.Column(6, lbl3ID) 'set the attainment
sub-level value from the groupmembers listbox

'set the attainment ID value using the attainment level and
gender from tblAttainmentIndicators
ATID = DLookup("[AttainmentID]", "tblAttainmentIndicators",
"[Attainment Level] = " & "'" & attainment & "'" & "And [Attainment
Gender]=" & "'" & Gender & "'")

'sets the comment values using the ATID value from
tblAttainmentSubLevel
skills = DLookup("[Basic Skills and Tactics]",
"tblAttainmentSubLevel", "[AttainmentID] =" & ATID & "And
[AttainmentSubLevel] =" & "'" & attainsub & "'")
performance = DLookup("[Performance and Physiology]",
"tblAttainmentSubLevel", "[AttainmentID]=" & ATID & "And
[AttainmentSubLevel] =" & "'" & attainsub & "'")
Target = DLookup("[Target]", "tblAttainmentSubLevel",
"[AttainmentID]=" & ATID & "And [AttainmentSubLevel] =" & "'" &
attainsub & "'")

'sets the effort comment value
effort = DLookup("[Effort Description]", "tblEffort", "[Effort
Grade]= 'b' ")
'Check to see if the groupmember selected already has a report
comments record
If DCount("[Comment ID]", "tblReportComments", "[Group Member ID]=
" & lstGroupMem.Column(0, lbl3ID)) = 0 Then
'add a new record in tblReportComments
rst.AddNew
rst![Group Member ID] = lstGroupMem.Column(0, lbl3ID)
'groupmember ID from the groupmember listbox
rst![Targets Comments] = Target 'set the target
comment
rst![Basic Skills and Tactics] = skills 'set the basic
skills comment
rst![Performance and Physiology] = performance 'set
the performance comment
rst![Effort Comments] = effort 'set the effort comment

'update the recordset
rst.Update

'else edit the current report comment record with the new values
Else

MsgBox lstGroupMem.Column(0, lbl3ID)

'edit current record in tblReportComments
rst.Edit
rst![Group Member ID] = lstGroupMem.Column(0, lbl3ID)
'groupmember ID from the groupmember listbox
rst![Targets Comments] = Target 'set the target
comment
rst![Basic Skills and Tactics] = skills 'set the basic
skills comment
rst![Performance and Physiology] = performance 'set
the performance comment
rst![Effort Comments] = effort 'set the effort comment
'update the recordset
rst.Update

End If

Next lbl3ID
Set dbs = Nothing
Set rst = Nothing

For i = 0 To lstGroupMem.ListCount
lstGroupMem.Selected(i) = False
Next i

DoCmd.SetWarnings True

End If

End Sub
Feb 15 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
David wrote:
Dear All

I'm trying to use a multi-select listbox as criteria to either add new
record or edit the current record of the destiantation table with each
row selected in the box. This works fine for adding a new record but
once records exist in the destination table, the routine seems to
overwrite the first highlighted record in the destination table with
the last highlighted record record, therefore giving two duplicate
records. I've listed the code below, any advice is greatfully
received as I seem to have drawn a complete blank and have been trying
various methods all day.

David

Private Sub cmdAutoReport_Click()

'Sets all group members reports to the default comments

For i = 0 To lstGroupMem.ListCount
lstGroupMem.Selected(i) = True

Next i
The above line works but if you were attempting to get info from it it
wouldn't. For example, if the listbox name was List0, the following
would blow up.
For intFor = 0 To Me.List0.ListCount
MsgBox Me.List0.Column(0, intFor)
Next

The reason? The first row is 0 in the list count. If you have 3 rows,
the rows would be 0,1,2. So normally one would make it ListCount-1.
>
If MsgBox("Set the final report comments for all highlighted group
members?", vbYesNo + vbQuestion, "Auto Set Reports") = vbNo Then

'Clear highlighted records
For i = 0 To lstGroupMem.ListCount
lstGroupMem.Selected(i) = False
Next i

Exit Sub

Else

DoCmd.SetWarnings False
'Declare variables

Dim ATID As Variant 'variable for attaiment indicators ID
Dim Gender As String 'variable for gender
Dim attainment As Variant 'variable for attainment level
Dim attainsub As Variant 'variable for attainment sub-level
Dim skills As String 'variable for skills comment
Dim performance As Variant 'variable for performance comment
Dim Target As Variant 'variable for target comment
Dim effort As String 'variable for effort comment
Dim lbl3ID As Variant
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

'Set recordset focus on tblReportComments
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblReportComments")

'Introduce variant
For Each lbl3ID In lstGroupMem.ItemsSelected 'the listbox with the
groupmember values

Gender = lstGroupMem.Column(10, lbl3ID) 'set the gender value
from the groupmembers listbox
attainment = lstGroupMem.Column(5, lbl3ID) 'set the attainment
value from the groupmembers listbox
attainsub = lstGroupMem.Column(6, lbl3ID) 'set the attainment
sub-level value from the groupmembers listbox

'set the attainment ID value using the attainment level and
gender from tblAttainmentIndicators
ATID = DLookup("[AttainmentID]", "tblAttainmentIndicators",
"[Attainment Level] = " & "'" & attainment & "'" & "And [Attainment
Gender]=" & "'" & Gender & "'")

'sets the comment values using the ATID value from
tblAttainmentSubLevel
skills = DLookup("[Basic Skills and Tactics]",
"tblAttainmentSubLevel", "[AttainmentID] =" & ATID & "And
[AttainmentSubLevel] =" & "'" & attainsub & "'")
performance = DLookup("[Performance and Physiology]",
"tblAttainmentSubLevel", "[AttainmentID]=" & ATID & "And
[AttainmentSubLevel] =" & "'" & attainsub & "'")
Target = DLookup("[Target]", "tblAttainmentSubLevel",
"[AttainmentID]=" & ATID & "And [AttainmentSubLevel] =" & "'" &
attainsub & "'")

'sets the effort comment value
effort = DLookup("[Effort Description]", "tblEffort", "[Effort
Grade]= 'b' ")
'Check to see if the groupmember selected already has a report
comments record
If DCount("[Comment ID]", "tblReportComments", "[Group Member ID]=
" & lstGroupMem.Column(0, lbl3ID)) = 0 Then
'add a new record in tblReportComments
rst.AddNew
rst![Group Member ID] = lstGroupMem.Column(0, lbl3ID)
'groupmember ID from the groupmember listbox
rst![Targets Comments] = Target 'set the target
comment
rst![Basic Skills and Tactics] = skills 'set the basic
skills comment
rst![Performance and Physiology] = performance 'set
the performance comment
rst![Effort Comments] = effort 'set the effort comment

'update the recordset
rst.Update

'else edit the current report comment record with the new values
Else

MsgBox lstGroupMem.Column(0, lbl3ID)

'edit current record in tblReportComments
rst.Edit
rst![Group Member ID] = lstGroupMem.Column(0, lbl3ID)
'groupmember ID from the groupmember listbox
rst![Targets Comments] = Target 'set the target
comment
rst![Basic Skills and Tactics] = skills 'set the basic
skills comment
rst![Performance and Physiology] = performance 'set
the performance comment
rst![Effort Comments] = effort 'set the effort comment
'update the recordset
rst.Update

End If

Next lbl3ID
Set dbs = Nothing
Set rst = Nothing

For i = 0 To lstGroupMem.ListCount
lstGroupMem.Selected(i) = False
Next i

DoCmd.SetWarnings True

End If

End Sub
Well, you got the count to determine if it existed. And so you edited
whatever record you were sitting on. I think you might find it crucial
to finding the record you are editing. In fact, the Dcount isn't needed
as far as I can see. For example
rst.FindFirst "[Group Member ID]= " & lstGroupMem.Column(0,lbl3ID))
If rst.NoMatch then'
rst.AddNew
Else
rst.Edit
Endif
would make more sense.

Boots
http://www.youtube.com/watch?v=nw7Qg_2JjWk

Feb 15 '08 #2

P: n/a
On Feb 15, 4:07*pm, Salad <o...@vinegar.comwrote:
David wrote:
Dear All
I'm trying to use a multi-select listbox as criteria to either add new
record or edit the current record of the destiantation table with each
row selected in the box. *This works fine for adding a new record but
once records exist in the destination table, the routine seems to
overwrite the first highlighted record in the destination table with
the last highlighted record record, therefore giving two duplicate
records. *I've listed the code below, any advice is greatfully
received as I seem to have drawn a complete blank and have been trying
various methods all day.
David
Private Sub cmdAutoReport_Click()
'Sets all group members reports to the default comments
For i = 0 To lstGroupMem.ListCount
* * * * *lstGroupMem.Selected(i) = True
* * Next i

The above line works but if you were attempting to get info from it it
wouldn't. *For example, if the listbox name was List0, the following
would blow up.
* * *For intFor = 0 To Me.List0.ListCount
* * * * *MsgBox Me.List0.Column(0, intFor)
* * *Next

The reason? *The first row is 0 in the list count. *If you have 3 rows,
the rows would be 0,1,2. *So normally one would make it ListCount-1.


If MsgBox("Set the final report comments for all highlighted group
members?", vbYesNo + vbQuestion, "Auto Set Reports") = vbNo Then
* * 'Clear highlighted records
* * For i = 0 To lstGroupMem.ListCount
* * * * lstGroupMem.Selected(i) = False
* * Next i
* * Exit Sub
Else
* * DoCmd.SetWarnings False
* * 'Declare variables
* * Dim ATID As Variant 'variable for attaiment indicators ID
* * Dim Gender As String 'variable for gender
* * Dim attainment As Variant 'variable for attainment level
* * Dim attainsub As Variant 'variable for attainment sub-level
* * Dim skills As String 'variable for skills comment
* * Dim performance As Variant 'variable for performance comment
* * Dim Target As Variant 'variable for target comment
* * Dim effort As String 'variable for effort comment
* * Dim lbl3ID As Variant
* * Dim dbs As DAO.Database
* * Dim rst As DAO.Recordset
* * * 'Set recordset focus on tblReportComments
* * * * Set dbs = CurrentDb
* * * * Set rst = dbs.OpenRecordset("tblReportComments")
* * 'Introduce variant
* * For Each lbl3ID In lstGroupMem.ItemsSelected 'the listbox with the
groupmember values
* * * Gender = lstGroupMem.Column(10, lbl3ID) 'set the gender value
from the groupmembers listbox
* * * attainment = lstGroupMem.Column(5, lbl3ID) 'set the attainment
value from the groupmembers listbox
* * * attainsub = lstGroupMem.Column(6, lbl3ID) 'set the attainment
sub-level value from the groupmembers listbox
* * * 'set the attainment ID value using the attainment level and
gender from tblAttainmentIndicators
* * * ATID = DLookup("[AttainmentID]", "tblAttainmentIndicators",
"[Attainment Level] = " & "'" & attainment & "'" & "And [Attainment
Gender]=" & "'" & Gender & "'")
* * * 'sets the comment values using the ATID value from
tblAttainmentSubLevel
* * * skills = DLookup("[Basic Skills and Tactics]",
"tblAttainmentSubLevel", "[AttainmentID] =" & ATID & "And
[AttainmentSubLevel] =" & "'" & attainsub & "'")
* * * performance = DLookup("[Performance and Physiology]",
"tblAttainmentSubLevel", "[AttainmentID]=" & ATID & "And
[AttainmentSubLevel] =" & "'" & attainsub & "'")
* * * Target = DLookup("[Target]", "tblAttainmentSubLevel",
"[AttainmentID]=" & ATID & "And [AttainmentSubLevel] =" & "'" &
attainsub & "'")
* * * 'sets the effort comment value
* * * effort = DLookup("[Effort Description]", "tblEffort", "[Effort
Grade]= 'b' ")
* * * *'Check to see if the groupmember selected already has a report
comments record
* * If DCount("[Comment ID]", "tblReportComments", "[Group Member ID]=
" & lstGroupMem.Column(0, lbl3ID)) = 0 Then
* * * 'add a new record in tblReportComments
* * * * * * rst.AddNew
* * * * * * * * rst![Group Member ID] = lstGroupMem.Column(0, lbl3ID)
'groupmember ID from the groupmember listbox
* * * * * * * * rst![Targets Comments] = Target 'set the target
comment
* * * * * * * * rst![Basic Skills and Tactics] = skills 'set the basic
skills comment
* * * * * * * * rst![Performance and Physiology] = performance 'set
the performance comment
* * * * * * * * rst![Effort Comments] = effort 'set the effort comment
* * * * * * 'update the recordset
* * * * * * rst.Update
* * * 'else edit the current report comment record with the new values
* * * Else
* * * * * * MsgBox lstGroupMem.Column(0, lbl3ID)
* * * * * * 'edit current record in tblReportComments
* * * * * * rst.Edit
* * * * * * * * rst![Group Member ID] = lstGroupMem.Column(0, lbl3ID)
'groupmember ID from the groupmember listbox
* * * * * * * * rst![Targets Comments] = Target 'set the target
comment
* * * * * * * * rst![Basic Skills and Tactics] = skills 'set the basic
skills comment
* * * * * * * * rst![Performance and Physiology] = performance 'set
the performance comment
* * * * * * * * rst![Effort Comments] = effort 'set the effort comment
* * * * * * 'update the recordset
* * * * * * rst.Update
* * End If
* * Next lbl3ID
* * * * Set dbs = Nothing
* * * * Set rst = Nothing
* * * * For i = 0 To lstGroupMem.ListCount
* * * * lstGroupMem.Selected(i) = False
* * * * * * Next i
* * * * DoCmd.SetWarnings True
End If
End Sub

Well, you got the count to determine if it existed. *And so you edited
whatever record you were sitting on. *I think you might find it crucial
to finding the record you are editing. *In fact, the Dcount isn't needed
as far as I can see. *For example
* *rst.FindFirst "[Group Member ID]= " & lstGroupMem.Column(0,lbl3ID))
* *If rst.NoMatch then'
* * * * rst.AddNew
* *Else
* * * * rst.Edit
* *Endif
would make more sense.

Bootshttp://www.youtube.com/watch?v=nw7Qg_2JjWk- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -
That's great thanks, the rst.FindFirst bit sorted it out for me!
Feb 16 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.