help with message box

[DP]

hi,

i've got a price of code, which checks to see if a film is on rent, or
available. but how can i actualyl make the cancel button do somthing?
because, the cursor gets stuck on the filmID field. this is wat i've got so
far;

Private Sub FilmID_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then
Cancel = True
MsgBox "This Film is out on rent, Do you want to enter another FilmID.",
_
vbYesNo + vbInformation, "Duplicate Film Entry"
End If
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available = Available =False WHERE
FilmID=" & Me.FilmID
End Sub

how do i assign somthing to cancel? e.g. if the user clicks cancel,
everything it undone, and the cursor is not stuck in the filmID field.

TIA

dev

[Wayne Morgan]

The problem is that you've cancelled the update, which stops the entry from
being written to the table, but the control is still dirty. So, as soon as
you go to move off of the entry again, the code runs again. If the user
chooses to cancel you need to be able to pick up on their choice and undo
the selection.

If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then
Cancel = True
MsgBox "This Film is out on rent, Do you want to enter another
FilmID.",
_
vbYesNo + vbInformation, "Duplicate Film Entry"
You ask Yes or No, then don't do anything with the answer. Instead, try
this:

If MsgBox("This Film is out....", vbYesNo + vbInformation, "Duplicate Film
Entry") = vbYes Then
Me.FilmID.Undo
End If

Note that when the MsgBox is used this way, you have to enclose the
arguments in parentheses.

--
Wayne Morgan
MS Access MVP
"DP" <[e-mail]> wrote in message
news:N5******************@newsfe6-win.ntli.net...
hi,

i've got a price of code, which checks to see if a film is on rent, or
available. but how can i actualyl make the cancel button do somthing?
because, the cursor gets stuck on the filmID field. this is wat i've got
so
far;

Private Sub FilmID_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then
Cancel = True
MsgBox "This Film is out on rent, Do you want to enter another
FilmID.",
_
vbYesNo + vbInformation, "Duplicate Film Entry"
End If
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available = Available =False
WHERE
FilmID=" & Me.FilmID
End Sub

how do i assign somthing to cancel? e.g. if the user clicks cancel,
everything it undone, and the cursor is not stuck in the filmID field.

TIA

dev

[Tim Marshall]

DP wrote:

Private Sub FilmID_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then
Cancel = True
MsgBox "This Film is out on rent, Do you want to enter another FilmID.",
_
vbYesNo + vbInformation, "Duplicate Film Entry"
End If
Try this where you have your msgbox:

if MsgBox ("This Film is out on rent, Do you want to enter another
FilmID.", vbYesNo + vbInformation, "Duplicate Film Entry") = vbYes then

'do something for vbyes

else

'do soemthing for vbno

end if

Personally, I find these sorts of logic paths hard to implement in the
Before_Update event of a text box, but that's just me - others may be
able to help you more. I prefer the after update and would do something
like (air code):

Private Sub FilmID_AfterUpdate()

If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then

me.filmid = null

if MsgBox (me.FilmID & " is currently rented." & _
vbcrlf & vbcrlf & "Do you want to enter another FilmID?", _
vbOkCancel + vbQuestion, "Duplicate Film Entry") = vbOk then

'Do what ever it is you did in the first place, perhaps a pop up
or something to populate FilmID?

End If

End If

End Sub
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available = Available =False WHERE
FilmID=" & Me.FilmID

Some of us warn strongly again using DoCmd.SetWarnings False. I find it
very dangerous and steer well clear of it - in your sample code, you
haven't turned them back on again, but doubtless you'll say "obviously
I'm going to change it!".

A safer way to do this is to use the DAO execute method with
dbfailonerror - I'm assuming you've A2003:

currentdb.execute "UPDATE tblFilm SET tblFilm.Available = Available
=False WHERE FilmID=" & Me.FilmID, dbFailOnError

This will bypass any advisory messages but will throw an error if your
SQL isn't correct.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[DP]

hi,

i've tried the code both of u have given me, and i get this error;

the microsoft jet database cannot find a record in the table tblFilm, with
key matching fields 'filmID.'

i understand wat u've tried to do, and i understand the problem.

i know y i get this error as well, its because, the subform has assigned the
record to the customer, as the membershipID field is still filled in.

i've got this code, which closes the form, but how do i stop the error from
popping up?

Private Sub FilmID_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then

Cancel = True
If MsgBox("This Film is out....", vbYesNo + vbInformation, "Duplicate Film
Entry ") = vbNo Then

DoCmd.Close

at this line;

CurrentDb.Execute "UPDATE tblFilm SET tblFilm.Available = Available =False
WHERE FilmID=" & Me.FilmID, dbFailOnError

End Sub

the above works, as it closes the form, which clears the subform as nothing
is added. but i can an error, at the line above. (Syntax error...)

thanx

dev
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:Yg*******************@newssvr21.news.prodigy. com...

The problem is that you've cancelled the update, which stops the entry from being written to the table, but the control is still dirty. So, as soon as
you go to move off of the entry again, the code runs again. If the user
chooses to cancel you need to be able to pick up on their choice and undo
the selection.

If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then
Cancel = True
MsgBox "This Film is out on rent, Do you want to enter another
FilmID.",
_
vbYesNo + vbInformation, "Duplicate Film Entry"

You ask Yes or No, then don't do anything with the answer. Instead, try
this:

If MsgBox("This Film is out....", vbYesNo + vbInformation, "Duplicate Film
Entry") = vbYes Then
Me.FilmID.Undo
End If

Note that when the MsgBox is used this way, you have to enclose the
arguments in parentheses.

--
Wayne Morgan
MS Access MVP
"DP" <[e-mail]> wrote in message
news:N5******************@newsfe6-win.ntli.net...

hi,

i've got a price of code, which checks to see if a film is on rent, or
available. but how can i actualyl make the cancel button do somthing?
because, the cursor gets stuck on the filmID field. this is wat i've got
so
far;

Private Sub FilmID_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then
Cancel = True
MsgBox "This Film is out on rent, Do you want to enter another
FilmID.",
_
vbYesNo + vbInformation, "Duplicate Film Entry"
End If
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available = Available =False
WHERE
FilmID=" & Me.FilmID
End Sub

how do i assign somthing to cancel? e.g. if the user clicks cancel,
everything it undone, and the cursor is not stuck in the filmID field.

TIA

dev

[DP]

lol.

i couldnt do it after an update, as the field would have already been
updated with other data.

the last part of the code i've got is fine. it updates another table
correctly.

its just the first bit i cant get to work, if a duplicate filmID is entered.
(i;ve tried using filmID = null and it dont work)

the best thing i can think of is to exit the form, and stop it from
displaying an error. is there anyway, u can disable the error popup that
displays?

dev

"Tim Marshall" <TI****@PurplePandaChasers.Moertherium> wrote in message
news:dt**********@coranto.ucs.mun.ca...

DP wrote:

Private Sub FilmID_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then
Cancel = True
MsgBox "This Film is out on rent, Do you want to enter another FilmID.", _
vbYesNo + vbInformation, "Duplicate Film Entry"
End If

Try this where you have your msgbox:

if MsgBox ("This Film is out on rent, Do you want to enter another
FilmID.", vbYesNo + vbInformation, "Duplicate Film Entry") = vbYes then

'do something for vbyes

else

'do soemthing for vbno

end if

Personally, I find these sorts of logic paths hard to implement in the
Before_Update event of a text box, but that's just me - others may be
able to help you more. I prefer the after update and would do something
like (air code):

Private Sub FilmID_AfterUpdate()

If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then

me.filmid = null

if MsgBox (me.FilmID & " is currently rented." & _
vbcrlf & vbcrlf & "Do you want to enter another FilmID?", _
vbOkCancel + vbQuestion, "Duplicate Film Entry") = vbOk then

'Do what ever it is you did in the first place, perhaps a pop up
or something to populate FilmID?

End If

End If

End Sub

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available = Available =False WHERE FilmID=" & Me.FilmID

Some of us warn strongly again using DoCmd.SetWarnings False. I find it
very dangerous and steer well clear of it - in your sample code, you
haven't turned them back on again, but doubtless you'll say "obviously
I'm going to change it!".

A safer way to do this is to use the DAO execute method with
dbfailonerror - I'm assuming you've A2003:

currentdb.execute "UPDATE tblFilm SET tblFilm.Available = Available
=False WHERE FilmID=" & Me.FilmID, dbFailOnError

This will bypass any advisory messages but will throw an error if your
SQL isn't correct.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Wayne Morgan]

You may need to undo the entire record, not just the one control. If that's
the case, the syntax is

Me.Undo

When you go to close the form, it will try to save the record if you've made
changes. If the record is incomplete, you either need to complete it or undo
it.

--
Wayne Morgan
MS Access MVP
"DP" <[e-mail]> wrote in message
news:Rv*******************@newsfe7-gui.ntli.net...

hi,

i've tried the code both of u have given me, and i get this error;

the microsoft jet database cannot find a record in the table tblFilm, with
key matching fields 'filmID.'

i understand wat u've tried to do, and i understand the problem.

i know y i get this error as well, its because, the subform has assigned
the
record to the customer, as the membershipID field is still filled in.

i've got this code, which closes the form, but how do i stop the error
from
popping up?

Private Sub FilmID_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then

Cancel = True
If MsgBox("This Film is out....", vbYesNo + vbInformation, "Duplicate Film
Entry ") = vbNo Then

DoCmd.Close

at this line;

CurrentDb.Execute "UPDATE tblFilm SET tblFilm.Available = Available
=False
WHERE FilmID=" & Me.FilmID, dbFailOnError

End Sub

the above works, as it closes the form, which clears the subform as
nothing
is added. but i can an error, at the line above. (Syntax error...)

thanx

dev
"Wayne Morgan" <co***************************@hotmail.com> wrote in
message
news:Yg*******************@newssvr21.news.prodigy. com...

The problem is that you've cancelled the update, which stops the entry

from

being written to the table, but the control is still dirty. So, as soon
as
you go to move off of the entry again, the code runs again. If the user
chooses to cancel you need to be able to pick up on their choice and undo
the selection.

> If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then
> Cancel = True
> MsgBox "This Film is out on rent, Do you want to enter another
> FilmID.",
> _
> vbYesNo + vbInformation, "Duplicate Film Entry"

You ask Yes or No, then don't do anything with the answer. Instead, try
this:

If MsgBox("This Film is out....", vbYesNo + vbInformation, "Duplicate
Film
Entry") = vbYes Then
Me.FilmID.Undo
End If

Note that when the MsgBox is used this way, you have to enclose the
arguments in parentheses.

--
Wayne Morgan
MS Access MVP
"DP" <[e-mail]> wrote in message
news:N5******************@newsfe6-win.ntli.net...

>
> hi,
>
> i've got a price of code, which checks to see if a film is on rent, or
> available. but how can i actualyl make the cancel button do somthing?
> because, the cursor gets stuck on the filmID field. this is wat i've
> got
> so
> far;
>
> Private Sub FilmID_BeforeUpdate(Cancel As Integer)
> If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then
> Cancel = True
> MsgBox "This Film is out on rent, Do you want to enter another
> FilmID.",
> _
> vbYesNo + vbInformation, "Duplicate Film Entry"
>
>
> End If
> DoCmd.SetWarnings False
> DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available = Available =False
> WHERE
> FilmID=" & Me.FilmID
>
>
> End Sub
>
> how do i assign somthing to cancel? e.g. if the user clicks cancel,
> everything it undone, and the cursor is not stuck in the filmID field.
>
> TIA
>
> dev
>
>
>

[DP]

thanx man.

at first that didint work, as i have this,

Private Sub FilmID_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then
Cancel = True
MsgBox "This Film is out on rent, Please Enter another FilmID.", _
vbOKOnly + vbInformation, "Duplicate Film Entry"

Me.Undo
End If
DoCmd.SetWarnings False
'DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available = Available =False WHERE
FilmID=" & Me.FilmID
End Sub

but then i deleted the sql line, and moved it to 'after update' of filmID.
and it works.

i've got a delete button, and everytime i use it to delete a record, it has
this error;

runtime error 3197

The Microsoft Jet Database Engine stopped becasue you and another user are
attempting to change the same data at the same time.
how can i overcome this error, as if i close the DB, and then open it again,
it will delete the record, and update the correct field?? (currently i have
to press the delete button twice to delete a record)

thanx

dev
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:Ih******************@newssvr21.news.prodigy.c om...

You may need to undo the entire record, not just the one control. If that's the case, the syntax is

Me.Undo

When you go to close the form, it will try to save the record if you've made changes. If the record is incomplete, you either need to complete it or undo it.

--
Wayne Morgan
MS Access MVP
"DP" <[e-mail]> wrote in message
news:Rv*******************@newsfe7-gui.ntli.net...

hi,

i've tried the code both of u have given me, and i get this error;

the microsoft jet database cannot find a record in the table tblFilm, with key matching fields 'filmID.'

i understand wat u've tried to do, and i understand the problem.

i know y i get this error as well, its because, the subform has assigned
the
record to the customer, as the membershipID field is still filled in.

i've got this code, which closes the form, but how do i stop the error
from
popping up?

Private Sub FilmID_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then

Cancel = True
If MsgBox("This Film is out....", vbYesNo + vbInformation, "Duplicate Film Entry ") = vbNo Then

DoCmd.Close

at this line;

CurrentDb.Execute "UPDATE tblFilm SET tblFilm.Available = Available
=False
WHERE FilmID=" & Me.FilmID, dbFailOnError

End Sub

the above works, as it closes the form, which clears the subform as
nothing
is added. but i can an error, at the line above. (Syntax error...)

thanx

dev
"Wayne Morgan" <co***************************@hotmail.com> wrote in
message
news:Yg*******************@newssvr21.news.prodigy. com...

The problem is that you've cancelled the update, which stops the entry

from

being written to the table, but the control is still dirty. So, as soon
as
you go to move off of the entry again, the code runs again. If the user
chooses to cancel you need to be able to pick up on their choice and undo the selection.
> If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then
> Cancel = True
> MsgBox "This Film is out on rent, Do you want to enter another
> FilmID.",
> _
> vbYesNo + vbInformation, "Duplicate Film Entry"

You ask Yes or No, then don't do anything with the answer. Instead, try
this:

If MsgBox("This Film is out....", vbYesNo + vbInformation, "Duplicate
Film
Entry") = vbYes Then
Me.FilmID.Undo
End If

Note that when the MsgBox is used this way, you have to enclose the
arguments in parentheses.

--
Wayne Morgan
MS Access MVP
"DP" <[e-mail]> wrote in message
news:N5******************@newsfe6-win.ntli.net...
>
> hi,
>
> i've got a price of code, which checks to see if a film is on rent, or > available. but how can i actualyl make the cancel button do somthing? > because, the cursor gets stuck on the filmID field. this is wat i've
> got
> so
> far;
>
> Private Sub FilmID_BeforeUpdate(Cancel As Integer)
> If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then
> Cancel = True
> MsgBox "This Film is out on rent, Do you want to enter another
> FilmID.",
> _
> vbYesNo + vbInformation, "Duplicate Film Entry"
>
>
> End If
> DoCmd.SetWarnings False
> DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available = Available =False
> WHERE
> FilmID=" & Me.FilmID
>
>
> End Sub
>
> how do i assign somthing to cancel? e.g. if the user clicks cancel,
> everything it undone, and the cursor is not stuck in the filmID field. >
> TIA
>
> dev
>
>
>

[Wayne Morgan]

I'm guessing you still have the record locked from starting to edit it. You
aren't trying to delete the new record you just used undo on are you? If
it's an edited record, that's ok, but you can't delete a new record that
doesn't exist yet.

In your SQL, you have "SET tblFilm.Available = (Available =False)"
(parentheses added for clarification). This will essentially reverse the
value of Available. You could have said "SET tblFilm.Available = Not
Available" and it would do the same thing. If that's what you want or if it
was a type, that's ok, just checking.

--
Wayne Morgan
MS Access MVP
"DP" <[e-mail]> wrote in message
news:2Z*******************@newsfe6-win.ntli.net...

thanx man.

at first that didint work, as i have this,

Private Sub FilmID_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then
Cancel = True
MsgBox "This Film is out on rent, Please Enter another FilmID.", _
vbOKOnly + vbInformation, "Duplicate Film Entry"

Me.Undo
End If
DoCmd.SetWarnings False
'DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available = Available =False
WHERE
FilmID=" & Me.FilmID
End Sub

but then i deleted the sql line, and moved it to 'after update' of filmID.
and it works.

i've got a delete button, and everytime i use it to delete a record, it
has
this error;

runtime error 3197

The Microsoft Jet Database Engine stopped becasue you and another user are
attempting to change the same data at the same time.
how can i overcome this error, as if i close the DB, and then open it
again,
it will delete the record, and update the correct field?? (currently i
have
to press the delete button twice to delete a record)

thanx

dev
"Wayne Morgan" <co***************************@hotmail.com> wrote in
message
news:Ih******************@newssvr21.news.prodigy.c om...

You may need to undo the entire record, not just the one control. If

that's

the case, the syntax is

Me.Undo

When you go to close the form, it will try to save the record if you've

made

changes. If the record is incomplete, you either need to complete it or

undo

it.

--
Wayne Morgan
MS Access MVP
"DP" <[e-mail]> wrote in message
news:Rv*******************@newsfe7-gui.ntli.net...

> hi,
>
> i've tried the code both of u have given me, and i get this error;
>
> the microsoft jet database cannot find a record in the table tblFilm, with > key matching fields 'filmID.'
>
> i understand wat u've tried to do, and i understand the problem.
>
> i know y i get this error as well, its because, the subform has
> assigned
> the
> record to the customer, as the membershipID field is still filled in.
>
> i've got this code, which closes the form, but how do i stop the error
> from
> popping up?
>
> Private Sub FilmID_BeforeUpdate(Cancel As Integer)
> If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then
>
> Cancel = True
> If MsgBox("This Film is out....", vbYesNo + vbInformation, "Duplicate Film > Entry ") = vbNo Then
>
> DoCmd.Close
>
> at this line;
>
> CurrentDb.Execute "UPDATE tblFilm SET tblFilm.Available = Available
> =False
> WHERE FilmID=" & Me.FilmID, dbFailOnError
>
> End Sub
>
> the above works, as it closes the form, which clears the subform as
> nothing
> is added. but i can an error, at the line above. (Syntax error...)
>
> thanx
>
> dev
>
>
> "Wayne Morgan" <co***************************@hotmail.com> wrote in
> message
> news:Yg*******************@newssvr21.news.prodigy. com...
>> The problem is that you've cancelled the update, which stops the entry
> from
>> being written to the table, but the control is still dirty. So, as
>> soon
>> as
>> you go to move off of the entry again, the code runs again. If the
>> user
>> chooses to cancel you need to be able to pick up on their choice and undo >> the selection.
>>
>>
>> > If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then
>> > Cancel = True
>> > MsgBox "This Film is out on rent, Do you want to enter another
>> > FilmID.",
>> > _
>> > vbYesNo + vbInformation, "Duplicate Film Entry"
>>
>> You ask Yes or No, then don't do anything with the answer. Instead,
>> try
>> this:
>>
>> If MsgBox("This Film is out....", vbYesNo + vbInformation, "Duplicate
>> Film
>> Entry") = vbYes Then
>> Me.FilmID.Undo
>> End If
>>
>> Note that when the MsgBox is used this way, you have to enclose the
>> arguments in parentheses.
>>
>> --
>> Wayne Morgan
>> MS Access MVP
>>
>>
>> "DP" <[e-mail]> wrote in message
>> news:N5******************@newsfe6-win.ntli.net...
>> >
>> > hi,
>> >
>> > i've got a price of code, which checks to see if a film is on rent, or >> > available. but how can i actualyl make the cancel button do somthing? >> > because, the cursor gets stuck on the filmID field. this is wat i've
>> > got
>> > so
>> > far;
>> >
>> > Private Sub FilmID_BeforeUpdate(Cancel As Integer)
>> > If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then
>> > Cancel = True
>> > MsgBox "This Film is out on rent, Do you want to enter another
>> > FilmID.",
>> > _
>> > vbYesNo + vbInformation, "Duplicate Film Entry"
>> >
>> >
>> > End If
>> > DoCmd.SetWarnings False
>> > DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available = Available
>> > =False
>> > WHERE
>> > FilmID=" & Me.FilmID
>> >
>> >
>> > End Sub
>> >
>> > how do i assign somthing to cancel? e.g. if the user clicks cancel,
>> > everything it undone, and the cursor is not stuck in the filmID field. >> >
>> > TIA
>> >
>> > dev
>> >
>> >
>> >
>>
>>
>
>

[DP]

thanx, i sorted everything now. i appreciate all the help.

thanx again

dev

"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:sN******************@newssvr25.news.prodigy.n et...

I'm guessing you still have the record locked from starting to edit it. You aren't trying to delete the new record you just used undo on are you? If
it's an edited record, that's ok, but you can't delete a new record that
doesn't exist yet.

In your SQL, you have "SET tblFilm.Available = (Available =False)"
(parentheses added for clarification). This will essentially reverse the
value of Available. You could have said "SET tblFilm.Available = Not
Available" and it would do the same thing. If that's what you want or if it was a type, that's ok, just checking.

--
Wayne Morgan
MS Access MVP
"DP" <[e-mail]> wrote in message
news:2Z*******************@newsfe6-win.ntli.net...

thanx man.

at first that didint work, as i have this,

Private Sub FilmID_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then
Cancel = True
MsgBox "This Film is out on rent, Please Enter another FilmID.", _
vbOKOnly + vbInformation, "Duplicate Film Entry"

Me.Undo
End If
DoCmd.SetWarnings False
'DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available = Available =False
WHERE
FilmID=" & Me.FilmID
End Sub

but then i deleted the sql line, and moved it to 'after update' of filmID. and it works.

i've got a delete button, and everytime i use it to delete a record, it
has
this error;

runtime error 3197

The Microsoft Jet Database Engine stopped becasue you and another user are attempting to change the same data at the same time.
how can i overcome this error, as if i close the DB, and then open it
again,
it will delete the record, and update the correct field?? (currently i
have
to press the delete button twice to delete a record)

thanx

dev
"Wayne Morgan" <co***************************@hotmail.com> wrote in
message
news:Ih******************@newssvr21.news.prodigy.c om...

You may need to undo the entire record, not just the one control. If

that's

the case, the syntax is

Me.Undo

When you go to close the form, it will try to save the record if you've

made

changes. If the record is incomplete, you either need to complete it or

undo

it.

--
Wayne Morgan
MS Access MVP
"DP" <[e-mail]> wrote in message
news:Rv*******************@newsfe7-gui.ntli.net...
> hi,
>
> i've tried the code both of u have given me, and i get this error;
>
> the microsoft jet database cannot find a record in the table tblFilm,

with

> key matching fields 'filmID.'
>
> i understand wat u've tried to do, and i understand the problem.
>
> i know y i get this error as well, its because, the subform has
> assigned
> the
> record to the customer, as the membershipID field is still filled in.
>
> i've got this code, which closes the form, but how do i stop the error > from
> popping up?
>
> Private Sub FilmID_BeforeUpdate(Cancel As Integer)
> If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then
>
> Cancel = True
> If MsgBox("This Film is out....", vbYesNo + vbInformation, "Duplicate

Film

> Entry ") = vbNo Then
>
> DoCmd.Close
>
> at this line;
>
> CurrentDb.Execute "UPDATE tblFilm SET tblFilm.Available = Available
> =False
> WHERE FilmID=" & Me.FilmID, dbFailOnError
>
> End Sub
>
> the above works, as it closes the form, which clears the subform as
> nothing
> is added. but i can an error, at the line above. (Syntax error...)
>
> thanx
>
> dev
>
>
> "Wayne Morgan" <co***************************@hotmail.com> wrote in
> message
> news:Yg*******************@newssvr21.news.prodigy. com...
>> The problem is that you've cancelled the update, which stops the entry > from
>> being written to the table, but the control is still dirty. So, as
>> soon
>> as
>> you go to move off of the entry again, the code runs again. If the
>> user
>> chooses to cancel you need to be able to pick up on their choice and

undo

>> the selection.
>>
>>
>> > If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then
>> > Cancel = True
>> > MsgBox "This Film is out on rent, Do you want to enter another
>> > FilmID.",
>> > _
>> > vbYesNo + vbInformation, "Duplicate Film Entry"
>>
>> You ask Yes or No, then don't do anything with the answer. Instead,
>> try
>> this:
>>
>> If MsgBox("This Film is out....", vbYesNo + vbInformation, "Duplicate >> Film
>> Entry") = vbYes Then
>> Me.FilmID.Undo
>> End If
>>
>> Note that when the MsgBox is used this way, you have to enclose the
>> arguments in parentheses.
>>
>> --
>> Wayne Morgan
>> MS Access MVP
>>
>>
>> "DP" <[e-mail]> wrote in message
>> news:N5******************@newsfe6-win.ntli.net...
>> >
>> > hi,
>> >
>> > i've got a price of code, which checks to see if a film is on rent,

or

>> > available. but how can i actualyl make the cancel button do

somthing?

>> > because, the cursor gets stuck on the filmID field. this is wat

i've >> > got
>> > so
>> > far;
>> >
>> > Private Sub FilmID_BeforeUpdate(Cancel As Integer)
>> > If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then
>> > Cancel = True
>> > MsgBox "This Film is out on rent, Do you want to enter another
>> > FilmID.",
>> > _
>> > vbYesNo + vbInformation, "Duplicate Film Entry"
>> >
>> >
>> > End If
>> > DoCmd.SetWarnings False
>> > DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available = Available
>> > =False
>> > WHERE
>> > FilmID=" & Me.FilmID
>> >
>> >
>> > End Sub
>> >
>> > how do i assign somthing to cancel? e.g. if the user clicks cancel, >> > everything it undone, and the cursor is not stuck in the filmID

field.

>> >
>> > TIA
>> >
>> > dev
>> >
>> >
>> >
>>
>>
>
>