Building a lease-tracking db

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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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!....
rose1013Author Commented:
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.
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

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.
rose1013Author Commented:
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?

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!


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.