update query- HELP??

[DP]

hi.,

i've got 3 tables, customer, film and filmrental.

i've got a customer form, with a sub form at the bottom, which is a film
rental subform.

i've created an update query, which when a filmid, is entered into the
subform, the 'available' check box in the film table, becomes false
(Unchecked). according to which filmID it is. heres the code, i;ve got in an
update query. "Available"=False

NOTE. it is not in VB, its just in the 'Update To' bit, in the design view.

this is all good, as it works.

how can i get it to do the opposite, when i delete a record, from the
subform? e.g. when the record is deleted, the checkbox becomes (Ticked) True
again?

tia

Dev

[Anthony England]

"DP" <[e-mail]> wrote in message
news:yw******************@newsfe5-gui.ntli.net...

hi.,

i've got 3 tables, customer, film and filmrental.

i've got a customer form, with a sub form at the bottom, which is a film
rental subform.

i've created an update query, which when a filmid, is entered into the
subform, the 'available' check box in the film table, becomes false
(Unchecked). according to which filmID it is. heres the code, i;ve got in
an
update query. "Available"=False

NOTE. it is not in VB, its just in the 'Update To' bit, in the design
view.

this is all good, as it works.

how can i get it to do the opposite, when i delete a record, from the
subform? e.g. when the record is deleted, the checkbox becomes (Ticked)
True
again?

tia

Dev

If the design of this database is as I understand, you simply add and delete
records from the filmrental table to model films being checked in and
checked out. Obviously this would leave you with no history of which
customers have rented which films - is that the intended design?
If so, then you don't need the field 'Available' in the films table as a
simple dlookup would tell you whether the film was in or out. Even with a
more sophisticated design, one could debate the pros and cons of having the
'Available' field - as it introduces the possibility of inconsistencies in
the database.

[DP]

yes, i do not require a history. so wat do u propose i do? is there a way to
prevent a film from being checked-out twice?? if so, how do i do that?

i just wanted to make the program a bit more sophisisticated by introducing
an available field, so it is clear to the user, that the film is either on
rent, or in store.

u mentioned dlookup, that just basically looks up wat is already in the
table. u cant edit the value?

dev

"Anthony England" <ae******@oops.co.uk> wrote in message
news:dt**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...

"DP" <[e-mail]> wrote in message
news:yw******************@newsfe5-gui.ntli.net...

hi.,

i've got 3 tables, customer, film and filmrental.

i've got a customer form, with a sub form at the bottom, which is a film
rental subform.

i've created an update query, which when a filmid, is entered into the
subform, the 'available' check box in the film table, becomes false
(Unchecked). according to which filmID it is. heres the code, i;ve got in an
update query. "Available"=False

NOTE. it is not in VB, its just in the 'Update To' bit, in the design
view.

this is all good, as it works.

how can i get it to do the opposite, when i delete a record, from the
subform? e.g. when the record is deleted, the checkbox becomes (Ticked)
True
again?

tia

Dev

If the design of this database is as I understand, you simply add and

delete records from the filmrental table to model films being checked in and
checked out. Obviously this would leave you with no history of which
customers have rented which films - is that the intended design?
If so, then you don't need the field 'Available' in the films table as a
simple dlookup would tell you whether the film was in or out. Even with a
more sophisticated design, one could debate the pros and cons of having the 'Available' field - as it introduces the possibility of inconsistencies in
the database.

[Anthony England]

"DP" <[e-mail]> wrote in message
news:3G*******************@newsfe5-gui.ntli.net...

yes, i do not require a history. so wat do u propose i do? is there a way
to
prevent a film from being checked-out twice?? if so, how do i do that?

i just wanted to make the program a bit more sophisisticated by
introducing
an available field, so it is clear to the user, that the film is either on
rent, or in store.

u mentioned dlookup, that just basically looks up wat is already in the
table. u cant edit the value?

dev

"Anthony England" <ae******@oops.co.uk> wrote in message
news:dt**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...

"DP" <[e-mail]> wrote in message
news:yw******************@newsfe5-gui.ntli.net...

> hi.,
>
> i've got 3 tables, customer, film and filmrental.
>
> i've got a customer form, with a sub form at the bottom, which is a
> film
> rental subform.
>
> i've created an update query, which when a filmid, is entered into the
> subform, the 'available' check box in the film table, becomes false
> (Unchecked). according to which filmID it is. heres the code, i;ve got in > an
> update query. "Available"=False
>
> NOTE. it is not in VB, its just in the 'Update To' bit, in the design
> view.
>
> this is all good, as it works.
>
> how can i get it to do the opposite, when i delete a record, from the
> subform? e.g. when the record is deleted, the checkbox becomes (Ticked)
> True
> again?
>
> tia
>
> Dev

