update a subform with current data

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

placek

hello
i have a subform based on a query, and i run a procedure
that amends the query. The query is named qryBook and the
subform is named fsubBooks.

My hope is that the subform should display the contents of
the query, though this does not happen. Although the query
does update, the subform does not. In order to display the
contents of the query in the subform i have to return to
design view and then to form view. I would like the
subform to update without having to do this. Can anyone
help? My procedure is............

Private Sub Command2_Click()
Dim query As QueryDef
Dim db As Database



Set db = DBEngine(0)(0)
Set query = db.QueryDefs("qryBook")
query.SQL = "select
tblacquisitionrelation.lngacquisitionnumbercnt,
tblbookrelation.strisbn, tblbookrelation.strtitle,
tblbookrelation.strAuthor, tblbookrelation.strcategory,
tblloanrelation.dtmdateborrowed FROM tblloanrelation right
join (tblacquisitionrelation INNER JOIN tblbookrelation ON
tblacquisitionrelation.strisbn=tblbookrelation.strisbn) on
tblloanrelation.lngacquisitionnumbercnt=tblacquisitionrelat
ion.lngacquisitionnumbercnt where isnull
(tblloanrelation.dtmdatereserved) AND not isnull
(tblloanrelation.dtmdateborrowed) OR not isnull
(tblloanrelation.dtmdatereserved) AND not isnull
(tblloanrelation.dtmdateborrowed)"

Me.fsubBooks.Form.Requery
query.Close
db.Close

End Sub

Many Thanks
Martin
 
Try

Me.Requery

Also do you need the Query?

stSQL="..............

me.RecordSource=stSQL

This sets the recordsource for the form and does a requery
 
Thanks Doug, but i think i need to explain my form a bit
more. I have a form (Form3) that contains, amoung other
controls, a command button and subform (fsubBooks). I want
to requery fsubBooks when i click the command button. By
doing what you have suggested, will that actually update
the data in fsubBooks? My text book says that it will
requery Form3 as the command button is a control within
this form.
 
Back
Top