Form/Subform interaction

[mbedford]

I'm building a db for tracking support issues.

The form has two lists which I want to update based on selections elsewhere in the form, and I want to open selected records in the form when selections in the lists are double clicked.

The details:
ID - Autonumber field used as support ID
cboUID - User ID
cboAssetID - Asset ID

listAssetSupportHistory
listUserSupportHistory

The lists have the following SQL code to pull their contents (this is for listAssetSupportHistory, listUserSupportHistory pulls the field tblSupport.AssetID and uses tblSupport.UID as the selection criteria):

SELECT tblSupport.ID, tblSupport.UID, tblSupport.Date, Left([tblSupport.Problem],50) AS Problem
FROM tblSupport
WHERE (((tblSupport.AssetID)=Forms!formSupport!cboAssetID))
ORDER BY tblSupport.ID;
 

Problem is a memo field in tblSupport and so I'm converting to a text field for use in the lists.

The VBA code for the form:

Private Sub cboUID_Change()
'requery User Support History list
On Error GoTo Err_Update_Click
 
    Me![listUserSupportHistory].Requery
 
Exit_Update_Click:
    Exit Sub
 
Err_Update_Click:
    MsgBox Err.Description
    Resume Exit_Update_Click
End Sub
 
Private Sub cboAsset_Change()
'requery Asset Support History list
On Error GoTo Err_Update_Click
 
    Me![listAssetSupportHistory].Requery
 
Exit_Update_Click:
    Exit Sub
 
Err_Update_Click:
    MsgBox Err.Description
    Resume Exit_Update_Click
 
End Sub
 
Private Sub ID_AfterUpdate()
On Error GoTo Err_Update_Click
 
    Me![listUserSupportHistory].Requery
    Me![listAssetSupportHistory].Requery
 
Exit_Update_Click:
    Exit Sub
 
Err_Update_Click:
    MsgBox Err.Description
    Resume Exit_Update_Click
End Sub
 
Private Sub listUserSupportHistory_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
 
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ID] = " & Me!listUserSupportHistory
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
 
Private Sub listAssetSupportHistory_AfterUpdate()
    'Set SoughtString with
    Dim rs As Object
 
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ID] = " & Me!listAssetSupportHistory
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
 

The goal:
As I'm scrolling through the records in the form, I want the two lists to update with all the records from tblSupport that correspond to cboUID or cboAssetID.

Also, when creating a new record, selecting the affected user and/or asset will show the support history for each.

Neither UID nor AssetID are required in the database as there are issues that correspond to users and not to assets and vice versa.

Making selections in cboUID and cboAssetID do update the lists appropriately, but navigating through the records does not.

I assume this means Private Sub ID_Change() isn't the correct trigger. This isn't a complete necessity as in operation the form will most frequently open with a new blank entry and most searching of the records will be based on the selections I've made while creating a new entry. But I want to know how to make it work this way to make it as user friendly as possible as well as to further my own knowledge of VBA.

Also, because I don't want to leave the initial support record accidentally, I'd like to set the trigger (criteria) for opening the record selected in either list to a doubleclick and/or enter key press.

I'd been trying to use DoubleClick as the event action trigger, but that had not been working until I changed it to Private Sub listAssetSupportHistory_AfterUpdate().

Is there a way to set it to the DoubleClick and Enter events to trigger the record search?

[NeoPa]

I suspect you're looking for the Form_OnCurrent() event procedure. Let us know if this helps :)

Welcome to Bytes!

[mbedford]

So using the OnCurrent for a form event in place of the AfterUpdate on the ID:

Private Sub formSupport_OnCurrent()
 
On Error GoTo Err_Update_Click
 
    Me![listAssetSupportHistory].Requery
    Me![listUserSupportHistory].Requery
 
Exit_Update_Click:
    Exit Sub
 
Err_Update_Click:
    MsgBox Err.Description
    Resume Exit_Update_Click
 
End Sub
 

This code doesn't work either.

The (faulty, apparently) logic I was using before was that anytime I navigated through the form, the ID field would necessarily change, and so tying the AfterUpdate event to the ID changing would thus trigger the necessary updates.

Should I tie the OnCurrent() event to something besides the entire form (formSupport)?

I've tested to make sure the requery syntax and system works by using the same action code attached to a button control.

[mbedford]

Perhaps the best question is: What is the best resource you have found for listing and describing the various event triggers and what types of controls they can be used on?

[NeoPa]

I use context-sensitive help.

Type Form in the Immediate Pane (or anywhere in your code would also work) then press F1. The Help window will open and prompt you to select Access Form (object) or just Access Form. Select the former and you will see a page open with loads of info about forms. Including a menu for Events.

[mbedford]

Ok, I found how I was misunderstanding your instructions.

I was assuming by "Form" you meant the name of my form, as in "formSupport".

So, I modified my code to use the Form control action. It's working very well now thanks.

[NeoPa]

LoL. I can see how that might be. I also didn't read your reply carefully enough, to be sure, or I might have picked it up. Never mind. Lessons learned so a good day :)