Requery a SubForm

[Guest]

Is it possible to Requery a subform, from a field within the subform? I'm
thinking not, as I keep getting an error '2118' "You must save the current
field before you run the Requery action.".

Any suggestions on how to do this without closing/reopening the form?

Thanks.

Sharkbyte

 

[John Vinson]

Is it possible to Requery a subform, from a field within the subform? I'm
thinking not, as I keep getting an error '2118' "You must save the current
field before you run the Requery action.".

What are you trying to accomplish? Requerying will move you back to
the first record in the subform's recordsource (off whatever record
you were on) anyway. Could you post your current code and explain the
context? A Requery may not be the most appropriate action!

John W. Vinson[MVP]

 

[Guest]

John:

What I have is a subform, whose results are based on the value in a
combobox, on the primary form.

Also on the subform are 2 buttons. One of those buttons modifies the
selected record in such a way that it no longer matches the criteria for the
result set on the subform.

Once the record update occurs, I want the result set to be refreshed. I am
not concerned with the cursor moving to the first record. In almost all
cases, the user will be working from the first record down.

Thanks.

Sharkbyte

 

[Guest]

Sorry. You did ask me to post the code. While I don't have it with me, the
code below is the basics of it. Just the 2 lines...

In the OnClick event, I have:

Docmd.RunSQL ("Update tblRecords set FieldA = txtFieldA where IDNumber =
txtIDNumber;")

[Forms]![PrimaryForm]![SubForm].Requery


Thanks.

Sharkbyte

 

[John Vinson]

Sorry. You did ask me to post the code. While I don't have it with me, the
code below is the basics of it. Just the 2 lines...

In the OnClick event, I have:

Docmd.RunSQL ("Update tblRecords set FieldA = txtFieldA where IDNumber =
txtIDNumber;")

[Forms]![PrimaryForm]![SubForm].Requery

Well, you'll need to do two things: save the current record on the
subform before you do this, and pass the value in txtFieldA rather
than its name to the query. Try

If Me.Dirty Then Me.Dirty = False
Docmd.RunSQL ("Update tblRecords set FieldA = '" & Me.txtFieldA _
& "' where IDNumber=" & Me.txtIDNumber & ";")

or (better) use the error-trappable Execute method:

Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.Querydef
On Error GoTo Proc_Error
If Me.Dirty Then Me.Dirty = False
strSQL = "Update tblRecords set FieldA = '" & Me.txtFieldA _
& "' where IDNumber=" & Me.txtIDNumber & ";"
Set db = CurrentDb
Set qd = db.CreateQueryDef("", strSQL) ' create an unnamed query
qd.Execute dbFailOnError
Set qd = Nothing
Me.Requery


John W. Vinson[MVP]