[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 523
  • Last Modified:

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.

TABLES:
tbl_owned
     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
tbl_leases
     ID, Equipment (lookup tbl_owned.ID), Contact (lookup tbl_contacts.id),Date Taken, Condition Taken, Date Returned, Condition Returned

FORMS:
frm_Owned
frm_Leases

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:

psudo-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.
0
rose1013
Asked:
rose1013
  • 4
  • 3
1 Solution
 
LennyGrayCommented:
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
Else
   Me!Returned = False
Endif

If you named the controlname something other than the name of the table column name, then use the control name in the Me!....
0
 
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.
0
 
LennyGrayCommented:
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.
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

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

0
 
LennyGrayCommented:
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.
0
 
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?
0
 
LennyGrayCommented:
Yup.

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!

Lenny
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now