Help using query results as variables

[mattpar]

Hello, I have been trying to get this code to work for a bit now and just can't figure it out so I could really use some help. A quick note as to what it is that I'm trying to do is basically count records in one table, with criteria, and then put that count into another table. So I have 2 tables one with the locations and the other with all the anomalies related to those locations. And I'm trying to use a query to get counts of anomalies, with certain criteria, for each of those locations.

The actual query that I use to do the count and update works just fine when I manually type in each location. But I have a few hundreds locations and so I'm trying to have code cycle through these locations and update the counts. So, here is the code that I'm trying to use ... and is failing miserably.

Public Function gridstatusanomalies() As Boolean
On Error Resume Next

' Requires reference to Microsoft Office 11.0 Object Library!!!!!!

Dim locals As Variant
Dim Var As Variant

'Then we tun off the warnings
DoCmd.SetWarnings False

'DAO.Database Related records
'DAO.Recordset Multi-valued field recordset
Dim db As DAO.database, rst As DAO.Recordset, sSQL As String

'Now the code sets the current database to the one we jsut worked on
Set db = CurrentDb()

'This next part selects the query to use as variables
sSQL = "SELECT Location FROM Status_Anomaly_Count"
Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot)

'This part then associates the Location to the strcolumn variables form above
If Not rst.BOF And Not rst.EOF Then
    Do Until rst.EOF
        locals = rst!Location
        
        For Each Var In locals
            Dim strColumn1 As String
            strColumn1 = Var
            Dim ACount As String
            
        'runs the update query with strcolumn1 variable
            ACount = "UPDATE Status_Anomaly_Count SET Status_Anomaly_Count.Num_Greater_40mV = (DCount('Target_ID', 'Anomaly_Table', 'CH3_final>40 AND [location] = '" & strColumn1 & "')) WHERE (((Status_Anomaly_Count.Location)= '" & strColumn1 & "'))"
            DoCmd.RunSQL ACount
       
        Next
    rst.MoveNext
Loop

End If

Set rst = Nothing
Set db = Nothing

'Turns warngins back on
DoCmd.SetWarnings True

'Finally the code displays a message box to assure you that it has updated fields
Dim iResponce As Integer
iResponce = MsgBox("Grid Status Anomalies Counted", vbOKOnly, "File Update")
    
End Function

Hopefully you guys can show me the error of my ways here, and I appreciate and help. Thanks

 

[PWise]

PWise (Programmer) Oct 27, 2011
you dont need all this
try this
Create a query

[CODE Qry_Anomaly_Count]
Select Location,count(Target_ID)
From Anomaly_Table
Group by Location
Where CH3_final>40[/code]

Dim db As DAO.database
Set db = CurrentDb()
dim sqlSter as String
Sqlstr = "Update Status_Anomaly_Count inner join Qry_Anomaly_Count
on Status_Anomaly_Count.Location=Qry_Anomaly_Count.Location Set Status_Anomaly_Count.Num_Greater_40mV = qry_Anomaly_Count.Num_Greater_40mV
db.execute sqlstr

 

[mattpar]

Thanks for the quick reply, I had thought of just using queries like that but my supervisor wanted the code (dont ask me why). Still, I tried your suggestion and it now gives me an error "Operation must use an updateable query".

 

[Darrylles]

matt,

You haven't mentioned WHY it 'is failing miserably'.

Would you like to elaborate?

(Your code may be syntactically correct, so we won't spot anything in the code - but your logic may be wrong in comparison to what you want it to do ).


ATB

Darrylles





Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[mattpar]

Very true, should have given a bit more info. I guess to begin I made the code, by request of my supervisor, because we will eventually have other criteria to also count. Also I used the Status_Anomaly_Count table to query for the data as we may also filter out some locations. So I made the one bit of code in the hope that it would be easier to change in the long run rather than multiple queries.

And as for how it is failing, it simply isn't doing anything ... well except the message box at the end, that works. I've tested each individual part and they work on their own, but once I put them together it doesn't update the table at all. It just runs through and goes to the message box with no erros when they are turned on.

Again Thanks for any help.

 

[PHV]

For Each Var In locals
What is the purpose of this ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mattpar]

to cycle through the location records in the Status_Anomaly_Count table.

 

[PHV]

cycle through the location records
Isn't the purpose of rst.MoveNext ????

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mattpar]

.... That is a very good point. Though took that bit out and it still doesn't work, so I'm guessing that wasn't affecting it and was just extraneous. Thanks for pointing that out though.

 

[Darrylles]

matt,

Toggle breakpoint on line "ACount = .....", note each variable value then produce a query with those values - does the query return anything?

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[TheAceMan1]

How are ya mattpar . . .

I can make sense out of your code, however it doesn't logically fit together for me.
[ol][li]You parse and update the same table:

[blue]sSQL = "SELECT Location FROM [purple][b]Status_Anomaly_Count[/b][/purple]"
   .
   .
"UPDATE [purple][b]Status_Anomaly_Count[/b][/purple] SET ...[/blue]

[/li]
[li]Your [blue]For Each Next[/blue] loop is suggesting a [blue]multivalue[/blue] field. Is this correct? If true, [blue]For each[/blue] can only iterate a collection object or array. The [blue]Split[/blue] function should work here.[/li]
[li]The context of your post origination mentions three tables, not two. Namely [purple]Locations, Anomaly Table, Status_Anomaly_Count[/purple].[/li]
[li]Are their any relationships between the tables? If so what are they?[/li]
[li]Can you post some typical data for the tables mentioned?[/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]