访问VBA SQL UPDATE [英] Access VBA SQL UPDATE
问题描述
我在VBA中使用SQL UPDATE语句时遇到问题。
我有：
表格：
- " frmClaim：主要形式。数据基于表权利要求。它有ClaimID和其他字段。 ClaimID是自动编号和唯一标识符。
- " frmClaimProducts"：附加到frmClaim的子表单。该表格基于表格claim_product。它有ClaimID，ProductID，TotalCost和其他字段。
- " frmClaimTransaction"：附加到frmClaim的子表单。该表格基于表格claim_transaction。它有ClaimID，Date，Transaction Type，Amount等字段。
到目前为止我做了什么：
当有人在frmClaim中创建新记录时， claim_id，transaction_id，date和amount被插入到表claim_transaction中。下面是代码。这非常有效。
你不要在表名和SET关键字之间有一个空格。而且你的Date（）函数和WHERE关键字之间没有空格。
但是你为什么这样做呢？我不知道你的表是如何设置的以及你的表单是如何设置的，但是通过规范化的表设计和级联关系设置，你需要手动插入和更新记录的情况很少。
您的表名与SET关键字之间没有空格。而且你的Date（）函数和WHERE关键字之间没有空格。
但是你为什么这样做呢？我不知道你的表是如何设置的以及你的表单是如何设置的，但是通过规范化的表设计和级联关系设置，你需要手动插入和更新记录的情况很少。
非常感谢您的快速回复，兔子！
我试着插入你告诉我的空间，无论是之前还是之后，都没有用......我写的其他SQL语句没有空间，但是它们有效很好。
我正在处理的这个数据库有点复杂......主表单附加了很多子表单。
希望我在这方面工作的人拥有这个数据库大约2年，这个旧数据库有太多问题。这个旧数据库的人很久以前就退出了，所以我试图使用旧表中的表来创建新的表单和查询。人们不希望我改变大多数功能和设计，特别是那些自动插入和更新。
旧数据库存在问题的例子，它一直显示错误信息，有些人无法进入数据库，有些日期字段总是显示错误的日期，如2004表格中的所有字段都不是使用wisard制作的。制作此数据库的人为表单中的每个字段创建标签或文本框，为所有这些字段编写VBA和SQL代码，即使他不必使用向导也这样做。所以我试图通过使用向导使数据库尽可能简单，并摆脱不必要的代码。
以下是主要和子表单的外观（如果您愿意，我可以通过电子邮件向您发送此表单的屏幕截图）：
主要表格= frmClaim :(根据表格索赔）
----------------------------- -------------------------------------------------- ------------------------------------
<第一个标签页表格：一般信息>
（所有字段都是组合框，除了claimID）
-claimID，
-status，
-claim原因，
-claimant id（在此下方，有一个子表格显示索赔人的信息，如地址，电话＃基于申请人ID），
-carrier（在此之下，有与申请人ID相同的子表格），
-origin state，
-origin city，
-origin（州，城市，原点是组合框。它根据用户在上一个框中选择的内容显示一个列表。例如，如果用户选择CA状态，则显示我们在城市组合中的城市盒子.B如此，有一个与索赔人身份相同的子表格，
-destination state，
-destination city，
-destination（与原点相同的组合框。在此之下，有一个与索赔人ID相同的子表单。
*除了claimID之外的每个字段旁边都有按钮，如果用户没有，用户可以点击这些按钮来添加值没有看到组合框中的值。
---------------------------- -------------------------------------------------- ---------------------------------------
< 2nd表单中的选项卡：返回和活动>
参考编号子表单（表格视图/基于表claim_refnum和claim_refnum类型）：
-Reference描述（组合框）， Refnum Value，InsertUser，UpdateUser，（隐藏字段：claimID，Refnum）
活动子表单（表视图/基于表claim_activity和claim_activity_type）
-Date ，活动描述（组合框），插入用户和更新用户（隐藏字段：ClaimID，ActivityID）
*两个子表单旁边都有用户可以点击的按钮为参考描述和A添加值ctivity描述，如果用户在组合框中没有看到它们。
*活动描述打开日期当用户创建新记录时，会将日期插入到记录中。
*活动描述Date Mailed当用户首次打印报告时，会将日期插入到记录中。
----------------------- -------------------------------------------------- --------------------------------------------
<表格中的第3个标签：产品/交易>
产品子表格（表视图/基于表claim_product和产品）
-ProductID（组合框），描述（显示基于ProductID的描述），单位，Wt /单位，成本，数量，总Wt（计算总重量），总成本（计算每行产品的成本*数量），（隐藏字段为ClaimID）
总产品子表单（基于查询qryClaimProductsTotal）
-总产品成本（从子表单产品中计算所有产品成本）
交易子表单（表格视图/基于表claim_transaction和claim_transaction_type）
-日期，交易类型，收到日期检查（除非用户收到支票和交易，否则不填写此字段类型与支票收据相关），金额（当客户付款时，它被插入为 -
100（例如）），CheckNo，InsertUser，UpdateUser
总余额子表单（基于查询qryTotalBalance）
-Total Balance（它计算交易子表单中的金额并显示余额。）
*交易子表单中的数据与报表中的交易字段相关，因此必须插入总产品成本。
*交易类型=总产品成本在用户首次创建记录时插入。如果Products子窗体中没有产品，则将金额插入为零。
*当用户在组合框中看不到产品和交易类型时，用户可以使用按钮。
----------------------------------------- -------------------------------------------------- ---------------------------
<表格中的第四个标签：评论>
外部Coments子表单（表格视图/基于表claim_comment）
-Date，插入用户，评论
内部备忘录（此字段附于主要形式，frmClaim，基于表格索赔）
---------------------------- -------------------------------------------------- ----------------------------------------
<在标题中：允许用户搜索子表格参考编号中的参考编号值。>
参考编号过滤器
- 参考编号类型（组合框）
-参考编号
我打开这有帮助。对于长名单感到抱歉。我希望我可以简化这个解释，但我不能做出比这更短的解释。
I am having trouble using SQL UPDATE statement in VBA.
I have:
Forms:
-"frmClaim": Main form. Data is based on table "claim." It has ClaimID, and other fields. ClaimID is the auto number and unique identifier.
-"frmClaimProducts":Subform attatched to frmClaim. This form is based on table "claim_product." It has ClaimID, ProductID, TotalCost, and other fields.
-"frmClaimTransaction": Subform attatched to frmClaim. This form is based on table "claim_transaction." It has ClaimID, Date, Transaction Type, Amount, and other fields.
What I have done so far:
When someone create new record in "frmClaim," claim_id, transaction_id, date, and amount are inserted into table claim_transaction. Below is the code. This works perfectly.
You don''t have a space between your table name and your SET keyword. And you don''t have a space between your Date() function and your WHERE keyword.
But why are you doing it this way? I don''t know how your tables are set up and how your forms are set up but with a normalized table design and cascading relationships set up, there are very few situations where you need to insert and update records manually.
You don''t have a space between your table name and your SET keyword. And you don''t have a space between your Date() function and your WHERE keyword.
But why are you doing it this way? I don''t know how your tables are set up and how your forms are set up but with a normalized table design and cascading relationships set up, there are very few situations where you need to insert and update records manually.
Thank you very much for your quick reply, Rabbit!
I tried to insert the space as you told me, both before and after, but did not work... Other SQL statement I wrote doesn''t have space, but they work fine.
This database that I am working on is kind of complicated... There are so many sub forms attached to the main form.
People who want me to work on this had this database for about 2 years, and this old database has too much problems. The guy who made this old database quit long time ago, so I am trying to make new forms and queries using the tables from the old one. The people do not want me to change most of the functions and designs, especially those automated insertion and update.
Example of the problems with old database are, it shows error message all the time, some people cannot get in to the database, some date fields always shows the way wrong date, like 2004, etc. All the fields in the form are not made using wisard. The guy who made this database created labels or text box for every single field in the form, write VBA and SQL code for all of them, even though he did not have to do so by using wizard. So I am trying to make the database as simple as I can by using wizards and to get rid of unnecessary codes.
Here is how the main and sub forms looks like (I can e-mail you screen shot of this form if you would like):
Main form=frmClaim: (based on table claim)
-------------------------------------------------------------------------------------------------------------------
<1st tab in the form: General Information>
(All fields are combo box except for claimID)
-claimID,
-status,
-claim reasons,
-claimant id (below this, there is a subform to show claimant''s information such as address, phone# based on claimant id),
-carrier (below this, there is a same kind of subform as claimant id),
-origin state,
-origin city,
-origin (state, city, origin are combo boxes. It shows a list based on what an user select in previous box. For example, if user selected "CA" in state, it shows cities we have in city combo box. Below this, there is a same kind of subform as claimant id),
-destination state,
-destination city,
-destination (same kind of combo boxes as origin. below this, there is a same kind of subform as claimant id)
*Next to each field except for claimID, there are buttons where a user can click to add values if the user doesn''t see the value in combo box.
---------------------------------------------------------------------------------------------------------------------
<2nd tab in the form: Returns and Activities>
Reference Numbers sub form (table view/ based on table claim_refnum and claim_refnum type):
-Reference Description (combo box), Refnum Value, InsertUser, UpdateUser, (hidden fields: claimID, Refnum)
Activities subform (table view / based on table claim_activity and claim_activity_type)
-Date, Activity Description (combo box), Insert User, and Update User (hidden fields: ClaimID, ActivityID)
*Next to both sub forms, there are buttons where a user can click to add values for Reference Description and Activity Description, if the user doesn''t see them in combo box.
*Activity Description "Date Opened" is inserted to the record with date when an user create new record.
*Activity Description "Date Mailed" is inserted to the record with date when an user first print sthe report.
---------------------------------------------------------------------------------------------------------------------
<3rd tab in the form: Products/ Transactions>
Products subform (table view / based on table claim_product and product)
-ProductID (combo box), Description (shows description based on ProductID), Units, Wt/Unit, Cost, Qty, Total Wt (calclates total weight), Total Cost (calclates Cost * Qty for each line of product), (hidden fields are ClaimID)
Total Products subform (based on query qryClaimProductsTotal)
-Total Product Cost (calclates all the products cost from subform Products)
Transaction subform (table view / based on table claim_transaction and claim_transaction_type)
-Date, Transaction Type, Date Check Received (this field is not filled unless the user receives check and transaction type is related to check receipt), Amount (when customer pays, it''s inserted as -
100 (e.g.)), CheckNo, InsertUser, UpdateUser
Total Balance subform (based on query qryTotalBalance)
-Total Balance (it calclates amount in transaction subform and shows balance.)
*Data in transaction subform is related to transaction field in the report, so Total Product Cost must be inserted.
*Transaction Type = "Total Product Costs" is inserted when the user first create the record. If there is no products in Products subform, then it inserts amount as zero.
*There are buttons for the user to add products and transaction type when the user doesnt see it on combo box.
----------------------------------------------------------------------------------------------------------------------
<4th tab in the form: Comments>
External Coments Subform (tabular view / based on table claim_comment)
-Date, Insert User, Comment
Internal Memo (this field is attached to main form, frmClaim, based on table claim)
----------------------------------------------------------------------------------------------------------------------
<In Header: enable the user to search reference number values in subform reference numbers.>
Reference number filter
-Reference Number Type (combo box)
-Reference Number
I hope this helps. Sorry about the long list. I wish I can make this explanation shorter, but I couldn?t make any shorter than this. .
这篇关于访问VBA SQL UPDATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
登录 关闭
