"Set value" on combo box when form opens does not update subforms

[emorris1000]

Wasn't sure how to write that title more succinctly

Anyways. I have a problem with a form I have. It is set to open using a context menu from a list box. When the form opens a combo box has it's value set to what was selected in the list box.

Currently this is done through the brute force macro of "Set Value" yada yada.

Within the form there are a couple of list boxes and subforms. The listboxes use an ad-hoc query that references the value in the combo-box, and the subforms use the master/child relationship with the combo-box.

Here's my problem. When the form opens the listboxes work correctly, the query finds the value set in the combo box and show the right data. The subforms, however, don't. If I open the combo box and reselect the value that the "set value" method sent it the subforms update correctly.

I know the reason for this is that when I send the value to the combo box like this it doesn't actually select the record that the subforms' master/child relationship recognizes, it's just a value typed in. The question then is how do I get the combo box to actually select the record that it is being sent?

edit:

Some more information. The combobox is unbound and is used for searching. The standard afterupdate() event is put in for this

    Dim rs As Object
    
'     Find General Data Record that Matches CatID
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[SupCatID] = '" & Me![CombCatalystLookup] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Now I tried adding this to a function called from the macro to try and get it to work

'sets the combobox value to the item I want
[Forms]![Catalyst Data]![CombCatalystLookup].Value = [Forms]![Switchboard]![lboxCatalyst].Value
 
dim rs as recordset
'moves the form to the value in the combo box
Set rs = [Forms]![Catalyst Data].Recordset.Clone
rs.FindFirst "[SupCatID] = '" & [Forms]![Catalyst Data]![CombCatalystLookup] & "'"
    If Not rs.EOF Then [Forms]![Catalyst Data].Bookmark = rs.Bookmark

that doesn't work

then I tried to get it to search directly to the value found in the listbox

    Set rs = [Forms]![Catalyst Data].Recordset.Clone
    rs.FindFirst "[SupCatID] = '" & [Forms]![Switchboard]![lboxCatalyst].Value & "'"
    If Not rs.EOF Then [Forms]![Catalyst Data].Bookmark = rs.Bookmark[/code[
 
that doesn't work either
 
I need the combo-box to fire the after update event somehow....

 

[jzwp22]

I'm not sure if this will work but have you tried to just requery the subform in your macro that opens the form and passes the value to the combo box? The requery would go at the very end of the macro.

 

[emorris1000]

Actually, ironically, I did have a form requery in the macro and that was what caused the problem (after I added the recordset code).

When I was using the recordset code with the bookmark (ie. standard unbound combo-box after update event on a search form) the code was setting the bookmark for the form to find the appropriate record (so the subforms could link to it), but the 'requery' command has a side affect of clearing all bookmarks.

I removed the blanket requery and only requeried the things I needed to (the listboxes built on ad-hoc queries) and problem solved.

Alls well that ends well.

 

[jzwp22]

Glad you got it worked out!