I have a table tblCosts which i display on an msaccess front end which enables users to add new entries as well as update existing ones. The table is structured as below.
ExpenseType Month Year Cost
Hardware June 2017 $500
Software July 2017 $300
Hardware Sept 2017 $150
I have an update and insert queries which work fine when run manually.
However I am having trouble differentiating the condition when to fire the query on the form. For example, if the record exists in the table, it should run the update query, if record does not exist, it should run the insert query.
For example if someone puts in
- Hardware Sept 2017 $120
it should update the 3rd entry from 150 to 120 but if someone puts in
- Furniture Sept 2017 $350
it should recognize that Furniture is not part of the DB and run the insert query.
I have the update and insert queries but need help in identifying the condition when to run them.
The Update query I'm using is:
Update tblCosts
set tblCosts.Cost=[Forms]![frmCost]![txtCost]
where tblCosts.ExpenseType = [Forms]![frmCost]![txtExpType]
and tblCosts.Month = [Forms]![frmCost]![txtMonth]
and tblCosts.Year = [Forms]![frmCost]![txtYear]
The Insert query I'm using is:
Insert into tblCosts (ExpenseType , Month, Year, Cost)
Select [Forms]![frmCost]![txtExpType] as Exp1,
[Forms]![frmCost]![txtMonth] as Exp2,
[Forms]![frmCost]![txtYear] as Exp 3,
[Forms]![frmCost]![txtCost] as Exp 4
Need code (VBA or macro) behind a form that determines which action query to run. In VBA something like:
If DCount("*", "tablename", "ExpenseType='" & Me.cbxExpense & "' AND [Month]='" & Me.tbxMonth & "' AND [Year]=" & Me.tbxYear) = 0 Then
CurrentDb.Execute "INSERT INTO tablename (Expense, [Month], [Year], Cost) VALUES ('" & Me.cbxExpense & "', '" & Me.tbxMonth & "', " & Me.tbxYear & ", " & Me.tbxCost & ")"
Else
CurrentDb.Execute "UPDATE tablename SET Cost=" & Me.tbxCost & " WHERE Expense='" & Me.cbxExpense & "' AND [Month]='" & Me.tbxMonth & ", [Year]=" & Me.tbxYear
End If
Probably also want some validation code to make sure all four controls have data before executing queries.
The real trick is figuring out what event to put code into - the Cost AfterUpdate will work as long as the other fields have data entered first, otherwise the validation will fail and user will have to re-enter cost.
Could have code that doesn't make each control available until previous value is entered.
Month and Year are reserved words and should not use reserved words as names for anything.
Would be better to save month numbers instead of month names for sorting purposes.
Why updating a value which really should be a calculated aggregation of transaction records?