List Box in Access with linked MySQL table

Question

I have an MSaccess database with 2 tables (process and data_type). Each process can contain 0-many types of data_type. Within access, I have a form that displays a process with a list-box, (see pic), that has all of the data_types. The user can then check the data_types that each process has.

We are in the process of migrating the data from access to MySQL. As part of that I've created a process2data table that links the process and data_type table. Unfortunately, I don't know how to create a form in Access (we are still, for now, using that as our display engine) that accomplishes the same thing with the online data.

You can download what I'm trying to do here:

(For simplicity, I've put copies of the online data tables that are in mySQL as offline tables in Access.)

Answer 1

The multi-valued fields works only on MS-Access databases, to emulate we need to add VBA code and a table to store the values.

1. Create intermediate table, in this case I have created [ProcessList_DataType], the [checked] field will be used to include the data type, see picture:

2. Create a form for ProcessList and a subform for [Datatypes] and [ProcessList_DataType] combined tables. Don't forget configure the link master fields.

3. Add code to check the referenced values are present on intermediate table:

   Private Sub Form_Current()
       On Error GoTo ErrExit
       If Not IsNull(Me!ProcessID) Then
           sql = "INSERT INTO ProcessList_DataType(ProcessList,DataType) " & _
                 "SELECT " & Me!ProcessID & ",datatype_id " & _
                 "FROM DataTypes WHERE datatype_id not in " & _
                   "(SELECT datatype_id from FilterQuery " & _
                   " WHERE ProcessList = " & Me!ProcessID & " )"
           Set db = CurrentDb()
           db.Execute sql
           cnt = db.RecordsAffected
           If cnt > 0 Then
               NeedRefresh = True
           End If
       End If
   ErrExit:
   End Sub
   

4. Enjoy!