Solved

Trying to get main form to requery

Posted on 2014-12-30
9
129 Views
Last Modified: 2014-12-31
I have this code in an afterupdate event of a combobox on a sub-form.  I want it to requery or refresh or whatever the data on the main form's current record but instead it is bringing the user back to the first record.  What am I doing wrong?

Private Sub cboClass_AfterUpdate()
    Forms!frmMainForm.Requery
End Sub

Open in new window

0
Comment
Question by:SteveL13
  • 3
  • 3
  • 3
9 Comments
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 40524833
A Requery will do that.  You need to store the unique ID field of the current record and after the requery move the pointer back to that record as shown below ...

Dim rst as Recordset
Dim varCurrentID

set rst = me.RecordsetClone
varCurrentId = Me.ID
Forms!frmMainForm.Requery
rst.FindFirst "[ID] = " & varCurrentID
Me.Bookmark = rst.Bookmark


ET
0
 
LVL 84
ID: 40525185
Why would you requery the datasource after selecting a value in a combo? Requery refetches the recordset from the datasource, so I doubt you want to do that.

What's the goal of your combo? Are you using it for record selections? If so, and if the record will be located somewhere in the form's Recordsource, you can use the Bookmark method.
0
 

Author Comment

by:SteveL13
ID: 40525418
The reason I need to requery the datasource of the main form is that after the combobox on the sub-form is updated there is a field on the main form that gets it's value from this code in the oncurrent event of the main form:

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim strMedClasses As String
    
    Set db = CurrentDb
    strSQL = "SELECT DISTINCT tblLOCALTempDrChromoPatientAndMedImport.ChronoID, tblLOCALTempDrChromoPatientAndMedImport.Class FROM tblLOCALTempDrChromoPatientAndMedImport WHERE tblLOCALTempDrChromoPatientAndMedImport.ChronoID = '" & Me.txtChronoID & "'"

    Set rs = db.OpenRecordset(strSQL)
    
    Do While Not rs.EOF
        strMedClasses = strMedClasses & ", " & rs.Fields("Class")
        rs.MoveNext
    Loop
    
    If Len(Trim(strMedClasses)) > 0 Then
        Me.txtMedClasses = Right(strMedClasses, Len(Trim(strMedClasses)) - 2)
    Else
        Me.txtMedClasses = ""
    End If
    
    Me.txtMedClasses2 = Me.txtMedClasses
    
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing

Open in new window


Using the first suggestion I am getting a "syntax error (missing operator) in expression".

Here is the after update code of the combobox on the sub-form:

Dim rst As Recordset
Dim varCurrentID

 Set rst = Me.RecordsetClone
 varCurrentID = Me.ID
 Forms!frmMainForm.Requery
 rst.FindFirst "[ID] = " & varCurrentID
 Me.Bookmark = rst.Bookmark

Open in new window

0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 84
ID: 40525438
Why don't you just run that code again when needed, from the Subform? You can add a function to the mainform, and call that function from the subform:

Me.Parent.NameOfYourFunction

If you must use the bookmark method:

Are you running this code on the SUBFORM? If so, then "Me" refers to the object in which the code is running. If you're running this on the subform, but intend to work with the recordset of the mainform, you'd have to do this:

Dim rst As Recordset
Dim varCurrentID

 Set rst = Me.Parent.RecordsetClone
 varCurrentID = Me.ID
 Forms!frmMainForm.Requery
 rst.FindFirst "[ID] = " & varCurrentID
 Me.Parent.Bookmark = rst.Bookmark
0
 

Author Comment

by:SteveL13
ID: 40525454
I like this idea:

"Why don't you just run that code again when needed, from the Subform? You can add a function to the mainform, and call that function from the subform:"

 Me.Parent.NameOfYourFunction

But how do I add the function to the main form?  And then do I just add

Me.Parent.NameOfYourFunction

to the afterupdate event of the combobox?
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40525477
You add a function to a form just like you do a standard module:

Function MyFunction()
  <code here
End Function

From there, you would call it just as you infer:

Me.Parent.MyFunction
0
 

Author Comment

by:SteveL13
ID: 40525510
Perfect.  Thank you very much.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 40525555
Yes, as Scott pointed out you will need to add the .Parent because you are requiring the main form from the sub form.  

If you want to put the code on the main form and call it from the sub form then try this ... Put this in the VBA module of your main form.


Public Function ResetCurrentRecordID()
 Dim rst as Recordset
 Dim varCurrentID

 set rst = me.RecordsetClone
 varCurrentId = Me.ID
 Forms!frmMainForm.Requery
 rst.FindFirst "[ID] = " & varCurrentID
 Me.Bookmark = rst.Bookmark

End Function  

In the AfterUpdate Event of the combobox on your sub form enter the following:

Me.Parent.ResetCurrentRecordID


ET
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 40525565
Sorry ... I did not refresh my screen before posting.

ET
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question