使用视图更新绑定的ms访问表单中的外键值(Updating foreign key values in bound ms access form fed with a view)

使用视图更新绑定的ms访问表单中的外键值(Updating foreign key values in bound ms access form fed with a view)

我正在使用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 = rsM

Everythink工作得很好，但问题是：

为底层视图假设以下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 = rsM

Everythink 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.ID

Now 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.ID

In 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.