Add new record to a log when existing records are modified?

  • Thread starter Thread starter Mick
  • Start date Start date
M

Mick

This may be a simple thing, but I figgured I'd ask just
the same...

I have a database that I got from somewhere for managing
a DVD library. I've added a couple of things for keeping
track of what DVDs are loaned out, and who they're loaned
to. I have a special form that connects to teh main table
and allows you to check a disc out, and record the date
and who borrowed it. What I'm trying to do is, whenever I
use the 'loan a DVD' form, I want it to copy the info
that's in that record to another table (a log) and add
that info as a new record. This should be done on close
of the 'loan' form provided the "out on loan" check box
is checked (I obviously wouldn't want it to add the
record if I had opened the wrong record and never checked
the 'loaned' box). SO generaly, I think the logic I'm
looking for is some sort of 'on close, if 'Loaned'=yes,
copy the current record to new record in Log'

Any suggestions and directions MUCH appreciated. Thanks!
 
What I'm trying to do is, whenever I
use the 'loan a DVD' form, I want it to copy the info
that's in that record to another table (a log) and add
that info as a new record. This should be done on close
of the 'loan' form provided the "out on loan" check box
is checked (I obviously wouldn't want it to add the
record if I had opened the wrong record and never checked
the 'loaned' box). SO generaly, I think the logic I'm
looking for is some sort of 'on close, if 'Loaned'=yes,
copy the current record to new record in Log'
Click to expand...

One common way to handle library loans in a database is to explicitly
have a Loans table related one-to-many to the table of "books" (DVD's
in this instance). Rather than having a yes/no "out on loan" field in
the table of DVD's, you would have a Subform into which you would
explicitly add the information - who borrowed it, when, for how long.
This provides your historical log; in order to determine if a DVD is
out on loan, you can simply search this table for a checked-out date
in the past and a check-in date that is either NULL or in the future.

But to directly answer your question, yes - you can put VBA code in
the Form's BeforeUpdate event that will look at the value of the
checkbox and if it's True, open a Recordset and add a new record.
 
Back
Top