Show Results in a Form or a Table

[joesmithf1]

Hi,

I have a list-box(Category) and a 'ok' button on the form.

My OK button has the following [Event Procedure]:
Private Sub OK_Click()
Me.Visible = False
DoCmd.RunSQL "Select [Barg Unit],[Medical Option],[Medical Coverage Tier] FROM RetireeCensus Group By [" & Category & "];"
End Sub

First of all, is my syntax correct in the above codes?
Second, what VBScript codes do I need to add in so that when I click the 'OK' button, a report or a table will show the query results from my 'Select' statement?

Thank you.

 

[gemma-the-husky]

you won't be able to make a button invisible while it has the focus, so that will fail, I think

not sure about the sql.

you could say docmd.openquery with the same sqlstring i think.

 

[joesmithf1]

I figured it out! Thank you! Here is the code in case someone need this in the future. What i did was to create a 'querydef' and then execute it. Now I just need to figure out how to put the query results in a report. Any suggestion?


Private Sub OK_Click()
Me.Visible = False
Dim dbs As Object
Dim qdf As Object
Dim SQL As String
Set dbs = CurrentDb
For Each qdf In dbs.querydefs
If qdf.NAME = "qryRetireeCensus" Then
dbs.querydefs.Delete qdf.NAME
dbs.querydefs.Refresh
End If
Next qdf

'Property Not Found could mean that in your query statement, 1 or more fields does not exist in the table that you are querying from
Select Case Category
Case "Barg Unit"
SQL = "Select First([Medical Option]), First([Medical Coverage Tier]), Sum([Medical Premium Amount]),Sum([Total Grant]),Sum([Health Allocation]),sum([Medicare Allocation]) FROM RetireeCensus Group By [" & Category & "];"
Case "Medical Option"
SQL = "Select First([Barg Unit]),First([Medical Coverage Tier]) FROM RetireeCensus Group By [" & Category & "];"
Case "Medical Coverage Tier"
SQL = "Select First([Barg Unit]),First([Medical Option]) FROM RetireeCensus Group By [" & Category & "];"
End Select



Set qdf = dbs.CreateQueryDef("qryRetireeCensus", SQL)
DoCmd.OpenQuery "qryRetireeCensus"

Set dbs = Nothing

End Sub