If the design of this database is as I understand, you simply add and

delete

records from the filmrental table to model films being checked in and
checked out. Obviously this would leave you with no history of which
customers have rented which films - is that the intended design?
If so, then you don't need the field 'Available' in the films table as a
simple dlookup would tell you whether the film was in or out. Even with
a
more sophisticated design, one could debate the pros and cons of having

the

'Available' field - as it introduces the possibility of inconsistencies
in
the database.

The fact that you can't edit the value is the beauty of it - you cannot have
inconsistent data. Sure, you can place a control on the main form to show
this: a colour-coded textbox showing either "Available" or "On Loan" would
be one possibility. But the important thing is that the database decides
automatically for you whether the film is available or not by looking in the
filmrental table.
We could give further advice on how to do this, but you have not mentioned
how your database handles quantities of films. Does each entry in the film
table relate to a single copy of the film, or would you list the film as,
say, "Pulp Fiction" and say that you have 3 copies?

[DP]

oh, i'm sort of understanding u.

ive got a film like pulpfiction, but it is duplicated 5 times, so each copy
has is own ID.
it relates as a single copy.

have u got any examples, or somthing i could use to learn how to do that? or
is there a way to stop a film from being rented twice?

thanx

devin

"Anthony England" <ae******@oops.co.uk> wrote in message
news:dt*********@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...

"DP" <[e-mail]> wrote in message
news:3G*******************@newsfe5-gui.ntli.net...

yes, i do not require a history. so wat do u propose i do? is there a way to
prevent a film from being checked-out twice?? if so, how do i do that?

i just wanted to make the program a bit more sophisisticated by
introducing
an available field, so it is clear to the user, that the film is either on rent, or in store.

u mentioned dlookup, that just basically looks up wat is already in the
table. u cant edit the value?

dev

"Anthony England" <ae******@oops.co.uk> wrote in message
news:dt**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...

"DP" <[e-mail]> wrote in message
news:yw******************@newsfe5-gui.ntli.net...
> hi.,
>
> i've got 3 tables, customer, film and filmrental.
>
> i've got a customer form, with a sub form at the bottom, which is a
> film
> rental subform.
>
> i've created an update query, which when a filmid, is entered into the > subform, the 'available' check box in the film table, becomes false
> (Unchecked). according to which filmID it is. heres the code, i;ve got

in

> an
> update query. "Available"=False
>
> NOTE. it is not in VB, its just in the 'Update To' bit, in the design
> view.
>
> this is all good, as it works.
>
> how can i get it to do the opposite, when i delete a record, from the
> subform? e.g. when the record is deleted, the checkbox becomes

(Ticked) > True
> again?
>
> tia
>
> Dev
If the design of this database is as I understand, you simply add and

delete

records from the filmrental table to model films being checked in and
checked out. Obviously this would leave you with no history of which
customers have rented which films - is that the intended design?
If so, then you don't need the field 'Available' in the films table as a simple dlookup would tell you whether the film was in or out. Even with a
more sophisticated design, one could debate the pros and cons of having

the

'Available' field - as it introduces the possibility of inconsistencies
in
the database.

The fact that you can't edit the value is the beauty of it - you cannot

have inconsistent data. Sure, you can place a control on the main form to show
this: a colour-coded textbox showing either "Available" or "On Loan" would
be one possibility. But the important thing is that the database decides
automatically for you whether the film is available or not by looking in the filmrental table.
We could give further advice on how to do this, but you have not mentioned
how your database handles quantities of films. Does each entry in the film table relate to a single copy of the film, or would you list the film as,
say, "Pulp Fiction" and say that you have 3 copies?

[Anthony England]

"DP" <[e-mail]> wrote in message
news:d3******************@newsfe5-gui.ntli.net...

oh, i'm sort of understanding u.

ive got a film like pulpfiction, but it is duplicated 5 times, so each
copy
has is own ID.
it relates as a single copy.

have u got any examples, or somthing i could use to learn how to do that?
or
is there a way to stop a film from being rented twice?

thanx

devin

There is good news and bad news. The good news is that with the database
design as it is it will be extremely easy to stop a film being rented
twice - if you put a unique index on the field filmrental.film then you are
saying that no film can appear twice in the table. It is impossible to make
a mistake in your coding and have the film out twice.

The bad news is that ther is, in fact, no need for the filmrental table.
You only need tables customer and film. In the film table you need to store
which customer has borrowed the film or leave it blank if no-one has the
film. This is extremely simple and guarantees that any copy of a film can
only be on loan to one customer - or to no customers if it is available.
There would only be a need for the third table if you wanted to store
history - which you have said you don't.

