Combo Box Event advice (1 Viewer)

[PNGBill]

Hi Forum, access 2010 accdb.
I have a main form and continuous sub form.
Left to Right on the subform, one control, if populated with "Refinance" and another control if populated with 0 (zero) then we want a combo box to be activated and allow data to be selected.

How do I setup the events for this ??
Can I use the On Click event of the combo box ? It doesn't appear to work.

Would the After Update event of the 2nd control (if zero) be a good place?

 

[jdraw]

I don't have 2010, but thinking as I type...

Could you start with the combo not visible
then, if your conditions are met, make it visible (and enabled)
then the user can select the combo value as needed.

Just my thoughts.

 

[PNGBill]

Thanks jdraw, That may well resolve the seeing it or not but first I have to get it to Kick IN ??

I just tried the After Update of the "zero" control but it doesn't work as this control is not actually user updated.
The value is provided by a function so I assume this is why the After Update event doesn't fire any activity.

 

[PNGBill]

I have resolved some of the issues.
The combo box event to use is the On Enter event of the last of the three combo boxes.
It appears the On Click event only runs the combo box itself ie, if you want to alter the control properties, you need to do this with the On Enter event and then the On Click will work as normal.

Here is my On Enter code

Private Sub cboRefinanceID_Enter()
   On Error GoTo cboRefinanceID_Enter_Error
 
     'check data on record to determine if RefinanceID is required
    If Me.cboPayMethod = "Refinance" Then
        If Me.txtRefinanceID = 0 Then                       'Refinance ID is required
            Dim dbs As DAO.Database, rst As DAO.Recordset
            Dim strSQL As String
            Dim lngOldLoanID As Long        'Loan ID to be Refinanced
            Set dbs = CurrentDb
                'populate variables
            lngOldLoanID = Me!cboLoanID
                'Check if SID exists in TblRefinance Record for this LoanID and SID combination
            Set rst = dbs.OpenRecordset("SELECT TblRefinance.RefinanceID As RefID, TblRefinance.RefinanceAmount " & _
                    "FROM TBLLOAN LEFT JOIN TblRefinance ON TBLLOAN.LDPK = TblRefinance.OldLoanID " & _
                    "WHERE (((TBLLOAN.LDPK)=" & lngOldLoanID & ") AND ((TblRefinance.RefinanceSID) Is Null));", dbOpenDynaset)
            If rst.RecordCount > 0 Then             'Record exists. Populate form control
                'add code to set control properties and populate
                'No data in Form Control. Record exists in TblRefinance but SID not recorded in TblRefinance - allow user to populate the combo box
                With Me.cboRefinanceID
                    .RowSource = "SELECT TblRefinance.RefinanceID As RefID, TblRefinance.RefinanceAmount " & _
                    "FROM TBLLOAN LEFT JOIN TblRefinance ON TBLLOAN.LDPK = TblRefinance.OldLoanID " & _
                    "WHERE (((TBLLOAN.LDPK)=" & lngOldLoanID & ") AND ((TblRefinance.RefinanceSID) Is Null));"
                    .ColumnCount = 2
                    .ColumnWidths = "1cm;1cm"
                    .BoundColumn = 1
                End With
            Else
                MsgBox "No record exists of this loan to be Refinanced. Check your Loan Application and try again."
            End If
        End If
    End If
 
    'Cleanup
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
   On Error GoTo 0
   Exit Sub
cboRefinanceID_Enter_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cboRefinanceID_Enter of VBA Document Form_frmBankStatementsRepaysubfrm"
End Sub

Here is my After Update code

Private Sub cboRefinanceID_AfterUpdate()
   On Error GoTo cboRefinanceID_AfterUpdate_Error
    Dim RefinanceRef As Integer, StatementRef As Integer
    Dim strSQL As String
 
    RefinanceRef = Me.cboRefinanceID
    StatementRef = Me.Parent!txtStatementID
    If RefinanceRef & "" <> "" Then
        DoCmd.SetWarnings False
        strSQL = "UPDATE TblRefinance SET TblRefinance.RefinanceSID = " & StatementRef & " " & _
            "WHERE (((TblRefinance.RefinanceID)= " & RefinanceRef & "));"
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
            'disable combobox
    Me.cboRefinanceID.Locked = True
    Me.cboRefinanceID.Enabled = False
 
    End If
 
   On Error GoTo 0
   Exit Sub
cboRefinanceID_AfterUpdate_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cboRefinanceID_AfterUpdate of VBA Document Form_frmBankStatementsRepaysubfrm"
End Sub

The issue now is to clear the recordset for the combo box.

After I click and perform the task, the value remains and as it is a continuous Form, it is plastered on every record and if you scroll through the main form, it still appears.

To make the control invisible, I fear would then prevent it from being used next time ?