SteveL13
asked on
Trying to get main form to requery
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
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.
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.
ASKER
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:
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 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
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
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.NameOfYourFuncti on
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
Me.Parent.NameOfYourFuncti
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
ASKER
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.NameOfYourFuncti on
But how do I add the function to the main form? And then do I just add
Me.Parent.NameOfYourFuncti on
to the afterupdate event of the combobox?
"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.NameOfYourFuncti
But how do I add the function to the main form? And then do I just add
Me.Parent.NameOfYourFuncti
to the afterupdate event of the combobox?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect. Thank you very much.
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.ResetCurrentReco rdID
ET
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.ResetCurrentReco
ET
Sorry ... I did not refresh my screen before posting.
ET
ET
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