Query in vba

[bjaj@vestas.com]

Hi
I need some help to my VBA code.

I want to open a query in Edit mode
Change a parameter and then save the qyery and close it.

A)I now how to do open the query in editmode
docmd.openquery ect ect.

B)I dont now how to change the parameter

C)but I now how to save the qyery and close it.

Can anyone help me to do the B) part ?

best regards
Ronald

[Smartin]

Re: Query in vba

bjaj@vestas.com wrote:[color=blue]
> Hi
> I need some help to my VBA code.
>
> I want to open a query in Edit mode
> Change a parameter and then save the qyery and close it.
>
> A)I now how to do open the query in editmode
> docmd.openquery ect ect.
>
> B)I dont now how to change the parameter
>
> C)but I now how to save the qyery and close it.
>
> Can anyone help me to do the B) part ?
>
> best regards
> Ronald
>[/color]

Assuming your query is called "MyQuery" here's one way (I'm sure there
are others).

==== begin code
Sub ChangeQuery()
Dim oDB As Database
Dim oQuery As QueryDef
Set oDB = CurrentDb
Set oQuery = oDB.QueryDefs(" MyQuery")
oQuery.SQL = (put the new SQL statement here)
Set oQuery = Nothing
Set oDB = Nothing
End Sub
==== end code

HTH
--
Smartin

[Danny J. Lesandrini]

Re: Query in vba

Why not make the query parameter dynamic?

Private m_varQueryParam As Variant
Public Function SetQueryParam(B yVal sValue as Variant)
m_varQueryParam = sValue
End Function
Public Function GetQueryParam() As Variant
GetQueryParam = m_varQueryParam
End Function

Query:
SELECT * FROM tblYourTable WHERE [FilterField] = GetQueryParam()

The VBA Code to lajunch the query will look like this.
SetQueryParam "your value here"
DoCmd.OpenQuery "qryYourQueryHe re"

--

Danny J. Lesandrini
dlesandrini@hot mail.com

http://amazecreations.com/datafast/

<bjaj@vestas.co m> wrote ...[color=blue]
> Hi
> I need some help to my VBA code.
>
> I want to open a query in Edit mode
> Change a parameter and then save the qyery and close it.
>
> A)I now how to do open the query in editmode
> docmd.openquery ect ect.
>
> B)I dont now how to change the parameter
>
> C)but I now how to save the qyery and close it.
>
> Can anyone help me to do the B) part ?
>
> best regards
> Ronald
>[/color]

[bjaj@vestas.com]

Re: Query in vba

Ok and thank you.
I have tried what Smartin did say and it works fine, but now I want to
get i dynamic.
I got this SQL statement:

1)"SELECT DISTINCTROW Format$(T_FejlR ecords.DatoOpr, 'mmmm yyyy')" & _
2)"AS [DatoOpr By Month], T_FejlRecords.V areNr, Count(*)" & _
3)"AS [Count Of T_FejlRecords] " & _
4)"FROM T_FejlRecords GROUP BY Format$(T_FejlR ecords.DatoOpr, 'mmmm
yyyy'), T_FejlRecords.V areNr,
Year(T_FejlReco rds.DatoOpr)*12 +DatePart('m',T _FejlRecords.Da toOpr)-1,
T_FejlRecords.I ntern " & _
5)"HAVING (((T_FejlRecord s.VareNr)=VareV ar) AND
T_FejlRecords.I ntern)=Yes) AND
((Year([T_FejlRecords].[DatoOpr])*12+DatePart(' m',[T_FejlRecords].[DatoOpr])-1)>=Year(Date() )*12+DatePart(' m',Date())-4))"

If you look at 5) saying: HAVING (((T_FejlRecord s.VareNr)=VareV ar)
VareVar is a VAR. How do I "tell" the SQL statement that it is a VAR ?
It need some signs around, but witch one.

best regards
Ronald
Danny J. Lesandrini wrote:[color=blue]
> Why not make the query parameter dynamic?
>
> Private m_varQueryParam As Variant
> Public Function SetQueryParam(B yVal sValue as Variant)
> m_varQueryParam = sValue
> End Function
> Public Function GetQueryParam() As Variant
> GetQueryParam = m_varQueryParam
> End Function
>
> Query:
> SELECT * FROM tblYourTable WHERE [FilterField] = GetQueryParam()
>
> The VBA Code to lajunch the query will look like this.
> SetQueryParam "your value here"
> DoCmd.OpenQuery "qryYourQueryHe re"
>
> --
>
> Danny J. Lesandrini
> dlesandrini@hot mail.com
> http://amazecreations.com/datafast/
>
>
>
> <bjaj@vestas.co m> wrote ...[color=green]
> > Hi
> > I need some help to my VBA code.
> >
> > I want to open a query in Edit mode
> > Change a parameter and then save the qyery and close it.
> >
> > A)I now how to do open the query in editmode
> > docmd.openquery ect ect.
> >
> > B)I dont now how to change the parameter
> >
> > C)but I now how to save the qyery and close it.
> >
> > Can anyone help me to do the B) part ?
> >
> > best regards
> > Ronald
> >[/color][/color]