Updating Recordset Problems

Hi

I'm using a listbox, with multiple select enabled to add new records
in my database. The code is written in Access 2000 and works fine
running on my laptop in that environment; however when I copy across
to the desktop which is running Access 2003, after sometime (it works
Ok initially) I start to get the Error routine triggered saying that
no rows in the listbox are highlighted when I attempt to insert the
new records, even though they clearly are. When I try to omit the
error routine the process stops at the rst.Update command. Once this
happens it won't work properly, regardless of whether I come out of
Access altogether and restart. I've checked the references in VBA and
the only difference is that my laptop is running MS Access Object
Library 9.0 and the desktop- version 11.0. The code is listed below,
grateful for any help here.

Private Sub cmdAddMems_Click()
On Error GoTo Err_cmdAddMems_Click


DoCmd.SetWarnings False

'Declare variables!

Dim dbs As Database
Dim rst As DAO.Recordset
Dim lblID As Variant
Dim Class As Double
Dim stu As Double
Dim strMessage As String, strTitle As String
Dim count As Double

'Check to see if any rows are highlighted.
If lstStudents.ItemsSelected.count = 0 Then

GoTo Err_cmdAddMems_Click

Exit Sub

Else
'Count rows and include in msgbox question
count = lstStudents.ItemsSelected.count
strMessage = "Do you want to add" & " " & count & " " & "group
members?"
strTitle = "Add New Group Members"

'Check to see if user wants to proceed
If MsgBox(strMessage, vbYesNo + vbQuestion, strTitle) = vbNo Then

Exit Sub

Else


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


For Each lblID In lstStudents.ItemsSelected

'Set variables for [Student ID] and [Class Year]
stu = lstStudents.Column(0, lblID)
Class = lstStudents.Column(6, lblID) - 1



'Add new values
rst.AddNew
rst![Student ID] = lstStudents.Column(0, lblID)
rst![Group Code ID] = [Group Code ID]
rst![Surname] = lstStudents.Column(1, lblID)
rst![First Name] = lstStudents.Column(2, lblID)
rst![Class Year] = lstStudents.Column(6, lblID)
rst![Initials] = Nz(lstStudents.Column(3, lblID),
Null)
rst![Form] = lstStudents.Column(7, lblID)
rst![SEN Status] = lstStudents.Column(4, lblID)
rst![Gifted/Talented Status] = lstStudents.Column(5,
lblID)
rst![Gender] = lstStudents.Column(8, lblID)
rst![Current WL Level] = [txtWL]
rst![LY Effort] = Nz(DLookup("[Overall Effort]",
"tblGroupMembers", "[Student ID] =" & stu & "And [Class Year]=" &
Class), Null)
rst![LY Attain] = Nz(DLookup("[Final Attainment]",
"tblGroupMembers", "[Student ID]=" & stu & "And [Class Year]=" &
Class), Null)

rst.Update


Next

Set dbs = Nothing
Set rst = Nothing

[lstMembers].Requery
[cboSEN].Requery
[cboGiftTal].Requery


Dim varItem As Variant
For Each varItem In lstStudents.ItemsSelected
lstStudents.Selected(varItem) = False
Next varItem

DoCmd.SetWarnings True

End If

End If

Exit_cmdAddMems_Click:
Exit Sub

Err_cmdAddMems_Click:
MsgBox "You must choose a student to add!", vbExclamation, "Select
Student"


End Sub

Re: Updating Recordset Problems

