Refresh subform

Vassago · Apr 2, 2003

This may be a fairly simple question. I have learned Access on my own(and with the help of everyone here) so don't know all the simple stuff.

I have a form with a few search options that build a query when a button is clicked. I have a subform that is bound to the query that is built by the button. What I would like is for the subform to automatically refresh when the button is clicked after the new query is built. I have tried doing

frm_subform.refresh

but the subform still displays the old information. The only thing that seems to work is to reopen the form. This is not what I want. There has to be a way to refresh this subform, do you have the answer? Please help!

Thanks in advance,

Vassago

WayneRyan · Apr 2, 2003

Vassago,

Forms![YourMainForm]![YourSubform].Requery
Forms![YourMainForm]![YourSubform].Refresh

Wayne

Vassago · Apr 2, 2003

Thanks for the response. I tried your code and recieved the following error:

Run-Time Error 438

Object doesn't support this property or method.

I got this error on the second line of that code.

Any ideas?

Thanks!

KevinM · Apr 2, 2003

Try the first of Wayne's lines of code instead of both.

Vassago · Apr 2, 2003

Thanks for the reply

I tried just the first line, and it did not work. Any other ideas? I am in great need of this.

Vassago

KevinM · Apr 2, 2003

Works on mine ok.

Check your spelling & syntax or post code or sample db

Vassago · Apr 2, 2003

Here is a sample of my database. It's not complete yet (obviously). Please help me in figuring this out.

Try selecting "K" from the software list and hit <Search>

What I was is for when you enter your options and click <Search>, it will refresh the subform which is bound to the temp query, so that new results are shown.

Thanks!

Vassago

P.S. NOt everything works yet.

Vassago · Apr 2, 2003

Sorry, forgot the db.

WayneRyan · Apr 3, 2003

Vassago,

I took a look at your db.

When you come into the form qryInfoPull gets ALL.

If you change to 'K', the subform does requery, but still shows
ALL.

If you exit and reenter the form, it will show just the 'K'!

If you look at the query, before the subform's requery, it is OK!

The form appears to be using the query in the state it was when
the form opened.

I even tried setting the subform recordsource to Null and
then set it back to qryInfoPull, but that changed nothing.

I tried DoEvents, Refresh and everything I could think of.

I don't use querydefs, I just use queries statically bound to
search fields, or just assign a SQL string at runtime.

I do recall a post recently about "changes to records not
sticking" and I think it was similar to this.

I'm sure the final solution will be something simple. I'll keep
looking ...

good luck,
Wayne

Rich · Apr 3, 2003

You have to delete the original query

'*** delete the previous query
db.QueryDefs.Delete "qryMyQuery"
Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)

WayneRyan · Apr 3, 2003

Rich & Vassago,

This is now driving me nuts too!

I tried this on his db and it won't properly requery the subform
unless you close the form and reenter it.

Code:

dbNm.QueryDefs.Delete "qryInfoPull"
Set qryDef = dbNm.CreateQueryDef("qryInfoPull", strSQL & " " & strWhere & "" & strOrder)
qryDef.Close
Set qryDef = Nothing
Forms![frm_software]![frm_softwaresub].Requery

Wayne

Jon K · Apr 3, 2003

Instead of a Requery, reset the Record Source of the subform. That is change the last line of the original code to:-

Forms![frm_software]![frm_softwaresub].Form.RecordSource = "qryInfoPull"

WayneRyan · Apr 3, 2003

Jon,

I thought that I had done that. It works, here's the final code.

Code:

dbNm.QueryDefs.Delete "qryInfoPull"
Set qryDef = dbNm.CreateQueryDef("qryInfoPull", strSQL & " " & strWhere & "" & strOrder)
qryDef.Close
Set qryDef = Nothing
Forms![frm_software]![frm_softwaresub].Form.RecordSource = "qryInfoPull"
Forms![frm_software]![frm_softwaresub].Requery

Wayne

Vassago · Apr 3, 2003

Thanks a lot guys! Couldn't have done it without you, that's for sure.

Refresh subform

Vassago

Former Staff Turned AWF Retiree

WayneRyan

AWF VIP

Vassago

Former Staff Turned AWF Retiree

KevinM

Registered User.

Vassago

Former Staff Turned AWF Retiree

KevinM

Registered User.

Vassago

Former Staff Turned AWF Retiree

Vassago

Former Staff Turned AWF Retiree

Attachments

WayneRyan

AWF VIP

Rich

Guest

WayneRyan

AWF VIP

Jon K

Registered User.

WayneRyan

AWF VIP

Vassago

Former Staff Turned AWF Retiree

Similar threads

Users who are viewing this thread