If the database is really this simple, then I'd guess this is not a
real-life application, more of an excercise to get to know Access - am I
right?

[DP]

hi,

the film tables only hold the film names, actors, type, etc...
i do need the 3 tables, because thats wat links them altogether. if i only
had 2 tables, i would have a many-to-many relationship, which does not work.

if the 3 table idea is easy, y cant i stop it from holding duplicates?? the
filmID is unique, and its a primary key, the membershipID is unque and a
primary key, and rentledID is unique and a primary key.

however, after all that, i've got this piece of code, which stops duplicate
entries. ;

Private Sub FilmID_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then
Cancel = True
MsgBox "This value has already been used.", _
vbOKOnly + vbInformation, "Duplicate Entry"
End If
End Sub

now the problem is, the cursor is on the filmID field, and i cant move it,
untill i enter an ID (Number), how can i stop this? is there a way to just
'back-out' without having to add a filmID for the sake of it??

thanx

dev

"Anthony England" <ae******@oops.co.uk> wrote in message
news:dt**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...

"DP" <[e-mail]> wrote in message
news:d3******************@newsfe5-gui.ntli.net...

oh, i'm sort of understanding u.

ive got a film like pulpfiction, but it is duplicated 5 times, so each
copy
has is own ID.
it relates as a single copy.

have u got any examples, or somthing i could use to learn how to do that? or
is there a way to stop a film from being rented twice?

thanx

devin

There is good news and bad news. The good news is that with the database
design as it is it will be extremely easy to stop a film being rented
twice - if you put a unique index on the field filmrental.film then you

are saying that no film can appear twice in the table. It is impossible to make a mistake in your coding and have the film out twice.

The bad news is that ther is, in fact, no need for the filmrental table.
You only need tables customer and film. In the film table you need to store which customer has borrowed the film or leave it blank if no-one has the
film. This is extremely simple and guarantees that any copy of a film can
only be on loan to one customer - or to no customers if it is available.
There would only be a need for the third table if you wanted to store
history - which you have said you don't.

If the database is really this simple, then I'd guess this is not a
real-life application, more of an excercise to get to know Access - am I
right?

[Anthony England]

"DP" <[e-mail]> wrote in message
news:91******************@newsfe5-win.ntli.net...

hi,

the film tables only hold the film names, actors, type, etc...
i do need the 3 tables, because thats wat links them altogether. if i only
had 2 tables, i would have a many-to-many relationship, which does not
work.

if the 3 table idea is easy, y cant i stop it from holding duplicates??
the
filmID is unique, and its a primary key, the membershipID is unque and a
primary key, and rentledID is unique and a primary key.

however, after all that, i've got this piece of code, which stops
duplicate
entries. ;

Private Sub FilmID_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then
Cancel = True
MsgBox "This value has already been used.", _
vbOKOnly + vbInformation, "Duplicate Entry"
End If
End Sub

now the problem is, the cursor is on the filmID field, and i cant move it,
untill i enter an ID (Number), how can i stop this? is there a way to just
'back-out' without having to add a filmID for the sake of it??

thanx

dev

"Anthony England" <ae******@oops.co.uk> wrote in message
news:dt**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...

"DP" <[e-mail]> wrote in message
news:d3******************@newsfe5-gui.ntli.net...

> oh, i'm sort of understanding u.
>
> ive got a film like pulpfiction, but it is duplicated 5 times, so each
> copy
> has is own ID.
> it relates as a single copy.
>
> have u got any examples, or somthing i could use to learn how to do that? > or
> is there a way to stop a film from being rented twice?
>
> thanx
>
> devin

There is good news and bad news. The good news is that with the database
design as it is it will be extremely easy to stop a film being rented
twice - if you put a unique index on the field filmrental.film then you

are

saying that no film can appear twice in the table. It is impossible to

make

a mistake in your coding and have the film out twice.

The bad news is that ther is, in fact, no need for the filmrental table.
You only need tables customer and film. In the film table you need to

store

which customer has borrowed the film or leave it blank if no-one has the
film. This is extremely simple and guarantees that any copy of a film
can
only be on loan to one customer - or to no customers if it is available.
There would only be a need for the third table if you wanted to store
history - which you have said you don't.

If the database is really this simple, then I'd guess this is not a
real-life application, more of an excercise to get to know Access - am I
right?

You would be better off detecting that the film is on loan before you get to
the BeforeUpdate event. For example, imagine if you presented the user a
list of available films only then you would not start creating a record with
an unavailable film and then have to try to sort it out.

