ms access - For i = Parameter INSERT Multiple Values to table

Question

Good afternoon,

I've searched my but off in order to find a solution for my problem.

I'm trying to get access to insert multiple rows to my database, but al the rows are coming with different values.

For example: I've got two people who scored a goal so the manager will put in the form two people who scored with times etc.

The method I'm using right now works but it's doing the same thing up to 10 times.

This is what I got now.

Select Case LCounter
    Case 1
        dbs.Execute " INSERT INTO tblMatchPlayer " _
            & "(MatchID, PlayerID, SubstituteID, PositionID, Surname, ScoreTime, RedCards, YellowCards, Substitude, Penalty, OwnGoal, Assist) VALUES " _
            & "(" & Me.MatchID & ", '', '', '', '" & Me.cmScoreName1 & "', " & Me.tbScoreTime1 & ", '', '', '', " & Me.cbPenalty1 & ", " & Me.cbOwnGoal1 & ", '" & Me.cmAssist1 & "');"

Up to Case 10

What I've tried to do is making a loop.

If Location.Value = "Thuis" Then InsertScore = ResultHomeTeam.Value Else InsertScore = ResultAwayTeam.Value

For i = 1 To InsertScore
   QueryInsert = " INSERT INTO tblMatchPlayer " _
    & "(MatchID, PlayerID, SubstituteID, PositionID, Surname, ScoreTime, RedCards, YellowCards, Substitude, Penalty, OwnGoal, Assist) VALUES " _
    & "(" & Me.MatchID & ", '', '', '', '" & Me.cmScoreName & i & "', " & Me.tbScoreTime & i & ", '', '', '', " & Me.cbPenalty & i & ", " & Me.cbOwnGoal & i & ", '" & Me.cmAssist & i & "');"
   Debug.Print QueryInsert
   dbs.Execute QueryInsert
Next

My thought where this would do the same thing but only in stead of a Select Case I'm using a For Loop with the " & " as the value to use when 1 player has scored or 2 players or 10 players have.

But this isn't working.

Any ideas on how I can make this work without using the 10 cases?

With kind regards,

Patrick

Answer 1

As HansUp wrote, in addition to the normal way of addressing form controls: Me!myTextBox1, you can also use this syntax: Me("myTextBox1"), and with this you can do string concatenations and loops: Me("myTextBox" & i).

Another thing: your INSERT statement is vulnerable to SQL injection or at least errors, imagine a surname O'Neil.

I suggest a more secure and better readable variant using DAO:

Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim i As Long, InsertScore As Long

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tblMatchPlayer", dbOpenDynaset, dbAppendOnly)

For i = 1 To InsertScore
    With rs
        ' add a record
        .AddNew
        !MatchID = Me!MatchID
        !Surname = Me("cmScoreName" & i)
        !ScoreTime = Me("tbScoreTime" & i)
        ' etc. for all fields you want to fill
        ' ...
        ' save the record
        .Update
    End With
Next i

rs.Close