How to populate field in tbl while counting records

[Benny via AccessMonster.com]

I have a routine that counts the number of new records. What I need is to
place a “YES” in the new field of the tblAuditInput table if that record is
new. The routine is appending new records to the table. “New” is just a
flag to let me know that these records are new.



How do I place “yes” in the NEW field of tblAuditInput for each count of the
new record??



Here’s my code:



'(gather and count new SSN records)

'compare Import List to audit and archive. If no match found, then count #of
new SSN's

Const SQL = "SELECT Count(*) AS NewRecords " & _

"FROM tblImport LEFT JOIN tblAuditInput ON tbl Import.SSN =
tblAuditInput.SSN " & _

"WHERE tblAuditInput.SSN Is Null"



' (count new SSN records)

Set db = CurrentDb

Set rs = db.OpenRecordset(SQL)



'displays the count/# of new records

If Not rs.EOF Then

MsgBox rs(0) & " New Records", vbInformation

End If



Set rs = Nothing

Set db = Nothing





'Next, it appends the new SSN's to the Audit table – must have ‘yes’ in the
NEW field of tblAuditInput

DoCmd.OpenQuery "qryAppendToAudit"



End If

 

[Guest]

YOu can do this in your qryAppendToAudit
If the New field is text then in the Field row of the New field column in
your query just put in "Yes", If it is a Yes/No field, then just put in True