Update Query by VBA code

[ice051505]

Hi all, I want to update my query AreaQuery2 by VBA code, and Can anyone help me to check why my code does not work? Thanks!

Dim Sql1 As String
Dim Sql2 As String

Sql1 = "Select [SmallArea] From [AreaQuery1] WHERE ([SmallArea]<>'NA');"


If Not IsNull(Sql1) Then
Sql2 = "Update [AreaQuery2] SET [AreaQuery2].[SmallArea]= Sql1 WHERE [AreaQuery1].[AreaCode]=[AreaQuery2].[AreaCode];"
End If

[JoeM]

It looks to me like you are trying to insert your "Sql1" variable in your "Sql2" code, but they way you have it written won't work. Since "Sql1" is placed between the quotes, it is treated as a literal value and not a variable. It would need to be something like this:

Sql2 = "...": & Sql1 & "..."

But I am not sure how you have it written will work anyway.

Maybe if you explain exactly how you want it to work, we can help you write the code. A simple example may be helpful too.

[ice051505]

OK, I will try my best to explain.
I have a selection query "AreaQuery1"which contain 6 field: AreaCode, Zone, TotalArea, SmallArea, SmallerArea, SmallestArea

Step1: I want to develop "AreaQuery2" with the initial field AreaCode, Zone, TotalArea which obtained from "AreaQuery1".

Step2: for column value 4 until max # columns in "AreaQuery1" (field after "TotalArea"), check query for presence of "NA" values in the current column

Step3: for the column does not have "NA"values, add (or update) the column to "AreaQuery2", and loop through to the next clumn

Step4: For the column have "NA" values, count the number of instances of Area+1 level for each of the Areas where "NA" is present
this will likely to be a 2-stage query with a query for each level of area resolution
the two pieces will be the query of AreaCode,CurrentArea(=NA),Area+1level, the second query will be a count of each of the Areas at Level+1 within the query being created.
Area Level +1 means if the loop is through SmallArea, then TotalArea would be the Area level+1

I know the steps are really confused and complex, right now I just try to update my "AreaQuery2"

[ice051505]

Here is my code, Can someone check it for me to see if it can acturely work or not? Thanks

Dim db As DAO.Database
Dim qr1 As DAO.QueryDef
Dim Sql1 As String
Dim Sql2 As String
Dim Sql3 As String
Dim Sql4 As String
Dim Sql5 As String
Dim Sql6 As String
Dim CntNA As Integer
Do
Sql1 = "Select [SmallArea],[AreaCode] From [AreaQuery1] WHERE ([SmallArea]<>'NA');"
If Not IsNull(Sql1) Then
Sql2 = "Update [AreaQuery2] SET [AreaQuery2].[SmallArea]= " & Sql1 & " WHERE [AreaQuery1].[AreaCode]=[AreaQuery2].[AreaCode];"
End If

Sql3 = "Select [SmallArea],[TotalArea],[AreaCode] From [AreaQuery1] WHERE ([SmallArea]='NA');"

If Not IsNull(Sql3) Then
Set db = CurrentDb()
Set qr1 = db.QueryDefs("AreaQuery3")
Sql4 = "SELECT Count(AreaQuery1.TotalArea) AS CountOfTotalArea, AreaQuery2.TotalArea" & _
"FROM AreaQuery2 INNER JOIN AreaQuery1 ON AreaQuery2.AreaCode = AreaQuery1.AreaCode" & _
"GROUP BY AreaQuery2.TotalArea;"

qr1.SQL = Sql4
End If

Sql5 = "SELECT AreaCode, CountOfTotalArea FROM AreaQuery3 WHERE CountOfTotalArea = 1;"
If Not IsNull(Sql5) Then
Sql6 = "Update [AreaQuery2] SET [AreaQuery2].[SmallArea]= " & Sql3 & " WHERE [AreaQuery2].[AreaCode]=[AreaQuery3].[AreaCode];"
End If
Loop

[JoeM]

I can't really say I follow your explanation of what you are trying to do, but I do think there definitely problems with your code. Let's look at the first part.

   Sql1 = "Select [SmallArea],[AreaCode] From [AreaQuery1] WHERE ([SmallArea]<>'NA');"
   If Not IsNull(Sql1) Then
      Sql2 = "Update [AreaQuery2] SET [AreaQuery2].[SmallArea]= " & Sql1 & " WHERE [AreaQuery1].[AreaCode]=[AreaQuery2].[AreaCode];"
   End If

Sql1 and Sql2 are just strings. They are not queries, and they will not return the results of a query unless they are applied to a query definition. So, if you are hoping to return the value of the SQL code in "Sql1" in order to incorporate it into your code for Sql2, that will not do that.

So what exactly are you trying to accomplish by this first section (maybe posting a small data sample and working through it with your expected results would help)?
Maybe something like a DLOOKUP or a nested query would work better for you?

[ice051505]

Thanks for your advice, I will try the nested query with small amount of data

[alcapps]

'this is a string for a sql1 statement
Sql1 = "Select [SmallArea],[AreaCode] From [AreaQuery1] WHERE ([SmallArea]<>'NA');"

'you are only checking if the sql1 only has a string value .. not executing here..(which it always will..) your not executing the qry1 here and checking for number of 'row count
If Not IsNull(Sql1) Then
Sql2 = "Update [AreaQuery2] SET [AreaQuery2].[SmallArea]= " & Sql1 & " WHERE [AreaQuery1].[AreaCode]=[AreaQuery2].[AreaCode];"
End If
'you are only checking if the sql3only has a string value .. not executing here..(which it always will..) your not executing the qry3 here and checking for number of 'row count
Sql3 = "Select [SmallArea],[TotalArea],[AreaCode] From [AreaQuery1] WHERE ([SmallArea]='NA');"

