Append Query Problem - Posting record only once

  • Thread starter Thread starter wesley.allen
  • Start date Start date
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.
 
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
 
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
 
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
..
 
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.
 
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
..
 
Back
Top