Building a lease-tracking db

Posted on 2008-11-16
Last Modified: 2013-11-28
I own a lot of equipment that I lend/rent out to friends/business partners. Until now, I have not felt a need to develop some way of tracking the equipments location, but it has become necessary. Therefore I am building a database in Access to accommodate this.

     ID,Equipment, Price/day, Price/week,Condition,notes,purchase price, purchase date, returned
tbl_contacts (linked to my separate contacts db)
     ID, normal address book info
     ID, Equipment (lookup tbl_owned.ID), Contact (lookup,Date Taken, Condition Taken, Date Returned, Condition Returned


When you add a new entry to tbl_leases, via frm_leases, and provide a Date Taken value, tbl_owned.returned should become 0 (no). Then when you update that entry with a Date Returned value, tbl_owned.returned should become 1.

I don't know how to do this. I tried to write a vb code:

date_returned after update
     tbl_owned.returned = 1

but that returned the "Object required error." My guess is I am referring to the table or field in the wrong way, but I can't seem to get a handle on such references. Thanks.
Question by:rose1013
    LVL 10

    Expert Comment

    I assume that the data element, Returned, is a boolen (yes/no). If you have a checkbox on your form with Returned as the controlsource, then you would place the following code in the afterupdate of the DateReturned control:

    If Not IsNull(Me!DateReturned) then
        Me!Returned = True
       Me!Returned = False

    If you named the controlname something other than the name of the table column name, then use the control name in the Me!....

    Author Comment

    Thanks, but...

    "You can't assign a value to this object." is stated to the Me!Returned = True. It won't let me change the value of the check box...This was actually the first thing I tried, and it gave that result :< So I tried to add the tbl_owned.returned boolean to the forms data source. No good still.
    LVL 10

    Expert Comment

    Check the controlsource of the check box (use the pulldown on the right of the controlsource line). It sounds like you do not have a vaild column name assigned to it.

    You also get that error when the underlying query is not updateable.

    Author Comment

    Ctrl Src=[tbl_Owned]![Returned] ... looks right. But something clearly is not.

    LVL 10

    Expert Comment

    the checkbox control source should only read: Returned

    The table name is in the form's record source and the form recordsource should read: tbl_Owned

    Use the pulldowns on each property line when appropriate.

    The form should be bound to a table and teh controls bound to columns in the table.

    Author Comment

    Problem is most of the info on this form is designed for tbl_leases. The check is the only object that needs tbl_owned at all (other than display lookups for equipment and contact). But if I make the record source a query then it becomes not updatable, so the problem would persist, right?
    LVL 10

    Accepted Solution


    You should have a table for the owners and a table for the leased items.

    Then you can create a form and a subform that you could use.

    Look in Northwinds and look at the orders form for subform examples.

    It seems that you need to rethink the design.

    Good Luck!


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now