Run Query On Form Open Event Procedure???

[cschuette]

I think the vacation vacated my brain...I'm trying to have a certain query run (update) when I open a form using an On Open Event Procedure. On this form is a button to print a report using the data acquired from the query. When I change info on the form and then need to print this report I need updated query info and I can't get the syntax for the VB code correct. Can anyone help me with the code to do this? I just need to run a specific query On Open of a form using an Event Procedure. Any help would be appreciated!!! Thanks a million.

Chad

 

[bsupport]

cschuette,

Try the following in the On Openform event:

Dim stDocName As String

stDocName = "Name Of Your Query Here"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Note: acNormal and acEdit are parameters, complete yours as necessary.

 

[LonnieJohnson]

DoCmd.SetWarnings (0)
DoCmd.OpenQuery "QueryNameHere"

 

[cschuette]

Thanks for your input, I ended up finding a way to work this before I checked back here. Below is a quick synopsis of what I did:

First of all I made the query a make-table query, I filtered the data that I needed and output this to another table. I then used this output table as my record source for the report. I used a button on the form and ran a sub routine (ie. Call SelectCase)

Private Sub cmdPreviewStatus_Click()

Call SelectCase

End Sub

I then have a Function set in my code module for this button (cmdPreviewStatus) as listed below:

Function cmdPreviewStatus(sql As String, rptName As String, stDocName As String)

On Error GoTo Err_cmdPreviewStatus

stDocName = "InProcessDCNQuery"
DoCmd.OpenQuery stDocName, acNormal, acEdit

SendKeys ("{%}R+{enter}&quot, True 'Save record
DoCmd.OpenReport rptName, acPreview, , sql



Exit_cmdPreviewStatus:
Exit Function

Err_cmdPreviewStatus:
MsgBox Err.Description
Resume Exit_cmdPreviewStatus

End Function

I also have a Function SelectCase (which I mentioned in my subroutine earlier) which will perform functions based upon which form is open:

Function SelectCase()

On Error Resume Next
'Set form and control variables
Set frm = Screen.ActiveControl.Parent
Set ctl = Screen.ActiveControl

'Check to see if the control with focus is in a subform, if so, find parent form
If frm.CurrentView = 2 Then Set frm = frm.Parent 'Datasheet view check
rptName = frm.Name

'Determine what form is currently being used and set filter (sql statement)
Select Case frm.Name

and the case for my particular form:

Case "DCN"
sql = "[DCN].[DCN] = " & Forms![DCN].[DCN].Value
If ctl.Name = "cmdPrintBlank" Then sql = "[DCN].[DCN] = 0": Set ctl = frm.Controls.Item("cmdPrint&quot
If ctl.Name = "cmdPreviewStatus" Then sql = "": Set ctl = frm.Controls.Item("cmdPreviewStatus&quot
rptName = "DCNStatusReport"

This is all wrapped up with a Call command for my button based upon my form name.

'Determine what control has focus to run appropriate function
Select Case ctl.Name

and

Case "cmdPreviewStatus"
Call cmdPreviewStatus(sql, rptName, stDocName)


I know that there are better ways to do this but due to the limited time and large framework of this database it was the easiest way I could think of. If you have any suggestions or questions please contact me, and thanks for your help.

Chad