By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,969 Members | 1,522 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,969 IT Pros & Developers. It's quick & easy.

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

P: 3
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboVersionType_AfterUpdate()
  2.  
  3. Me.Refresh
  4.  
  5.   Dim Version As Long
  6.  
  7.   'set variable to current record ID
  8.     Version = Me.VersionID
  9.  
  10.      Forms![frm3EstStep1].Form![cboVersionSearch].Requery 'Main form combobox
  11.  
  12.          'return form to original record on the subform
  13.       With Me.RecordsetClone
  14.         .FindFirst "VersionID= " & Version
  15.             If .NoMatch Then 'incase another current user deletes the record
  16.             MsgBox "Record not found!", vbCritical
  17.               Else 'go to that record on the subform
  18.              Me.Bookmark = .Bookmark
  19.             End If
  20.       End With
  21.  
  22. End Sub
2 Weeks Ago #1
Share this Question
Share on Google+
7 Replies


zmbd
Expert Mod 5K+
P: 5,380
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
2 Weeks Ago #2

P: 3
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?
2 Weeks Ago #3

NeoPa
Expert Mod 15k+
P: 31,347
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 :
Expand|Select|Wrap|Line Numbers
  1. 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.
2 Weeks Ago #4

zmbd
Expert Mod 5K+
P: 5,380
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.
2 Weeks Ago #5

P: 3
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboVersionType_AfterUpdate()
  2.  
  3. Dim Version As Long
  4.  
  5.   Version = Me!VersionID
  6.   Me.Parent.Recalc
  7.   With Me.RecordsetClone
  8.     .FindFirst "[VersionID]=" & Version
  9.     If Not .NoMatch Then
  10.        If Me.Dirty Then
  11.           Me.Dirty = False
  12.        End If
  13.        Me.Bookmark = .Bookmark
  14.     End If
  15.   End With
  16.  
  17.  
  18. End Sub
1 Week Ago #6

NeoPa
Expert Mod 15k+
P: 31,347
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.
1 Week Ago #7

zmbd
Expert Mod 5K+
P: 5,380
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.
1 Week Ago #8

Post your reply

Sign in to post your reply or Sign up for a free account.