Newbie: Code to Increment Unbound Table Field

[Nunnsby]

Hi There

Simple answer required for newbie, if that is possible when dealing
with code!

I have a mailing list with a form, bound to a table: tbl_mailingList. I
need to record the numbers of records we delete.

I have created a new table, tbl_deletedRecords, with only a number
field: deleted_records. No primary required, I don't think.

I have a button on the form to delete records. All I need to do is
increment the deleted_records field when the button is clicked. That is
all. Nothing fancy.

I think I know what needs to be done, and I thought something along the
lines of the code below would help, but I am struggling with the
language, as I realise it is wrong, and I can't figure it out.

'**I AM a Newbie don't forget.

dim countDeleted as Integer
countDeleted = 0
countDeleted = tbl_deletedRecords.deleted_records.value
tbl_deletedRecords.deleted_records.value = countDeleted + 1

'**
I realise this is seriously wrong, but the logic is right to me, so if
anyone can point it out to me. The biggest issue I have is the syntax
of referencing the value, of field in the table, as it is unbound.

I also know there is the DMax field, but can't seem to get the syntax
right in that either, and not too sure if that is right either.

As for the records, before anyone asks, once they are gone, they can
stay gone, as we won't need the historical data from them. I just want
to count the numbers we are losing over the period of a year.

Any help?

Thanks greatly

Richard

 

[Douglas J Steele]

Unfortunately, you can't work directly with tables in VBA.

Your two options are to create a recordset and work with it:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDB()
Set rsCurr = dbCurr.OpenRecordset( _
"SELECT deleted_records FROM tbl_deletedRecords")
If Not rsCurr.EOF Then
rsCurr.Edit
rsCurr!deleted_records = rsCurr!deleted_records + 1
rsCurr.Update
Else
rsCurr.AddNew
rsCurr!deleted_records = 1
rsCurr.Update
End If

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing

or use SQL:

Dim strSQL As String

strSQL = "UPDATE tbl_deletedRecords " & _
"SET deleted_records = deleted_records + 1"

CurrentDb.Execute strSQL, dbFailOnError

Note that it's almost always better to use SQL rather than a recordset when
you can.

Having said all that, though, note that another option is not to physically
delete the records from your table, but instead simply set a DeleteFG field
to True when they're deleted. In that way, you can create queries that
return only the active records, or only the deleted records, and you don't
need the second table (you can always determing how many records have been
deleted by doing a count on the table)