对于 i = Parameter INSERT Multiple Values to table

本文介绍了对于 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