If Not IsNull(Sql3) Then
Set db = CurrentDb()
Set qr1 = db.QueryDefs("AreaQuery3")
Sql4 = "SELECT Count(AreaQuery1.TotalArea) AS CountOfTotalArea, AreaQuery2.TotalArea" & _
"FROM AreaQuery2 INNER JOIN AreaQuery1 ON AreaQuery2.AreaCode = AreaQuery1.AreaCode" & _
"GROUP BY AreaQuery2.TotalArea;"
'you are actually changing the query based on the string above in sql4 here..
qr1.SQL = Sql4
End If

Sql5 = "SELECT AreaCode, CountOfTotalArea FROM AreaQuery3 WHERE CountOfTotalArea = 1;"
If Not IsNull(Sql5) Then
Sql6 = "Update [AreaQuery2] SET [AreaQuery2].[SmallArea]= " & Sql3 & " WHERE [AreaQuery2].[AreaCode]=[AreaQuery3].[AreaCode];"
End If
Loop

if you want to make the queries run.. you need to
example..
'add recordset
dim rst as DAO.recordset
Set db = CurrentDb()

Sql1 = "Select [SmallArea],[AreaCode] From [AreaQuery1] WHERE ([SmallArea]<>'NA');"

set rst = db.Openrecordset(sq1, dbopensnapshot)
if rst.Recordcount > 0 then
'you can reuse Sql1
Sql1 = "Update [AreaQuery2] SET [AreaQuery2].[SmallArea]= " & Sql1 & " WHERE [AreaQuery1].[AreaCode]=[AreaQuery2].[AreaCode];"
db.execute sql1, dbfailonerror
end if
rst.close
just use this technique... for all of the code you have...
hope this helps..

[ice051505]

Hi, Thank you for your reply, Just one question here: I am thinking to do all the sql, such as sql3 and sql4, on a array, which I won't need to create more query in my database. Do you think that would be work? and can you give me some direction on how to pass sql to the array in VBA? Thanks!!!

dim rst as DAO.recordset
Set db = CurrentDb()
Sql1 = "Select [SmallArea],[AreaCode] From [AreaQuery1] WHERE ([SmallArea]<>'NA');"
set rst = db.Openrecordset(sq1, dbopensnapshot)
if rst.Recordcount > 0 then
'you can reuse Sql1
Sql1 = "Update [AreaQuery2] SET [AreaQuery2].[SmallArea]= " & Sql1 & " WHERE [AreaQuery1].[AreaCode]=[AreaQuery2].[AreaCode];"
db.execute sql1, dbfailonerror
end if
rst.close
just use this technique... for all of the code you have...
hope this helps..

[ice051505]

Hi, sorry for the confuse, what I acturely mean is, Can you tell me how to write code to built a loop? Since in the sql I provide, it is only to check on the SmallArea, which is the column 4 in my query, I also want to continue to check column5 (SmallerArea) and column6(SmallestArea), I think I will need to develop a array for the loop such as

With rst
        .MoveLast
        .MoveFirst
        lRecordCount = .RecordCount
        If lRecordCount > 0 Then
            vResults = Empty
            vResults = .GetRows(lRecordCount)
            For i = LBound(column4, TotalRecordNumber) To UBound(column6,TotalRecordNumber)
            Next i
        End If
    End With

In the loop, how can I make it to search the column4 to column5 and define the record number, since it depends on the selection of AreaQuery1?

Please Help me! Thanks!

just use this technique... for all of the code you have...
hope this helps..

[alcapps]

With rst .MoveLast .MoveFirst lRecordCount = .RecordCount If lRecordCount > 0 Then vResults = Empty vResults = .GetRows(lRecordCount) For i = LBound(column4, TotalRecordNumber) To UBound(column6,TotalRecordNumber) Next i End If End With



rst.movefirst
if rst.recordcount > 0
do while not rst.eof
Your code goes here in the loop..of recordset
rst.moveNext
loop
end if
rst.close

[ice051505]

Thank you for your code, I just have a small question here: How do I define the column in the query to make my loop recognize it?
right now I have the code like below:

Dim AreaArray()
Dim i As Long
Dim j As Long
Dim tdf As DAO.TableDef
Dim NumFld As Long
Dim RecordCnt As Long
Set db = CurrentDb()
Set tdf = db.TableDefs(AreaQuery1)
NumFld = tdf.Fields.Count
RecordCnt = DCount("*", "AreaQuery1")
For i = 4 To NumFld
 For j = 0 To RecordCnt
    ReDim AreaArray(i, j)
rs.MoveFirst
Do While Not rs.EOF
AreaArray() = "SELECT [SmallArea] From [AreaQuery1] WHERE ([SmallArea]<>'NA');"  (Here is the column I want to use to this loop)
Set rs = db.OpenRecordset(AreaArray(), dbOpenSnapshot)
If rs.RecordCount > 0 Then
AreaArray() = "Update [AreaQuery2]SET[AreaQuery2].[SmallArea]=" & Sql1 & "WHERE [AreaQuery1].[AreaCode]=[AreaQuery2].[AreaCode];"
db.Execute AreaArray(), dbfilonerror
End If
rs.Close
 Next i
Next j

my concern is while the loop finish [SmallArea] column, it won't recognize the next column, can you give me little help here? Thanks!

rst.movefirst if rst.recordcount > 0 do while not rst.eof
Your code goes here in the loop..of recordset rst.moveNext loop end if rst.close