On 12 Oct, 11:06, David wrote:
> Hi
>
> I'm using a listbox, with multiple select enabled to add new records
> in my database. The code is written in Access 2000 and works fine
> running on my laptop in that environment; however when I copy across
> to the desktop which is running Access 2003, after sometime (it works
> Ok initially) I start to get the Error routine triggered saying that
> no rows in the listbox are highlighted when I attempt to insert the
> new records, even though they clearly are. When I try to omit the
> error routine the process stops at the rst.Update command. Once this
> happens it won't work properly, regardless of whether I come out of
> Access altogether and restart. I've checked the references in VBA and
> the only difference is that my laptop is running MS Access Object
> Library 9.0 and the desktop- version 11.0. The code is listed below,
> grateful for any help here.
>
> Private Sub cmdAddMems_Click()
> On Error GoTo Err_cmdAddMems_Click
>
> DoCmd.SetWarnings False
>
> 'Declare variables!
>
> Dim dbs As Database
> Dim rst As DAO.Recordset
> Dim lblID As Variant
> Dim Class As Double
> Dim stu As Double
> Dim strMessage As String, strTitle As String
> Dim count As Double
>
> 'Check to see if any rows are highlighted.
> If lstStudents.ItemsSelected.count = 0 Then
>
> GoTo Err_cmdAddMems_Click
>
> Exit Sub
>
> Else
> 'Count rows and include in msgbox question
> count = lstStudents.ItemsSelected.count
> strMessage = "Do you want to add" & " " & count & " " & "group
> members?"
> strTitle = "Add New Group Members"
>
> 'Check to see if user wants to proceed
> If MsgBox(strMessage, vbYesNo + vbQuestion, strTitle) = vbNo Then
>
> Exit Sub
>
> Else
>
> 'Set recordset focus on tblGroupMembers
> Set dbs = CurrentDb
> Set rst = dbs.OpenRecordset("tblGroupMembers")
>
> For Each lblID In lstStudents.ItemsSelected
>
> 'Set variables for [Student ID] and [Class Year]
> stu = lstStudents.Column(0, lblID)
> Class = lstStudents.Column(6, lblID) - 1
>
> 'Add new values
> rst.AddNew
> rst![Student ID] = lstStudents.Column(0, lblID)
> rst![Group Code ID] = [Group Code ID]
> rst![Surname] = lstStudents.Column(1, lblID)
> rst![First Name] = lstStudents.Column(2, lblID)
> rst![Class Year] = lstStudents.Column(6, lblID)
> rst![Initials] = Nz(lstStudents.Column(3, lblID),
> Null)
> rst![Form] = lstStudents.Column(7, lblID)
> rst![SEN Status] = lstStudents.Column(4, lblID)
> rst![Gifted/Talented Status] = lstStudents.Column(5,
> lblID)
> rst![Gender] = lstStudents.Column(8, lblID)
> rst![Current WL Level] = [txtWL]
> rst![LY Effort] = Nz(DLookup("[Overall Effort]",
> "tblGroupMembers", "[Student ID] =" & stu & "And [Class Year]=" &
> Class), Null)
> rst![LY Attain] = Nz(DLookup("[Final Attainment]",
> "tblGroupMembers", "[Student ID]=" & stu & "And [Class Year]=" &
> Class), Null)
>
> rst.Update
>
> Next
>
> Set dbs = Nothing
> Set rst = Nothing
>
> [lstMembers].Requery
> [cboSEN].Requery
> [cboGiftTal].Requery
>
> Dim varItem As Variant
> For Each varItem In lstStudents.ItemsSelected
> lstStudents.Selected(varItem) = False
> Next varItem
>
> DoCmd.SetWarnings True
>
> End If
>
> End If
>
> Exit_cmdAddMems_Click:
> Exit Sub
>
> Err_cmdAddMems_Click:
> MsgBox "You must choose a student to add!", vbExclamation, "Select
> Student"
>
> End Sub

Update! I've worked out that it is only happening for records when
the [Form] field is empty from the underlying recordsource in the
listbox. I've updated the code for the rst.AddNew entry for that
field to include an NZ.....null eventuality but it still causes the
error. I anticipated this for the [Initials] field and the
NZ......null works fine with that. As soon as I manually enter a
[Form] value in the underlying table the problem is resolved. However
this isn't an option for what would be quite a laborious process. Any
further advice greatly received.

thanks

David

Re: Updating Recordset Problems

