Update table from results of a query

[doobybug]

Hi,

I have a table Branches and I want to update it with the result of a query.

Is it possible? Here is what I've done:

Private Sub GetData_Click()
Dim SQL As String
SQL = "UPDATE Branches " & _
"SET Branches.[A Outlets] = d.[SumOfA]" & _
"Where (((Branches.[BranchId]) = " & Me.Text86 & "));"
DoCmd.RunSQL SQL
End Sub

d is the name of the query

The query is this:
SELECT Sum(CityMarket.[A Outlets]) AS SumOfA
FROM CityMarket
WHERE (((CityMarket.BranchId)=[Forms]![RegBranch]![RegBranch1].[Form].[BranchId]));

 

[Tombra]

Hi,

I have a table Branches and I want to update it with the result of a query.

Is it possible? Here is what I've done:

Private Sub GetData_Click()
Dim SQL As String
Dim xx as currency

xx = nz(dsum("[A Outlets]","CityMarket","CityMarket.BranchId)=" & [Forms]![RegBranch]![RegBranch1].[Form].[BranchId]"),0)

SQL = "UPDATE Branches " & _
"SET Branches.[A Outlets] = " & xx & _
"Where (((Branches.[BranchId]) = " & Me.Text86 & "));"
DoCmd.RunSQL SQL
End Sub

d is the name of the query

The query is this:
SELECT Sum(CityMarket.[A Outlets]) AS SumOfA
FROM CityMarket
WHERE (((CityMarket.BranchId)=[Forms]![RegBranch]![RegBranch1].[Form].[BranchId]));

try change this way working without query

 

[doobybug]

It is giving me a syntax error at

xx = nz(dsum("[A Outlets]","CityMarket","CityMarket.BranchId)=" & [Forms]![RegBranch]![RegBranch1].[Form].[BranchId]"),0)

 

[Tombra]

xx = nz(dsum("[A Outlets]","CityMarket","CityMarket.BranchId)=" & [Forms]![RegBranch]![RegBranch1].[Form].[BranchId]"),0)

Sorry
there is one extra bracket