表单 - 使用基于参数Query的子表单，可以编辑结果。 [英] Forms - with Subforms based on a parameter Query, for whic Results can be edited.
问题描述
我一直很疯狂，我一直在各个论坛上寻找答案。有人可以帮帮我吗？
我是一个具有基本VBA知识的高级Access用户。
（无ADO或SQL）
我为维修中心公司创建了这个数据库，以跟踪收到的项目，转移给第三方技术人员，并发回客户。
我用以下字段创建了3个主表。
1）tblReceived
--- ReceiveNo：Autonumber
--- ReceiveDate：日期
---描述：Combobox（无需指定来源）
--- SerialNo：文字
- --Status：Combobox（无需指定来源）
---技术员：Combobox（无需指定来源）
2）tblTransferred
--- TransferNo：Autonumber
--- TransferDate：日期
--- TransferItemsNo： ??? - 帮助这里
---技术员：Combobox（无需指定来源）
---状态：Combobox（无需指定来源）
3）tblReleased
--- ReleaseNo：Autonumber
---发布日期：日期
- --ReleaseItemsNo： ??? - 帮助这里
---状态：Combobox（需要指定来源）
然后我有2个连接表来连接多对多关系：
1）tblTransferItems
--- TransferItemsNo：自动编号
--- TransferNo：链接到tblTransfer
--- ReceiveNo：链接到tblReceive
2）tblReleaseItems
--- ReleaseItemsNo：Autonumber
--- ReleaseNo：链接到tbl发布
--- TransferNo：链接到tblTransfer
以下是我遇到问题的表格。
1）frmReceive ：来源是qryReceive
2）frmTransfer ：来源是tblTransfer
----- subfrmTransferItems：来源是tblTransferItesm
3）frmRelease ：来源是tblRelease
----- subfrmReleaseItems：来源是tblReleaseItems
4）frmSetTransfer ''：未绑定
5 frmSetRelease）：未绑定
我想要发生的是用户输入关于tblTransfer的信息，这些信息是来自tblReceive的多个项目。
还输入有关tblRelease的信息，这些信息来自
tblTransfer：
这是 < u>大图 。
ReleaseNo1
----- TransferNo1
---------- ReceiveNo1
---------- ReceiveNo2
----- TransferNo2
----------- ReceiveNo3
----------- ReceiveNo4
ReleaseNo2
----- TransferNo3
------------ ReceiveNo5
------------ ReceiveNo6
----- TransferNo4
----------- ReceiveNo7
----------- ReceiveNo8
我想要的是使用未绑定的 frmSetTransfer ，会出现项目< subfrmTransferItems 中的
。
所以我认为可能是子表单的来源
将是基于未绑定表单的参数查询。
但是我希望它能够在显示结果后，用户可以查看所有
信息（不仅是第一列）并编辑它们。
当用户在 frmTransfer 的组合框技术人员中选择一个选项时，
所有技师 subfrmTransferItems 的字段也将根据该字段更改
。
同样适用于状态 frmTransfer 的字段，应该更新
subfrmTransferDetails 上所有记录的状态字段。
我似乎无法找到方法！我的计划太复杂了吗？
甚至可以在 Big Picture 中按照我计划的方式设置相关的表格和表格上面？
I''ve been going nuts about it and I''ve been searching all over the forums to find answers. Can somebody please help me?
I am a somehow advanced Access user with basic VBA knowledge.
(No ADO or SQL)
I have created this database for a Repair Center company to keep track of items that are received, transferred to a third party technicians, and released back to customers.
I created 3 Main tables with the following fields.
1) tblReceived
---ReceiveNo : Autonumber
---ReceiveDate : Date
---Description : Combobox (no need to specify source)
---SerialNo : Text
---Status : Combobox (no need to specify source)
---Technician : Combobox (no need to specify source)
2) tblTransferred
---TransferNo : Autonumber
---TransferDate : Date
---TransferItemsNo : ??? - Help Here
---Technician : Combobox (no need to specify source)
---Status : Combobox (no need to specify source)
3) tblReleased
---ReleaseNo : Autonumber
---ReleaseDate : Date
---ReleaseItemsNo : ??? - Help Here
---Status : Combobox (need to specify source)
Then I have 2 joining tables to connect the many-to-many relationships:
1) tblTransferItems
---TransferItemsNo : Autonumber
---TransferNo : Links to tblTransfer
---ReceiveNo : Links to tblReceive
2) tblReleaseItems
---ReleaseItemsNo : Autonumber
---ReleaseNo : Links to tbl Release
---TransferNo : Links to tblTransfer
And here are my forms where I am having problems with.
1) frmReceive : source is qryReceive
2) frmTransfer : Source is tblTransfer
-----subfrmTransferItems : Source is tblTransferItesm
3) frmRelease : Source is tblRelease
-----subfrmReleaseItems : Source is tblReleaseItems
4) frmSetTransfer '': unbound
5 frmSetRelease ) : unbound
What I want to happen is that user inputs information on tblTransfer which are
multiple items from the tblReceive.
And also input information on the tblRelease which are multiple items from the
tblTransfer:
Here is the Big Picture.
ReleaseNo1
-----TransferNo1
----------ReceiveNo1
----------ReceiveNo2
-----TransferNo2
-----------ReceiveNo3
-----------ReceiveNo4
ReleaseNo2
-----TransferNo3
------------ReceiveNo5
------------ReceiveNo6
-----TransferNo4
-----------ReceiveNo7
-----------ReceiveNo8
What I want is by using the unbound frmSetTransfer, items would appear
in the subfrmTransferItems.
So I figure maybe the source for the subform
would be a parameter query based on the unbound form.
But I want it in a way that after the results are shown, users can see all
information (not only the first column) and edit them as well.
When a user selects a choice in the combobox Technician of frmTransfer,
all the Technician Field of subfrmTransferItems will change
according to that as well.
The same goes to the Status Field of frmTransfer which should update
the Status field of all records on the subfrmTransferDetails.
I can''t seem to find a way! Is my plan too complex?
Is it even possible to have tables and forms related in the way that I was planning it in the Big Picture above?
推荐答案
我一直在疯狂，我去过搜索遍布论坛以寻找答案。有人可以帮帮我吗？
我是一个具有基本VBA知识的高级Access用户。
（无ADO或SQL）
我为维修中心公司创建了这个数据库，以跟踪收到的项目，转移给第三方技术人员，并发回客户。
我用以下字段创建了3个主表。
1）tblReceived
--- ReceiveNo：Autonumber
--- ReceiveDate：日期
---描述：Combobox（无需指定来源）
--- SerialNo：文字
- --Status：Combobox（无需指定来源）
---技术员：Combobox（无需指定来源）
2）tblTransferred
--- TransferNo：Autonumber
--- TransferDate：日期
--- TransferItemsNo： ??? - 帮助这里
---技术员：Combobox（无需指定来源）
---状态：Combobox（无需指定来源）
3）tblReleased
--- ReleaseNo：Autonumber
---发布日期：日期
- --ReleaseItemsNo： ??? - 帮助这里
---状态：Combobox（需要指定来源）
然后我有2个连接表来连接多对多关系：
1）tblTransferItems
--- TransferItemsNo：自动编号
--- TransferNo：链接到tblTransfer
--- ReceiveNo：链接到tblReceive
2）tblReleaseItems
--- ReleaseItemsNo：Autonumber
--- ReleaseNo：链接到tbl发布
--- TransferNo：链接到tblTransfer
以下是我遇到问题的表格。
1）frmReceive ：来源是qryReceive
2）frmTransfer ：来源是tblTransfer
----- subfrmTransferItems：来源是tblTransferItesm
3）frmRelease ：来源是tblRelease
----- subfrmReleaseItems：来源是tblReleaseItems
4）frmSetTransfer ''：未绑定
5 frmSetRelease）：未绑定
我想要发生的是用户输入关于tblTransfer的信息，这些信息是来自tblReceive的多个项目。
还输入有关tblRelease的信息，这些信息来自
tblTransfer：
这是 < u>大图 。
ReleaseNo1
----- TransferNo1
---------- ReceiveNo1
---------- ReceiveNo2
----- TransferNo2
----------- ReceiveNo3
----------- ReceiveNo4
ReleaseNo2
----- TransferNo3
------------ ReceiveNo5
------------ ReceiveNo6
----- TransferNo4
----------- ReceiveNo7
----------- ReceiveNo8
我想要的是使用未绑定的 frmSetTransfer ，会出现项目< subfrmTransferItems 中的
。
所以我认为可能是子表单的来源
将是基于未绑定表单的参数查询。
但是我希望它能够在显示结果后，用户可以查看所有
信息（不仅是第一列）并编辑它们。
当用户在 frmTransfer 的组合框技术人员中选择一个选项时，
所有技师 subfrmTransferItems 的字段也将根据该字段更改
。
同样适用于状态 frmTransfer 的字段，应该更新
subfrmTransferDetails 上所有记录的状态字段。
我似乎无法找到方法！我的计划太复杂了吗？
甚至可以在 Big Picture 中按照我计划的方式设置相关的表格和表格以上？
I''ve been going nuts about it and I''ve been searching all over the forums to find answers. Can somebody please help me?
I am a somehow advanced Access user with basic VBA knowledge.
(No ADO or SQL)
I have created this database for a Repair Center company to keep track of items that are received, transferred to a third party technicians, and released back to customers.
I created 3 Main tables with the following fields.
1) tblReceived
---ReceiveNo : Autonumber
---ReceiveDate : Date
---Description : Combobox (no need to specify source)
---SerialNo : Text
---Status : Combobox (no need to specify source)
---Technician : Combobox (no need to specify source)
2) tblTransferred
---TransferNo : Autonumber
---TransferDate : Date
---TransferItemsNo : ??? - Help Here
---Technician : Combobox (no need to specify source)
---Status : Combobox (no need to specify source)
3) tblReleased
---ReleaseNo : Autonumber
---ReleaseDate : Date
---ReleaseItemsNo : ??? - Help Here
---Status : Combobox (need to specify source)
Then I have 2 joining tables to connect the many-to-many relationships:
1) tblTransferItems
---TransferItemsNo : Autonumber
---TransferNo : Links to tblTransfer
---ReceiveNo : Links to tblReceive
2) tblReleaseItems
---ReleaseItemsNo : Autonumber
---ReleaseNo : Links to tbl Release
---TransferNo : Links to tblTransfer
And here are my forms where I am having problems with.
1) frmReceive : source is qryReceive
2) frmTransfer : Source is tblTransfer
-----subfrmTransferItems : Source is tblTransferItesm
3) frmRelease : Source is tblRelease
-----subfrmReleaseItems : Source is tblReleaseItems
4) frmSetTransfer '': unbound
5 frmSetRelease ) : unbound
What I want to happen is that user inputs information on tblTransfer which are
multiple items from the tblReceive.
And also input information on the tblRelease which are multiple items from the
tblTransfer:
Here is the Big Picture.
ReleaseNo1
-----TransferNo1
----------ReceiveNo1
----------ReceiveNo2
-----TransferNo2
-----------ReceiveNo3
-----------ReceiveNo4
ReleaseNo2
-----TransferNo3
------------ReceiveNo5
------------ReceiveNo6
-----TransferNo4
-----------ReceiveNo7
-----------ReceiveNo8
What I want is by using the unbound frmSetTransfer, items would appear
in the subfrmTransferItems.
So I figure maybe the source for the subform
would be a parameter query based on the unbound form.
But I want it in a way that after the results are shown, users can see all
information (not only the first column) and edit them as well.
When a user selects a choice in the combobox Technician of frmTransfer,
all the Technician Field of subfrmTransferItems will change
according to that as well.
The same goes to the Status Field of frmTransfer which should update
the Status field of all records on the subfrmTransferDetails.
I can''t seem to find a way! Is my plan too complex?
Is it even possible to have tables and forms related in the way that I was planning it in the Big Picture above?
嗨！
因为我知道了事情就是这样。
1. tblReceived.ReceiveNo唯一标识单个项目
2. tblTransferred.TransferNo唯一标识多个项目的单个转移
3. tblReleased.ReleaseNo唯一标识单个发布多个项目
4.并非所有单次转移项目都必须在单个版本中。
如果是这样，你唯一需要的就是添加FK基于表tblTransferred中的PK字段的字段和tblReleased to tblReceived。
1）tblReceived
--- ReceiveNo：Autonumber
--- ReceiveDate：日期
---描述：Combobox（无需指定来源）
--- SerialNo：Text
---状态：有限公司mbobox（无需指定来源）
---技术员：Combobox（无需指定来源）
--- TransferNo：Long，FK，lookup（ tblTransferred）
--- ReleaseNo：Long，FK，lookup（tblReleased）
< b> 2）tblTransferred
--- TransferNo：自动编号，PK
--- TransferDate：日期
--- TransferItemsNo： 删除此
---技术员：Combobox（无需指定来源）
---状态：Combobox（无需指定来源）
3）tblReleased
--- ReleaseNo：Autonumber
---发布日期：日期
--- ReleaseIt emsNo： 删除此
---状态：Combobox（需要指定来源）
不需要其余的表，使用连接。
这看起来像你期望的那样吗？
Hi!
As I''ve got it the things are going this way.
1. tblReceived.ReceiveNo uniquely identifies single item
2. tblTransferred.TransferNo uniquely identifies single transfer of multiple items
3. tblReleased.ReleaseNo uniquely identifies single release of multiple items
4. Not all items of single transfer must be within single release.
If so the only thing you need is to add FK fields based on PK fields in tables tblTransferred and tblReleased to tblReceived.
1) tblReceived
---ReceiveNo : Autonumber
---ReceiveDate : Date
---Description : Combobox (no need to specify source)
---SerialNo : Text
---Status : Combobox (no need to specify source)
---Technician : Combobox (no need to specify source)
---TransferNo : Long, FK, lookup(tblTransferred)
---ReleaseNo : Long, FK, lookup(tblReleased)
2) tblTransferred
---TransferNo : Autonumber, PK
---TransferDate : Date
---TransferItemsNo : Remove this
---Technician : Combobox (no need to specify source)
---Status : Combobox (no need to specify source)
3) tblReleased
---ReleaseNo : Autonumber
---ReleaseDate : Date
---ReleaseItemsNo : Remove this
---Status : Combobox (need to specify source)
The rest tables are not needed, use joins.
Does this look like what you''ve been expected?
嗨！
因为我已经得到了这样的事情。
1. tblReceived.ReceiveNo唯一标识单项
2. tblTransferred.TransferNo唯一标识多个项目的单次转移
3. tblReleased.ReleaseNo唯一标识多个项目的单个发布
4.并非所有单次转移项目都必须在单一版本中。
如果是这样，您唯一需要的是根据表tblTransferred中的PK字段添加FK字段，并将tblReleased添加到tblReceived。
1）tblReceived
--- ReceiveNo：Autonumber
--- ReceiveDate：日期
---描述：Combobox（无需指定来源）
--- SerialNo：Text < br $>
---状态：Combobox（无需指定来源）
---技术员：Combobox（无需指定来源）
--- TransferNo：Long，FK，lookup（tblTransferred）
--- ReleaseNo：Long，FK，lookup（tblReleased）
2）tblTransferred
--- TransferNo：Autonumber，PK
--- TransferDate：日期
--- TransferItemsNo： 删除此
---技术员：Combobox（无需指定来源） ）
---状态：Combobox（无需指定来源）
3）tblReleased
--- ReleaseNo：Autonumber
---发布日期：日期
--- ReleaseItemsNo： 删除此
---状态：Combobox（需要指定源代码）
不需要其余的表，使用连接。
这看起来像你期望的那样吗？
Hi!
As I''ve got it the things are going this way.
1. tblReceived.ReceiveNo uniquely identifies single item
2. tblTransferred.TransferNo uniquely identifies single transfer of multiple items
3. tblReleased.ReleaseNo uniquely identifies single release of multiple items
4. Not all items of single transfer must be within single release.
If so the only thing you need is to add FK fields based on PK fields in tables tblTransferred and tblReleased to tblReceived.
1) tblReceived
---ReceiveNo : Autonumber
---ReceiveDate : Date
---Description : Combobox (no need to specify source)
---SerialNo : Text
---Status : Combobox (no need to specify source)
---Technician : Combobox (no need to specify source)
---TransferNo : Long, FK, lookup(tblTransferred)
---ReleaseNo : Long, FK, lookup(tblReleased)
2) tblTransferred
---TransferNo : Autonumber, PK
---TransferDate : Date
---TransferItemsNo : Remove this
---Technician : Combobox (no need to specify source)
---Status : Combobox (no need to specify source)
3) tblReleased
---ReleaseNo : Autonumber
---ReleaseDate : Date
---ReleaseItemsNo : Remove this
---Status : Combobox (need to specify source)
The rest tables are not needed, use joins.
Does this look like what you''ve been expected?
是的。绝对！！！我从来没有人会理解我
因为我很难解释我的问题。谢谢。
你给了我更多的光。
还有一件事。
我已经有一个Autonumber字段（只是没有说明）
除了ReceiveNo，TransferNo和ReleaseNo。
我已将此字段设置为Number（长整数）并且我希望它每次在添加输入模式下加载表单时增加
。
但它根本没有增加。
这些字段的格式是
ReceiveNo ：HPRec 0700
TransferNo：HPTra 0700
发行编号：HPRel 0700
和我的表格编码如下：
Private Sub Form_Load（）
如果Me.NewRecord那么
Me！[ReceiveNo] = DMax（[ReceiveNo]，" tblReceive"）+ 1
Else
Me！[ReceiveNo] =我！[ReceiveNo]
结束如果
结束子
但它只是没有增加。每次打开表单
在添加输入模式时，字段只显示为 HPRec 0700，HPTra 0700和HPRel 0700 。它没有增加它。我在这里错过了什么吗？
提前致谢
Yes. Absolutely!!! I never though somebody would understand me
coz i had a hard time explaining my problem as well. Thanks.
You gave me more light to this.
just one more thing.
I already have an Autonumber field ( just didn''t specify)
aside from the ReceiveNo, TransferNo, and ReleaseNo.
I''ve set this fields to Number (Long integer) and I want it
to increment everytime the form loads in add entry mode.
But it just doesn''t increment at all.
The Format''s for these fields are
ReceiveNo : "HPRec 07"00
TransferNo : "HPTra 07"00
Release No: "HPRel 07"00
and I have my forms coded like this:
Private Sub Form_Load( )
If Me.NewRecord Then
Me![ReceiveNo] = DMax([ReceiveNo], "tblReceive") + 1
Else
Me![ReceiveNo] = Me![ReceiveNo]
End If
End Sub
But it just doesn''t increment. every time the form is opened
at add entry mode, the fields just show as HPRec 0700, HPTra 0700, and HPRel 0700. It doesn''t increment it. Is there something I''ve missed here?
Thanks in advance
是的。绝对！！！我从来没有人会理解我
因为我很难解释我的问题。谢谢。
你给了我更多的光。
还有一件事。
我已经有一个Autonumber字段（只是没有说明）
除了ReceiveNo，TransferNo和ReleaseNo。
我已将此字段设置为Number（长整数）并且我希望它每次在添加输入模式下加载表单时增加
。
但它根本没有增加。
这些字段的格式是
ReceiveNo ：HPRec 0700
TransferNo：HPTra 0700
发行编号：HPRel 0700
和我的表格编码如下：
Private Sub Form_Load（）
如果Me.NewRecord那么
Me！[ReceiveNo] = DMax（[ReceiveNo]，" tblReceive"）+ 1
Else
Me！[ReceiveNo] =我！[ReceiveNo]
结束如果
结束子
但它只是没有增加。每次打开表单
在添加输入模式时，字段只显示为 HPRec 0700，HPTra 0700和HPRel 0700 。它没有增加它。我在这里错过了什么吗？
提前付款
Yes. Absolutely!!! I never though somebody would understand me
coz i had a hard time explaining my problem as well. Thanks.
You gave me more light to this.
just one more thing.
I already have an Autonumber field ( just didn''t specify)
aside from the ReceiveNo, TransferNo, and ReleaseNo.
I''ve set this fields to Number (Long integer) and I want it
to increment everytime the form loads in add entry mode.
But it just doesn''t increment at all.
The Format''s for these fields are
ReceiveNo : "HPRec 07"00
TransferNo : "HPTra 07"00
Release No: "HPRel 07"00
and I have my forms coded like this:
Private Sub Form_Load( )
If Me.NewRecord Then
Me![ReceiveNo] = DMax([ReceiveNo], "tblReceive") + 1
Else
Me![ReceiveNo] = Me![ReceiveNo]
End If
End Sub
But it just doesn''t increment. every time the form is opened
at add entry mode, the fields just show as HPRec 0700, HPTra 0700, and HPRel 0700. It doesn''t increment it. Is there something I''ve missed here?
Thanks in advance
你没有，而且没有能够将值写入自动编号字段。
每次添加新记录时Access将使用下一个编号填充自动编号字段。你我！[ReceiveNo]可能没有链接到tblReceived.ReceiveNo字段，否则会导致错误（smthng就像字段是只读的）。
如果你想要通过格式属性显示前缀ID，然后您应该执行以下操作：
表单;）设计视图。
检查控件格式属性是否设置为HP ... 0700
检查控件是否链接到源表自动编号字段！
并删除上面的代码。它永远不会与Autonumber字段一起使用。
祝你好运。
You don''t have and moreover not able to write values to autonumber field.
Every time a new record is added Access will fill autonumber field with next number. You Me![ReceiveNo] is probably not linked to tblReceived.ReceiveNo field, otherwise it will result in error (smthng like "field is read-only").
If you want ID''s to appear with prefix via format property then you should do the following:
In form ;) design view.
Check that control format property is set to "HP... 07"00
And check that the control is linked to the source table autonumber field!
And get rid of the code above. It will never work with Autonumber fields.
Good luck.
这篇关于表单 - 使用基于参数Query的子表单，可以编辑结果。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
