vba 在访问表中添加或更改记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12640197/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Adding or changing record in access table
提问by Arlen Beiler
I am making a rather simple inventory tracking database. And I want to retrieve the record by ID, and add or remove the specified number to the amount. If it doesn't exist I want to add it. Is it even possible to do this without binding to a table?
我正在制作一个相当简单的库存跟踪数据库。我想通过 ID 检索记录，并在金额中添加或删除指定的数字。如果它不存在，我想添加它。甚至可以在不绑定到表的情况下执行此操作吗？
采纳答案by HansUp
Sounds like you have a candidate IDvalue. Maybe it's contained in a numeric variable named MyID. And you have another numeric value, MyAmtChange, which is to be added to the value in a field named amountin your table for the row where the IDfield value matches MyID.
听起来你有一个候选ID值。也许它包含在一个名为MyID. 并且您还有另一个数值MyAmtChange，它将被添加到amount您的表中为ID字段值匹配的行命名的字段中的值中MyID。
A complication is there may be no row in your table whose IDvalue matches MyID. In that case, you need to add a row for it.
一个复杂的问题是您的表中可能没有ID值匹配的行MyID。在这种情况下，您需要为其添加一行。
If that's correct, INSERTa row for MyIDwhen one doesn't exist. Then you can simply UPDATEthe amountin the row which matches MyID.
如果这是正确的，INSERT那么一行MyID不存在。然后，你可以简单地UPDATE将amount其匹配的行MyID。
Dim strInsert As String
Dim strUpdate As String
Dim db As DAO.Database
Set db = CurrentDb
If DCount("*", "YourTableNameHere", "ID = " & MyID) = 0 Then
strInsert = "INSERT INTO YourTableNameHere (ID)" & vbCrLf & _
"VALUES (" & MyID & ");"
Debug.Print strInsert
db.Execute strInsert, dbFailOnError
End If
strUpdate = "UPDATE YourTableNameHere" & vbCrLf & _
"SET amount = Nz(amount, 0) + " & MyAmtChange & vbCrLf & _
"WHERE ID = " & MyID & ";"
Debug.Print strUpdate
db.Execute strUpdate, dbFailOnError
Set db = Nothing
If this guess is reasonably close, add an error handler block to that code to deal with any issues surfaced by dbFailOnError... or any other errors.
如果此猜测相当接近，请向该代码添加一个错误处理程序块，以处理由dbFailOnError... 或任何其他错误引起的任何问题。
回答by Ould Abba
I don't know what do you want exactly, but this code show how to manipulate data with VB-Access.
我不知道你到底想要什么，但这段代码展示了如何使用 VB-Access 操作数据。
Sub fnStudent()
On Error GoTo insertError
Dim studentQuery As String
'INSERTING INTO TABLE
studentQuery = "INSERT INTO Students values ('10','YAHYA','02/10/2012')"
CurrentDb.Execute studentQuery, dbFailOnError
'UPDATING
studentQuery = "UPDATE Students Set name='YAHYA OULD ABBA' WHERE stdID='10'"
CurrentDb.Execute studentQuery, dbFailOnError
'LISTING VALUES
Dim studentsRS As Recordset
Set studentsRS = CurrentDb.OpenRecordset("SELECT * FROM Students WHERE upper(name) like '%YAHYA%';")
Do While Not studentsRS.EOF
MsgBox "ID : " & studentsRS.Fields(0) & "Name : " & studentsRS.Fields(1) & "Birth Date : " & studentsRS.Fields(2)
studentsRS.MoveNext
Loop
'DELETING
studentQuery = "DELETE FROM Students WHERE stdID='10'"
CurrentDb.Execute studentQuery, dbFailOnError
Exit Sub 'exit if there was no error
'UPDATE:
errorHandler:
If Err.Number = 3022 Then
MsgBox "Can't have duplicate key; index changes were unsuccessful", vbMsgBoxRtlReading + vbCritical, "Error " & Err.Number
Else : MsgBox "Error" & vbCrLf & Err.Description, vbMsgBoxRtlReading + vbCritical, "Error " & Err.Number
End If
End Sub
here you find a list of vbaerrors http://www.halfile.com/vb.html
在这里您可以找到vba错误列表http://www.halfile.com/vb.html
