Using VBA to advance to the next record in a DATA ENTRY form

[rghollenbeck]

Access 2007 Windows 7 64 bit

BACKGROUND:
I'm building a "grades" program for an English professor at our local community college.

The table tblStudents has two ID numbers, one of them, the key field, is an AutoNumber and I call it intStudentNumber and the other is their ID at school and has nothing to do with their ID for the sake of this program, but it is useful for reporting the grades at the end of each semester. I call that intRCC_ID. The table also has also has strFirstName and strLastName

The table tblCourses has only three fields: intCourseCode (key), strCourseDescription, and StartDate (date/time datatype).

There are obviously many students in any course and each student may take many courses. To avoid a many-to-many relationship, I created a third table with a double key: tblStudentsInCourses. This table only has two fields and they are both keys: intStudentNumber and intCourseCode. The other two tables both have a one-to-many relationship with tblStudentsInCourses but no direct relationship with each other.

All relationships are enforcing referential integrity and are cascading updates and deletions.

I have a total of six tables, but this should be sufficient table background to ask my question.



Now I need to explain the forms involved in my question. The first form opens automatically with the database. It asks the user to select an existing course or create a new one. Once a course is selected, other options become available, such as viewing the roster or adding a student to the course. I have a form called frmAddStudent which is in Data Entry mode and uses tblStudents as its data source.

I need to save the student to the tblStudents before I can add them to to the tblStudentsInCourses table. If I use a command button the automatically advances to the next record, that will be done. No problem. But I want to also add this new student to the course (tblStudentsInCourses). I could do that programmatically with an INSERT INTO SQL statement, but the button's click event is tied to an embedded macro (advancing to the next record) instead of VBA code.


*-*-*-*-*

QUESTION:
If I avoid the macro completely and just do everything in code, is there a way to force Access to advance to the next record (thus saving the record) using only VBA code? That way I will have a record to add to the tblStudentsInCourses table. I will have to store the AutoNumber in a variable first so I can use it in the INSERT INTO query, but I already know how to do most of that. It is just using VBA for record navigation that I'm not familiar with.

I want one click of the button to both add the student to tblStudents and tblStudentsInCourses. I will already know the course code because that first form is still open and is displaying the course code. I have already written some code that that reads from that combo box on the first form. I already have that data handy for the INSERT INTO query.

*-*-*-*-*

I have attempted to be excessively unambiguous in explaining what I am trying to do. I am sorry if I was a little too wordy.

Thanks.

[Dan]

recordset.edit...do something here...recordset.update...recordset.movenext. Read the documentation or buy a good VBA book.