By the way, just to have another go at explaining my comments on table
design:
If each film in the film table represents a single physical copy and there
is no history being kept then each film cannot appear more than once in your
filmrental table. Do you agree? So for both tables, the FilmID is unique.
This is a one-to-one relationship between the two tables and means that you
could actually get rid of the film rental table.

I am not saying this is how I would design the database, but as you have
chosen to set it up, you have a simple one-to-many relationship between
customers and films. That is, a customer may borrow many films but any film
can only be borrowed by one customer.

[DP]

yea, yor right.

i've actually come to far, to start changing my design now, i'm almost
finished.

i only got this last little problem to sort out, and then i'm done. i've
only gotta make a prototype version, and this db program wat i've made is
lookin brilliant.

how would i detect if the film is on loan before? i thought u can only
detect, when the field is entered with a number? becuase there is no number
in there otherwise, to detect?

(I cant use a combo/list box, cos even if it worked, there would be too many
films in the list!!)

thanx

dev

"Anthony England" <ae******@oops.co.uk> wrote in message
news:dt**********@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...

"DP" <[e-mail]> wrote in message
news:91******************@newsfe5-win.ntli.net...

hi,

the film tables only hold the film names, actors, type, etc...
i do need the 3 tables, because thats wat links them altogether. if i only had 2 tables, i would have a many-to-many relationship, which does not
work.

if the 3 table idea is easy, y cant i stop it from holding duplicates??
the
filmID is unique, and its a primary key, the membershipID is unque and a
primary key, and rentledID is unique and a primary key.

however, after all that, i've got this piece of code, which stops
duplicate
entries. ;

Private Sub FilmID_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then
Cancel = True
MsgBox "This value has already been used.", _
vbOKOnly + vbInformation, "Duplicate Entry"
End If
End Sub

now the problem is, the cursor is on the filmID field, and i cant move it, untill i enter an ID (Number), how can i stop this? is there a way to just 'back-out' without having to add a filmID for the sake of it??

thanx

dev

"Anthony England" <ae******@oops.co.uk> wrote in message
news:dt**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...

"DP" <[e-mail]> wrote in message
news:d3******************@newsfe5-gui.ntli.net...
> oh, i'm sort of understanding u.
>
> ive got a film like pulpfiction, but it is duplicated 5 times, so each > copy
> has is own ID.
> it relates as a single copy.
>
> have u got any examples, or somthing i could use to learn how to do that?

> or
> is there a way to stop a film from being rented twice?
>
> thanx
>
> devin
There is good news and bad news. The good news is that with the database design as it is it will be extremely easy to stop a film being rented
twice - if you put a unique index on the field filmrental.film then you

are

saying that no film can appear twice in the table. It is impossible to

make

a mistake in your coding and have the film out twice.

The bad news is that ther is, in fact, no need for the filmrental table. You only need tables customer and film. In the film table you need to

store

which customer has borrowed the film or leave it blank if no-one has the film. This is extremely simple and guarantees that any copy of a film
can
only be on loan to one customer - or to no customers if it is available. There would only be a need for the third table if you wanted to store
history - which you have said you don't.

If the database is really this simple, then I'd guess this is not a
real-life application, more of an excercise to get to know Access - am I right?

You would be better off detecting that the film is on loan before you get

to the BeforeUpdate event. For example, imagine if you presented the user a
list of available films only then you would not start creating a record with an unavailable film and then have to try to sort it out.

By the way, just to have another go at explaining my comments on table
design:
If each film in the film table represents a single physical copy and there
is no history being kept then each film cannot appear more than once in your filmrental table. Do you agree? So for both tables, the FilmID is unique. This is a one-to-one relationship between the two tables and means that you could actually get rid of the film rental table.

I am not saying this is how I would design the database, but as you have
chosen to set it up, you have a simple one-to-many relationship between
customers and films. That is, a customer may borrow many films but any film can only be borrowed by one customer.

[Anthony England]

"DP" <[e-mail]> wrote in message
news:NQ*******************@newsfe7-win.ntli.net...

yea, yor right.

i've actually come to far, to start changing my design now, i'm almost
finished.

i only got this last little problem to sort out, and then i'm done. i've
only gotta make a prototype version, and this db program wat i've made is
lookin brilliant.

how would i detect if the film is on loan before? i thought u can only
detect, when the field is entered with a number? becuase there is no
number
in there otherwise, to detect?

(I cant use a combo/list box, cos even if it worked, there would be too
many
films in the list!!)

thanx

dev

The user is going to type in a number? Well, the absolute simplest way
would be to have a button which pops up an inputbox asking for the number.
You can then use your dlookup idea to see if the film is in the rental
table, if so let the user know it is on loan. If not, you can take that
number and use it to fill out a new record in the filmrental table.