如何运行具有 Excel 中的顺序有序参数的访问表单并返回值

问：

这里的菜鸟。我有一个 Excel VBA 代码，旨在将 4 个值从 Excel 传递到 Access 表单并返回 2 个结果。该表单由另一个团队开发，是我们工程师使用的一种很好的表单。我的目标是，当我们在Excel中有几行数据时，使用窗体的进程来填充它们各自的返回值。

表单的顺序过程是：

1. 用户在未绑定的文本框 cmbEntry 中输入“Conn PN”，它将运行查询、qryCmbPinNumber 和 qryCmbCAVNOLst。
2. 用户在未绑定的文本框 cmbPinNmbr 中输入“Cav No”，它将运行查询、qryCmbLst、qryWrSzOptns 和 qryCmbEntryLst。
3. 用户在未绑定的 cmb 框cmbx_trmlProperty中输入“终端类型”，它会运行查询 qryCBLSEALPNCONV、qryWrCblSeal 和 qryWrSZRng1。
4. 用户在未绑定的文本框 qryWrSZRng1 中输入“线径”，它运行 qryTERMPNCONV 和 qryWrSzRng。 结果被放入文本框txtbx_trmnlPN、txtbx_MinWr、txtbx_MaxWr和txtbx_cblseal。

下面是VBA代码，其中我得到了一个对象变量或未设置块变量。除了错误之外，我不相信我有好的代码，尤其是对于所需的序列。

感谢任何支持，:)

Sub PassParametersToAccess()

    Dim AccessApp As Object
    Dim db As Object
    Dim form As Object
    Dim TermPN As Variant
    Dim cmbEntry As Variant
    Dim cmbx_trmlProperty As Variant
    Dim Wiresize As Variant
    Dim txtbox_WrSz As Variant
    Dim cmbPinNmbr As Variant
    Dim txtbx_trmnlPN As String
    Dim txtbx_cblseal As String
    Dim result1 As String
    Dim result2 As String

    Set AccessApp = CreateObject("Access.Application")

    AccessApp.OpenCurrentDatabase "C:\Users\Engineering\Terminal_Values_R5 WITH CABLE SEAL_REMOTE_PLAY.accdb"

    Set db = AccessApp.CurrentDb
    Set form = db.OpenForm("frmTrmVl")

    AccessApp.Forms("frmTrmVl").Controls("cmbEntry").Value = "7283-8854-30"
    form.Requery
    AccessApp.Forms("frmTrmVl").Controls("cmbPinNmbr").Value = "7"
    form.Requery
    AccessApp.Forms("frmTrmVl").Controls("cmbx_trmlProperty").Value = "Tin"
    AccessApp.Forms("frmTrmVl").Controls("txtbox_WrSz").Value = "0.5"
    form.Requery

    AccessApp.DoCmd.Close "frmTrmVl"

    result1 = form.Controls("txtbx_trmnlPN").Value
    result2 = form.Controls("txtbx_cblseal").Value

    AccessApp.Quit
    Set AccessApp = Nothing

    ThisWorkbook.Sheets("Sheet1").Range("E1").Value = result1
    ThisWorkbook.Sheets("Sheet1").Range("F1").Value = result2
    
End Sub

@Shahram 首先，感谢您的帮助。 我使用了代码，我无权访问表单代码。我将代码更新为以下内容： ' 选项显式

    Sub Term_PN()
    
    Dim Result1 As String
    Dim Result2 As String
    Dim AccessApp As New Access.Application
    Dim FRM As Access.Form
    
    
    AccessApp.OpenCurrentDatabase ("C:\Users\t0989sp\Documents\Engineering\FCA_Terminal_Values_R5 WITH CABLE SEAL_REMOTE_PLAY.accdb")
    
    AccessApp.Visible = True ' REQUIRED TO ENABLE FOCUS IN FORM CONTROLS
    
    AccessApp.DoCmd.OpenForm "frmTrmVl"
    Set FRM = AccessApp.Forms!frmTrmVl
    
    FRM.Controls("cmbEntry").SetFocus
    FRM.Controls("cmbEntry").Value = "7283-8854-30"
     
    FRM.Controls("cmbPinNmbr").SetFocus
    FRM.Controls("cmbPinNmbr").Value = "7"
    
    FRM.Controls("cmbx_TrmlPrprty").SetFocus
    FRM.Controls("ccmbx_TrmlPrprty").Value = "Tin"
    
    FRM.Controls("txtbox_WrSz").SetFocus
    FRM.Controls("txtbox_WrSz").Value = "0.5"
    
    FRM.BTN_Wire_Ranges.SetFocus
    FRM.BTN_Wire_Ranges_Click ' IF REQUIRED
    SendKeys "{ENTER}", True  ' IF REQUIRED
    
    Result1 = FRM.Controls("txtbx_trmnlPN").Value
    Result2 = FRM.Controls("txtbx_cblseal").Value
    
    
    ThisWorkbook.Sheets("Sheet1").Range("E1").Value = Result1
    ThisWorkbook.Sheets("Sheet1").Range("F1").Value = Result2
    
    AccessApp.CloseCurrentDatabase
    AccessApp.Quit
    
    End Sub

