I am a SQL Server developer who is just starting out in Access. I need to build an app with a many-to-many join (see description below). I can create the tables, primary keys, relationships, all that. And I can write the SQL and VB Script needed. But surely in Access I should be able to make a (bound?) form that automatically provides edit capability for joins. In the Form Wizard you can make one that just edits a table and requires no SQL or VBA script. But how about a relationship?
Here's the schema:
Student Table -
Program Table -
StudentToProgram Table -
I want a form with 2 list boxs (and some other control):
lstStudents bound to Student!SSNumber
lstPrograms bound to Program!Name
When I select a student SSNumber, I want another list box or something to display all the programs the student is in. Then I want to be able to add and delete programs from that list and have that reflected in the StudentToProgram table.
As I say, I have already written this in SQL and VBA. But it is alot of work and doesn't take advantage of Access's capabilities. And this is such a basic DB operation. Thanks in advance to anyone who wants to point me in the right direction.