在表单上查看计算的查询字段 [英] Viewing a calculated query field on a form

问题描述

您好，我正在使用Access 2003数据库进行库存控制。我正在设置一个表单（frmProducts），用于查看和添加产品信息。我有几个表格，这些信息来自：

tblProd带字段：item（PK），CatID（FK），List Price和其他与此问题无关的字段


tblCat包含字段：CatID（PK），类别，税率


我有一个查询：qryListPrice包含字段tblProd.ListPrice和tblCat.tax和计算字段列表价格来计算包含税的价格。


我希望能够在我的frmProducts上查看定价。我在表单上创建了一个未绑定的文本框，并使其控制源= [qryListPrice]！[List Price]但是当我查看表单时，该字段显示#Name？。我知道我可以在现场进行正确的计算，但我会在我的许多报告中使用清单价格，并认为从同一个地方计算所有对清单价格的参考会更好。


任何人都可以帮我获取字段的计算值以反映查询计算，或者只是在文本框中进行计算更好吗？非常感谢任何和所有的帮助和建议。

Hello, I am working on an Access 2003 Database for inventory control. I am setting up a form (frmProducts) for the viewing and adding of product information. I have several tables that this information is coming from:

tblProd with fields: item(PK), CatID(FK), List Price and other not fields not related to this question

tblCat with fields: CatID(PK), Category, Tax Rate

I have a query:qryListPrice with fields tblProd.ListPrice and tblCat.tax and a calculation field List Price to calculate the price including tax.

I would like to be able to view the list price on my frmProducts. I have created an unbound textbox on the form and made its control source =[qryListPrice]![List Price] but when I view the form the field shows #Name?. I know that I can do the calculation right in the field, but I will use the list price in many of my reports and thought it would be better to have all references to list price calculated from the same place.

Can anyone help me get the calculated value of the field to reflect the query calculation, or is it a better idea to simply do the calculation in the text box? Any and all help and advice is greatly appreciated.

推荐答案

klarae。


为了实现它你需要设置将Form.RecordSource改为[qryListPrice]，然后计算[List Price]字段将出现在ControlSource下拉列表中。当然你需要检索绑定到表单控件的所有字段。


问候，

鱼


PS不确定在TextBox控件中计算值是否更好。有时是，有时没有。在这种情况下，我宁愿通过查询来做，因为SQL执行速度比控制计算快。

Hi, klarae.

To make it you need to set Form.RecordSource to [qryListPrice], then calculated[List Price] field will appear in ControlSource dropdown. Certainly you need to retrieve to the query all fields bound to the form controls.

Regards,
Fish

P.S. Not sure whether it is a better idea to calculte value in TextBox control. Sometimes yes, sometimes no. In that kind of situation I would prefer to do it via query as SQL performs faster than control calculation.

Fish，


谢谢你的快速回复，我真的很感谢你能给我的任何帮助。我已经尝试过将Form.Recordsource更改为[qryListPrice]然后在控件源中选择列表价格的建议。当我这样做并切换回窗体视图时，整个窗体是空白的，没有字段，没有标签，没有。


目前所有其他字段都链接到tblProducts。我曾尝试使用来自tblProducts，tblCatigories，tblSubcategories，tbl Events和tbl供应商的信息进行排队，这些供应商包含了我在表单上所需的所有字段，但却遇到了这个错误，即代码过于复杂。


然后我试图窝'几个查询以获得相同的字段并且可以使其工作（某种程度上）但我有问题创建我的组合框我不能即使我将表单更改为数据输入，也会在表单中显示新记录。这就是为什么我回到从tblProducts创建表单，并使用其他表作为组合框控件源。


我知道我已经阅读过各种各样的帖子，最好从查询中创建表单，这是我首先尝试的原因之一，但我一直在现在在这个数据库工作了两个星期，我的老板似乎认为这让我感到很长时间。


任何建议都将不胜感激。非常感谢您的帮助！

Fish,

Thanks for your quick reply, I really appreciate any help you can give me. I have tried your suggestion of changing the Form.Recordsource to [qryListPrice] then selecting list price in the control source. When I do this and switch back to form view, the entire form is blank, no fields, no lables, nothing.

Currently all of the other fields are linked to tblProducts. I had tried to make a queary with the information from tblProducts, tblCatigories, tblSubcategories, tbl Events and tbl Vendors that included all the fields that I wanted on my form but ran into the error that the queary was too complex.

I then tried to ''nest'' a couple of queries to get the same fields and could make that work (sort of) but I had issues creating my comboboxes and I could not get a new record to appear in the form, even when I changed the form to data entery. Which is why I went back to creating the form from the tblProducts, and using the other tables as combobox control sources.

I know that I have read in various threads that it is better to create forms from queries, which is part of the reason I tried in the first place, but I''ve been working on this database for two weeks now and my boss seems to think it is taking me to long.

Any advice would be greatly appreciated. Thanks so much for your help!

您好，klarae99。


我怀疑您是否可以将所有表格加入一个查询并将其添加到一种形式。

从一些不太雄心勃勃的任务开始。你有填充[tblProducts]的表单，你想添加一个计算字段。因此，只需从连接检索[tblProducts]中的所有字段和所需的一个计算字段创建一个查询。将表单RecordSource设置为查询并将表单控件ControlSource设置为查询的对应字段。

Hi, klarae99.

I doubt whether you can join all the tables to one query and put it to one form.
Start with some less ambitious task. You had form which was populating [tblProducts], you wanted to add one calculated field. So create a query from only necessary tables joined retrieving all the fields from [tblProducts] and one calculated field you need. Set the form RecordSource to the query and set form controls ControlSource to correspondent fields of the query.

这篇关于在表单上查看计算的查询字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！