MS Access组合框在重新查询显示值后未获取值 [英] MS Access Combobox not taking a value after requery shows value

问题描述

我有Combobox，可让用户编辑此字段的相关值 -使用按钮的cboBEA.我决定在需要添加新值时使用NotInList例程.编辑部分可以正常工作，但NotInList部分需要在名为frmBEA_JDIR的弹出表单中提供相关字段之后接受一个值.

起初，cboBEA的重新查询无法正常工作，因此我通过将行源设置为"，然后将行源的实际SQL设置为"来对行源进行了更加有意的重置.

这是弹出表单frmBEA_JDR的保存"按钮中的代码

Private Sub cmdSave_Click()

    Select Case Me.OpenArgs
    Case "Edit"
        DoCmd.Save
        DoCmd.Close acForm, "frmBEA_JDIR"

        With Form_sfm_AddSPDistro
        .cboBEA.Requery
        .cboBESA.Requery
        .cmbPROGRAM.RowSource = ""
        .cmbPROGRAM.RowSource = "SELECT * FROM qLU_BEA_JDIR;"
        .cmbPROGRAM = .cboBEA
        End With


    Case "AddNew"


        Dim strSQL As String

        strSQL = "SELECT LU_BEA_JDIR.ID, LU_BEA_JDIR.BEA, LU_BEA_JDIR.BESA, LU_BEA_JDIR.ORGANIZATION " _
                   & "FROM LU_BEA_JDIR;"


        With Form_sfm_AddSPDistro
        'cboBEA.Requery doesn't work, so...
        .cboBEA.RowSource = ""
        .cboBEA.RowSource = strSQL

        .cboBESA.Requery
        .cboBEA.Value = Me.txtBEA
        .cmbPROGRAM.RowSource = ""
        .cmbPROGRAM.RowSource = "SELECT * FROM qLU_BEA_JDIR;"
        .cmbPROGRAM = .cboBEA
        End With

        DoCmd.Close acForm, "frmBEA_JDIR"

    End Select

End Sub

这是调用表单的NotInList事件:

Private Sub cboBEA_NotInList(NewData As String, Response As Integer)
    Dim MsgBoxAnswer As Variant

    Response = acDataErrContinue
    Me!cboBEA.Undo  'Used this to prevent the requery error caused by frmBEA_JDIR

    MsgBoxAnswer = MsgBox(NewData & " is not in the list.  Do you want to add it?", vbQuestion + vbYesNo, "Add " & NewData & "?")

    If MsgBoxAnswer = vbNo Then
        Me.cboBEA = Null
        DoCmd.GoToControl "cboBEA"
    Else

        DoCmd.OpenForm "frmBEA_JDIR", acNormal, , , acFormAdd, , "AddNew"
        Form_frmBEA_JDIR.txtBEA = NewData


    End If

End Sub

因此，根据调用此表单的方式-NotInList或Edit，我将其放在调用frmBEA_JDIR的openargs参数中.这就是我在保存"按钮中处理更新的方式.同样，编辑部分可以正常工作，但是NotInList事件中的AddNew即使在重新查询cboBEA之后也不会填充它，而且我可以在其中看到新的值.

解决方案

简而言之:不要用openargs来确定弹出窗口中的操作，而是让acFormAdd为您完成； acFormAdd将打开该表单以创建新记录.

在openargs中发送新数据.

以对话框模式打开frmBEA_JDIR.它将停止当前代码，直到关闭打开的表单.

' open frmBEA_JDIR in dialog mode to add the new data.
DoCmd.OpenForm "frmBEA_JDIR", acNormal, , , acFormAdd, acDialog, "NewData"

'data is added, now requery the dropdown
cboBEA.Requery

在没有案例编辑"部分的情况下无所事事，直到您看到它起作用为止.然后使用acFormEdit添加编辑部件.您可以检查弹出表单的数据模式，以查看它是添加还是编辑.

I have Combobox that lets users either edit the related values for this field - cboBEA using a button. I decided to use a NotInList routine whenever they want to ADD a new value. The edit part works without a hitch, but the NotInList part needs to accept a value after providing related fields in a popup form called frmBEA_JDIR

At first the requery of cboBEA wasn't working, so I did a more deliberate resetting of the rowsource by first setting it to "" then the actual SQL of the rowsource.

Here is the code in the "Save" button of the popup form frmBEA_JDR

Private Sub cmdSave_Click()

    Select Case Me.OpenArgs
    Case "Edit"
        DoCmd.Save
        DoCmd.Close acForm, "frmBEA_JDIR"

        With Form_sfm_AddSPDistro
        .cboBEA.Requery
        .cboBESA.Requery
        .cmbPROGRAM.RowSource = ""
        .cmbPROGRAM.RowSource = "SELECT * FROM qLU_BEA_JDIR;"
        .cmbPROGRAM = .cboBEA
        End With


    Case "AddNew"


        Dim strSQL As String

        strSQL = "SELECT LU_BEA_JDIR.ID, LU_BEA_JDIR.BEA, LU_BEA_JDIR.BESA, LU_BEA_JDIR.ORGANIZATION " _
                   & "FROM LU_BEA_JDIR;"


        With Form_sfm_AddSPDistro
        'cboBEA.Requery doesn't work, so...
        .cboBEA.RowSource = ""
        .cboBEA.RowSource = strSQL

        .cboBESA.Requery
        .cboBEA.Value = Me.txtBEA
        .cmbPROGRAM.RowSource = ""
        .cmbPROGRAM.RowSource = "SELECT * FROM qLU_BEA_JDIR;"
        .cmbPROGRAM = .cboBEA
        End With

        DoCmd.Close acForm, "frmBEA_JDIR"

    End Select

End Sub

Here is the NotInList event of the calling form:

Private Sub cboBEA_NotInList(NewData As String, Response As Integer)
    Dim MsgBoxAnswer As Variant

    Response = acDataErrContinue
    Me!cboBEA.Undo  'Used this to prevent the requery error caused by frmBEA_JDIR

    MsgBoxAnswer = MsgBox(NewData & " is not in the list.  Do you want to add it?", vbQuestion + vbYesNo, "Add " & NewData & "?")

    If MsgBoxAnswer = vbNo Then
        Me.cboBEA = Null
        DoCmd.GoToControl "cboBEA"
    Else

        DoCmd.OpenForm "frmBEA_JDIR", acNormal, , , acFormAdd, , "AddNew"
        Form_frmBEA_JDIR.txtBEA = NewData


    End If

End Sub

So depending on what calls this form - the NotInList or the Edit, I put it in the openargs parameter that calls frmBEA_JDIR. This is how I handle the update in the SAVE button. Again, the edit part works perfectly, but the AddNew from the NotInList event just won't populate cboBEA even after it is requeried and I can see the new value in it.

解决方案

In brief: Instead of figuring out what to do in the popup with openargs, let acFormAdd do it for you; acFormAdd will open the form to a new record.

Send the new data in openargs.

Open frmBEA_JDIR in dialog mode. It stops the current code until the opened form is closed.

' open frmBEA_JDIR in dialog mode to add the new data.
DoCmd.OpenForm "frmBEA_JDIR", acNormal, , , acFormAdd, acDialog, "NewData"

'data is added, now requery the dropdown
cboBEA.Requery

Fool around with this without the 'case edit' section until you see it working. Then add the edit part using acFormEdit. You can check the datamode of the popped-up form to see if it's add or edit.

这篇关于MS Access组合框在重新查询显示值后未获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！