对于 i = Parameter 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

我试图做的是制作一个循环.

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

我认为这会做同样的事情但只是代替 Select Case 我正在使用带有"& "的 For 循环作为当 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，您还可以使用以下语法: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 = Parameter INSERT Multiple Values to table的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！