Subform not updating

  • Thread starter Thread starter placek
  • Start date Start date
P

placek

Hello

I'm hoping someone can show me the error of my ways. I'm
pretty inexperienced in VBA and i hope one of you guys can
push me in the right direction.

TblBorrowerRelation has two fields: lngBorrowerNumberCnt
and strBorrowerName. QryBorrowerRelation is based on
tblBorrowerRelation.

Form5 contains a text box, command button and subform. The
subform is based on qryBorrowerRelation. The user inputs a
Borrower Number in the text box and clicks the command
button. The event handler for the command button updates
qryBorrowerRelation. The code is as follows.

Private Sub Command0_Click()
Dim dbDatabase As Database
Dim qdfQueryDef As QueryDef
Dim varText_Box As Variant



varText_Box = Text5.Value
Set dbDatabase = DBEngine(0)(0)
Set qdfQueryDef = dbDatabase.QueryDefs
("qryBorrowerRelation")
qdfQueryDef.SQL = "select * from tblBorrowerRelation where
tblBorrowerRelation.lngBorrowerNumberCnt=" & varText_Box

End Sub

The problem is that the sub form does not update after the
command button is clicked. The only way i can get the sub
form to update is by going into design view and then
returning to form view. How can i get the sub form to
update after the command button is clicked (without having
to return to design view)? Any help is much appreciated.

Martin
 
You need to Requery the Subform Control --

Me.sbfYourSub.Requery.

Check Help for details on Requery.

However, if, as it appears, the BorrowerNumber appears in the main Form and
in the RecordSource of the Form embedded in the Subform Control, you may be
able to do this automatically in a much simpler fashion, simply by
specifying the Control on the main form as the LinkMasterFields property and
the Field in the Form embedded in the Subform Control as the LinkChildFields
property of the Subform Control.

The approach you use may provide a performance edge if the data being shown
in the subform is a subset of a very large set of data. With modest amounts
of data, the performance impact will be negligible to small.

Larry Linson
Microsoft Access MVP
 
Hi Larry

Thanks for replying. However, using me.subform.requery
does not do anything new. I still need to return to design
view and then to form view to update the subform.

This is quite a strange problem. I have searched numerous
websites for the solution and they all say what you have.
Other forum members have also suggested the same as you as
i have posted this question before. Alas, i think this one
has got the better of me. Have you got any more
suggestions?

Thanks in advance, Martin
 
Back
Top