对于i =参数INSERT Multiple Values to table [英] For i = Parameter INSERT Multiple Values to table

问题描述

下午好，

我已经搜索过，但是为了找到解决我问题的方法。

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.

我现在使用的方法有效但是它最多可以做10次相同的事情。

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

这就是我现在得到的。

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 & "');"

第10种情况

我做什么

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

我以为这样做会做同样的事情，但只是代替Select Case，我使用带有&的For Loop作为当1个球员得分或2个球员或10个球员得分时使用的值。

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.

但是这不起作用。

关于不使用10种情况下如何进行这项工作的任何想法？

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

出于问候，

帕特里克

推荐答案

正如HansUp所写，除了解决表单控件的常规方法： Me！myTextBox1 ，您还可以使用以下语法： code> Me（ myTextBox1），并可以执行字符串连接和循环： Me（ myTextBox& i）。

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).

另一件事：您的INSERT语句容易受到SQL注入或至少有错误的影响，想象一个姓 O'Neil 。

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

我建议使用DAO进行更安全，更易读的变体：

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

这篇关于对于i =参数INSERT Multiple Values to table的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！