Thanks for posting this!
I've managed to use the same code to try it out as well. It works great for the sample .mdb file, however when using it for a slightly larger data set (eg 36 rows with both input fields as string) my results came back somewhat inconsistent.
After much playing around, i realised that when the query results are in an open window, switching back and forth between windows would cause some sort of a recalculation, so some of my results would be repeated. Even when avoiding this, in some cases it wouldn't convert all of the values into rows (eg Product = A, Orders = "1,2,3" would only result 2 x rows in the new table when 3 would be expected ie for each of the 3 x order ids). Other times it would run it and it would work fine (a quirk of ms access?)
Therefore, I amended the code a little to make it completely VBA driven, it went something like this.
Make sure you have the DAO 3.6 object library (or similar) ticked, in your VBA window look in Tools --> References - should be on by default, but if you get error check there first.
Create a form, then throw a button on there and cancel the wizard that pops up. Right click on the new button, select built event then select code builder. Drop the following code in and modify as needs.
The code above is similar to what I used in the office. For me, it gives consistent results and runs a bit faster - which is great for even larger datasets. It can also be hidden from the user if you need to use this more often from a user form .
I've managed to use the same code to try it out as well. It works great for the sample .mdb file, however when using it for a slightly larger data set (eg 36 rows with both input fields as string) my results came back somewhat inconsistent.
After much playing around, i realised that when the query results are in an open window, switching back and forth between windows would cause some sort of a recalculation, so some of my results would be repeated. Even when avoiding this, in some cases it wouldn't convert all of the values into rows (eg Product = A, Orders = "1,2,3" would only result 2 x rows in the new table when 3 would be expected ie for each of the 3 x order ids). Other times it would run it and it would work fine (a quirk of ms access?)
Therefore, I amended the code a little to make it completely VBA driven, it went something like this.
Make sure you have the DAO 3.6 object library (or similar) ticked, in your VBA window look in Tools --> References - should be on by default, but if you get error check there first.
Create a form, then throw a button on there and cancel the wizard that pops up. Right click on the new button, select built event then select code builder. Drop the following code in and modify as needs.
PHP:
Private Sub Command86_Click()
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim RecID As String
Dim MultiField As String
Dim VarArray As Variant
Dim i As Long
Dim strSQL As String
Set db = CurrentDB
Set rs = DB.OpenRecordset("table name")
Do While Not rs.EOF
RecID = rs.Fields("fieldname1/columname1").value 'the first field'
MultiField = rs.Fields("fieldname2/columname2").value 'the field with multiple values eg "1,2,3,4,5"'
'then re use the code above'
VarArray = Split(MultiField, ",")
For i = 0 To UBound(VarArray)
strSQL = " INSERT INTO tbl2 (ID, NewField) Values ('" & RecID & "', '" & VarArray(i) & "')"
CurrentDb.Execute strSQL
Next i
rs.MoveNext
Loop
Set rs = nothing
End SubThe code above is similar to what I used in the office. For me, it gives consistent results and runs a bit faster - which is great for even larger datasets. It can also be hidden from the user if you need to use this more often from a user form .
