How to requery a comboBox on a main form from a subform and then return back to the s

[GeneH]

Hello,
I am trying to requery a control on a main form from an AfterUpdate event of a control on a subform and then return back to the record and control at the subform. Instead i am returned to the first record of the subform.
When i debug, it seems correct, the record i want does show correct at the findfirst statement. but at the form the first record is returned.

Any help would be greatly appreciated.

Here is my current code

Private Sub cboVersionType_AfterUpdate()
 
Me.Refresh
 
  Dim Version As Long
 
  'set variable to current record ID
    Version = Me.VersionID
 
     Forms![frm3EstStep1].Form![cboVersionSearch].Requery 'Main form combobox
 
         'return form to original record on the subform
      With Me.RecordsetClone
        .FindFirst "VersionID= " & Version
            If .NoMatch Then 'incase another current user deletes the record
            MsgBox "Record not found!", vbCritical
              Else 'go to that record on the subform
             Me.Bookmark = .Bookmark
            End If
      End With
 
End Sub

[zmbd]

Before the me.refresh you need to capture the primary key of the record on the subform.

I personally would use the tempvars collection to store the value.

Once you have that value then you can do the findfirst method to move the pointer to the record -AFTER you do the me.(refresh/requery)

You should also be aware that me.requery and me.refresh do NOT do the same thing:

form.refresh method: (...)In an Access database, the Refresh method shows only changes made to records in the current set. Because the Refresh method doesn't actually requery the database, the current set won't include records that have been added or exclude records that have been deleted since the database was last requeried, nor will it exclude records that no longer satisfy the criteria of the query or filter. To requery the database, use the Requery method. When the record source for a form is requeried, the current set of records will accurately reflect all data in the record source. (...)

You might find this reference helpful too:
http://allenbrowne.com/ser-29.html

[GeneH]

Thank you zmbd,
I'm not sure i understand the tempvars, I'll read up on them.

Could you post the code as you would do it?

[NeoPa]

Your explanation and question make no mention of requerying or refreshing the data in the subform yet that's the first thing in your code.

It seems likely that the problem you're describing is caused by exactly that discrepancy.

GeneH:
Could you post the code as you would do it?

No. That's not really helpful. It just saves you the effort of understanding your own work while we're here to help you do just that.

Have a crack at it yourself then post how and why if you fail.

Don't get me wrong - you've done a great job so far with your first question here. We'd like to make sure you get the best help we can offer. That does not equate to doing it for you, but rather helping you to understand what you're doing and why it's not working as you might expect.

It may well be that you need to have some clever code that returns the selection to the correct record. On the other hand it may be a complete red-herring based on your code including unnecessary lines.

By the way, it may be helpful to know that referencing a parent form - that is one that contains a SubForm control holding the current Form - can be easily and most simply achieved with a reference in the form :

Me.Parent.Blah

So, I suspect your reference to Forms![frm3EstStep1].Form![cboVersionSearch].Requery could actually be written instead as Me.Parent.cboVersionSearch.Requery. All Forms have a .Form property but they only refer back to the same Form object so they're entirely redundant. Where a .Form property is important is for an item such as a SubForm control. These are not themselves Forms, but they do contain one. Hence the need to refer to them separately.

[zmbd]

Good Morning GeneH
Please check your bytes.com Inbox
I sent you a copy of a boilerplate with several links to reference sites I've found useful over the years.
Towards the bottom of that list are two links dealing with how to reference subforms, their properties, and controls.

[GeneH]

Hello zmbd,
Thanks for your response. I'm still unsure of the tempVars and how to use them. After ascertaing the difference between requery and recalc I the following works as desired.

Private Sub cboVersionType_AfterUpdate()
 
Dim Version As Long
 
  Version = Me!VersionID
  Me.Parent.Recalc
  With Me.RecordsetClone
    .FindFirst "[VersionID]=" & Version
    If Not .NoMatch Then
       If Me.Dirty Then
          Me.Dirty = False
       End If
       Me.Bookmark = .Bookmark
    End If
  End With
 
 
End Sub

[NeoPa]

We suggest Requery()/Refresh() and you come back with Recalc() instead. Inspired if it works. I've been working in Access quite a while now and wasn't aware of that one so you've taught me a new trick :-)

Do check it works in all cases though. There are differences between the three methods so make sure you use the one most appropriate for your needs.

[zmbd]

Recalc Method [Access 2003 VBA Language Reference]
Using this method is equivalent to pressing the F9 key when a form has the focus. You can use this method to recalculate the values of controls that depend on other fields for which the contents may have changed.

Sorry, was traveling between states this past few days...

So long as GeneH is concerned with ONLY the calculated controls on the form then this will work; however, if we're modifying, adding, and/or deleting records this is NOT a reliable method of updating the form.