Requery doesn't work in form

[cadoltt]

Hello everybody,

I have a form based on a query ('qry_1'). There is an option group in the form ('my_opgrp') clicking which causes the query to change (one option is to select all records, the other to select a subset).


Here is the code (simplified) I use for the OnClick event of the option group:

Private Sub my_opgrp_Click()

  Dim sSQL As String

  If my_opgrp.Value = 2 Then
    sSQL = "SELECT * FROM tbl_1;" 'all records
  Else  
    sSQL = "SELECT * FROM tbl_1 WHERE var1=1;" 'subset
  End If

  Call CreateQuery(sSQL, "qry_1")

  Me.Requery

End Sub

For some reason the form doesn't requery despite the fact the query changes (the sub CreateQuery works, this is not the first time I use it). If I close the form and the reopen it, it displays the right set or records (corresponding to the value of the option group before closing).

Can anyone help me resolve this?

Thank you,
Alex

 

[Ed2020]

If you replace:

Call CreateQuery(sSQL, "qry_1")

with:

Me.Recordsource=sSQL

Does that work?

Ed Metcalfe.

Please do not feed the trolls.....

 

[cadoltt]

Ed,

It works. Surprisingly to me it works even if I remove Me.Requery.

Thank Heaven there are always people who know more than you...

Thanks again,
Alex

 

[Ed2020]

Thank Heaven there are always people who know more than you...

Ah yes, but I only posted that suggestion because I couldn't think why it wasn't working when you amended the querydef's SQL.

Me.Recordet.Requery might work in your original code.

As a side note amending querydefs' SQL at runtime is best avoided if possible - it contributes to database bloat.

Ed Metcalfe.

Please do not feed the trolls.....

 

[cadoltt]

Ed,

Honesly, I started playing with Recordsource before your post, I did this way:

Call CreateQuery(sSQL, "qry_1")
Me.Recordsource=qry_1

and it statrted working.


However I took your suggestion because it simplifies things. Now, when you warned me about changing queries on the fly I'll stick to your method even stronger.

Thank you again,
Alex

 

[TheAceMan1]

How are ya cadoltt . . .

[blue]Any time you write to the [blue]Recordsource[/blue] property of a form, [purple]you automatically requery![/purple][/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[cadoltt]

Hi TheAceMan1!

'How To Get Good Answers To Your Questions' - is a good compilation of what's needed to make people answer questions, I always try my best to follow the advices.

And thank you for info in the quote.

Alex