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

"DP" <DP@hotmail.com> wrote in message
news:yw1Kf.27336$m13.4466@newsfe5-gui.ntli.net...[color=blue]
> 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[/color]


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.

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" <aengland@oops.co.uk> wrote in message
news:dtac6f$6vi$1@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...[color=blue]
> "DP" <DP@hotmail.com> wrote in message
> news:yw1Kf.27336$m13.4466@newsfe5-gui.ntli.net...[color=green]
> > 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[/color][/color]
in[color=blue][color=green]
> > 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[/color]
>
>
> If the design of this database is as I understand, you simply add and[/color]
delete[color=blue]
> 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[/color]
the[color=blue]
> 'Available' field - as it introduces the possibility of inconsistencies in
> the database.
>
>
>[/color]

"DP" <DP@hotmail.com> wrote in message
news:3GgKf.29967$m13.12648@newsfe5-gui.ntli.net...[color=blue]
> 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" <aengland@oops.co.uk> wrote in message
> news:dtac6f$6vi$1@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...[color=green]
>> "DP" <DP@hotmail.com> wrote in message
>> news:yw1Kf.27336$m13.4466@newsfe5-gui.ntli.net...[color=darkred]
>> > 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[/color][/color]
> in[color=green][color=darkred]
>> > 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[/color]
>>
>>
>> If the design of this database is as I understand, you simply add and[/color]
> delete[color=green]
>> 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[/color]
> the[color=green]
>> 'Available' field - as it introduces the possibility of inconsistencies
>> in
>> the database.[/color][/color]




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?

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" <aengland@oops.co.uk> wrote in message
news:dtc5hf$kl$1@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...[color=blue]
> "DP" <DP@hotmail.com> wrote in message
> news:3GgKf.29967$m13.12648@newsfe5-gui.ntli.net...[color=green]
> > yes, i do not require a history. so wat do u propose i do? is there a[/color][/color]
way[color=blue][color=green]
> > 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[/color][/color]
on[color=blue][color=green]
> > 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" <aengland@oops.co.uk> wrote in message
> > news:dtac6f$6vi$1@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...[color=darkred]
> >> "DP" <DP@hotmail.com> wrote in message
> >> news:yw1Kf.27336$m13.4466@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[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> >> > subform, the 'available' check box in the film table, becomes false
> >> > (Unchecked). according to which filmID it is. heres the code, i;ve[/color][/color][/color]
got[color=blue][color=green]
> > in[color=darkred]
> >> > 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[/color][/color][/color]
(Ticked)[color=blue][color=green][color=darkred]
> >> > True
> >> > again?
> >> >
> >> > tia
> >> >
> >> > Dev
> >>
> >>
> >> If the design of this database is as I understand, you simply add and[/color]
> > delete[color=darkred]
> >> 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[/color][/color][/color]
a[color=blue][color=green][color=darkred]
> >> simple dlookup would tell you whether the film was in or out. Even[/color][/color][/color]
with[color=blue][color=green][color=darkred]
> >> a
> >> more sophisticated design, one could debate the pros and cons of having[/color]
> > the[color=darkred]
> >> 'Available' field - as it introduces the possibility of inconsistencies
> >> in
> >> the database.[/color][/color]
>
>
>
>
> The fact that you can't edit the value is the beauty of it - you cannot[/color]
have[color=blue]
> 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[/color]
the[color=blue]
> 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[/color]
film[color=blue]
> 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?
>
>[/color]

"DP" <DP@hotmail.com> wrote in message
news:d3mKf.31812$m13.5317@newsfe5-gui.ntli.net...[color=blue]
> 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[/color]


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?

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" <aengland@oops.co.uk> wrote in message
news:dtcrjk$1tj$1@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...[color=blue]
> "DP" <DP@hotmail.com> wrote in message
> news:d3mKf.31812$m13.5317@newsfe5-gui.ntli.net...[color=green]
> > 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[/color][/color]
that?[color=blue][color=green]
> > or
> > is there a way to stop a film from being rented twice?
> >
> > thanx
> >
> > devin[/color]
>
>
> 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[/color]
are[color=blue]
> saying that no film can appear twice in the table. It is impossible to[/color]
make[color=blue]
> 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[/color]
store[color=blue]
> 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?
>
>[/color]

"DP" <DP@hotmail.com> wrote in message
news:91nKf.67299$0N1.2705@newsfe5-win.ntli.net...[color=blue]
> 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" <aengland@oops.co.uk> wrote in message
> news:dtcrjk$1tj$1@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...[color=green]
>> "DP" <DP@hotmail.com> wrote in message
>> news:d3mKf.31812$m13.5317@newsfe5-gui.ntli.net...[color=darkred]
>> > 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[/color][/color]
> that?[color=green][color=darkred]
>> > or
>> > is there a way to stop a film from being rented twice?
>> >
>> > thanx
>> >
>> > devin[/color]
>>
>>
>> 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[/color]
> are[color=green]
>> saying that no film can appear twice in the table. It is impossible to[/color]
> make[color=green]
>> 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[/color]
> store[color=green]
>> 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?[/color][/color]



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.

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" <aengland@oops.co.uk> wrote in message
news:dtemqp$b9i$1@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...[color=blue]
>
> "DP" <DP@hotmail.com> wrote in message
> news:91nKf.67299$0N1.2705@newsfe5-win.ntli.net...[color=green]
> > 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[/color][/color]
only[color=blue][color=green]
> > 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[/color][/color]
it,[color=blue][color=green]
> > untill i enter an ID (Number), how can i stop this? is there a way to[/color][/color]
just[color=blue][color=green]
> > 'back-out' without having to add a filmID for the sake of it??
> >
> > thanx
> >
> > dev
> >
> > "Anthony England" <aengland@oops.co.uk> wrote in message
> > news:dtcrjk$1tj$1@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...[color=darkred]
> >> "DP" <DP@hotmail.com> wrote in message
> >> news:d3mKf.31812$m13.5317@newsfe5-gui.ntli.net...
> >> > oh, i'm sort of understanding u.
> >> >
> >> > ive got a film like pulpfiction, but it is duplicated 5 times, so[/color][/color][/color]
each[color=blue][color=green][color=darkred]
> >> > 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[/color]
> > that?[color=darkred]
> >> > 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[/color][/color][/color]
database[color=blue][color=green][color=darkred]
> >> 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[/color]
> > are[color=darkred]
> >> saying that no film can appear twice in the table. It is impossible to[/color]
> > make[color=darkred]
> >> 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[/color][/color][/color]
table.[color=blue][color=green][color=darkred]
> >> You only need tables customer and film. In the film table you need to[/color]
> > store[color=darkred]
> >> which customer has borrowed the film or leave it blank if no-one has[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> >> 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[/color][/color][/color]
available.[color=blue][color=green][color=darkred]
> >> 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[/color][/color][/color]
I[color=blue][color=green][color=darkred]
> >> right?[/color][/color]
>
>
>
> You would be better off detecting that the film is on loan before you get[/color]
to[color=blue]
> 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[/color]
with[color=blue]
> 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[/color]
your[color=blue]
> filmrental table. Do you agree? So for both tables, the FilmID is[/color]
unique.[color=blue]
> This is a one-to-one relationship between the two tables and means that[/color]
you[color=blue]
> 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[/color]
film[color=blue]
> can only be borrowed by one customer.
>
>[/color]

"DP" <DP@hotmail.com> wrote in message
news:NQCKf.42581$K42.34542@newsfe7-win.ntli.net...[color=blue]
> 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[/color]


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.