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

Question

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.

Examine the values that code attempts to insert into your table. Create a new query in the query designer, switch to SQL View, paste in this query, and run it: SELECT 'me.lstStudent' AS StudentID, 'me.cboCourseID' AS CourseID,'me.txtCourseNoteID' AS NoteID — HansUp
– HansUp, Commented Oct 10, 2015 at 14:35

HansUp · Accepted Answer · 2015-10-10 16:54:28Z

2

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.

edited Oct 10, 2015 at 16:54

answered Oct 10, 2015 at 16:38

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

EnglischAlternativ Over a year ago

Thanks very much HansUp, that is now doing exactly what I need it too! I greatly appreciate the explanation too. The reason the form is not bound to the data set is that the data is being pulled from multiple tables to populate drop down boxes and I need that info to then be written to another separate table. I'm unsure if this is the most direct way to my goal but it's my first try at doing anything like this and I am learning as I go. Again, thanks for your help, and thanks to @HarveyFrench too.

Community · Accepted Answer · 2017-05-23 12:29:55Z

0

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 "');"

1 Comment

Excel Hero Over a year ago

I think I just received an invite from you to join on LinkedIn. The problem is that it came through to an email address that is associated with the wrong profile for me on LinkedIn. Here is my correct LinkedIn profile: linkedin.com/profile/…

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related