` 我收到错误 #2110 Access 无法将焦点移动到控件 cmbPinNmbr。如果我忽略该错误，则下一个设置的焦点将出现相同的错误。

我确实可以访问表单代码，并且我认为将变量公开。

Option Compare Database
Option Explicit
Public cmbPinNmbr As String
Public cmbx_TrmlPrprty As String
Public txtbx_WrSz As Byte
Public cmbEntry As String

Private Sub cmbEntry_AfterUpdate()
Me.cmbPinNmbr.Requery
Me.cmbPinNmbr.Visible = True
Me.lbl_PinNmbr.Visible = True
Me.cmbPinNmbr.Value = ""
Me.cmbx_TrmlPrprty.Value = ""
Me.txtbx_WrSz.Value = ""
Me.lbl_Rslts.Visible = False
Me.lbl_trmnlPN.Visible = False
Me.txtbx_trmnlPN.Visible = False
Me.lbl_MinWr.Visible = False
Me.lbl_MaxWr.Visible = False
Me.txtbx_MinWr.Visible = False
Me.txtbx_MaxWr.Visible = False
Me.cmd_WrRngbttn.Visible = False
End Sub

Private Sub cmbPinNmbr_AfterUpdate()
Me.cmbx_TrmlPrprty.Visible = True
Me.lbl_TrmlPrprty.Visible = True
Me.cmbx_TrmlPrprty.Value = ""
Me.txtbx_WrSz.Value = ""
Me.lbl_Rslts.Visible = False
Me.lbl_trmnlPN.Visible = False
Me.txtbx_trmnlPN.Visible = False
Me.lbl_MinWr.Visible = False
Me.lbl_MaxWr.Visible = False
Me.txtbx_MinWr.Visible = False
Me.txtbx_MaxWr.Visible = False
Me.cmd_WrRngbttn.Visible = False
End Sub

Private Sub cmbx_TrmlPrprty_AfterUpdate()
Me.txtbx_WrSz.Visible = True
Me.lbl_WrSz.Visible = True
Me.txtbx_WrSz.Value = ""
Me.lbl_Rslts.Visible = False
Me.lbl_trmnlPN.Visible = False
Me.txtbx_trmnlPN.Visible = False
Me.lbl_MinWr.Visible = False
Me.lbl_MaxWr.Visible = False
Me.txtbx_MinWr.Visible = False
Me.txtbx_MaxWr.Visible = False
Me.cmd_WrRngbttn.Visible = True
End Sub

Private Sub cmdClr_Click()
Me.txtbx_cblseal.Visible = False
Me.cmbPinNmbr.Visible = False
Me.lbl_PinNmbr.Visible = False
Me.cmbPinNmbr.Visible = False
Me.lbl_TrmlPrprty.Visible = False
Me.cmbx_TrmlPrprty.Visible = False
Me.lbl_WrSz.Visible = False
Me.txtbx_WrSz.Visible = False
Me.cmbEntry.Value = ""
Me.txtbx_WrSz.Value = ""
Me.cmbx_TrmlPrprty.Value = ""
Me.txtbx_WrSz.Value = ""
Me.lbl_Rslts.Visible = False
Me.lbl_trmnlPN.Visible = False
Me.txtbx_trmnlPN.Visible = False
Me.lbl_MinWr.Visible = False
Me.lbl_MaxWr.Visible = False
Me.txtbx_MinWr.Visible = False
Me.txtbx_MaxWr.Visible = False
Me.cmd_WrRngbttn.Visible = False
End Sub

Private Sub txtbx_WrSz_AfterUpdate()
Me.txtbx_trmnlPN.Requery
Me.txtbx_MinWr.Requery
Me.txtbx_MaxWr.Requery
Me.lbl_Rslts.Visible = True
Me.lbl_trmnlPN.Visible = True
Me.lbl_MinWr.Visible = True
Me.lbl_MaxWr.Visible = True
Me.txtbx_trmnlPN.Visible = True
Me.txtbx_MinWr.Visible = True
Me.txtbx_MaxWr.Visible = True
Me.txtbx_cblseal.Visible = True
End Sub

对于 excel vba 代码，它与您在添加的行中提供的代码相同：

结果 1 = FRM。控件（“txtbx_trmnlPN”）。价值 结果 2 = FRM。控件（“txtbx_cblseal”）。价值

我收到错误 40036 对象“Forms”的“Item”方法失败。

我猜可以访问表单代码的代码比其他代码更好。

再次感谢您的帮助。