[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

table data Macro to VBA

Posted on 2014-08-18
9
Medium Priority
?
59 Views
Last Modified: 2016-06-15
Hello all.
i' m developing an access database with front-and and back-end.
Now i need to integrate this DB with another one that containg "data Macro" in his table..
i heve upload this here:
https://drive.google.com/file/d/0B1xOFynAx5bickRmb0FPNzBFVWM/edit?usp=sharing

Open in new window


the back-end wil be changed in MS server soon, so i need to translate xml language/data macro istruction to Vba or another language for use it in user form (Masks)
i post some code:
<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><DataMacro Name="RegistraDonazioneBenefattore"><Parameters>
<Parameter Name="prmIDBenefattore"/><Parameter Name="prmImporto"/>
</Parameters><Statements><LookUpRecord><Data><Reference>T_Benefattori</Reference>
<WhereCondition>[IDBenefattori]=[prmIDBenefattore]</WhereCondition>
</Data><Statements><EditRecord><Data/><Statements><Action Name="SetField">
<Argument Name="Field">[TotaleDonato]</Argument>
<Argument Name="Value">[TotaleDonato]+[prmImporto]</Argument>
</Action></Statements></EditRecord></Statements></LookUpRecord>
</Statements></DataMacro></DataMacros>

Open in new window

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><DataMacro Name="RegistraDonazioneProgetto"><Parameters>
<Parameter Name="prmIDProgetti"/><Parameter Name="prmImporto"/>
</Parameters><Statements><LookUpRecord><Data><Reference>T_Progetti</Reference><WhereCondition>[IDProgetti]=[prmIDProgetti]</WhereCondition></Data><Statements>
<EditRecord><Data/><Statements><Action Name="SetField">
<Argument Name="Field">[DonazioniRicevute]</Argument><Argument Name="Value">[T_Progetti].[DonazioniRicevute]+[prmImporto]</Argument></Action></Statements>
</EditRecord></Statements></LookUpRecord></Statements></DataMacro>
</DataMacros>

Open in new window

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><DataMacro Name="RicalcolaTotali"><Statements><ForEachRecord>
<Data Alias="T_Benefattori"><Query><References><Reference Source="T_Benefattori"/></References><Results><Property Source="T_Benefattori" Name="IDBenefattori"/>
<Property Source="T_Benefattori" Name="TotaleDonato"/></Results>
</Query></Data><Statements><Action Name="SetLocalVar"><Argument Name="Name">varTotalDonated</Argument><Argument Name="Value">0</Argument></Action><ForEachRecord><Data Alias="T_Donazioni"><Query><References>
<Reference Source="T_Donazioni"/></References><Results>
<Property Source="T_Donazioni" Name="IDBenefattore"/>
<Property Source="T_Donazioni" Name="Importo"/></Results></Query><WhereCondition>[IDBenefattore]=[T_Benefattori].[IDBenefattori]</WhereCondition></Data><Statements>
<Action Name="SetLocalVar"><Argument Name="Name">varTotalDonated</Argument><Argument Name="Value">[varTotalDonated]+[Importo]</Argument></Action>
</Statements></ForEachRecord><EditRecord><Data/><Statements>
<Action Name="SetField"><Argument Name="Field">TotaleDonato</Argument>
<Argument Name="Value">[varTotalDonated]</Argument></Action></Statements></EditRecord></Statements></ForEachRecord><Comment>Questo ciclo ricalcola tutti i totali per la tabella Benefattori. </Comment><ForEachRecord><Data Alias="T_Progetti"><Query><References>
<Reference Source="T_Progetti"/></References><Results><Property Source="T_Progetti" Name="IDProgetti"/><Property Source="T_Progetti" Name="DonazioniRicevute"/>
</Results></Query></Data><Statements><Action Name="SetLocalVar"><Argument Name="Name">varDonationsReceived</Argument><Argument Name="Value">0</Argument></Action><ForEachRecord><Data Alias="T_Donazioni"><Query><References>
<Reference Source="T_Donazioni"/></References><Results><Property Source="T_Donazioni" Name="IDProgetto"/>
<Property Source="T_Donazioni" Name="Importo"/></Results></Query>
<WhereCondition>[IDProgetto]=[T_Progetti].[IDProgetti]</WhereCondition>
</Data><Statements><Action Name="SetLocalVar"><Argument Name="Name">varDonationsReceived</Argument><Argument Name="Value">[varDonationsReceived]+[Importo]</Argument></Action></Statements></ForEachRecord><EditRecord><Data/><Statements><Action Name="SetField"><Argument Name="Field">DonazioniRicevute </Argument>
<Argument Name="Value">[varDonationsReceived]</Argument></Action></Statements></EditRecord>
</Statements></ForEachRecord><Comment>Questo ciclo ricalcola tutti i totali per la tabella Benefattori. </Comment><Comment>Questo ciclo ricalcola tutti i totali per la tabella Benefattori. </Comment><ForEachRecord><Data Alias="T_Utenti"><Query><References><Reference Source="T_Utenti"/>
</References><Results><Property Source="T_Utenti" Name="IDUtenti"/>
<Property Source="T_Utenti" Name="TotContributo"/></Results></Query>
</Data><Statements><Action Name="SetLocalVar"><Argument Name="Name">varTotalUsed</Argument><Argument Name="Value">0</Argument></Action><ForEachRecord>
<Data Alias="Contributi"><Query><References><Reference Source="T_ContributiE"/></References><Results><Property Source="T_ContributiE" Name="ID_Contributo"/>
<Property Source="T_ContributiE" Name="Ammontare"/></Results>
</Query><WhereCondition>[ID_Contributo]=[T_Utenti].[IDUtenti]</WhereCondition></Data><Statements><Action Name="SetLocalVar">
<Argument Name="Name">varTotalUsed</Argument><Argument Name="Value">[varTotalUsed]+[Ammontare]</Argument></Action></Statements></ForEachRecord>
<EditRecord><Data/><Statements><Action Name="SetField"><Argument Name="Field">TotContributo</Argument><Argument Name="Value">[varTotalUsed]</Argument></Action></Statements></EditRecord></Statements></ForEachRecord><Comment>Questo ciclo ricalcola tutti i totali per la tabella Benefattori. </Comment><ForEachRecord>
<Data Alias="T_Progetti"><Query><References><Reference Source="T_Progetti"/></References><Results><Property Source="T_Progetti" Name="IDProgetti"/><Property Source="T_Progetti" Name="Utilizzati"/></Results></Query></Data><Statements><Action Name="SetLocalVar">
<Argument Name="Name">varDonationsUsed</Argument><Argument Name="Value">0</Argument></Action><ForEachRecord><Data Alias="Contributi"><Query><References>
<Reference Source="T_ContributiE"/></References><Results><Property Source="T_ContributiE" Name="IDproject"/><Property Source="T_ContributiE" Name="Ammontare"/>
</Results></Query><WhereCondition>[IDProject]=[T_Progetti].[IDProgetti]</WhereCondition></Data><Statements><Action Name="SetLocalVar"><Argument Name="Name">varDonationsUsed</Argument><Argument Name="Value">[varDonationsUsed]+[Ammontare]</Argument>
</Action></Statements></ForEachRecord><EditRecord><Data/>
<Statements><Action Name="SetField"><Argument Name="Field">Utilizzati</Argument>
<Argument Name="Value">[varDonationsUsed]</Argument></Action></Statements>
</EditRecord></Statements></ForEachRecord></Statements></DataMacro></DataMacros>

Open in new window


thanks for all that help

p.s: i'm italian.
0
Comment
Question by:Antonio Biasuzzi
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 14

Accepted Solution

by:
Russell Fox earned 1200 total points
ID: 40269120
It looks like you just need to figure out what the xml code blocks are trying to do, and then create a stored procedure that does the same thing. For example, the first script appears to be updating the amount that person has donated, so you can replace it with a proc like the one below. This will create a stored procedure in SQL Server which you can execute from VBA in your Access front end:
CREATE PROC RegistraDonazioneBenefattore (
	@prmIDBenefattore INT,
	@prmImporto float
	)
AS 
UPDATE T_Benefattori
	SET TotaleDonato += @prmImporto
WHERE IDBenefattori = @prmIDBenefattore

Open in new window

Does that get you started, or do you need help with the other code blocks?
0
 

Author Comment

by:Antonio Biasuzzi
ID: 40269801
Ok, thanks for your fast reply...

i have try to do the second Block

CREATE Proc
RegistraDonazioneProgetto (
@prmIDProgetti INT,
@prmImporto float
)
AS
Update T_Progetti
Set
DonazioniRicevute = T_Progetti.DonazioniRicevute += @prmImporto
where IDProgetti += @prmIDProgetti

Open in new window


is it ok??

so, now i haven't a sql where work, and i use as back end an accdb..

is possible use this code with Vba?
i have think something like this:
i have create a new module..
Private Sub Form_AfterInsert()
Dim prmIDBenefattore As Variant
Dim prmImporto As Variant
Dim TotaleDonato As Integer
Dim IDBenefattore As Integer
Dim Importo As Integer
Dim IDBenefattori As Integer
Dim strSQL As String
prmIDBenefattore = Me!IDBenefattore.Value
prmImporto = Me!Importo.Value
strSQL = "Update [T_Benefattori]" _
           & "SET [TotaleDonato] = (" & prmImporto & ")" _
           & "WHERE IDBenefattori = (" & prmIDBenefattore & ")"
DoCmd.RunSQL strSQL

End Sub

Open in new window

I have insert it after insert event mask "donations"..

look my file, there are in T_donazioni event after add, after update, after delate..

if you need i can update my work here..

thanks again
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 800 total points
ID: 40269951
You can do this with VBA if you'd like, but you shouldn't do it in the After_Insert event. The AFter_Insert events occurs when a NEW record is inserted into Access, and that happens well before you really think it does. If you want to run an event that occurs when a record is Added or Updated, then use either (a) the Form's Before Update event (which is, generally, a bad idea) or (b) an After_Update event of a particular control that should be fired when the value is changed.

However: Are you working with Linked tables and Bound Forms? It seems as if you are, and if so, then you should not directly issue SQL statements against your datasource, if your Form is bound to the same tables. For example, in your last VBA section you're updating a table named T-Benefattori. If you're doing this on a Form that is also bound to that table, then you run the risk of the dreaded "You can't update this record because it's locked by another user blah blah blah ..." error. Basically, if your form is tied to the same datasource, then you should use the Form's module to do those updates.

Also, you SQL is not properly formed in that last bit. It should be:

strSQL = "Update [T_Benefattori]" _
           & " SET [TotaleDonato] = " & prmImporto _
           & " WHERE IDBenefattori = " & prmIDBenefattore

I removed the parentheses, and included spacing before SET and WHERE. Also, if IDBenefattri is a Text value, you'd have to enclose the prmIDBenefattore value in single or double quotes.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 14

Assisted Solution

by:Russell Fox
Russell Fox earned 1200 total points
ID: 40270556
I think I confused you with "+=". Here's your block #2 with corrections and the less confusing way of adding numbers:
CREATE PROC RegistraDonazioneProgetto 
	(
	@prmIDProgetti INT,
	@prmImporto float	-- I'm assuming FLOAT, it could also be MONEY or DECIMAL(16,2) or even INT
	)
AS
	Update T_Progetti
		SET DonazioniRicevute = T_Progetti.DonazioniRicevute + ISNULL(@prmImporto, 0.00) --Good habit to check for nulls
	WHERE IDProgetti = @prmIDProgetti

Open in new window

Yes, you can use VBA to create the update string like you and @Scott have above, and that's fine if you're more comfortable with that. There are some great benefits to creating a proc, though, like if you make this update in multiple forms you can call the same proc, and if you need to change how it behaves then you only need to change it in one place instead of multiple. You can accomplish the same thing with VBA Subs, though. I suspect calling the proc is more efficient, but if you aren't hitting performance issues, that really doesn't matter.
0
 

Author Comment

by:Antonio Biasuzzi
ID: 40271220
Hello.. thanks for help.
look this:
https://drive.google.com/file/d/0B1xOFynAx5biSXpnN3p4dTZ2Zk0/edit?usp=sharing

the table "T_Donazioni" and T_ContributiE have similar code.

start with T_Donazioni (the other tab became easy)

it have 3 data macro (code on first post) renamed in "Rec1" and "Rec2"
and code on event "after insert", after update, and after delate that call  (code below)

After insert:
<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><DataMacro Event="AfterInsert"><Statements><Action Name="RunDataMacro"><Argument Name="MacroName">T_Donazioni.RegistraDonazioneBenefattore</Argument><Parameters><Parameter Name="prmIDBenefattore" Value="[IDBenefattore]"/><Parameter Name="prmImporto" Value="[Importo]"/></Parameters></Action><Action Name="RunDataMacro"><Argument Name="MacroName">T_Donazioni.RegistraDonazioneProgetto</Argument><Parameters><Parameter Name="prmIDProgetti" Value="[IDProgetto]"/><Parameter Name="prmImporto" Value="[Importo]"/></Parameters></Action></Statements></DataMacro></DataMacros>

Open in new window


After update
<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><DataMacro Event="AfterUpdate"><Statements><ConditionalBlock><If><Condition>Updated("IDBenefattore")</Condition><Statements><Comment>Se il benefattore è cambiato, sottrae il vecchio importo dal benefattore precedente e aggiunge il nuovo importo al nuovo benefattore </Comment><Action Collapsed="true" Name="RunDataMacro"><Argument Name="MacroName">T_Donazioni.RegistraDonazioneBenefattore</Argument><Parameters><Parameter Name="prmIDBenefattore" Value="[Old].[IDBenefattore]"/><Parameter Name="prmImporto" Value="[Old].[Importo]"/></Parameters></Action><Action Collapsed="true" Name="RunDataMacro"><Argument Name="MacroName">T_Donazioni.RegistraDonazioneBenefattore</Argument><Parameters><Parameter Name="prmIDBenefattore" Value="[IDBenefattore]"/><Parameter Name="prmImporto" Value="[Importo]"/></Parameters></Action></Statements></If></ConditionalBlock><ConditionalBlock><If><Condition>Updated("Importo")</Condition><Statements><ConditionalBlock><If Collapsed="true"><Condition>Updated("IDProgetto")</Condition><Statements><Comment>Se la campagna è stata modificata, sottrae il vecchio importo dalla campagna precedente e aggiunge il nuovo importo alla nuova campagna </Comment><Action Collapsed="true" Name="RunDataMacro"><Argument Name="MacroName">T_Donazioni.RegistraDonazioneProgetto</Argument><Parameters><Parameter Name="prmIDProgetti" Value="[Old].[IDProgetto]"/><Parameter Name="prmImporto" Value="[Old].[Importo]"/></Parameters></Action><Action Name="RunDataMacro"><Argument Name="MacroName">T_Donazioni.RegistraDonazioneProgetto</Argument><Parameters><Parameter Name="prmIDProgetti" Value="[IDProgetto]"/><Parameter Name="prmImporto" Value="[Importo]"/></Parameters></Action></Statements></If></ConditionalBlock><Comment>Se l'importo è cambiato, registra la modifica </Comment><Action Collapsed="true" Name="SetLocalVar"><Argument Name="Name">varModificaImporto</Argument><Argument Name="Value">[Importo]-[Old].[Importo]</Argument></Action><ConditionalBlock><If><Condition>Not (Updated("IDBenefattore"))</Condition><Statements><Comment>Se la donazione del benefattore non è stata ancora registrata, registra la modifica per la donazione del benefattore </Comment><Action Name="RunDataMacro"><Argument Name="MacroName">T_Donazioni.RegistraDonazioneBenefattore</Argument><Parameters><Parameter Name="prmIDBenefattore" Value="[IDBenefattore]"/><Parameter Name="prmImporto" Value="[varModificaImporto]"/></Parameters></Action></Statements></If></ConditionalBlock><ConditionalBlock><If><Condition>Not (Updated("IDProgetto"))</Condition><Statements><Comment>Se la donazione per la campagna non è stata ancora registrata, registra la modifica per la donazione </Comment><Action Name="RunDataMacro"><Argument Name="MacroName">T_Donazioni.RegistraDonazioneProgetto</Argument><Parameters><Parameter Name="prmIDProgetti" Value="[IDProgetto]"/><Parameter Name="prmImporto" Value="[varModificaImporto]"/></Parameters></Action></Statements></If></ConditionalBlock></Statements></If></ConditionalBlock></Statements></DataMacro></DataMacros>

Open in new window


i have clean a bit this one:

Event="AfterUpdate"><Statements>
<If>Updated("IDBenefattore") then
<Comment>Se il benefattore è cambiato, sottrae il vecchio importo dal benefattore precedente e aggiunge il nuovo importo al nuovo benefattore </Comment>
RunDataMacro Rec1</Argument>
<Parameters>
prmIDBenefattore=[Old].[IDBenefattore]
prmImporto=[Old].[Importo]
RunDataMacro
Rec1
<Parameters>prmIDBenefattore=[IDBenefattore]
prmImporto=[Importo]
end If
If
Updated("Importo") then
If Updated("IDProgetto") then
<Comment>Se la campagna è stata modificata, sottrae il vecchio importo dalla campagna precedente e aggiunge il nuovo importo alla nuova campagna </Comment>
RunDataMacro Rec2
<Parameters>
prmIDProgetti=[Old].[IDProgetto]
prmImporto=[Old].[Importo]
"RunDataMacro Rec2
<Parameters>
prmIDProgetti=[IDProgetto]
prmImporto=[Importo]
end If>
Comment>Se l'importo è cambiato, registra la modifica </Comment>
SetLocalVar
varModificaImporto=[Importo]-[Old].[Importo]
If Not (Updated("IDBenefattore")) then
<Comment>Se la donazione del benefattore non è stata ancora registrata, registra la modifica per la donazione del benefattore </Comment>
RunDataMacro Rec1
<Parameters>
prmIDBenefattore=[IDBenefattore]
prmImporto=[varModificaImporto]
end If>
 IF Not (Updated("IDProgetto"))
<Comment>Se la donazione per la campagna non è stata ancora registrata, registra la modifica per la donazione 
RunDataMacro Rec2
<Parameters>
prmIDProgetti=[IDProgetto]
prmImporto = [varModificaImporto]
	end If
end if

Open in new window


and the last after delate:
<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><DataMacro Event="AfterDelete"><Statements><Action Name="RunDataMacro"><Argument Name="MacroName">Rec1</Argument><Parameters><Parameter Name="prmIDBenefattore" Value="[Old].[IDBenefattore]"/>
<Parameter Name="prmImporto" Value="-[Old].[Importo]"/></Parameters></Action><Action Name="RunDataMacro"><Argument Name="MacroName">Rec2</Argument>
<Parameters><Parameter Name="prmIDProgetti" Value="[Old].[IDProgetto]"/><Parameter Name="prmImporto" Value="-[Old].[Importo]"/></Parameters></Action></Statements></DataMacro></DataMacros>

Open in new window



Now. pleast try test my job:
1) create a new file accdb and link my table, and create mask for each table.
2) insert my code in after insert in Donation mask.
i have think to do this:

