Code for new query

[naungsai]

Dear Friends

I am stuck again in the following code. the run-time error code is 3265.
I am stucking in "Set qryDef = CurrentDb.QueryDefs(strQueryName)".

Can somebody check my error?
thanks in advance.

Option Compare Database
Private Sub Calculate_Click()
Dim strLDOMonth As String
Dim QueryName As String
Dim qryDef As DAO.QueryDef
Dim strSelect As String
 
strLDOMonth = Me.LDOMth1
QueryName = "MonthlyBalanceQ1"
strNewT = newTable
strQueryName = QueryName
 
strSelect = "SELECT " & strLDOMonth & " AS Period,"
strSelect = strSelect & " BalanceStockMain.Stock, alanceStockMain.ITEM, BalanceStockMain.BAL FROM BalanceStockMain;"
 
Set qryDef = CurrentDb.QueryDefs(strQueryName)
 
qryDef.SQL = strSelect
qryDef.Close
DoCmd.OpenQuery QueryName, acNormal, acEdit
End Sub

 

[Ron_dK]

strSelect = strSelect & " BalanceStockMain.Stock, alanceStockMain.ITEM, BalanceStockMain.BAL FROM BalanceStockMain;"

I'm not sure but shouldn't that be :

strSelect = strSelect & " BalanceStockMain.Stock, BalanceStockMain.ITEM, BalanceStockMain.BAL FROM BalanceStockMain;"

 

[naungsai]

Dear Ron

Thanks for your reply.
There is the same problem. It is still stuck at
Set qryDef = CurrentDb.QueryDefs(strQueryName)

the run-time error is 3265.

Thanks in advance for your help.

Option Compare Database
Private Sub Calculate_Click()
Dim strLDOMonth As String
Dim QueryName As String
Dim qryDef As DAO.QueryDef
Dim strSelect As String

strLDOMonth = Me.LDOMth
QueryName = "MonthlyBalQ1"
strNewT = newTable
strQueryName = QueryName

strSelect = "SELECT " & strLDOMonth & " AS Period, "
strSelect = strSelect & "BalanceStockMain.Stock, BalanceStockMain.ITEM, BalanceStockMain.BAL FROM BalanceStockMain;"
 
Set qryDef = CurrentDb.QueryDefs(strQueryName)

qryDef.SQL = strSelect
qryDef.Close
DoCmd.OpenQuery QueryName, acNormal, acEdit
End Sub

 

[Ron_dK]

Dear Ron

Thanks for your reply.
There is the same problem. It is still stuck at
Set qryDef = CurrentDb.QueryDefs(strQueryName)

the run-time error is 3265.

Thanks in advance for your help.

Option Compare Database
Private Sub Calculate_Click()
Dim strLDOMonth As String
Dim QueryName As String
Dim qryDef As DAO.QueryDef
Dim strSelect As String

strLDOMonth = Me.LDOMth
QueryName = "MonthlyBalQ1"
strNewT = newTable
strQueryName = QueryName

strSelect = "SELECT " & strLDOMonth & " AS Period, "
strSelect = strSelect & "BalanceStockMain.Stock, BalanceStockMain.ITEM, BalanceStockMain.BAL FROM BalanceStockMain;"
 
Set qryDef = CurrentDb.QueryDefs(strQueryName)

qryDef.SQL = strSelect
qryDef.Close
DoCmd.OpenQuery QueryName, acNormal, acEdit
End Sub

You might try this :

Set qryDef = CurrentDb.QueryDefs("strQueryName")

Hth

 

[naungsai]

You might try this :

Set qryDef = CurrentDb.QueryDefs("strQueryName")

Hth

Dea Ron

Can you please check the code with my attached file?

Thanks in advance

 

[raskew]

Hi -

Private Sub Calculate_Click()

Dim strLDOMonth As String
Dim QueryBal As String
Dim qryDef As DAO.QueryDef
Dim strBalSelect As String


strLDOMonth = [COLOR="Red"]LDOMth[/COLOR]

QueryBal = "MontlhyBalQ1"

[COLOR="red"]strQueryBal[/COLOR] = QueryBal

strBalSelect = "SELECT " & strLDOMonth & " AS Periord,"

For starters, LDOMth is undefined, undimensioned.

strQueryBal is undimensioned.

There is no date field in table BalanceStockMain, the one you are querying.

Bob

 

[naungsai]

Hi -

Private Sub Calculate_Click()
 
Dim strLDOMonth As String
Dim QueryBal As String
Dim qryDef As DAO.QueryDef
Dim strBalSelect As String
 
 
strLDOMonth = [COLOR=red]LDOMth[/COLOR]
 
QueryBal = "MontlhyBalQ1"
 
[COLOR=red]strQueryBal[/COLOR] = QueryBal
 
strBalSelect = "SELECT " & strLDOMonth & " AS Periord,"

For starters, LDOMth is undefined, undimensioned.

strQueryBal is undimensioned.

There is no date field in table BalanceStockMain, the one you are querying.

Bob

Dear Bob

Thanks for your response.
The LDOMth is the name of the text box in form "LDMonth".
I try to declase the strQueryBal with "Dim StrQueryBal As String".

In my new query which will created by this code, the "Period" will be added using the "LDMonth".
After creating this new query "MonthlyBalQ1", this query will be used to run another Query "MonthlyBalQ2".

The main aim of the file is I want to creat stock balance. Although the main stock balance could be check in "BalanceStockMain". I want to append the Balance into a new table. Then deduct the second month's usage.



What should i do next?

Thanks in advance for your help.