Related Fields

[Jbunks]

Hi

I'm trying to set up a database to list all my vinyl records. I have set up
four tables.

Catalogue - which holds the individual artist/title/catalogue code etc of
each vinyl.
Genre - simply holds an autonumber and a genre name
Loan - which holds information about any records i may have lent to people.
Record Label - which holds the contact details of the record label.

The catalogue table is main part of the database which links to all the
other tables. For example it is linked to the genre table, loan table and
record label table. No other relationships exist.

Obviously if a record comes is release on a particular label or is a
particular genre then this is not going to change and causes no problems.

With regard to the loan to the loan table, this obvously can change when
people return the record. If i have set the form to look up a value in loan
table then i cannot remove this when the record is returned as i get the
following error:

You tried to assign a Null value to a variable that is not a variant data
type.

I am able to delete the value in that field if i view the record through the
table view.

So i actually have two questions:

1. Is it possible to delete the loan name through the form view;
2. The catalogue table has a yes/no field titled loan. Can i set the form to
only display the loan information if this field value is set to yes.

Any help would be much appreciated as this has been bugging me for years!!

 

[Klatuu]

I would move the loan field to the loan database and rename it returned.
Then I would not delete any loan records. That way you have a history of
loans.
Then, I would filter the loans table on returned = False in my form. Then
you would see only outstanding loans.
In fact, maybe leave the loan field in the catalogue table and use it to
identify any albums you have borrowed.

 

[Jbunks]

Thanks for your speed reponse. The way i saw it, whether the record is on
loan is an attritube of an individual piece of vinyl not part of the borrower
information. I'm a bit new to these discussion group is it possible to
publish databases or send them for other member to look at and give views?

 

[Klatuu]

No, it would not be part of the borrower info. A loan would be an entity on
it's own. If you are going to keep info on borrowers, you might consider a
borrower table. So the tables I would consider are:
Catalogue
Tracks
Genre
Record Label
Artist
Loan
Borrower

Yes, it is acceptable to share databases. The only issue is that some are
reluctant to publish their E-mail address in a news group. My problem is
where I work, mdb files and zip files are blocked coming in. I have had a few
sent to me, but I always request that the other party give me their E-mail
address. I work for a government defense contractor and the security is
pretty high.

 

[Jbunks]

i'm not really concerned about publishing mine it's (e-mail address removed).

If you could get in contact and help me out it would be much appreciated.