INSERT INTO - 错误，但允许输入到表中 [英] INSERT INTO - errors, but allows input into table

问题描述

由于我看不到的原因，我收到以下错误消息:

For reasons I cannot see I get the following error message:

编译错误:未找到方法或数据成员

当我使用以下内容时:

Private Sub cmd_Add_Click()

Dim strSQL As String

strSQL = " INSERT INTO BERTHAGE " _
& "(BOAT, LOCATION, BERTH_WEEK, BERTH_YEAR, BERTHED) VALUES " _
    & Me.Add_Boat & "','" _
    & Me.LOCATION & "','" _
    & Me.txt_week & "','" _
    & Me.txt_year & "','" _
    & Me.In_Port & "');"

cmd_Clear_Click

End Sub

一旦我单击确定"并使用刷新按钮，该条目就会被放入数据库中，但是每次我执行一个条目时，我都必须进入相同的过程.

Once I click OK and use the refresh button the entry is put into the database, but each time I do an entry I have to go to the same process.

我想弄清楚缺少什么方法或数据?

I would like to figure out what method or data is missing?

我应该补充一点，这张表 (Berth_ID) 上有一个 outnumber 主键字段，每次我使用 cmd_Add 按钮时，都会为新记录创建一个新的 ID 号.这包括为触发错误的新记录创建一个新的 ID 号.

I should add that there is an outnumber primary key field on this table (Berth_ID), and each time I use the cmd_Add button a new ID number is created for the new record. This includes creating a new ID number for the new record that triggers the error.

这是与此表单相关的所有 VBA

Here is all the VBA associated with this form

Private Sub Form_Load()
DoCmd.RunCommand acCmdRecordsGoToLast
End Sub

Private Sub LOCATION_Change()
    Me.txt_Cur_Flo = Me.LOCATION.Column(1)
    Me.txt_Cur_Doc = Me.LOCATION.Column(2)
    Me.txt_Cur_Ori = Me.LOCATION.Column(3)
End Sub

Private Sub cmd_Add_Click()

Dim strSQL As String

strSQL = " INSERT INTO BERTHAGE " _
& "(BOAT, LOCATION, BERTH_WEEK, BERTH_YEAR, BERTHED) VALUES " _
    & Me.Add_Boat & "','" _
    & Me.LOCATION & "','" _
    & Me.txt_week & "','" _
    & Me.txt_year & "','" _
    & Me.In_Port & "');"

cmd_Clear_Click

End Sub

Private Sub cmd_Clear_Click()
Me.Add_Boat = ""
Me.LOCATION = ""
Me.txt_Cur_Flo = ""
Me.txt_Cur_Doc = ""
Me.txt_Cur_Ori = ""  
Me.Add_Boat.SetFocus
End Sub

Private Sub cmd_Close_Click()
DoCmd.Close
End Sub

推荐答案

考虑 参数化 而不是 SQL 与 VBA 变量混合的字符串连接.由于缺少引号，编译器尝试引用列名而不是其文字值.相反，请考虑使用 Access SQL 支持的定义类型的参数化 QueryDefs.注意下面，SQL 和 VBA 是完全分开的.

Consider the best practice of parameterization and not string concatenation of SQL mixed with VBA variables. Due to missing quotes, the compiler attempts to reference a column name and not its literal value. Instead, consider parameterization with defined types which is supported with Access SQL using QueryDefs. Notice below, SQL and VBA are complete separate.

SQL (另存为存储查询)

PARAMETERS prmBoat TEXT, prmLoc INT, prmBerthed INT;
INSERT INTO BERTHAGE (BOAT, LOCATION, BERTHED)
VALUES(prmBoat, prmLoc, prmBerthed)

VBA

Dim db As Database
Dim qdef As QueryDef
Dim strSQL As String

Set db = CurrentDb
Set qdef = db.QueryDefs("mySavedParamQuery")

' BIND PARAM VALUES
qdef!prmBoat = Me.Add_Boat
qdef!prmLoc = Me.LOCATION
qdef!prmBerthed = Me.In_Port

' EXECUTE ACTION QUERY
qdef.Execute

Set qdef = Nothing
Set db = Nothing

<小时>

更好的是，使用完整的表单控件保存您的查询，只需调用 OpenQuery:

SQL (另存为存储查询)

INSERT INTO BERTHAGE(BOAT, LOCATION, BERTHED)
VALUES(Forms!myForm!Add_Boat, Forms!myForm!LOCATION, Forms!myForm!In_Port)

VBA

Private Sub cmd_Add_Click()
   Dim strSQL As String

   DoCmd.SetWarnings False                   ' TURN OFF APPEND PROMPTS
   DoCmd.OpenQuery "mySavedActionQuery"
   DoCmd.SetWarnings True                    ' RESET WARNINGS

   Call cmd_Clear_Click

End Sub

这篇关于INSERT INTO - 错误，但允许输入到表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！