我正在使用MS Access 2013前端,该前端的表单绑定到从视图中读取的DAO记录集。 视图的主键是通过Access中的VBA设置的,如下所示:
CurrentDb.Execute "CREATE UNIQUE INDEX ID ON View_X (ID) WITH PRIMARY"View写在我的SQL Server 2005上,我通过ODBC连接到它,并通过DAO记录集绑定到表单,如下所示:
Set rsM = DB.OpenRecordset("View_X") Set Me.Recordset = rsMEverythink工作得很好,但问题是:
为底层视图假设以下SQL:
Select Tbl_Main.Quality_Note, Tbl_ProductGroup.PG_Description From Tbl_Main inner join Tbl_ProductGroup on Tbl_Main.PG_ID = Tbl_ProductGroup.ID现在我希望能够在我的Access窗体中有一个Combobox,它绑定到我视图中的视图字段PG_Description,它的列表只包含PG_Description的可能条目。
因为我的PK已定义,我实际上可以在表单中编辑记录。 但我遇到的问题是编辑转到字段Tbl_ProductGroup.PG_Description并更改条目的描述(不好)但我只是想要它编辑外键值Tbl_Main.PG_ID(即从3到5)
有什么方法/技巧我可以使用Access中的绑定表单来完成此操作,还是必须通过VBA中的SQL“手动”更新记录?
任何帮助将不胜感激
I am using an MS Access 2013 frontend which has a form that is bound to a DAO recordset read from a view. The view´s Primary key is set via VBA in Access like this:
CurrentDb.Execute "CREATE UNIQUE INDEX ID ON View_X (ID) WITH PRIMARY"The View is written on my SQL Server 2005 to which i connect via ODBC and is bound to the form via a DAO recordset like this:
Set rsM = DB.OpenRecordset("View_X") Set Me.Recordset = rsMEverythink works great but here is the Problem:
Assume the following SQL for the underlying View:
Select Tbl_Main.Quality_Note, Tbl_ProductGroup.PG_Description From Tbl_Main inner join Tbl_ProductGroup on Tbl_Main.PG_ID = Tbl_ProductGroup.IDNow I want to be able to have a Combobox in my Access form which is bound to the view-field PG_Description in my view and its list just contains the possible entries of PG_Description.
since my PK is defined i can actually edit the record in the form. BUT the Problem I have is that the edit goes to the field Tbl_ProductGroup.PG_Description and changes the description of the entries (not good) but I just want it to edit the foreign key value Tbl_Main.PG_ID (ie. from 3 to 5)
Is there any way/trick I can accomplish this with bound forms in Access or do I have to update the record "manually" through SQL in VBA?
Any help will be greatly appreciated
最满意答案
Stolzenberg,
在你的view_X中添加
Select Tbl_Main.Quality_Note, Tbl_ProductGroup.PG_Description, Tbl_Main.PG_ID From Tbl_Main inner join Tbl_ProductGroup on Tbl_Main.PG_ID = Tbl_ProductGroup.ID在前端表单中添加一个绑定到值PG_ID的组合框。 作为组合框的Record / RowSource添加
SELECT Tbl_ProductGroup.ID, Tbl_ProductGroup.PG_Description FROM Tbl_ProductGroup;最后将组合框的列数设置为2,将列宽设置为0; 这将隐藏第一个键列,仅显示产品说明。
如果你被困住,请告诉我们。
Stolzenberg,
In your view_X add
Select Tbl_Main.Quality_Note, Tbl_ProductGroup.PG_Description, Tbl_Main.PG_ID From Tbl_Main inner join Tbl_ProductGroup on Tbl_Main.PG_ID = Tbl_ProductGroup.IDIn your front-end form add a combobox bounding to the value PG_ID. As the Record/RowSource for the combobox add
SELECT Tbl_ProductGroup.ID, Tbl_ProductGroup.PG_Description FROM Tbl_ProductGroup;Finally set the combobox's column count to 2 and column width to 0; this will hide the first key column and only show the product description.
let us know if you are stuck.
更多推荐
发布评论