Hi Bashar,
Why the two dots in front of each statement in the With / End With? That is
clearly not correct. It should be one dot only.
With rcdstudentname
.AddNew
.Fields("studentname") = [Forms]![frmdegree]![studentnametextbox]
.Fields("studentid") = [Forms]![frmdegree]![stid]
.Update
End With
Also, you should close the recordset and set it and the database variable
equal to Nothing:
rcdstudentname.Close: Set rcdstudentname = Nothing
Set dbLawTrack = Nothing
End Sub
Actually, you are making this a lot more complicated than need be. Since you
are adding a new record, why not just run an append query, something like
this (uncomment the Debug.Print statement to see the SQL statement for the
append query written to the Immediate Window):
Private Sub studentnametextbox_AfterUpdate()
On Error GoTo ProcError
Dim intResponse As Integer
' Debug.Print "INSERT Into tbldegree " _
& "(stid, studentname) " _
& "VALUES (" & stID & ", '" & _
Replace(studentnametextbox, "'", "''") & "');"
intResponse = MsgBox("Do you want to add this student?", _
vbInformation + vbYesNoCancel, "Degree's Earned...")
If intResponse = vbYes Then
CurrentDb.Execute "INSERT Into tbldegree " _
& "(stid, studentname) " _
& "VALUES (" & stID & ", '" & _
Replace(studentnametextbox, "'", "''") & "');"
End If
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure studentnametextbox_AfterUpdate..."
Resume ExitProc
End Sub
Assumes:
1.) Access 2000 or higher, since we are using the built-in Replace function,
to handle any names with single quotes, ie. O'Malley becomes O''Malley. You
need to double up any single quotes in names.
2.) stid is a numeric student ID. If it is a text field, then you will need
to wrap it in single quotes, similar to the way that the student name is
wrapped in single quotes.
No need to open a recordset. However, I'm a bit curious about in that you
seem to be grabbing the student's name from a combo box (studentnametextbox),
but the stid (student ID?) from another control, presumably a text box.
Normally, you would include both values in the row source for the combo box.
This way, you could just pick a name from the list and insert both the stid
and the student's name into your tblDegree.
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
bashar said:
hi
i creat database for student in form of degree "frmdegree" i was want add
student name and student ID from student tabl to degree table by using
unbound combobox.
i but this code in after update
Private Sub studentnametextbox_AfterUpdate()
Dim dbLawTrack As DAO.Database
Dim rcdstudentname As DAO.Recordset
Set dbLawTrack = CurrentDb
Set rcdstudentname = _
dbLawTrack.OpenRecordset("tbldegree")
With rcdstudentname
..AddNew
..Fields("studentname") = [Forms]![frmdegree]![studentnametextbox]
..Fields("studentid") = [Forms]![frmdegree]![stid]
..Update
End With
End Sub
the result in table there is no nams and ID but other info. like degree is
found .
pleas hhhhhhhhhhhhhhhhhhhhhelp