Private Sub Form_AfterInsert()
Call registradonazionebenefattore
Call registradonazioneprogetto
End Sub

Open in new window

Create a new module and insert this:
Public Sub registradonazionebenefattore()
DoCmd.SetWarnings False
Dim prmIDBenefattore As Variant
Dim prmImporto As Variant
Dim TotaleDonato As Integer
Dim IDBenefattore As Integer
Dim Importo As Integer
Dim IDBenefattori As Integer
Dim strSQL As String
prmIDBenefattore = IDBenefattore
prmImporto = Importo
 strSQL = "Update [T_Benefattori]" _
           & " SET [TotaleDonato] = [TotaleDonato] + " & prmImporto _
           & " WHERE IDBenefattori = " & prmIDBenefattore
DoCmd.RunSQL strSQL
End Sub
_________________________________________________________________________________
Public Sub registradonazioneprogetto()
DoCmd.SetWarnings False
Dim prmIDProgetti As Variant
Dim prmImporto As Variant
Dim DonazioniRicevute As Integer
Dim Importo As Integer
Dim IDProgetti  As Integer
Dim strSQL As String
prmIDProgetti = IDProgetti
prmImporto = Importo
 strSQL = "Update [T_Progetti]" _
           & " SET [DonazioniRicevute] = [DonazioniRicevute] + " & prmImporto _
           & " WHERE IDProgetti = " & prmIDProgetti
