MS Access:如何将数据从查询加载到窗体 [英] MS Access: How to load data from query into form

问题描述

我有一个表单可以帮助用户向数据库中添加一些数据，但是我注意到添加的每个产品上的一些字段非常相似.因此，我添加了第二个常用字段数据库，可以通过从组合框中选择来预先填充这些字段.
例如，如果用户正在添加产品XX-X，但产品XX-X是YY-Y的同一系列，并且我在Database2中已经有YY-Y的数据，我只想加载这些参数.我做了一个查询，可以根据需要返回参数，但是我不知道如何将其添加到FORM.
基本上，我有一个空白的VBA代码槽，用于"ComboBox_Change".
我希望ComboBox_Change函数从查询中加载字段X并将其粘贴到当前表单的字段X1中. 希望我能正确地解释自己.
谢谢！

I have a form that helps the user add some data into a Database, however I noticed that a few of the fields on each product added are very similar. So I'm adding a second database of commonly used fields that can be pre-filled by selecting from a combo-box.
For example if the user is adding Product XX-X but product XX-X is of the same family of YY-Y and I already have YY-Y's data in Database2, I want to just load those parameters. I made a query that returns the parameters as I want however I dont know how to add this to the FORM.
Basically I have a blank VBA code slot for "ComboBox_Change".
I want the ComboBox_Change function to load field X from query and paste it into field X1 on the current form. Hope I'm explaining myself correctly.
Thanks!

感谢您的建议这是到目前为止有错误的代码

THANKS for the suggestion this is the code so far that has an error

Private Sub LoadMatCB_Change()
Dim rs As Recordset
Dim db As Database

Set db = CurrentDb

Set rs = CurrentDb.OpenRecordset("VendorDeetsQuery")

If Nz(Me.Input_Vendor.Value, "") = "" Then Me.Input_Vendor.Value = rs![Origin]

Set rs = Nothing
Set db = Nothing

End Sub

推荐答案

您将需要使用表的ID字段将查询中的相关记录捕获到记录集对象中.然后，您可以使用记录集的字段在要填充的其他每个字段中设置值.您可以添加if语句来检查控件是否为空(如果您需要的话).

You will need to use the table's ID field to grab the related record from your query into a recordset object. You can then use the fields of the recordset to set the value in each of the other fields you want to populate; you can add the if statements to check if the controls are blank if that is a condition you need.

    Dim rs As Recordset
    Dim db as Database
    Dim qry as QueryDef

    Set db = CurrentDb
    Set qry = db.QueryDefs("YourQueryName")

    qry.Parameters("ParamName") = comboBox.Value '<pass your parameter here>
    'repeat the above for any other parameters you need to pass

    Set rs = qry.OpenRecordset

    'for each of these, use your control names and whatever you named the fields from your query'
    If Nz(Me.txtBox1.Value, "") = "" Then Me.txtBox1 = rs![fieldName1] 
    If Nz(Me.txtBox2.Value, "") = "" Then Me.txtBox2 = rs![fieldName2]
    ...
    ...
    If Nz(Me.txtBoxN.Value, "") = "" Then Me.txtBoxN = rs![fieldNameN]

    Set rs = Nothing
    Set qry = Nothing
    Set db = Nothing

这篇关于MS Access:如何将数据从查询加载到窗体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！