用于更新表的vba代码 [英] vba code to update table

问题描述

很棒的人， 

hi wonderful people, 

我再次尝试从访问表单中更新现有记录。我使用单个按钮来实现这个"添加按钮"。我已将其设置为将标题更改为"更新"一旦选择了编辑按钮。  我进一步使用
一个IF THEN语句来检查它是否是用户试图估算的UPDATE或NEW RECORD。添加新记录工作得很好，但是当我尝试更新现有记录时收到错误消息。

i am again trying to update an existing record form an access form. I am using a single button to achieve this "add button". i have set it to change the caption to "UPDATE" once the the Edit Button is selected.  i have further used an IF THEN statement to check if it is an UPDATE or a NEW RECORD the user is trying to impute. The ADD NEW RECORD works just fine, but i am getting an error message when i try to update an existing record.

我已将PK存储在Edit_click程序下的标签中"我.txtBoxID.Tag = .Fields（" customer_id"）"。这就是我的ADD按钮的样子..........

I have stored the PK in a tag under the Edit_click procedure "Me.txtBoxID.Tag = .Fields("customer_id")". This is what my ADD button looks like..........

Private Sub cmdAddRecord_Click（）

  Dim strQury As String

  Dim strUpdate As String



如果txtboxFName.Value =""然后



结束



结束如果



 如果是Me.txtBoxID.Tag& "" =""然后



        strQury =" INSERT INTO customer（  customer_fname，customer_lname，"& _

    " customer_phoneno，customer_address，customer_email）VALUES" &安培; _

        "（txtboxFName.value，txtboxLName.value，txtboxTelephone.value，txtboxAddress.value，txtboxEmail.value）;"
$


      DoCmd.RunSQL（strQury）



否则



  strUpdate =" UPDATE customer" &安培; _

                   " SET customer_id =" &安培; Me.txtBoxID& _

                  "，customer_fname ='" &安培; Me.txtboxFName& """ &安培; _

                  "，customer_lname ='" &安培; Me.txtboxLName& """ &安培; _

                  "，customer_phoneno ='" &安培; Me.txtboxTelephone& """ &安培; _

                  "，customer_address ='" &安培; Me.txtboxTelephone& """ &安培; _

                  "，customer_email ='" &安培; Me.txtboxEmail& """ &安培; _

                  " WHERE customer_id =" &安培; Me.txtBoxID.Tag



      DoCmd.RunSQL（strUpdate）

   结束如果








$ b



$
Me.Form.Refresh



$
cmdClear_Click



CurrentDb.Close



$
End Sub

Private Sub cmdAddRecord_Click()
  Dim strQury As String
  Dim strUpdate As String

If txtboxFName.Value = "" Then

End

End If

 If Me.txtBoxID.Tag & "" = "" Then

        strQury = "INSERT INTO customer (  customer_fname, customer_lname," & _
        " customer_phoneno, customer_address, customer_email)VALUES" & _
        "( txtboxFName.value, txtboxLName.value, txtboxTelephone.value, txtboxAddress.value, txtboxEmail.value);"

     DoCmd.RunSQL (strQury)

Else

  strUpdate = "UPDATE customer" & _
                   " SET customer_id = " & Me.txtBoxID & _
                  ", customer_fname = '" & Me.txtboxFName & "'" & _
                  ", customer_lname = '" & Me.txtboxLName & "'" & _
                  ", customer_phoneno = '" & Me.txtboxTelephone & "'" & _
                  ", customer_address = '" & Me.txtboxTelephone & "'" & _
                  ", customer_email = '" & Me.txtboxEmail & "'" & _
                  " WHERE customer_id =" & Me.txtBoxID.Tag

     DoCmd.RunSQL (strUpdate)
    End If








Me.Form.Refresh


cmdClear_Click

CurrentDb.Close


End Sub

然而，这会引发错误消息。我认为这与我处理主键的方式有关........请帮忙吗？  欢呼

However, this throws up an error message. I think it is something that has got to do with the way i handled the primary key........ Pls can any one help out?  cheers

Galli Pedro IT Apprentice。年轻人应该成长企业

Galli Pedro IT Apprentice. The Young Shall Grow Enterprise

推荐答案

您是否正在尝试更新主键？你也忘了连接值了。试试这个，

You are trying to UPDATE a Primary Key? Also you forgot to concatenate values. Try this,

Private Sub cmdAddRecord_Click()
	Dim strSQL As String
	
	If Len(Me.txtboxFName & vbNullString) = 0 Then Exit Sub

	If Len(Me.txtBoxID.Tag & vbNullString) = 0 Then
		strSQL = "INSERT INTO customer (customer_fname, customer_lname," & _
				  " customer_phoneno, customer_address, customer_email) VALUES " & _
				  "('" & Me.txtboxFName & "', '" & Me.txtboxLName & "', '" & Me.txtboxTelephone & _
				  "', '" & Me.txtboxAddress & "', '" & Me.txtboxEmail & "');"
	Else
		strSQL = "UPDATE customer" & _
					" SET customer_fname = '" & Me.txtboxFName & "'" & _
					", customer_lname = '" & Me.txtboxLName & "'" & _
					", customer_phoneno = '" & Me.txtboxTelephone & "'" & _
					", customer_address = '" & Me.txtboxTelephone & "'" & _
					", customer_email = '" & Me.txtboxEmail & "'" & _
					" WHERE customer_id =" & Me.txtBoxID.Tag
	End If
	CurrentDb.Execute strSQL
	
	Me.Form.Refresh
	cmdClear_Click
End Sub

这篇关于用于更新表的vba代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！