DoCmd.RunSQL strSQL
End Sub

Open in new window


is it  good? how can be better?
i work with link table.
i think that there will only one people for time that use this.
i prefer use vba as @Scott, so please follow this way.

thanks again
0
 
LVL 14

Assisted Solution

by:Russell Fox
Russell Fox earned 1200 total points
ID: 40271316
You are declaring variables that you are not using, so those can be eliminated. Also, if you have the values  available when you run the procedure, you can pass those into the proc directly (my VBA is a little rusty, so any help from @Scott is appreciated!):
Sub registradonazionebenefattore(IDBenefattore As String, Importo As String)

DoCmd.SetWarnings False

Dim strSQL As String

strSQL = "Update [T_Benefattori]" _
           & " SET [TotaleDonato] = [TotaleDonato] + " & Importo _
           & " WHERE IDBenefattori = " & IDBenefattore

DoCmd.RunSQL strSQL


End Sub

Open in new window

And then you would call the procs using the values in the parentheses. For example, if those values were in myTextbox1 and myTextbox2 on an open form:
Private Sub Form_AfterInsert()
Call registradonazionebenefattore(Forms!myForm.myTextbox1.Value.ToString, Forms!myForm.myTextbox2.Value.ToString)
Call registradonazioneprogetto(...something similar here...)
End Sub

