help with message box

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DP - 22 Feb 2006 18:31 GMT

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

Reply to this Message

Tim Marshall - 22 Feb 2006 19:47 GMT

> Private Sub FilmID_BeforeUpdate(Cancel As Integer)
> If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then
[quoted text clipped - 4 lines]
>
> 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.

Signature

Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

Reply to this Message

DP - 23 Feb 2006 19:02 GMT

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

> > Private Sub FilmID_BeforeUpdate(Cancel As Integer)
> > If DCount("*", "tblFilmRental", "FilmID=" & Me![FilmID]) > 0 Then
[quoted text clipped - 64 lines]
> /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
> /^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

Reply to this Message

Wayne Morgan - 22 Feb 2006 19:51 GMT

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.

Signature

Wayne Morgan
MS Access MVP

> hi,
>
[quoted text clipped - 26 lines]
>
> dev

Reply to this Message

DP - 23 Feb 2006 18:52 GMT

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

> 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
[quoted text clipped - 54 lines]
> >
> > dev

Reply to this Message

Wayne Morgan - 23 Feb 2006 19:45 GMT

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.

Signature

Wayne Morgan
MS Access MVP

> hi,
>
[quoted text clipped - 100 lines]
>> >
>> > dev

Reply to this Message

DP - 24 Feb 2006 15:52 GMT

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

> You may need to undo the entire record, not just the one control. If that's
> the case, the syntax is
[quoted text clipped - 113 lines]
> >> >
> >> > dev

Reply to this Message

Wayne Morgan - 24 Feb 2006 23:38 GMT

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.

Signature

Wayne Morgan
MS Access MVP

> thanx man.
>
[quoted text clipped - 169 lines]
>> >> >
>> >> > dev

Reply to this Message

DP - 28 Feb 2006 14:44 GMT

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

thanx again

dev

> 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
[quoted text clipped - 184 lines]
> >> >> >
> >> >> > dev

Reply to this Message