On 12 Oct, 11:54, David wrote:
> On 12 Oct, 11:06, David wrote:
>
>
>
>
>
> > Hi
>
> > I'm using a listbox, with multiple select enabled to add new records
> > in my database. The code is written in Access 2000 and works fine
> > running on my laptop in that environment; however when I copy across
> > to the desktop which is running Access 2003, after sometime (it works
> > Ok initially) I start to get the Error routine triggered saying that
> > no rows in the listbox are highlighted when I attempt to insert the
> > new records, even though they clearly are. When I try to omit the
> > error routine the process stops at the rst.Update command. Once this
> > happens it won't work properly, regardless of whether I come out of
> > Access altogether and restart. I've checked the references in VBA and
> > the only difference is that my laptop is running MS Access Object
> > Library 9.0 and the desktop- version 11.0. The code is listed below,
> > grateful for any help here.
>
> > Private Sub cmdAddMems_Click()
> > On Error GoTo Err_cmdAddMems_Click
>
> > DoCmd.SetWarnings False
>
> > 'Declare variables!
>
> > Dim dbs As Database
> > Dim rst As DAO.Recordset
> > Dim lblID As Variant
> > Dim Class As Double
> > Dim stu As Double
> > Dim strMessage As String, strTitle As String
> > Dim count As Double
>
> > 'Check to see if any rows are highlighted.
> > If lstStudents.ItemsSelected.count = 0 Then
>
> > GoTo Err_cmdAddMems_Click
>
> > Exit Sub
>
> > Else
> > 'Count rows and include in msgbox question
> > count = lstStudents.ItemsSelected.count
> > strMessage = "Do you want to add" & " " & count & " " & "group
> > members?"
> > strTitle = "Add New Group Members"
>
> > 'Check to see if user wants to proceed
> > If MsgBox(strMessage, vbYesNo + vbQuestion, strTitle) = vbNo Then
>
> > Exit Sub
>
> > Else
>
> > 'Set recordset focus on tblGroupMembers
> > Set dbs = CurrentDb
> > Set rst = dbs.OpenRecordset("tblGroupMembers")
>
> > For Each lblID In lstStudents.ItemsSelected
>
> > 'Set variables for [Student ID] and [Class Year]
> > stu = lstStudents.Column(0, lblID)
> > Class = lstStudents.Column(6, lblID) - 1
>
> > 'Add new values
> > rst.AddNew
> > rst![Student ID] = lstStudents.Column(0, lblID)
> > rst![Group Code ID] = [Group Code ID]
> > rst![Surname] = lstStudents.Column(1, lblID)
> > rst![First Name] = lstStudents.Column(2, lblID)
> > rst![Class Year] = lstStudents.Column(6, lblID)
> > rst![Initials] = Nz(lstStudents.Column(3, lblID),
> > Null)
> > rst![Form] = lstStudents.Column(7, lblID)
> > rst![SEN Status] = lstStudents.Column(4, lblID)
> > rst![Gifted/Talented Status] = lstStudents.Column(5,
> > lblID)
> > rst![Gender] = lstStudents.Column(8, lblID)
> > rst![Current WL Level] = [txtWL]
> > rst![LY Effort] = Nz(DLookup("[Overall Effort]",
> > "tblGroupMembers", "[Student ID] =" & stu & "And [Class Year]=" &
> > Class), Null)
> > rst![LY Attain] = Nz(DLookup("[Final Attainment]",
> > "tblGroupMembers", "[Student ID]=" & stu & "And [Class Year]=" &
> > Class), Null)
>
> > rst.Update
>
> > Next
>
> > Set dbs = Nothing
> > Set rst = Nothing
>
> > [lstMembers].Requery
> > [cboSEN].Requery
> > [cboGiftTal].Requery
>
> > Dim varItem As Variant
> > For Each varItem In lstStudents.ItemsSelected
> > lstStudents.Selected(varItem) = False
> > Next varItem
>
> > DoCmd.SetWarnings True
>
> > End If
>
> > End If
>
> > Exit_cmdAddMems_Click:
> > Exit Sub
>
> > Err_cmdAddMems_Click:
> > MsgBox "You must choose a student to add!", vbExclamation, "Select
> > Student"
>
> > End Sub
>
> Update! I've worked out that it is only happening for records when
> the [Form] field is empty from the underlying recordsource in the
> listbox. I've updated the code for the rst.AddNew entry for that
> field to include an NZ.....null eventuality but it still causes the
> error. I anticipated this for the [Initials] field and the
> NZ......null works fine with that. As soon as I manually enter a
> [Form] value in the underlying table the problem is resolved. However
> this isn't an option for what would be quite a laborious process. Any
> further advice greatly received.
>
> thanks
>
> David- Hide quoted text -
>
> - Show quoted text -

Further update- I needed to change the field value property in the new
importing table to allow a zero-length string. It now seems to be
working fine. Thanks for anyone who read the post.

David