467,146 Members | 1,008 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,146 developers. It's quick & easy.

Query in vba

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

Nov 13 '05 #1
  • viewed: 12996
Share:
3 Replies
bj**@vestas.com wrote:
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


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
Nov 13 '05 #2
Why not make the query parameter dynamic?

Private m_varQueryParam As Variant
Public Function SetQueryParam(ByVal 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 "qryYourQueryHere"

--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast/

<bj**@vestas.com> wrote ...
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

Nov 13 '05 #3
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_FejlRecords.DatoOpr,'mmmm yyyy')" & _
2)"AS [DatoOpr By Month], T_FejlRecords.VareNr, Count(*)" & _
3)"AS [Count Of T_FejlRecords] " & _
4)"FROM T_FejlRecords GROUP BY Format$(T_FejlRecords.DatoOpr,'mmmm
yyyy'), T_FejlRecords.VareNr,
Year(T_FejlRecords.DatoOpr)*12+DatePart('m',T_Fejl Records.DatoOpr)-1,
T_FejlRecords.Intern " & _
5)"HAVING (((T_FejlRecords.VareNr)=VareVar) AND
T_FejlRecords.Intern)=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_FejlRecords.VareNr)=VareVar)
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:
Why not make the query parameter dynamic?

Private m_varQueryParam As Variant
Public Function SetQueryParam(ByVal 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 "qryYourQueryHere"

--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast/

<bj**@vestas.com> wrote ...
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


Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by jaysonsch | last post: by
9 posts views Thread by netpurpose | last post: by
3 posts views Thread by Harvey | last post: by
4 posts views Thread by Diamondback | last post: by
14 posts views Thread by Dave Thomas | last post: by
4 posts views Thread by Stan | last post: by
6 posts views Thread by jsacrey@comcast.net | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.