Append Query Problem - Posting record only once

W

wesley.allen

Hello,

I have a query that calculates a bank balance. I want this query to
append the current days date and the bank balance to a table. I have
a macro setup to autorun the query when my database opens, but can't
get it to work correctly. Details:


Table where I want the records added: BankingBalanceRecord
Query that will run: BankingRecordBalance
-This query is setup to pull
today's date and the balance. It is funtioning properly.


The main problem I am having is that if the primary record (Today's
date) already exists, it gives me an error message. As I open the
database multiple times a day, i only want it to record the balance
the first time.


Thanks for the help.
 
D

Dale Fye

Wes,

Before you run the append query, you need to test to determine whether a
record for that date already exists. Something like:

If DCOUNT("SomeField", "BankingBalanceRecord", "DateField = #" & date() &
"#") = 0 then
currentdb.execute qry_BankingRecordBalance
Endif

HTH
Dale
 
W

wesley.allen

I tried your suggestion. I attempted to add it into the query, in a
private sub function, and a module. Nothing seems to work. When I
enter the text, it turns red and says there is a compile error. I am
not very experienced with SQL, so I may be missing some code or not
entering it in the correct place.

Thanks
 
J

John Spencer

Did you enter the following as ONE LINE. The newsgroup reader may have
wrapped it to TWO Lines.

If DCOUNT("SomeField", "BankingBalanceRecord", "DateField = #" & date() &
"#") = 0 then

currentdb.execute qry_BankingRecordBalance

Also there was a small error in the code, EndIf should be End If

Or try it this way with a line continuation so wrapping should not be a
problem

If DCOUNT("SomeField", "BankingBalanceRecord", _
"DateField = #" & date() & "#") = 0 then

Currentdb.Execute qry_BankingRecordBalance
End If

That does not go in the query, but should be in a sub or function. Probably
the sub for the Open Event of a form.

Another method might be to modify your query, but to suggest an alternative
for that would require that you post the SQL of the query.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
W

wesley.allen

INSERT INTO BankingBalanceRecord ( Balance, [Date] )
SELECT SumBalance.[Working Balance], Date() AS [Date]
FROM SumBalance;

This is a copy of the code. I am still having trouble. Sorry.
 
J

John Spencer

You might try the following UNTESTED SQL

INSERT INTO BankingBalanceRecord (Balance, [Date])
SELECT SumBalance.[Working Balance], Date() AS [Date]
FROM SumBalance
WHERE Not Exists
(SELECT *
FROM BankingBalanceRecord
WHERE BankingBalanceRecord.Date = Date())

The SQL should run but should not add a record if one already exists for the
date
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Top