Access, populate subform with combobox

Question

As I am such a beginner at using Access/VBA I couldn't tell if my query had already been addressed in another forum, so I apologise if it had.

I am trying to populate a subform using the values from the combo boxes on the main form. I am hoping that when I select a student in the combo box and then a following module in the other combobox and click 'Add Module' it will add the module to the subform and show all modules chosen for that student in the bottom subform. I followed a video to obtain the code I currently have and this is displayed below along with a screenshot of my current layout (excuse design features these haven't even begun to be considered yet).

Please note: The code is definitely incorrect for the docmd, I am unsure what to use in order to populate the subform so it will display the selected student and all modules chosen.

When you open form it looks like this, ideally how I want it to look when records are added for specific students

How it looks after I click 'Add module' goes to new record and doesn't display full list unless I click back on arrows.

Code -

'combo box for StudentID updates all other student detail text boxes.
Private Sub studentIDcombo_AfterUpdate()
    programmetb = DLookup("ProgrammeID", "tblStudent", "[StudentID]=studentidcombo")
    firstnametb = DLookup("FirstName", "tblStudent", "[StudentID]=studentidcombo")
    surnametb = DLookup("Surname", "tblStudent", "[StudentID]=studentidcombo")
    StudentID1 = studentIDcombo
End Sub

'combo box for ModuleCode updates all other module detail text boxes.
Private Sub modulecodecombo_AfterUpdate()
    modulenametb = DLookup("ModuleName", "tblModule", "[ModuleCode]=modulecodecombo")
    creditstb = DLookup("Credits", "tblModule", "[ModuleCode]=modulecodecombo")
    semester1tb = DLookup("Semester_1", "tblModule", "[ModuleCode]=modulecodecombo")
    semester2tb = DLookup("Semester_2", "tblModule", "[ModuleCode]=modulecodecombo")
    prereqtb = DLookup("Pre_requisites", "tblModule", "[ModuleCode]=modulecodecombo")
End Sub

Private Sub AddModuleBut_Click()
    'Verification that studentID is selected.
    If IsNull(studentIDcombo) Then
        MsgBox "Please select student", , "Required"
        studentIDcombo.SetFocus
        Exit Sub
    End If
    'Verification that modulecode is selected.
    If IsNull(modulecodecombo) Then
        MsgBox "Please select a course", , "Required"
        modulecodecombo.SetFocus
        Exit Sub
    'Else create a record in the subform using the combo box data.
    Else
        DoCmd.GoToRecord , , acNewRec
        StudentID1 = studentIDcombo
        modulecodecombo.SetFocus
    End If
End Sub

Extra information:

After I am able to add modules for specific students I will begin coding conditions, such as you can only choose module A if you have done module B previously etc etc. Will this be possible to achieve with this current layout?

I will be making a student form to add students to and then they will add modules on this form.

Thanks in advance for any help and I hope this makes sense!

Best Regards, <3

Looks like you just need an append query to pull your control values, insert into the table, and then requery your subform. Everything you mention is possible in this layout. — MoondogsMaDawg

SunKnight0 SunKnight0 · Accepted Answer · 2017-01-04T19:45:56

First of all, consider re-designing your tables to use proper primary keys (auto-increment integers). Having said that what you are missing is the command to create the new student-module record. It would look something like this:

DoCmd.RunSQL "INSERT INTO [studentmodulelinktable] (ProgrammeID,ModuleName) VALUES (" & studentIDcombo & ",'" & modulecodecombo & "')"

The above assumes studentIDcombo has numerical values and modulecodecombo has text values. The difference is whether you use the single quotes or not.

Access, populate subform with combobox

1 Answers