Updating a recordset from form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to add a record to a group with a button from a form, it will
change one field in the record student. I run this from a form which has the
group and student on it. but it comes up with " The expression On Click you
entered as the ecent property setting produced the following error: Member
aleardy exsits in an object module from which this object module derives.
*The expression may not result in the name of the macro, the name of a
user-defined function, or [Event Procedure]. * There may have been an error
evaluating the function, event or macro."

I'm very new to access/vba programming. What am I doing wrong and is there a
better way of doing it?

Mark

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSql As String
Dim GROUPCOD As Long
Dim StudentCID As Long
Dim Message, Title
Option Compare Database

Private Sub Command15_Click()

Set GROUPCOD = Me.GROUPCOD.Value


Message = "Enter the CID of the Student to add to " & GROUPCOD
Title = "Add student to group"
StudentCID = InputBox(Message, Title)

Set cnn = New ADODB.Connection
Set cnn = CurrentProject.Connection

strSql = "select CID, SURNAME from STUDENT where CID = " & StudentCID


Set rst = New ADODB.Recordset
With rst
.Open Source:=strSql, _
ActiveConnection:=cnn, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic

Do While Not .EOF
If .Fields("cid").Value = StudentCID Then
.Fields(Group).Value = GROUPCOD
.Update
.MoveNext
End If
Loop
.Close
End With

cnn.Close
Set rst = Nothing
Set cnn = Nothing

End Sub
 
I've fixed the problem below, but I now get a different one, it's

Run-time error '-2147217912(80040e07)':
Data type mismatch in criteria expression

And when I debug it highlights the .Open source upto adLockOptimistic
with the yellow arrow on the LockType line.

Any help?
 
It would appear that CID is not a numeric field and your SQL sting is coded
as if it were:

strSql = "select CID, SURNAME from STUDENT where CID = " & StudentCID

If CID is a text field, you need text delimiters for the SQL

strSql = "select CID, SURNAME from STUDENT where CID = '" & StudentCID & "'"
--
Dave Hargis, Microsoft Access MVP


Markm-s said:
I've fixed the problem below, but I now get a different one, it's

Run-time error '-2147217912(80040e07)':
Data type mismatch in criteria expression

And when I debug it highlights the .Open source upto adLockOptimistic
with the yellow arrow on the LockType line.

Any help?

Markm-s said:
I'm trying to add a record to a group with a button from a form, it will
change one field in the record student. I run this from a form which has the
group and student on it. but it comes up with " The expression On Click you
entered as the ecent property setting produced the following error: Member
aleardy exsits in an object module from which this object module derives.
*The expression may not result in the name of the macro, the name of a
user-defined function, or [Event Procedure]. * There may have been an error
evaluating the function, event or macro."

I'm very new to access/vba programming. What am I doing wrong and is there a
better way of doing it?

Mark

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSql As String
Dim GROUPCOD As Long
Dim StudentCID As Long
Dim Message, Title
Option Compare Database

Private Sub Command15_Click()

Set GROUPCOD = Me.GROUPCOD.Value


Message = "Enter the CID of the Student to add to " & GROUPCOD
Title = "Add student to group"
StudentCID = InputBox(Message, Title)

Set cnn = New ADODB.Connection
Set cnn = CurrentProject.Connection

strSql = "select CID, SURNAME from STUDENT where CID = " & StudentCID


Set rst = New ADODB.Recordset
With rst
.Open Source:=strSql, _
ActiveConnection:=cnn, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic

Do While Not .EOF
If .Fields("cid").Value = StudentCID Then
.Fields(Group).Value = GROUPCOD
.Update
.MoveNext
End If
Loop
.Close
End With

cnn.Close
Set rst = Nothing
Set cnn = Nothing

End Sub
Click to expand...
Click to expand...
 
Back
Top