Passing a new CustID back to a ComboBox and Requery/Refresh the form.

[SueHopson]

Ok, sorry folks, but I simply can't wrap my head around this no matter how much I research

I have a ComboBox cmb_CxLookup whose row source is

SELECT qry_CustMAIN.CustID, qry_CustMAIN.CustName, * FROM qry_CustMAIN ORDER BY qry_CustMAIN.CustName;

The Bound column is 1 CustID, which is hidden from the user

My AfterUpdate code is

Private Sub cmb_CxLookup_AfterUpdate()
    DoCmd.SearchForRecord acDataForm, "frmMain", acFirst, "[CustID] = " & str(Nz([Screen].[ActiveControl], 0))
    sbfmQuotesRefresh
End Sub

This works perfectly on my main form frmMAIN (record Source is qry_CustMAIN) which has 2 subforms that display with the correct customer information once the selection has been made from ComboBox cmb_CxLookup
- sbfm_CustDetails
- lstQuoteSummary

To keep users from accidentally deleting customer information on the frmMAIN, I have locked sbfm_CustDetails and users must open frmCustMAIN (record Source is also qry_CustMAIN) from one of 2 buttons New Customer or Edit Customer. Edit works fine, but when I add a new customer (new CustID created) and close the form I'm stalling out.

My OnClose event is below

Private Sub Form_Close()
'Requery the Main Customer List
    DoCmd.OpenQuery "qry_CustMAIN"
    DoCmd.Requery 'the record shows at this stage
    DoCmd.Close
'Requery Customer List and set value to new record
    Forms![frmMAIN].Requery
    Forms![frmMAIN].cmb_CxLookup.Requery
    Forms![frmMAIN].cmb_CxLookup.Value = Me.CustID
    Forms![frmMAIN].cmb_CxLookup.setFocus
End Sub

When the form closes and the focus returns to frmMAIN the customer is indeed selected in the cmb_CxLookup as expected, but I still need to click to get the AfterUpdate code to run for the new company to display in the subforms. Is there any way to automate this?

Any help, as always is greatly appreciated.

[NeoPa]

Hi Sue.

I've not got a system to check against at the moment, but I seem to recall that Events don't get triggered in Access Forms if they're set in code. I may be wrong, but it's ringing a bell somewhere.

Anyway, to get around it you can call the Event procedure directly from your code.

Actually, there's a more appropriate way to handle this. Write a separate procedure to do the work required after the update happens and call this same procedure from both the Event procedure and the code where you set the value directly.

It's wise to avoid calling Event procedures directly in code as they are predefined to work in reaction to the specific event firing - so having code running in a separate context, that is expected to run in a specific context, is likely to lead to confusion. If not for you then at least for someone who may follow you. Not a good approach.

[SueHopson]

Hey Neo,

After reading your comments, I decided a different approach would be better and instead called the sbfm edits from within the subform itself. I need the subform locked until the user makes a conscious decision to edit them because well, my users aren't the most attentive...

All 3 subforms begin with sbfm_Cx and I have all 3 locked on open. For space they are set on a Tab Control
tab_Customers
stb_CxInfo
stb_CxContacts
stb_CxNotes

The code below works great when I am editing within the subform itself, but I have a new problem when accessing the sbfm from the frmMain

Private Sub CxSbfmUnlock()
Dim ctrl As Control
Dim CxEditClr As Long
    CxEditClr = RGB(230, 185, 184)
    Me.Detail.BackColor = CxEditClr
    Me.AllowEdits = True
    Me.AllowDeletions = True
    Me.AllowAdditions = True
End Sub
 
Private Sub CxSbfmLock()
Dim ctrl As Control
Dim CxRtnClr As Long
    CxRtnClr = RGB(242, 220, 219)
    Me.Detail.BackColor = CxRtnClr
    Me.AllowEdits = False
    Me.AllowDeletions = False
    Me.AllowAdditions = False
End Sub
 
Private Sub btn_CxEdit_Click()
    CxSbfmUnlock
End Sub
 
Private Sub btn_CxNew_Click()
    CxSbfmUnlock
    DoCmd.GoToRecord , , acNewRec
End Sub

The Allow functions are failing when I try and access the sbfm from the frmMain (the records stay locked). I know I can manually change the Unlock code to reference each sbfm individually but I run into the same error with the Allow fields failing.

My research leads me to believe that there is something in the relationship of the Controls between the form and subform that I am missing, but my understanding is still marginal at best. I've spent the last 3 days playing with the code below to see if there is a way to unlock all three forms at once on edit/new and have the Allow to function.

Dim ctl As Control
For Each ctl In Me.Controls

As always - all thoughts appreciated.

[NeoPa]

Hi Sue.

It's late for me here so I'll just post a link (Referring to Items on a Sub-Form) as I suspect you may be confusing the SubForm control with the Form that may, or may not, be held within that control.

Let me know if that unlocks your thinking or if we need to delve further.