ms access form closes when running query from button

Question

I have a form with one combo boxes and ok button. When a value from combo box is selected and clicked "OK", it opens a query based on the selected value.

That is fine, but it closes the form and then opens the query. I have to again click on the form tab to select another value and run query.

Is it possible, query runs in another window while form window is still open?

For combo box

I have a code in row source like

select distinct format(columndate, 'mm-dd-yyyy') from table1

For OK Button, I have a code as below :

Private Sub Submit_Click()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Set db = CurrentDb
    Set qdf = db.QueryDefs("query")
    strSQL = "SELECT columndate," & _
      "sum(qty1)," & _
      "sum(qty2)," & _
      "sum(qty3)," & _
      "sum(qy4)" & _
      "FROM table1 " & _
      "WHERE table1.column_date = '" & Me.datefield.value & "' " & _
      "group by table1.[columndate];"

    qdf.sql = strSQL
    DoCmd.Restore
    DoCmd.OpenQuery ("query")
    DoCmd.Close acForm, "Me.Form3"

    Set qdf = Nothing
    Set db = Nothing

    Debug.Print strSQL
End Sub

I have one more question on this. The date field in fact in the format "dd-mm-yyyy" in the table, but in query it shows blank result as long as I change the format to "mm-dd-yyyy" in row sources as in the first query here

Olivier Jacot-Descombes Olivier Jacot-Descombes · Accepted Answer · 2012-07-17T18:13:28

Your OK-button click handler would have to look like this

Private Sub btnOk_Click()
    DoCmd.OpenQuery(Me!cboQuery, acViewNormal, acReadOnly)
End Sub

Also make sure that the Cancel property of your button is set to No.

ms access form closes when running query from button

1 Answers