子例程语法错误 [英] Sub routine syntax errors
问题描述
请帮我更正subBoundData中的语句
以下子项位于模块中。一切都运行没有错误消息，
但是，数据没有到达文本框。数据被附加到
WorkingTable但未按文本框中的值过滤。如果我将数据输入到
文本框中，它将被子例程删除，表明
ctla.ItemData（varItm）为空。
Sub BoundData（）
Dim frm As Form
Dim Form3 As Form
Dim ctla As Control，ctlb As Control ，ctlc作为Control''列表框，Multi
Select = Simple
Dim varItm As Variant
Dim ItemData As Variant
Dim<字段列表已剪切为字段
Dim MainTable，WorkingTable As TableDefs
DoCmd.RunSQL删除WorkingTable。< field1FROM WorkingTable ;"
设置frm = Form3
设置ctla = Forms！Form3！ctla
For each varItm in ctla.ItemsSelected
Forms！Form3.Text1 = ctla.ItemData（varItm）''Text1是文本框的名称
$ 3 $ b on form3
DoCmd.RunMacroAppendData ''SQL 256个字符
下一个varItm
结束子
Chuck写道：
请帮我修正subBoundData中的语句
以下子在一个模块中。一切都运行没有错误消息，
但是，数据没有到达文本框。数据被附加到
WorkingTable但未按文本框中的值过滤。如果我将数据输入到
文本框中，它将被子例程删除，表明
ctla.ItemData（varItm）为空。
Sub BoundData（）
Dim frm As Form
Dim Form3 As Form
Dim ctla As Control，ctlb As Control ，ctlc作为Control''列表框，Multi
Select = Simple
Dim varItm As Variant
Dim ItemData As Variant
Dim<字段列表已剪切为字段
Dim MainTable，WorkingTable As TableDefs
DoCmd.RunSQL删除WorkingTable。< field1FROM WorkingTable ;"
设置frm = Form3
设置ctla = Forms！Form3！ctla
For each varItm in ctla.ItemsSelected
Forms！Form3.Text1 = ctla.ItemData（varItm）''Text1是文本框的名称
$ 3 $ b on form3
DoCmd.RunMacroAppendData ''SQL 256个字符
下一个varItm
End Sub
您正在存储来自的数据所有在ctla中选择的项目都进入Text1，超过
及以上。这里没有人真正知道AppendData实际上做了什么。
因为你似乎知道一些VBA，为什么不使用VBA而不是宏。
Dim lngID As很长
Dim strSQL As String
For Me.ListBoxName.ItemsSelected中的每个varItem
lngID = Me.ListBoxName.Column（0，varItem）
strSQL = ...
Currentdb.Execute strSQL，dbFailOnError
下一页
现在，您的子名称是BoundData。 text1是绑定字段吗？只需将
填入绑定文本框中的值就不会做任何事情，除非记录是
保存。
BTW，你试图对于符合条件的记录，删除（删除）字段或更新字段
为Null？
您可能想要解释您的问题一些额外的细节。我很肯定我们很多人都使用VBA，因为你可以比我们一无所知的
a宏更容易调试问题。
2007年8月11日星期六13:08:45 -0700，Salad< oi*@vinegar.comwrote：
> Chuck写道：
>请帮我纠正subBoundData中的语句
以下子语言位于模块中。一切都运行没有错误消息，但是，数据没有到达文本框。数据被附加到
WorkingTable，但未按文本框中的值进行过滤。如果我将数据输入到
文本框中，它将被子例程删除，表明
ctla.ItemData（varItm）为空。
Sub BoundData（）
Dim Form3 As Form
Dim ctla As Control，ctlb As Control，ctlc As Control''列表框，Multi
Select = Simple
Dim varItm As Variant
Dim ItemData As Variant
Dim<字段列表剪切为字段
Dim MainTable，WorkingTable As TableDefs
DoCmd.RunSQL删除WorkingTable。< field1FROM WorkingTable;"
设置frm = Form3
设置ctla = Forms！Form3！ctla
For each varItm in ctla.ItemsSelected
Forms！Form3.Text1 = ctla.ItemData（varItm）''Text1是form3上文本框的名称
DoCmd.RunMacro" AppendData" ''SQL 256个字符
下一个变量
End Sub
您将来自ctla中所选项目的数据存储到Text1中，通过
以上。这里没有人真正知道AppendData实际上做了什么。
我一直在测试一个选定的项目。
我瞄准敌人的是每个通过for next循环传递一组不同的
数据从MainTable附加到WorkingTable。任何地方从一个到每个通行证可以附加40个记录。
>
因为你似乎知道一些VBA ，为什么不使用VBA而不是宏。
Dim lngID As Long
Dim strSQL As String
For Me.ListBoxName中的每个varItem。 ItemsSelected
如果我插入我。在ListBoxName前面，一条错误消息说不正确使用
Me。
lngID = Me.ListBoxName.Column（0，varItem）
将（varItm）更改为.Column（0，varItm）无效。
> strSQL = ...
Currentdb.Execute strSQL，dbFailOnError
Next
SQL字符串超过1200个字符。我以为我只能以这种方式使用少于256个字符的SQL
语句。
>现在，你的名字sub是BoundData。 text1是绑定字段吗？
不，它没有约束。 SQL语句的Where部分使用
文本框中的值。如果我将值硬连接到文本框中，则查询运行正常。
>简单地将值填充到绑定文本框中除此之外什么都不做该记录已保存。
记录只能在Append查询运行之前存活。
>
顺便说一句，您是否尝试删除（删除）字段或将字段的内容更新为Null以获取符合条件的记录？
DoCmd.RunSQL"删除WorkingTable。< field1FROM WorkingTable;"删除表中每个记录的每个
字段。它工作正常。
>
你可能想用一些额外的细节来解释你的问题。我确定很多人都使用VBA，因为你可以比我们一无所知的宏更容易调试问题。
附加查询存在。它作为非常长的SQL语句。宏仅仅
运行附加查询：
打开查询
查询名称：AppendDataToWorkingTable
查看：数据表
数据模式：编辑
主题子例程语法错误本身就是错误的。语法是
就好了。我所拥有的是逻辑错误。子例程正在完成它被告知要做的事情。但不幸的是，这不是我想要的。如果我
可以纠正逻辑，我认为这就是所需要的。
试试这个...
首先在包含
ctla和text1的Form上创建一个名为btnRunThis的命令按钮。
转到按钮的属性并在OnClick事件中调用Code Builder
并粘贴在下面的代码中
空气代码警告......
************** *********
Sub btnRunThis_OnClick（）
On Error goto btnRunThis_OnClick_Error
Dim db as CurrentDB
Dim varItem as Variable
将strSql作为字符串调整
''删除WorkingTable中的所有记录
strSql ="删除* FROM WorkingTable;"
db.execute strSql
''我指的是包含此代码的表单
For Me.ctla.ItemsSelected中的每个varItem
Me.Text1 = Me.ctla.ItemData（varItem）
DoCmd.RunMacro" AppendData"
下一个varItm
恢复btnRunThis_OnClick_Exit：
退出子
btnRunThis_OnClick_Error：
msgbox Err.Number& " " &安培; Err.Description
恢复btnRunThis_OnClick_Exit
结束子
*************** *******************
祝你好运
Len Robichaud
Please help me correct the statements in sub "BoundData"
The following sub is in a module. Everything runs with no error messages,
however,data is not reaching the text box. Data is being appended to
WorkingTable but not filtered by value in text box. If I input data into the
text box, it gets deleted by the sub routine, indicating that
ctla.ItemData(varItm) is null.
Sub BoundData()
Dim frm As Form
Dim Form3 As Form
Dim ctla As Control, ctlb As Control, ctlc As Control ''List boxes, Multi
Select = Simple
Dim varItm As Variant
Dim ItemData As Variant
Dim <list of fields snipped As Fields
Dim MainTable, WorkingTable As TableDefs
DoCmd.RunSQL "Delete WorkingTable.<field1FROM WorkingTable;"
Set frm = Form3
Set ctla = Forms!Form3!ctla
For Each varItm In ctla.ItemsSelected
Forms!Form3.Text1 = ctla.ItemData(varItm) '' Text1 is name of text box
on form3
DoCmd.RunMacro "AppendData" '' SQL 256 characters
Next varItm
End Sub
Chuck wrote:Please help me correct the statements in sub "BoundData"
The following sub is in a module. Everything runs with no error messages,
however,data is not reaching the text box. Data is being appended to
WorkingTable but not filtered by value in text box. If I input data into the
text box, it gets deleted by the sub routine, indicating that
ctla.ItemData(varItm) is null.
Sub BoundData()
Dim frm As Form
Dim Form3 As Form
Dim ctla As Control, ctlb As Control, ctlc As Control ''List boxes, Multi
Select = Simple
Dim varItm As Variant
Dim ItemData As Variant
Dim <list of fields snipped As Fields
Dim MainTable, WorkingTable As TableDefs
DoCmd.RunSQL "Delete WorkingTable.<field1FROM WorkingTable;"
Set frm = Form3
Set ctla = Forms!Form3!ctla
For Each varItm In ctla.ItemsSelected
Forms!Form3.Text1 = ctla.ItemData(varItm) '' Text1 is name of text box
on form3
DoCmd.RunMacro "AppendData" '' SQL 256 characters
Next varItm
End Sub
You are storing data from all items selected in ctla into Text1, over
and over. Nobody here would actually know what AppendData actually does.
Since you appear to know some VBA, why not use VBA instead of a Macro.
Dim lngID As Long
Dim strSQL As String
For Each varItem In Me.ListBoxName.ItemsSelected
lngID = Me.ListBoxName.Column(0, varItem)
strSQL = ...
Currentdb.Execute strSQL, dbFailOnError
Next
Now, the name of your sub is BoundData. Is text1 a bound field? Simply
stuffing a value into a bound textbox does nothing unless the record is
saved.
BTW, are you attempting to delete (drop) a field or update the contents
of the field to Null for records that match your criteria?
You might want to explain your problem with some additional detail. I''m
sure a lot of us use VBA because you can debug a problem far easier than
a macro we know nothing about.
On Sat, 11 Aug 2007 13:08:45 -0700, Salad <oi*@vinegar.comwrote:
>Chuck wrote:>Please help me correct the statements in sub "BoundData"
The following sub is in a module. Everything runs with no error messages,
however,data is not reaching the text box. Data is being appended to
WorkingTable but not filtered by value in text box. If I input data into the
text box, it gets deleted by the sub routine, indicating that
ctla.ItemData(varItm) is null.
Sub BoundData()
Dim frm As Form
Dim Form3 As Form
Dim ctla As Control, ctlb As Control, ctlc As Control ''List boxes, Multi
Select = Simple
Dim varItm As Variant
Dim ItemData As Variant
Dim <list of fields snipped As Fields
Dim MainTable, WorkingTable As TableDefs
DoCmd.RunSQL "Delete WorkingTable.<field1FROM WorkingTable;"
Set frm = Form3
Set ctla = Forms!Form3!ctla
For Each varItm In ctla.ItemsSelected
Forms!Form3.Text1 = ctla.ItemData(varItm) '' Text1 is name of text box
on form3
DoCmd.RunMacro "AppendData" '' SQL 256 characters
Next varItm
End Sub
You are storing data from all items selected in ctla into Text1, over
and over. Nobody here would actually know what AppendData actually does.I''ve been testing with a single selected Item.
What I''m aiming foe is at each pass thru the for next loop a different set of
data is appended to the WorkingTable from the MainTable. Anywhere from one to
40 records could be appended on each pass.
>
Since you appear to know some VBA, why not use VBA instead of a Macro.
Dim lngID As Long
Dim strSQL As String
For Each varItem In Me.ListBoxName.ItemsSelectedIf I insert Me. in front of ListBoxName, an error message says improper use of
Me.
lngID = Me.ListBoxName.Column(0, varItem)Changing (varItm) to .Column(0, varItm) had no effect.
> strSQL = ...
Currentdb.Execute strSQL, dbFailOnError
Next
The SQL string is over 1200 characters. I thought I could only use an SQL
statement with less than 256 characters in this manner.
>Now, the name of your sub is BoundData. Is text1 a bound field?No, it is not bound. The Where part of the SQL statement uses the value in the
text box. If I hard wire a value into the text box, the query runs just fine.
>Simply
stuffing a value into a bound textbox does nothing unless the record is
saved.The record only has to survive until the Append query runs.
>
BTW, are you attempting to delete (drop) a field or update the contents
of the field to Null for records that match your criteria?DoCmd.RunSQL "Delete WorkingTable.<field1FROM WorkingTable;" deletes every
field for every records in the table. It works correctly.
>
You might want to explain your problem with some additional detail. I''m
sure a lot of us use VBA because you can debug a problem far easier than
a macro we know nothing about.The Append query exists. It as the very long SQL statement. The macro merely
runs the Append query:
Open query
Query name: AppendDataToWorkingTable
View: Datasheet
Data mode: Edit
The subject "Sub routine syntax errors" is itself in error. The syntax is
just fine. What I have is logic errors. The sub routine is doing exactly what
it is told to do. But, unfortunately, that is not what I want it to do. If I
can correct the logic I think That is all that will be needed.
Try this...
First create a Command Button named btnRunThis on the Form that contains
ctla and text1.
Go to the button''s properties and in the OnClick event invoke Code Builder
and paste in the code below
Air Code Warning...
***********************
Sub btnRunThis_OnClick()
On Error goto btnRunThis_OnClick_Error
Dim db as CurrentDB
Dim varItem as Variable
Dim strSql as String
''Delete all Records from WorkingTable
strSql = "Delete * FROM WorkingTable;"
db.execute strSql
''Me refers to the form that contains this code
For Each varItem In Me.ctla.ItemsSelected
Me.Text1 = Me.ctla.ItemData(varItem)
DoCmd.RunMacro "AppendData"
Next varItm
Resume btnRunThis_OnClick_Exit:
Exit sub
btnRunThis_OnClick_Error:
msgbox Err.Number & " " & Err.Description
Resume btnRunThis_OnClick_Exit
End Sub
**********************************
Good Luck
Len Robichaud
这篇关于子例程语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
