Asset Tracking: Disposals of Stock

18 Jun 2009 3:42 PM

VT

Hello

I have a fairly conventional demo stock tracking database, where each loan
is handled by a form and subform based on a loan header table and a loan
details table.

How do I handle the situation where a demo unit is kept permanently by the
borrower? I need to remove the demo unit in question but each piece of stock
has many related records in the loan details table, which cannot be deleted.

Many thanks

VT

18 Jun 2009 4:24 PM

John Spencer MVP

Add a NoLongerAvailable field to the table where the unit is recorded. The
field can be a simple yes/no field or it can be a text field where you list
the reason or a date field where you input the date when the unit was lost,
stolen, broken, retired from service, etc.

Now you just check to see if the field is null (or false if you use a yes/no
field) to decide if the unit is available for use.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

VT wrote:
Show quoteHide quote

> Hello
>
> I have a fairly conventional demo stock tracking database, where each loan
> is handled by a form and subform based on a loan header table and a loan
> details table.
>
> How do I handle the situation where a demo unit is kept permanently by the
> borrower? I need to remove the demo unit in question but each piece of stock
> has many related records in the loan details table, which cannot be deleted.
>
> Many thanks
>
> VT

18 Jun 2009 4:29 PM

Fred

The simplest method would be to put a field in there that says that, and have
your views not show those that are gone permanently.

18 Jun 2009 5:33 PM

Steve

I can't say specifically without seeing the design of your tables. But if
your tables are normalized, you just need to set Cacade Delete in the
relationship and when you delete a demo unit, all related records in the
loan details table will be automatically deleted.

Steve
san***@penn.com

Show quoteHide quote

"VT" <V*@discussions.microsoft.com> wrote in message
news:BEAFE09D-4A02-43D1-8185-DEA1FCA953DC@microsoft.com...
> Hello
>
> I have a fairly conventional demo stock tracking database, where each loan
> is handled by a form and subform based on a loan header table and a loan
> details table.
>
> How do I handle the situation where a demo unit is kept permanently by the
> borrower? I need to remove the demo unit in question but each piece of
> stock
> has many related records in the loan details table, which cannot be
> deleted.
>
> Many thanks
>
> VT

18 Jun 2009 5:55 PM

VT

Steve,

I don't want the loan details deleted, I just need to show that an item has
been disposed of but the asset's history must remain.

Show quoteHide quote

"Steve" wrote:

> I can't say specifically without seeing the design of your tables. But if
> your tables are normalized, you just need to set Cacade Delete in the
> relationship and when you delete a demo unit, all related records in the
> loan details table will be automatically deleted.
>
> Steve
> san***@penn.com
>
>
> "VT" <V*@discussions.microsoft.com> wrote in message
> news:BEAFE09D-4A02-43D1-8185-DEA1FCA953DC@microsoft.com...
> > Hello
> >
> > I have a fairly conventional demo stock tracking database, where each loan
> > is handled by a form and subform based on a loan header table and a loan
> > details table.
> >
> > How do I handle the situation where a demo unit is kept permanently by the
> > borrower? I need to remove the demo unit in question but each piece of
> > stock
> > has many related records in the loan details table, which cannot be
> > deleted.
> >
> > Many thanks
> >
> > VT
>
>
>

18 Jun 2009 7:19 PM

Steve

Sorry, I misread your post! John and Fred have the answer for you.

Steve

Show quoteHide quote

"VT" <V*@discussions.microsoft.com> wrote in message
news:E4526F21-12A7-4368-A35A-A4C18CBB6120@microsoft.com...
> Steve,
>
> I don't want the loan details deleted, I just need to show that an item
> has
> been disposed of but the asset's history must remain.
>
> "Steve" wrote:
>
>> I can't say specifically without seeing the design of your tables. But if
>> your tables are normalized, you just need to set Cacade Delete in the
>> relationship and when you delete a demo unit, all related records in the
>> loan details table will be automatically deleted.
>>
>> Steve
>> san***@penn.com
>>
>>
>> "VT" <V*@discussions.microsoft.com> wrote in message
>> news:BEAFE09D-4A02-43D1-8185-DEA1FCA953DC@microsoft.com...
>> > Hello
>> >
>> > I have a fairly conventional demo stock tracking database, where each
>> > loan
>> > is handled by a form and subform based on a loan header table and a
>> > loan
>> > details table.
>> >
>> > How do I handle the situation where a demo unit is kept permanently by
>> > the
>> > borrower? I need to remove the demo unit in question but each piece of
>> > stock
>> > has many related records in the loan details table, which cannot be
>> > deleted.
>> >
>> > Many thanks
>> >
>> > VT
>>
>>
>>

18 Jun 2009 8:51 PM

VT

Thanks John, Fred and Steve for your help!