Open in new window

0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 800 total points
ID: 40272202
Russel's suggestion is accurate, although I'd use Currentdb.Execute instead of RunSQL:

Sub registradonazionebenefattore(IDBenefattore As String, Importo As String)
  Dim strSQL As String

  strSQL = "Update [T_Benefattori]" _
           & " SET [TotaleDonato] = [TotaleDonato] + " & Importo _
           & " WHERE IDBenefattori = " & IDBenefattore

  Currentdb.Execute strSQL
End Sub

Open in new window

Note too that if IDBenefattori is a Text value, you'd have to do this:

  strSQL = "Update [T_Benefattori]" _
           & " SET [TotaleDonato] = [TotaleDonato] + " & Importo _
           & " WHERE IDBenefattori = '" & IDBenefattore & "'"

Basically, I wrapped that value in single quotes. You'd only do this if it were a Text value, however.

Regarding your other Data Macros - instead of dumping the XML, post the desired result here, and we can help.
0
 

Author Comment

by:Antonio Biasuzzi
ID: 40274374
yuppy!!
i have done something that work !!

this is the code As the 3 data macro. IT WORK!! insert into a separeted module

Option Compare Database
Option Explicit
Public prmIDBenefattore As Variant
Public prmImporto As Variant
Public prmIDProgetti As Variant

