Requery problem

Discussion in 'Access Forms Coding' started by Beetle, Sep 23, 2008.

  1. Beetle

    Beetle Guest

    I'm sure I'm missing something simple here, but I'm just not seeing it
    right now.

    A2003. I have a form which uses a query as it's recordset. I have a command
    button on the form that resorts the records when clicked. The code all works
    fine except that the form won't requery. The stored query gets the new SQL
    an if I open it directly it shows the correct results, but the form won't
    show the
    new query records until I close and reopen it, even with Me.Requery in the
    Code (example) is as follows;

    Dim strSQL As String
    Dim qdf As QueryDef

    strSQL = "blah blah blah a bunch of SQL"

    set qdf = Currentdb.QueryDefs ("qryCustomerSort")

    qdf.SQL = strSQL
    Me.Requery '<<< this doesn't work

    Set qdf = Nothing

    Like I said, I'm probably missing something completely obvious, but so far
    all I'm getting is bruises on my forehead.

    Any ideas?
    Beetle, Sep 23, 2008
    1. Advertisements

  2. Beetle

    Maurice Guest

    If me.requery doesn't work why not reset the recordsource to see if that helps.

    I assume the querydef is the recordsource for the form.
    After you've created the querydef set the recordsource to "" and then
    re-attach the recordsource by setting it to the querydef.

    and the me.requery for a final tick
    Maurice, Sep 23, 2008
    1. Advertisements

  3. Beetle

    Beetle Guest

    Yeah, that worked. I just had to do;

    qdf.SQL = strSQL
    Me.RecordSource = "qryCustomerSort"

    No need to set the RecordSource to "" or to requery.

    Thanks for the jump.
    Beetle, Sep 24, 2008
  4. Beetle

    jlguitar287 Guest

    Beetle wrote on 09/23/2008 15:35 ET
    I have exactly the same problem, can you please help
    It should be super simple to understand

    Option Compare Databas

    Private Sub btnRun_Click(

    Dim db As DAO.Databas
    Dim qdf As DAO.QueryDe
    Dim varItem As Varian
    Dim strCriteria As Strin
    Dim strSQL As Strin

    Set db = CurrentDb(
    Set qdf = db.QueryDefs("Query"
    For Each varItem In Me!listBox.ItemsSelecte
    strCriteria = strCriteria & "," & Me!listBox.ItemData(varItem
    & "
    Next varIte
    If Len(strCriteria) = 0 The
    MsgBox "You did not select anything."
    , vbExclamation, "Nothing to find!

    Exit Su
    End I

    strCriteria = Right(strCriteria, Len(strCriteria) - 1
    strSQL = "SELECT * FROM TestTable " &
    "WHERE TestTable.Field1 IN(" & strCriteria & ");

    strSQL = "SELECT * FROM TestTable Where TestTable.Field1 Lik
    """ & strCriteria & """;
    qdf.SQL = strSQ
    MsgBox strSQ

    DoCmd.OpenQuery "Query
    Me.RecordSource = "Query" '<-----i press the button to query, i
    goes to the correct form an
    everything, but i go back to the form, select something ne
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''from my lis
    box, but it just stays the same as the first query..
    Set db = Nothin
    Set qdf = Nothing
    jlguitar287, Jun 13, 2012
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.