CODEWITHSUNDEEP
sqlformsms-accessinsertvba
EnglischAlternativ
EnglischAlternativ

Reputation: 33

How do I update a table with a form in access using VBA or a Macro?

I am doing my best to build my first database, but I have come up against a problem I just cannot find an answer to. I am a complete newbie in this forum and writing any sort of code so please be gentle.

I am trying to create a new record in a table when a student's name is double clicked inside a list box which is inside a form.

List box where I want to take first (StudentID) column value from = lstStudent

Combo box where I want to take the second (CourseID) column value from: cboCourseID

Text box where I want to take third (NoteID) column value from = txtCourseNoteID

The new record is being created in the desired table and there are no incorrect code errors but there are no values being carried across to the fields. The autonumber is being created (AttendanceID) but the other columns are blank. Here is my code:

  Private Sub lstStudent_DblClick(Cancel As Integer)
     CurrentDb.Execute "INSERT INTO tblAttendance (StudentID, CourseID, NoteID) VALUES ('me.lstStudent','me.cboCourseID','me.txtCourseNoteID')" 
End Sub

The fields are populated, so this isn't the issue. The formatting is correct for the target fields and I can't think of anything else in my way.

Upvotes: 3

Views: 784

Answers (2)

HansUp
HansUp

Reputation: 97101

The new record is being created in the desired table and there are no incorrect code errors but there are no values being carried across to the fields. The autonumber is being created (AttendanceID) but the other columns are blank.

With this INSERT statement, you're supplying text values for those 3 fields in the new row ... 

INSERT INTO tblAttendance (StudentID, CourseID, NoteID)
VALUES ('me.lstStudent','me.cboCourseID','me.txtCourseNoteID')

However StudentID, CourseID, and NoteID are numeric fields, so will not accept those text values. In that situation, there is nothing the db engine can insert. You still get a new row added (with the new autonumber value in AttendanceID), but those other fields are empty.

If you include the dbFailOnError option with .Execute, Access will notify you about the problem (error #3464, "Data type mismatch in criteria expression") and abort the insert --- a new row will not be added ...

CurrentDb.Execute "INSERT INTO tblAttendance (StudentID, CourseID, NoteID) VALUES ('me.lstStudent','me.cboCourseID','me.txtCourseNoteID')", dbFailOnError

Use an approach similar to what @HarveyFrench suggested, but eliminate the single quotes from the VALUES list ...

Dim strInsert As String
strInsert = "INSERT INTO tblAttendance (StudentID, CourseID, NoteID)" & vbCrLf & _
    "VALUES (" & Me.lstStudent.Value & "," & Me.cboCourseID.Value & "," & _
    Me.txtCourseNoteID.Value & ");"
Debug.Print strInsert ' <- view this in Immediate window; Ctl+g will take you there
CurrentDb.Execute strInsert, dbFailOnError

Based on what you've told us, I suspect that will work, but I'm not sure it's the best way to reach your goal. You could open a DAO.Recordset and add the new row there. Or if you can use a bound form, Access would automatically take care of storing a new row ... without the need to write code.

Upvotes: 2

HarveyFrench
HarveyFrench

Reputation: 4568

Change this

      CurrentDb.Execute "INSERT INTO tblAttendance (StudentID, CourseID, NoteID) VALUES ('me.lstStudent','me.cboCourseID','me.txtCourseNoteID')"

to be this

 CurrentDb.Execute "INSERT INTO tblAttendance (StudentID, CourseID, NoteID) _ 
 VALUES ('" & me.lstStudent & "','" & me.cboCourseID & "','" & me.txtCourseNoteID "');"

See also here for useful info

Upvotes: 0

Related Questions