Sub registradonazionebenefattore()
Dim IDBenefattore0 As Integer
Dim Importo0 As Integer
DoCmd.SetWarnings False
  Dim strSQL As String

  strSQL = "Update [T_Benefattori]" _
           & " SET [TotaleDonato] = [TotaleDonato] + " & Importo0 & "" _
           & " WHERE IDBenefattori = " & IDBenefattore0 & ""
DoCmd.RunSQL strSQL

End Sub


Sub registradonazioneprogetto()
DoCmd.SetWarnings False
Dim DonazioniRicevute As Integer
Dim Importo0 As Integer
Dim IDProgetto0  As Integer
Dim strSQL As String
strSQL = "Update [T_Progetti]" _
           & " SET [DonazioniRicevute] = [DonazioniRicevute] + " & Importo0 & "" _
           & " WHERE IDProgetti = " & IDProgetto0 & ""
DoCmd.RunSQL strSQL
End Sub


Sub ricalcola()
Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
Dim TotaleDonato As Integer
Dim DonazioniRicevute As Integer
Dim Importo As Integer
strSQL = "Update [T_Benefattori]" _
& "SET TotaleDonato = 0"
DoCmd.RunSQL strSQL
strSQL1 = "UPDATE T_Benefattori LEFT JOIN T_Donazioni ON" _
& " T_Benefattori.[IDBenefattori] = T_Donazioni.[IDBenefattore]" _
& " SET T_Benefattori.TotaleDonato = [T_Benefattori]![TotaleDonato]+[T_Donazioni]![Importo];"
DoCmd.RunSQL strSQL1
strSQL3 = "UPDATE T_Progetti SET T_Progetti.DonazioniRicevute = 0" _
& " WHERE (((T_Progetti.DonazioniRicevute) Is Null)) OR (((T_Progetti.DonazioniRicevute)=0));"
DoCmd.RunSQL strSQL3
strSQL4 = "UPDATE T_Progetti LEFT JOIN T_Donazioni ON" _
& " T_Progetti.[IDProgetti] = T_Donazioni.[IDProgetto]" _
& " SET T_Progetti.DonazioniRicevute = [T_Progetti]![DonazioniRicevute]+[T_Donazioni]![Importo];"
DoCmd.RunSQL strSQL4
End Sub

