MS Access Help Needed

[marycp]

I am not sure if anyone can help me, but here it goes...

I have a Microsoft Access DB with two tables, customer and transactions. The tables are related by customer_id field in a one to many relationship. One record in Customer can relate to many records in transactions.

I have a form to allow data input the customer input form has a sub form for all the transactions for the customer. It is working great for data entry. However, I need to the transaction subform to update the account_balance field in the customer table each time a new transaction is added.

I know how to do this with SQL Server, you use an update trigger. But with MS Access I am lost. I know its an Event on the transaction_cost field on the transaction subform but have not been able to write it.

Does anyone have any MS Access expertise and can offer some advice? Don't tell me to use SQL Server, because I can't.

TIA
Mary

[Iman74]

***Bump***

My wife really needs help with this.

[doctorgonzo]

Using triggers, while possible, is a bad idea anyway.

From what I understand, the account balance field in the customers table is simply a calculated field based on the sum of all of the customer's transaction. If that is the case, then the best thing to do is to drop the field entirely and calculate that number in a query on the fly. Whenever you have a calculated field stored in a table like that, there is always the possibility that it won't get updated correctly.

If you absolutely MUST have that calculated amount in the customers table, I would create an update query that adjusts the amount and run it whenever a new transaction is added. That could still fail to keep the data correct, however.

I would be happy to take a further look at the particular problem.

[marycp]

Originally posted by doctorgonzo Using triggers, while possible, is a bad idea anyway. From what I understand, the account balance field in the customers table is simply a calculated field based on the sum of all of the customer's transaction. If that is the case, then the best thing to do is to drop the field entirely and calculate that number in a query on the fly. Whenever you have a calculated field stored in a table like that, there is always the possibility that it won't get updated correctly. If you absolutely MUST have that calculated amount in the customers table, I would create an update query that adjusts the amount and run it whenever a new transaction is added. That could still fail to keep the data correct, however. I would be happy to take a further look at the particular problem.

I have to use a calculated amount in a field. You are right though, calculating the field on the fly is a better way to do it, but the rules of the assignment are specific.

My problem is that I don't understand how to set up and run an update query to launch when the transaction table is changed in Access. Everything I write doesn't work. I am not good enough with Access to figure it out.

[doctorgonzo]

I don't know of a way to set up a query to automatically run when data in a table is changed. However, you can set up a query to run when data in a form changes, using one of the form events. If the data is changed using the form, it will work. If somebody directly edits the table, though, the query won't run and it won't be updated.

If I had to do it, a parameterized update query that accepted the customerID would probably work best. You can call it from the "After Insert" or "After Update" event of the transaction form.

[marycp]

Originally posted by doctorgonzo I don't know of a way to set up a query to automatically run when data in a table is changed. However, you can set up a query to run when data in a form changes, using one of the form events. If the data is changed using the form, it will work. If somebody directly edits the table, though, the query won't run and it won't be updated. If I had to do it, a parameterized update query that accepted the customerID would probably work best. You can call it from the "After Insert" or "After Update" event of the transaction form.

Yes that is what I meant, when the FORM changes. The problem is I do not know how to write the parameteritized update query and how to put it in the event. Update queries don't see to show up in expression builder. Can you give me an example of a Parameteritized update query and then the best way to hook it into a form?

Boy give me SQL server and Cold Fusion any day

TIA

[doctorgonzo]

Here is something using ADO:

Set qryDel = New ADODB.Command
With qryDel
.ActiveConnection = CurrentProject.Connection
.CommandText = "qupdAccountBalance"
.CommandType = adCmdStoredProc
.Execute Parameters:=customerID
End With

I use ADO in my databases, but you could also use DAO (may be slightly faster for Access). In that case, you would use a QueryDef object.

Check out this link for some other information: http://msdn.microsoft.com/library/de...ml/sa00e19.asp

[marycp]

Ok this really helped, but now my problem is actually writing the update statement. I can write a select statement that sums up the transaction cost:

SELECT Sum([transaction].[transaction_cost]) AS Expr1
FROM [transaction]
WHERE (((transaction.customer_id)=[Forms]![Customer Input Form]![customer_id]));

When I run it, it asks me for the customer_id.

however when I try to do this in a update statement it simply won't let me do it. It gives me all sorts of weird errors.

What would the update statement look like?

I am so lost in this application, its killing me.

[marycp]

I tried to hook my select query up to code and it doesn't work. This shouldn't be this complicated should it? I can't understand why its so hard to figure it out.

[doctorgonzo]

I think the best thing to do would be to use code to change the SELECT query instead of using a parameter. So in the form event, change the query SQL to something like this:

strSQL = "SELECT Sum([transaction].[transaction_cost]) AS Expr1
FROM [transaction]
WHERE (((transaction.customer_id)=" & Me![Customer Input Form]![customer_id] & "))"

Save the SQL string to the query (how this works depends on whether you use ADO or DAO; DAO is probably easier in this example). Then, created an UPDATE query based on this SELECT query. Because the SELECT query will have the actual customerID in it, instead of a parameter, it will be able to run without giving any errors. It is not the most elegant solution, but it will work.

[marycp]

DrGonzo... I decided that my teacher is tricking us and going back to your original suggestion to sum up the transactions during a query and forgo all this VBA BS. There is no way he could be asking us to do this much difficult work in this class.

The only thing I want to do is have another field updated when I add a transaction. So I put an account_balance field with does a
=DSum("[transaction_cost]","transaction","[customer_id] = [Forms]![Customer Input Form]![customer_id]")

when the form is first loaded. Now I just need to make the transaction_cost field update that field when it is updated. That has to be possible.