Open in new window


i insert this code in the mask:

Option Compare Database
Option Explicit
Public prmIDBenefattore As Variant
Public prmImporto As Variant
Public prmIDProgetti As Variant

Private Sub Form_AfterInsert()
Call registradonazionebenefattore
Call registradonazioneprogetto
End Sub

Private Sub Form_AfterUpdate()
Dim strOldID As Integer
Dim strOldImp As Integer
Dim strOldProg As Integer

If Not (strOldID = IDBenefattore) Then

prmIDBenefattore = [IDBenefattore0].OldValue
prmImporto = [Importo0].OldValue
Call registradonazionebenefattore

prmIDBenefattore = [IDBenefattore]
prmImporto = [Importo]
Call registradonazionebenefattore
End If
If Not (strOldImp = Importo) Then
If Not (strOldProg = IDProgetto) Then
Call registradonazioneprogetto
prmIDProgetti = [IDProgetto0].OldValue
prmImporto = [Importo0].OldValue
Call registradonazioneprogetto
prmIDProgetti = [IDProgetto]
prmImporto = [Importo]
End If
Dim varModificaImporto As Variant
varModificaImporto = [Importo] - [Importo0].OldValue
If (strOldID = IDBenefattore) Then
'Se la donazione del benefattore non è stata ancora registrata, registra la modifica per la donazione del benefattore </Comment>
Call registradonazionebenefattore
prmIDBenefattore = [IDBenefattore]
prmImporto = [varModificaImporto]
End If
If (strOldProg = IDProgetto) Then
Call registradonazioneprogetto
prmIDProgetti = [IDProgetto]
prmImporto = [varModificaImporto]
    End If
    
End If

Open in new window

Call ricalcola
End Sub

Open in new window

Private Sub Form_Delete(Cancel As Integer)
prmIDBenefattore = [IDBenefattore0].OldValue
prmImporto = -[Importo0].OldValue
Call registradonazionebenefattore
prmIDProgetti = [IDProgetto0].OldValue
prmImporto = -[Importo0].OldValue
Call registradonazioneprogetto

End Sub

Open in new window


AFTER UPDATE not work good... but i solved call recalc function..

i want make it better.
i have problem with After update because use "oldvalue"
as "if old value is different of newvalue ecc.."
i don't know how white it...
thanks you!!!
0
 
LVL 28

Expert Comment

by:MacroShadow
ID: 41655163
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Split:
-- Russell Fox (https:#a40269120)
-- Scott McDaniel (Microsoft Access MVP - EE MVE ) (https:#a40269951)
-- Russell Fox (https:#a40270556)
-- Russell Fox (https:#a40271316)
-- Scott McDaniel (Microsoft Access MVP - EE MVE ) (https:#a40272202)


If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

MacroShadow
Experts-Exchange Cleanup Volunteer
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question