By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,834 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

deleting records

P: n/a
DP
hi,

i've got a form, with a subform in it. i've got a delete button in the
subform. the code i;ve got is;

Private Sub cmdDeleteRecord_Click()

msg = "Are you sure you want to delete this film rental record?"
Style = vbYesNo + vbQuestion + vbDefaultButton2

Response = MsgBox(msg, Style, "Film Rental Database")
If Response = vbYes Then

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

Recordset.Delete
Else
MsgBox "You cancelled the delete operation"

End If

End Sub

so when i lcick the delete button, i get the following 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

Feb 23 '06 #1
Share this Question
Share on Google+
46 Replies


P: n/a
Error 3197 will occur if you have another form open that displays the same
record. Do you have another form open?

An easier way to delete a record is to put the following code in the double
click event of any field on your form:

DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1125 users have come to me from the newsgroups requesting help
re******@pcdatasheet.com
"DP" <DP@hotmail.com> wrote in message
news:eK******************@newsfe4-gui.ntli.net...
hi,

i've got a form, with a subform in it. i've got a delete button in the
subform. the code i;ve got is;

Private Sub cmdDeleteRecord_Click()

msg = "Are you sure you want to delete this film rental record?"
Style = vbYesNo + vbQuestion + vbDefaultButton2

Response = MsgBox(msg, Style, "Film Rental Database")
If Response = vbYes Then

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

Recordset.Delete
Else
MsgBox "You cancelled the delete operation"

End If

End Sub

so when i lcick the delete button, i get the following 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

Feb 23 '06 #2

P: n/a
To the original poster:

It is commonly accepted that these newsgroups are for free exchange of
information. Please be aware that PC Datasheet is a notorious job hunter.
If you are considering doing business with him then I suggest that you take
a look at the link below first.

http://home.tiscali.nl/arracom/whoissteve.html
"PC Datasheet" <no****@nospam.spam> wrote in message
news:jj*****************@newsread2.news.atl.earthl ink.net...
Error 3197 will occur if you have another form open that displays the same
record. Do you have another form open?

An easier way to delete a record is to put the following code in the double click event of any field on your form:

DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1125 users have come to me from the newsgroups requesting help
re******@pcdatasheet.com
"DP" <DP@hotmail.com> wrote in message
news:eK******************@newsfe4-gui.ntli.net...
hi,

i've got a form, with a subform in it. i've got a delete button in the
subform. the code i;ve got is;

Private Sub cmdDeleteRecord_Click()

msg = "Are you sure you want to delete this film rental record?"
Style = vbYesNo + vbQuestion + vbDefaultButton2

Response = MsgBox(msg, Style, "Film Rental Database")
If Response = vbYes Then

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

Recordset.Delete
Else
MsgBox "You cancelled the delete operation"

End If

End Sub

so when i lcick the delete button, i get the following 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


Feb 23 '06 #3

P: n/a
DP
well yea, i have the main form open. it does not display the same record,
but it does display the same membershipID which is in the subform. (which is
wat i;m trying to delete)

how would the code u gave me help? as u would have to double click each
field wouldnt u?

dev

"PC Datasheet" <no****@nospam.spam> wrote in message
news:jj*****************@newsread2.news.atl.earthl ink.net...
Error 3197 will occur if you have another form open that displays the same
record. Do you have another form open?

An easier way to delete a record is to put the following code in the double click event of any field on your form:

DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1125 users have come to me from the newsgroups requesting help
re******@pcdatasheet.com
"DP" <DP@hotmail.com> wrote in message
news:eK******************@newsfe4-gui.ntli.net...
hi,

i've got a form, with a subform in it. i've got a delete button in the
subform. the code i;ve got is;

Private Sub cmdDeleteRecord_Click()

msg = "Are you sure you want to delete this film rental record?"
Style = vbYesNo + vbQuestion + vbDefaultButton2

Response = MsgBox(msg, Style, "Film Rental Database")
If Response = vbYes Then

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

Recordset.Delete
Else
MsgBox "You cancelled the delete operation"

End If

End Sub

so when i lcick the delete button, i get the following 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


Feb 24 '06 #4

P: n/a
If your subform is a continuous form, all the fields in any row comprise a
record. You could put the code in the double click event of any field in a
row and when you double clicked on that field, the entire record would be
deleted. If your subform is a single form, all the fields you see in the
subform at any point in time comprise a record. You could put the code in
the double click event of any field in the subform and when you double
clicked on that field, the entire record would be deleted.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1125 users have come to me from the newsgroups requesting help
re******@pcdatasheet.com

"DP" <DP@hotmail.com> wrote in message
news:g8*******************@newsfe4-win.ntli.net...
well yea, i have the main form open. it does not display the same record,
but it does display the same membershipID which is in the subform. (which
is
wat i;m trying to delete)

how would the code u gave me help? as u would have to double click each
field wouldnt u?

dev

"PC Datasheet" <no****@nospam.spam> wrote in message
news:jj*****************@newsread2.news.atl.earthl ink.net...
Error 3197 will occur if you have another form open that displays the
same
record. Do you have another form open?

An easier way to delete a record is to put the following code in the

double
click event of any field on your form:

DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True

--
PC Datasheet
Your Resource For Help With Access, Excel And Word
Applications
Over 1125 users have come to me from the newsgroups requesting
help
re******@pcdatasheet.com
"DP" <DP@hotmail.com> wrote in message
news:eK******************@newsfe4-gui.ntli.net...
> hi,
>
> i've got a form, with a subform in it. i've got a delete button in the
> subform. the code i;ve got is;
>
> Private Sub cmdDeleteRecord_Click()
>
> msg = "Are you sure you want to delete this film rental record?"
> Style = vbYesNo + vbQuestion + vbDefaultButton2
>
> Response = MsgBox(msg, Style, "Film Rental Database")
> If Response = vbYes Then
>
> DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available =True WHERE FilmID=" & > Me.FilmID
>
> Recordset.Delete
>
>
> Else
> MsgBox "You cancelled the delete operation"
>
> End If
>
> End Sub
>
> so when i lcick the delete button, i get the following 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
>
>
>



Feb 24 '06 #5

P: n/a
Hmmm, is this the 'good' Martin or the 'bad' Martin answering here ??
Getting confused again these days ... (sorry)

Arno R

Feb 24 '06 #6

P: n/a
DP
hi,

how come that works, if i assign it to a double click, but not work if i
assign it to a delete button?

I still get that error sometimes.

i'm trying to get it to work with the delete button, as it makes more sense.
(if u know wat i mean).
u dont normally double click to delete somthing.

dev
"PC Datasheet" <no****@nospam.spam> wrote in message
news:v6*****************@newsread1.news.atl.earthl ink.net...
If your subform is a continuous form, all the fields in any row comprise a
record. You could put the code in the double click event of any field in a
row and when you double clicked on that field, the entire record would be
deleted. If your subform is a single form, all the fields you see in the
subform at any point in time comprise a record. You could put the code in
the double click event of any field in the subform and when you double
clicked on that field, the entire record would be deleted.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1125 users have come to me from the newsgroups requesting help
re******@pcdatasheet.com

"DP" <DP@hotmail.com> wrote in message
news:g8*******************@newsfe4-win.ntli.net...
well yea, i have the main form open. it does not display the same record, but it does display the same membershipID which is in the subform. (which is
wat i;m trying to delete)

how would the code u gave me help? as u would have to double click each
field wouldnt u?

dev

"PC Datasheet" <no****@nospam.spam> wrote in message
news:jj*****************@newsread2.news.atl.earthl ink.net...
Error 3197 will occur if you have another form open that displays the
same
record. Do you have another form open?

An easier way to delete a record is to put the following code in the

double
click event of any field on your form:

DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True

--
PC Datasheet
Your Resource For Help With Access, Excel And Word
Applications
Over 1125 users have come to me from the newsgroups requesting
help
re******@pcdatasheet.com
"DP" <DP@hotmail.com> wrote in message
news:eK******************@newsfe4-gui.ntli.net...
> hi,
>
> i've got a form, with a subform in it. i've got a delete button in the > subform. the code i;ve got is;
>
> Private Sub cmdDeleteRecord_Click()
>
> msg = "Are you sure you want to delete this film rental record?"
> Style = vbYesNo + vbQuestion + vbDefaultButton2
>
> Response = MsgBox(msg, Style, "Film Rental Database")
> If Response = vbYes Then
>
> DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available =True WHERE FilmID="
&
> Me.FilmID
>
> Recordset.Delete
>
>
> Else
> MsgBox "You cancelled the delete operation"
>
> End If
>
> End Sub
>
> so when i lcick the delete button, i get the following 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
>
>
>



Feb 25 '06 #7

P: n/a
Double-Clicking somewhere in a record is a standard way to delete a record!

The code will work fine if your subform is a single form. If your subform is
a continuous form, when you move from a record to your delete button, Access
does not know which record you want to delete.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1125 users have come to me from the newsgroups requesting help
re******@pcdatasheet.com

"DP" <DP@hotmail.com> wrote in message
news:Nl*******************@newsfe6-win.ntli.net...
hi,

how come that works, if i assign it to a double click, but not work if i
assign it to a delete button?

I still get that error sometimes.

i'm trying to get it to work with the delete button, as it makes more
sense.
(if u know wat i mean).
u dont normally double click to delete somthing.

dev
"PC Datasheet" <no****@nospam.spam> wrote in message
news:v6*****************@newsread1.news.atl.earthl ink.net...
If your subform is a continuous form, all the fields in any row comprise
a
record. You could put the code in the double click event of any field in
a
row and when you double clicked on that field, the entire record would be
deleted. If your subform is a single form, all the fields you see in the
subform at any point in time comprise a record. You could put the code in
the double click event of any field in the subform and when you double
clicked on that field, the entire record would be deleted.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word
Applications
Over 1125 users have come to me from the newsgroups requesting
help
re******@pcdatasheet.com

"DP" <DP@hotmail.com> wrote in message
news:g8*******************@newsfe4-win.ntli.net...
> well yea, i have the main form open. it does not display the same record, > but it does display the same membershipID which is in the subform. (which > is
> wat i;m trying to delete)
>
> how would the code u gave me help? as u would have to double click each
> field wouldnt u?
>
> dev
>
> "PC Datasheet" <no****@nospam.spam> wrote in message
> news:jj*****************@newsread2.news.atl.earthl ink.net...
>> Error 3197 will occur if you have another form open that displays the
>> same
>> record. Do you have another form open?
>>
>> An easier way to delete a record is to put the following code in the
> double
>> click event of any field on your form:
>>
>> DoCmd.SetWarnings False
>> DoCmd.RunCommand acCmdDeleteRecord
>> DoCmd.SetWarnings True
>>
>> --
>> PC Datasheet
>> Your Resource For Help With Access, Excel And Word
>> Applications
>> Over 1125 users have come to me from the newsgroups requesting
>> help
>> re******@pcdatasheet.com
>>
>>
>> "DP" <DP@hotmail.com> wrote in message
>> news:eK******************@newsfe4-gui.ntli.net...
>> > hi,
>> >
>> > i've got a form, with a subform in it. i've got a delete button in the >> > subform. the code i;ve got is;
>> >
>> > Private Sub cmdDeleteRecord_Click()
>> >
>> > msg = "Are you sure you want to delete this film rental record?"
>> > Style = vbYesNo + vbQuestion + vbDefaultButton2
>> >
>> > Response = MsgBox(msg, Style, "Film Rental Database")
>> > If Response = vbYes Then
>> >
>> > DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available =True WHERE FilmID=" > &
>> > Me.FilmID
>> >
>> > Recordset.Delete
>> >
>> >
>> > Else
>> > MsgBox "You cancelled the delete operation"
>> >
>> > End If
>> >
>> > End Sub
>> >
>> > so when i lcick the delete button, i get the following 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
>> >
>> >
>> >
>>
>>
>
>



Feb 25 '06 #8

P: n/a
DP wrote in message <eK******************@newsfe4-gui.ntli.net> :
hi,

i've got a form, with a subform in it. i've got a delete button in the
subform. the code i;ve got is;

Private Sub cmdDeleteRecord_Click()

msg = "Are you sure you want to delete this film rental record?"
Style = vbYesNo + vbQuestion + vbDefaultButton2

Response = MsgBox(msg, Style, "Film Rental Database")
If Response = vbYes Then

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

Recordset.Delete
Else
MsgBox "You cancelled the delete operation"

End If

End Sub

so when i lcick the delete button, i get the following 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


I think the errormessage might be due to you having unsaved changes in
the form, then attempting to alter the same data programatically.

Try one of the save options prior to running anything, for instance

if me.dirty then me.dirty = false

(for more info on these methods of saving, and a discussion of them,
though unrelated to this, have a read at the following article by Allen
Browne http://allenbrowne.com/bug-01.html)

I've never been a fan of deletes on a recordset, though it might
probably work

dim strSql as string
strSql = "delete from yourtable where fillmid = " & me!FilmID.value
currentdb.execute strSql, dbFailOnError

--
Roy-Vidar
Feb 25 '06 #9

P: n/a
Oh - and forgot, sorry - after deleting a record, you might want to do
a

me.requery

to freshen up the display.

--
Roy-Vidar
Feb 25 '06 #10

P: n/a
"PC Datasheet" <no****@nospam.spam> wrote in
news:LD*****************@newsread1.news.atl.earthl ink.net:
Double-Clicking somewhere in a record is a standard way to
delete a record!
Not in any serious database application it isn't.
The code will work fine if your subform is a single form. If
your subform is a continuous form, when you move from a record
to your delete button, Access does not know which record you
want to delete.

Wrong. The code deletes the current record. If the button is on the
form's header or footer it works correctly on a continuous form. It
also works in a subform if the button is on the subform's header or
footer.

--
Bob Quintal

PA is y I've altered my email address.
Feb 25 '06 #11

P: n/a
PC Datasheet wrote:
Double-Clicking somewhere in a record is a standard way to delete a record!


Steve, if you meant right clicking, and bringing up a pop up menu with
an option to delete, I'd agree with you.

But double clicking to delete? Absolutely, positively not in any
serious database application I've ever used or developed in my
professional life.

Double clicking is easy for an inexperienced user to do accidentally.

This is very, very bad GUI advice. Double clicking is usually a means
to either bring up additional/ancilliary information in a pop up window
or drill down to other record displays in:

1) Computerized maintenance management systems,

2) Military ACIS

3) Point of sale systems

4) Inventory, including rental systems

5) Hazardous materials management

6) Significant incident reports

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto
Feb 25 '06 #12

P: n/a
DP
yea, thats wat i ment. i've never seen an app, that uses doube clicking to
delete a record.

anywayz i'm still working on this button, i'll let u know wat happens.

dev

"Tim Marshall" <TI****@PurplePandaChasers.Moertherium> wrote in message
news:dt**********@coranto.ucs.mun.ca...
PC Datasheet wrote:
Double-Clicking somewhere in a record is a standard way to delete a
record!
Steve, if you meant right clicking, and bringing up a pop up menu with
an option to delete, I'd agree with you.

But double clicking to delete? Absolutely, positively not in any
serious database application I've ever used or developed in my
professional life.

Double clicking is easy for an inexperienced user to do accidentally.

This is very, very bad GUI advice. Double clicking is usually a means
to either bring up additional/ancilliary information in a pop up window
or drill down to other record displays in:

1) Computerized maintenance management systems,

2) Military ACIS

3) Point of sale systems

4) Inventory, including rental systems

5) Hazardous materials management

6) Significant incident reports

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

Feb 26 '06 #13

P: n/a
DP
ok, so i've tried wat u have gave me, and it still is not working. i
understand the problem but dont have a clue wat else to do. i think i've
tried almost everything.

this is wat i've got; it might look confusing cos i been messing around with
it for a week now;

Private Sub cmdDeleteRecord_Click()

msg = "Are you sure you want to delete this film rental record?"
Style = vbYesNo + vbQuestion + vbDefaultButton2

Response = MsgBox(msg, Style, "Film Rental Database")
If Response = vbYes Then
DeleteRecord

Else
MsgBox "You cancelled the delete operation"

End If

End Sub
================================================== ===========

Private Sub DeleteRecord()

DoCmd.SetWarnings False
'DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available =True WHERE FilmID=" &
Me.FilmID
Update
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord

DoCmd.SetWarnings True
End Sub

================================================== ===================

Private Sub Update()
DoCmd.SetWarnings False
If FilmID = Null Then

MsgBox "Delete operation cannot be carried out"
'End If
Else
'DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available =True WHERE FilmID=" &
Me.FilmID
DoCmd.SetWarnings True
End If
End Sub
================================================== ===========

so, the code i've got works 'somtimes' as i get the error message described
before. (i've taken the double click event out, however that worked pretty
well.)

u know on the 'update' function, is it possible to check if the filmID is
null (empty) and then the sql update runs. (cos if i do it now, i just get
an error) i just wanted to create a little validation.

thanx

dev
"RoyVidar" <ro*************@yahoo.no> wrote in message
news:mn***********************@yahoo.no...
Oh - and forgot, sorry - after deleting a record, you might want to do
a

me.requery

to freshen up the display.

--
Roy-Vidar

Feb 26 '06 #14

P: n/a
DP wrote in message <0g*******************@newsfe3-gui.ntli.net> :
ok, so i've tried wat u have gave me, and it still is not working. i
understand the problem but dont have a clue wat else to do. i think i've
tried almost everything.

this is wat i've got; it might look confusing cos i been messing around with
it for a week now;

Private Sub cmdDeleteRecord_Click()

msg = "Are you sure you want to delete this film rental record?"
Style = vbYesNo + vbQuestion + vbDefaultButton2

Response = MsgBox(msg, Style, "Film Rental Database")
If Response = vbYes Then
DeleteRecord

Else
MsgBox "You cancelled the delete operation"

End If

End Sub
================================================== ===========

Private Sub DeleteRecord()

DoCmd.SetWarnings False
'DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available =True WHERE FilmID=" &
Me.FilmID
Update
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord

DoCmd.SetWarnings True
End Sub

================================================== ===================

Private Sub Update()
DoCmd.SetWarnings False
If FilmID = Null Then

MsgBox "Delete operation cannot be carried out"
'End If
Else
'DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available =True WHERE FilmID=" &
Me.FilmID
DoCmd.SetWarnings True
End If
End Sub
================================================== ===========

so, the code i've got works 'somtimes' as i get the error message described
before. (i've taken the double click event out, however that worked pretty
well.)

u know on the 'update' function, is it possible to check if the filmID is
null (empty) and then the sql update runs. (cos if i do it now, i just get
an error) i just wanted to create a little validation.

thanx

dev
"RoyVidar" <ro*************@yahoo.no> wrote in message
news:mn***********************@yahoo.no...
Oh - and forgot, sorry - after deleting a record, you might want to do
a

me.requery

to freshen up the display.

--
Roy-Vidar


I'm sorry, I can't see you've tried any of my suggestions from the code
you posted, so I don't know what to say, except to repeat

Do try
1 - save the record explicitly before doing anything else
2 - run a requery of the form after the process is finished

To test for contents in the control, you can for instance try

if len(me!FilmID.value & vbNullString)>0 then
' only if there's contents in the control, continue with routine
end if

The next question I would ask, is whether this table/record is open,
and
contains unsaved changes in another form. If so, try explicitly saving
the record there too, either before opening this form, or before trying
to update/save from this form.

--
Roy-Vidar
Feb 26 '06 #15

P: n/a
DP
ok, tanx, i'll try it now.

sorry, i pasted the code from another program, which was working. i'm using
2 programs, which i have copied and pasted, so i do not loose any good code.
i have tried yor code, another another version.

tanx

dev

"RoyVidar" <ro*************@yahoo.no> wrote in message
news:mn***********************@yahoo.no...
DP wrote in message <0g*******************@newsfe3-gui.ntli.net> :
ok, so i've tried wat u have gave me, and it still is not working. i
understand the problem but dont have a clue wat else to do. i think i've
tried almost everything.

this is wat i've got; it might look confusing cos i been messing around with it for a week now;

Private Sub cmdDeleteRecord_Click()

msg = "Are you sure you want to delete this film rental record?"
Style = vbYesNo + vbQuestion + vbDefaultButton2

Response = MsgBox(msg, Style, "Film Rental Database")
If Response = vbYes Then
DeleteRecord

Else
MsgBox "You cancelled the delete operation"

End If

End Sub
================================================== ===========

Private Sub DeleteRecord()

DoCmd.SetWarnings False
'DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available =True WHERE FilmID=" & Me.FilmID
Update
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord

DoCmd.SetWarnings True
End Sub

================================================== ===================

Private Sub Update()
DoCmd.SetWarnings False
If FilmID = Null Then

MsgBox "Delete operation cannot be carried out"
'End If
Else
'DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available =True WHERE FilmID=" & Me.FilmID
DoCmd.SetWarnings True
End If
End Sub
================================================== ===========

so, the code i've got works 'somtimes' as i get the error message described before. (i've taken the double click event out, however that worked pretty well.)

u know on the 'update' function, is it possible to check if the filmID is null (empty) and then the sql update runs. (cos if i do it now, i just get an error) i just wanted to create a little validation.

thanx

dev
"RoyVidar" <ro*************@yahoo.no> wrote in message
news:mn***********************@yahoo.no...
Oh - and forgot, sorry - after deleting a record, you might want to do
a

me.requery

to freshen up the display.

--
Roy-Vidar


I'm sorry, I can't see you've tried any of my suggestions from the code
you posted, so I don't know what to say, except to repeat

Do try
1 - save the record explicitly before doing anything else
2 - run a requery of the form after the process is finished

To test for contents in the control, you can for instance try

if len(me!FilmID.value & vbNullString)>0 then
' only if there's contents in the control, continue with routine
end if

The next question I would ask, is whether this table/record is open,
and
contains unsaved changes in another form. If so, try explicitly saving
the record there too, either before opening this form, or before trying
to update/save from this form.

--
Roy-Vidar

Feb 26 '06 #16

P: n/a
There's nothing wrong with double clicking in a record! In fact with using
double clicking the user knows explicitly which record he is about to
delete. In my opinion, a button on the main form to delete a record in the
subform is confusing. One would expect a button on the main form to do
something on the main form. A button on the subform can not be lined up with
buttons on the main form and so gives a less neat appearance to the overall
form. A delete button is less efficient. The user must first somehow go to
the record he wants to delete and then go to the delete button. This takes
two movements. Along the same lines, users who like to do everything with
the keyboard, will not prefer a delete button. It forces them to use the
mouse. As far as an inexperienced user accidently double clicking on a
record, he does not want to delete, he can more easily do the same thing by
being on the wrong record and then clicking a delete button. And so what if
a user double clicks on the wrong record; you have appropriate code to warn
the user when deleting a record:
Dim Msg As String
Dim Style as String
msg = "Are you sure you want to delete this film rental record?"
Style = vbYesNo + vbQuestion + vbDefaultButton2

Response = MsgBox(msg, Style, "Film Rental Database")
If Response = vbYes Then

DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True

Else
MsgBox "You cancelled the delete operation"

End If

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1125 users have come to me from the newsgroups requesting help
re******@pcdatasheet.com

"DP" <DP@hotmail.com> wrote in message
news:H_*******************@newsfe2-gui.ntli.net...
yea, thats wat i ment. i've never seen an app, that uses doube clicking to
delete a record.

anywayz i'm still working on this button, i'll let u know wat happens.

dev

"Tim Marshall" <TI****@PurplePandaChasers.Moertherium> wrote in message
news:dt**********@coranto.ucs.mun.ca...
PC Datasheet wrote:
> Double-Clicking somewhere in a record is a standard way to delete a

record!

Steve, if you meant right clicking, and bringing up a pop up menu with
an option to delete, I'd agree with you.

But double clicking to delete? Absolutely, positively not in any
serious database application I've ever used or developed in my
professional life.

Double clicking is easy for an inexperienced user to do accidentally.

This is very, very bad GUI advice. Double clicking is usually a means
to either bring up additional/ancilliary information in a pop up window
or drill down to other record displays in:

1) Computerized maintenance management systems,

2) Military ACIS

3) Point of sale systems

4) Inventory, including rental systems

5) Hazardous materials management

6) Significant incident reports

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto


Feb 26 '06 #17

P: n/a
DP
wat if i wanted it to display a message when there is NO content in the
field?

thats wat u gave me;

if len(me!FilmID.value & vbNullString)>0 then
cos i have to use an update query to update another table, and i wanted to
create a little validation. (and to stop an error popping up when there is
no record active.)

(so i basically want it to display an error, when there is nothing to
delete).

thanx

dev

"RoyVidar" <ro*************@yahoo.no> wrote in message
news:mn***********************@yahoo.no...
DP wrote in message <0g*******************@newsfe3-gui.ntli.net> :
ok, so i've tried wat u have gave me, and it still is not working. i
understand the problem but dont have a clue wat else to do. i think i've
tried almost everything.

this is wat i've got; it might look confusing cos i been messing around with it for a week now;

Private Sub cmdDeleteRecord_Click()

msg = "Are you sure you want to delete this film rental record?"
Style = vbYesNo + vbQuestion + vbDefaultButton2

Response = MsgBox(msg, Style, "Film Rental Database")
If Response = vbYes Then
DeleteRecord

Else
MsgBox "You cancelled the delete operation"

End If

End Sub
================================================== ===========

Private Sub DeleteRecord()

DoCmd.SetWarnings False
'DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available =True WHERE FilmID=" & Me.FilmID
Update
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord

DoCmd.SetWarnings True
End Sub

================================================== ===================

Private Sub Update()
DoCmd.SetWarnings False
If FilmID = Null Then

MsgBox "Delete operation cannot be carried out"
'End If
Else
'DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available =True WHERE FilmID=" & Me.FilmID
DoCmd.SetWarnings True
End If
End Sub
================================================== ===========

so, the code i've got works 'somtimes' as i get the error message described before. (i've taken the double click event out, however that worked pretty well.)

u know on the 'update' function, is it possible to check if the filmID is null (empty) and then the sql update runs. (cos if i do it now, i just get an error) i just wanted to create a little validation.

thanx

dev
"RoyVidar" <ro*************@yahoo.no> wrote in message
news:mn***********************@yahoo.no...
Oh - and forgot, sorry - after deleting a record, you might want to do
a

me.requery

to freshen up the display.

--
Roy-Vidar


I'm sorry, I can't see you've tried any of my suggestions from the code
you posted, so I don't know what to say, except to repeat

Do try
1 - save the record explicitly before doing anything else
2 - run a requery of the form after the process is finished

To test for contents in the control, you can for instance try

if len(me!FilmID.value & vbNullString)>0 then
' only if there's contents in the control, continue with routine
end if

The next question I would ask, is whether this table/record is open,
and
contains unsaved changes in another form. If so, try explicitly saving
the record there too, either before opening this form, or before trying
to update/save from this form.

--
Roy-Vidar

Feb 26 '06 #18

P: n/a
Your logic is unbelievable. Double clicking to delete a record? Most normal
users would expect that the record would be selected or opened in a new
window.

So users do not want to use a delete button because it involves using a
mouse? So what are you double clicking?

You actually get people to pay you for this type of logic? Do any come back?

John... Visio MVP
Feb 26 '06 #19

P: n/a
DP wrote in message <xn*******************@newsfe6-win.ntli.net> :
wat if i wanted it to display a message when there is NO content in the
field?

thats wat u gave me;

if len(me!FilmID.value & vbNullString)>0 then
cos i have to use an update query to update another table, and i wanted to
create a little validation. (and to stop an error popping up when there is
no record active.)

(so i basically want it to display an error, when there is nothing to
delete).

thanx

dev

"RoyVidar" <ro*************@yahoo.no> wrote in message
news:mn***********************@yahoo.no...
DP wrote in message <0g*******************@newsfe3-gui.ntli.net> :
ok, so i've tried wat u have gave me, and it still is not working. i
understand the problem but dont have a clue wat else to do. i think i've
tried almost everything.

this is wat i've got; it might look confusing cos i been messing around
with it for a week now;

Private Sub cmdDeleteRecord_Click()

msg = "Are you sure you want to delete this film rental record?"
Style = vbYesNo + vbQuestion + vbDefaultButton2

Response = MsgBox(msg, Style, "Film Rental Database")
If Response = vbYes Then
DeleteRecord

Else
MsgBox "You cancelled the delete operation"

End If

End Sub
================================================== ===========

Private Sub DeleteRecord()

DoCmd.SetWarnings False
'DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available =True WHERE FilmID=" &
Me.FilmID
Update
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord

DoCmd.SetWarnings True
End Sub

================================================== ===================

Private Sub Update()
DoCmd.SetWarnings False
If FilmID = Null Then

MsgBox "Delete operation cannot be carried out"
'End If
Else
'DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available =True WHERE FilmID=" &
Me.FilmID
DoCmd.SetWarnings True
End If
End Sub
================================================== ===========

so, the code i've got works 'somtimes' as i get the error message described
before. (i've taken the double click event out, however that worked pretty
well.)

u know on the 'update' function, is it possible to check if the filmID is
null (empty) and then the sql update runs. (cos if i do it now, i just get
an error) i just wanted to create a little validation.

thanx

dev
"RoyVidar" <ro*************@yahoo.no> wrote in message
news:mn***********************@yahoo.no...
Oh - and forgot, sorry - after deleting a record, you might want to do
a

me.requery

to freshen up the display.

--
Roy-Vidar


I'm sorry, I can't see you've tried any of my suggestions from the code
you posted, so I don't know what to say, except to repeat

Do try
1 - save the record explicitly before doing anything else
2 - run a requery of the form after the process is finished

To test for contents in the control, you can for instance try

if len(me!FilmID.value & vbNullString)>0 then
' only if there's contents in the control, continue with routine
end if

The next question I would ask, is whether this table/record is open,
and
contains unsaved changes in another form. If so, try explicitly saving
the record there too, either before opening this form, or before trying
to update/save from this form.

--
Roy-Vidar


Look at the Else clause of the If-Then-Else construct

If <some condition> Then
' perform some code (the updating stuff?)
Else
' perform some other code (perhaps a message box)
End If

It would be interesting to know whether the previous suggestions worked
with regards to the initial errors.

--
Roy-Vidar
Feb 26 '06 #20

P: n/a
Why don't you go and kiss Arno's A&$*##!!! You're an A&$*##!!! who makes no
contribution to this newsgroup and an embarassment to what MVP is suppose to
stand for.

"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:3Z********************@magma.ca...
Your logic is unbelievable. Double clicking to delete a record? Most
normal users would expect that the record would be selected or opened in a
new window.

So users do not want to use a delete button because it involves using a
mouse? So what are you double clicking?

You actually get people to pay you for this type of logic? Do any come
back?

John... Visio MVP

Feb 26 '06 #21

P: n/a

"PC Datasheet" <no****@nospam.spam> schreef in bericht news:LD*****************@newsread1.news.atl.earthl ink.net...
Double-Clicking somewhere in a record is a standard way to delete a record!


This is (again) sheer, utter nonsense !!

Arno R
Feb 26 '06 #22

P: n/a
"PC Datasheet" wrote
Why don't you go and kiss Arno's A&$*##!!!
You're an A&$*##!!! who makes no
contribution to this newsgroup and an embarass-
ment to what MVP is suppose to
stand for.


<SARCASM>How nice that you can maintain your cool when all about you are
losing theirs. Nice work, Steve. </SARCASM>

Double-clicking for delete would be a very unusual user interface practice.

There's an old axiom, "When you find you are digging yourself into a deeper
hole, stop digging." Continuing to try to justify bad advice that you have
given is definitely in the category of "digging yourself into a deeper
hole."

And, before you bother to bring up your invalid claim again, "NO, I would
never seek subcontract work from someone who has confessed that he stiffs
his subcontractors. Never. I may not be the brightest bulb in the
chandelier, but I am bright enough to know not to work for people who don't
pay what they owe."

Larry Linson
Feb 26 '06 #23

P: n/a
Why don't you go and kiss John Marshall's A&$*##!!! You're an A&$*##!!! who
makes no
contribution to this newsgroup.

"Arno R" <ar***********@tiscali.nl> wrote in message
news:44**********************@text.nova.planet.nl. ..

"PC Datasheet" <no****@nospam.spam> schreef in bericht
news:LD*****************@newsread1.news.atl.earthl ink.net...
Double-Clicking somewhere in a record is a standard way to delete a
record!


This is (again) sheer, utter nonsense !!

Arno R
Feb 26 '06 #24

P: n/a
PC Datasheet wrote:
Why don't you go and kiss


Seriously, Steve, it may be there are other issues that are intensifying
this, but double clicking to delete is really a very bad GUI feature in
that it is totally unexpected behaviour. It is indeed a pretty
universal windows function that double clicking a record drills down
somehow, rather than deleting.

I note your string for your msgbox uses vbyesno. One frequent issue
with users is the mind numb mode - double clicking a record is often an
accidental occurance and many people when in mind numb mode will simply
press the enter button without reading the message box. I would suggest
a system whereby double clicking is a delete action has not undergone
very much in the way of user trials.

Also, I notice you're using setwarnings which is not an effective way to
run an action statement. Use the execute method with dbfailonerror.
That way if for some reason there's a flaw in your sql, setwarnings is
not turned off permanently.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto
Feb 27 '06 #25

P: n/a
Tim,

I compliment you on the polite response!

We have a difference of opinion on how to delete a record and it may come
from the difference of the applications we write. Both work so it's like
most things in Access there are multiple ways to do a task. The OP has seen
the arguments and he can decide what he wants to do.

I do agree with you on using the execute method with dbfailonerror. Most of
the time I use that. Sometimes I forget and I revert to past practice
because I used it so much in the past. Right or wrong, I believe you will
see the SetWarnings recommendation much more in the newsgroup.

Steve
"Tim Marshall" <TI****@PurplePandaChasers.Moertherium> wrote in message
news:dt**********@coranto.ucs.mun.ca...
PC Datasheet wrote:
Why don't you go and kiss


Seriously, Steve, it may be there are other issues that are intensifying
this, but double clicking to delete is really a very bad GUI feature in
that it is totally unexpected behaviour. It is indeed a pretty universal
windows function that double clicking a record drills down somehow, rather
than deleting.

I note your string for your msgbox uses vbyesno. One frequent issue with
users is the mind numb mode - double clicking a record is often an
accidental occurance and many people when in mind numb mode will simply
press the enter button without reading the message box. I would suggest a
system whereby double clicking is a delete action has not undergone very
much in the way of user trials.

Also, I notice you're using setwarnings which is not an effective way to
run an action statement. Use the execute method with dbfailonerror. That
way if for some reason there's a flaw in your sql, setwarnings is not
turned off permanently.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

Feb 27 '06 #26

P: n/a
"PC Datasheet" <no****@nospam.spam> wrote in message
news:Ir*****************@newsread3.news.atl.earthl ink.net...
I compliment you on the polite response!
No need for a compliment. Tim is just behaving as a normal member of these
newsgroups. You on
the other hand have a lot to apologize for. Your behaviour is (and has been)
very juvenile.
We have a difference of opinion on how to delete a record and it may come
from the difference of the applications we write. Both work so it's like
most things in Access there are multiple ways to do a task.
It's not a matter of opinion, your suggestion like your behaviour is just
wrong.
Steve
PC Datasheet wrote:
Why don't you go and kiss

--
John... Visio MVP
Feb 27 '06 #27

P: n/a
"PC Datasheet" wrote
You're an <expletive deleted> who makes no contribution to this newsgroup.


Arno, if Steve were correct (which I know he is not, as I have read some of
your very real contributions to this newsgroup), it would at least mean you
did not mislead by giving potentially harmful advice, wouldn't it?

Larry
Feb 27 '06 #28

P: n/a
Why don't you go and kiss Arno's A&$*## for a second time!!! You're an
A&$*##!!! who makes no
contribution to this newsgroup and an embarassment to what MVP is suppose to
stand for.

"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:Sq********************@magma.ca...
"PC Datasheet" <no****@nospam.spam> wrote in message
news:Ir*****************@newsread3.news.atl.earthl ink.net...
I compliment you on the polite response!


No need for a compliment. Tim is just behaving as a normal member of these
newsgroups. You on
the other hand have a lot to apologize for. Your behaviour is (and has
been) very juvenile.
We have a difference of opinion on how to delete a record and it may come
from the difference of the applications we write. Both work so it's like
most things in Access there are multiple ways to do a task.


It's not a matter of opinion, your suggestion like your behaviour is just
wrong.
Steve
PC Datasheet wrote:
Why don't you go and kiss

--
John... Visio MVP

Feb 27 '06 #29

P: n/a
DP
sorry.
i've been trying them, but with no success. i've tried saving as u
mentioned. i saved after an update, after the record was changed, etc... i
even tried saving the main form and subform. (when dirty, on load, etc...)
i've tried the requery as well, but none of them work. it still comes out
with that same error.

i have not tried it in single form mode, as i need it in continous.

i dont know wat else to do, as the error only comes after u try deleting a
record straight away. If u delete it after a min, or after restarting, the
error does not arise. however, thancx for all the help, i know u have not
started arguing with other people. lol. (somone actually mentioed deleting a
record through double clicking, which i dont want to do, as it does not feel
correct, if u know wat i mean). thats bad HCI!!

i'll try the other bit of code u gave me now....

thanx

dev

"RoyVidar" <ro*************@yahoo.no> wrote in message
news:mn***********************@yahoo.no...
DP wrote in message <xn*******************@newsfe6-win.ntli.net> :
wat if i wanted it to display a message when there is NO content in the
field?

thats wat u gave me;

if len(me!FilmID.value & vbNullString)>0 then
cos i have to use an update query to update another table, and i wanted to create a little validation. (and to stop an error popping up when there is no record active.)

(so i basically want it to display an error, when there is nothing to
delete).

thanx

dev

"RoyVidar" <ro*************@yahoo.no> wrote in message
news:mn***********************@yahoo.no...
DP wrote in message <0g*******************@newsfe3-gui.ntli.net> :
ok, so i've tried wat u have gave me, and it still is not working. i
understand the problem but dont have a clue wat else to do. i think i've tried almost everything.

this is wat i've got; it might look confusing cos i been messing around with it for a week now;

Private Sub cmdDeleteRecord_Click()

msg = "Are you sure you want to delete this film rental record?"
Style = vbYesNo + vbQuestion + vbDefaultButton2

Response = MsgBox(msg, Style, "Film Rental Database")
If Response = vbYes Then
DeleteRecord

Else
MsgBox "You cancelled the delete operation"

End If

End Sub
================================================== ===========

Private Sub DeleteRecord()

DoCmd.SetWarnings False
'DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available =True WHERE FilmID=" & Me.FilmID
Update
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord

DoCmd.SetWarnings True
End Sub

================================================== ===================

Private Sub Update()
DoCmd.SetWarnings False
If FilmID = Null Then

MsgBox "Delete operation cannot be carried out"
'End If
Else
'DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available =True WHERE FilmID=" & Me.FilmID
DoCmd.SetWarnings True
End If
End Sub
================================================== ===========

so, the code i've got works 'somtimes' as i get the error message described before. (i've taken the double click event out, however that worked pretty well.)

u know on the 'update' function, is it possible to check if the filmID is null (empty) and then the sql update runs. (cos if i do it now, i just get an error) i just wanted to create a little validation.

thanx

dev
"RoyVidar" <ro*************@yahoo.no> wrote in message
news:mn***********************@yahoo.no...
> Oh - and forgot, sorry - after deleting a record, you might want to do> a
>
> me.requery
>
> to freshen up the display.
>
> --
> Roy-Vidar

I'm sorry, I can't see you've tried any of my suggestions from the code
you posted, so I don't know what to say, except to repeat

Do try
1 - save the record explicitly before doing anything else
2 - run a requery of the form after the process is finished

To test for contents in the control, you can for instance try

if len(me!FilmID.value & vbNullString)>0 then
' only if there's contents in the control, continue with routine
end if

The next question I would ask, is whether this table/record is open,
and
contains unsaved changes in another form. If so, try explicitly saving
the record there too, either before opening this form, or before trying
to update/save from this form.

--
Roy-Vidar


Look at the Else clause of the If-Then-Else construct

If <some condition> Then
' perform some code (the updating stuff?)
Else
' perform some other code (perhaps a message box)
End If

It would be interesting to know whether the previous suggestions worked
with regards to the initial errors.

--
Roy-Vidar

Feb 27 '06 #30

P: n/a
DP
sorry, wats the piece of code, to see if there is an empty string in the
field. i cant find it now.
thanx

dev

"RoyVidar" <ro*************@yahoo.no> wrote in message
news:mn***********************@yahoo.no...
DP wrote in message <xn*******************@newsfe6-win.ntli.net> :
wat if i wanted it to display a message when there is NO content in the
field?

thats wat u gave me;

if len(me!FilmID.value & vbNullString)>0 then
cos i have to use an update query to update another table, and i wanted to create a little validation. (and to stop an error popping up when there is no record active.)

(so i basically want it to display an error, when there is nothing to
delete).

thanx

dev

"RoyVidar" <ro*************@yahoo.no> wrote in message
news:mn***********************@yahoo.no...
DP wrote in message <0g*******************@newsfe3-gui.ntli.net> :
ok, so i've tried wat u have gave me, and it still is not working. i
understand the problem but dont have a clue wat else to do. i think i've tried almost everything.

this is wat i've got; it might look confusing cos i been messing around with it for a week now;

Private Sub cmdDeleteRecord_Click()

msg = "Are you sure you want to delete this film rental record?"
Style = vbYesNo + vbQuestion + vbDefaultButton2

Response = MsgBox(msg, Style, "Film Rental Database")
If Response = vbYes Then
DeleteRecord

Else
MsgBox "You cancelled the delete operation"

End If

End Sub
================================================== ===========

Private Sub DeleteRecord()

DoCmd.SetWarnings False
'DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available =True WHERE FilmID=" & Me.FilmID
Update
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord

DoCmd.SetWarnings True
End Sub

================================================== ===================

Private Sub Update()
DoCmd.SetWarnings False
If FilmID = Null Then

MsgBox "Delete operation cannot be carried out"
'End If
Else
'DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available =True WHERE FilmID=" & Me.FilmID
DoCmd.SetWarnings True
End If
End Sub
================================================== ===========

so, the code i've got works 'somtimes' as i get the error message described before. (i've taken the double click event out, however that worked pretty well.)

u know on the 'update' function, is it possible to check if the filmID is null (empty) and then the sql update runs. (cos if i do it now, i just get an error) i just wanted to create a little validation.

thanx

dev
"RoyVidar" <ro*************@yahoo.no> wrote in message
news:mn***********************@yahoo.no...
> Oh - and forgot, sorry - after deleting a record, you might want to do> a
>
> me.requery
>
> to freshen up the display.
>
> --
> Roy-Vidar

I'm sorry, I can't see you've tried any of my suggestions from the code
you posted, so I don't know what to say, except to repeat

Do try
1 - save the record explicitly before doing anything else
2 - run a requery of the form after the process is finished

To test for contents in the control, you can for instance try

if len(me!FilmID.value & vbNullString)>0 then
' only if there's contents in the control, continue with routine
end if

The next question I would ask, is whether this table/record is open,
and
contains unsaved changes in another form. If so, try explicitly saving
the record there too, either before opening this form, or before trying
to update/save from this form.

--
Roy-Vidar


Look at the Else clause of the If-Then-Else construct

If <some condition> Then
' perform some code (the updating stuff?)
Else
' perform some other code (perhaps a message box)
End If

It would be interesting to know whether the previous suggestions worked
with regards to the initial errors.

--
Roy-Vidar

Feb 27 '06 #31

P: n/a

"PC Datasheet" <no****@nospam.spam> schreef in bericht news:w5*****************@newsread2.news.atl.earthl ink.net...
Why don't you go and kiss John Marshall's A&$*##!!! You're an A&$*##!!! who
makes no
contribution to this newsgroup.


Why don't you stop 'contributing' lies, name-calling and sheer utter nonsense here?

Why don't you stop posting your false claim that you are a 'resource'?
Why don't you stop posting your idiotic claim 1100+ users requested your 'help'?

Arno R

Feb 27 '06 #32

P: n/a
DP wrote in message <L3*******************@newsfe4-win.ntli.net> :
sorry.
i've been trying them, but with no success. i've tried saving as u
mentioned. i saved after an update, after the record was changed, etc... i
even tried saving the main form and subform. (when dirty, on load, etc...)
i've tried the requery as well, but none of them work. it still comes out
with that same error.

i have not tried it in single form mode, as i need it in continous.

i dont know wat else to do, as the error only comes after u try deleting a
record straight away. If u delete it after a min, or after restarting, the
error does not arise. however, thancx for all the help, i know u have not
started arguing with other people. lol. (somone actually mentioed deleting a
record through double clicking, which i dont want to do, as it does not feel
correct, if u know wat i mean). thats bad HCI!!

i'll try the other bit of code u gave me now....

thanx

dev

"RoyVidar" <ro*************@yahoo.no> wrote in message
news:mn***********************@yahoo.no...
DP wrote in message <xn*******************@newsfe6-win.ntli.net> :
wat if i wanted it to display a message when there is NO content in the
field?

thats wat u gave me;

if len(me!FilmID.value & vbNullString)>0 then
cos i have to use an update query to update another table, and i wanted to
create a little validation. (and to stop an error popping up when there is
no record active.)

(so i basically want it to display an error, when there is nothing to
delete).

thanx

dev

"RoyVidar" <ro*************@yahoo.no> wrote in message
news:mn***********************@yahoo.no...
DP wrote in message <0g*******************@newsfe3-gui.ntli.net> :
> ok, so i've tried wat u have gave me, and it still is not working. i
> understand the problem but dont have a clue wat else to do. i think i've
> tried almost everything.
>
> this is wat i've got; it might look confusing cos i been messing around
> with it for a week now;
>
> Private Sub cmdDeleteRecord_Click()
>
> msg = "Are you sure you want to delete this film rental record?"
> Style = vbYesNo + vbQuestion + vbDefaultButton2
>
> Response = MsgBox(msg, Style, "Film Rental Database")
> If Response = vbYes Then
> DeleteRecord
>
> Else
> MsgBox "You cancelled the delete operation"
>
> End If
>
> End Sub
>
>
> ================================================== ===========
>
> Private Sub DeleteRecord()
>
> DoCmd.SetWarnings False
> 'DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available =True WHERE FilmID="
> & Me.FilmID
> Update
> DoCmd.SetWarnings False
> DoCmd.RunCommand acCmdDeleteRecord
>
> DoCmd.SetWarnings True
> End Sub
>
> ================================================== ===================
>
> Private Sub Update()
> DoCmd.SetWarnings False
> If FilmID = Null Then
>
> MsgBox "Delete operation cannot be carried out"
> 'End If
> Else
> 'DoCmd.SetWarnings False
> DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available =True WHERE FilmID=" &
> Me.FilmID
> DoCmd.SetWarnings True
> End If
> End Sub
> ================================================== ===========
>
> so, the code i've got works 'somtimes' as i get the error message
> described before. (i've taken the double click event out, however that
> worked pretty well.)
>
> u know on the 'update' function, is it possible to check if the filmID is
> null (empty) and then the sql update runs. (cos if i do it now, i just
> get an error) i just wanted to create a little validation.
>
> thanx
>
> dev
>
>
> "RoyVidar" <ro*************@yahoo.no> wrote in message
> news:mn***********************@yahoo.no...
>> Oh - and forgot, sorry - after deleting a record, you might want to do
>> a
>>
>> me.requery
>>
>> to freshen up the display.
>>
>> --
>> Roy-Vidar

I'm sorry, I can't see you've tried any of my suggestions from the code
you posted, so I don't know what to say, except to repeat

Do try
1 - save the record explicitly before doing anything else
2 - run a requery of the form after the process is finished

To test for contents in the control, you can for instance try

if len(me!FilmID.value & vbNullString)>0 then
' only if there's contents in the control, continue with routine
end if

The next question I would ask, is whether this table/record is open,
and
contains unsaved changes in another form. If so, try explicitly saving
the record there too, either before opening this form, or before trying
to update/save from this form.

--
Roy-Vidar


Look at the Else clause of the If-Then-Else construct

If <some condition> Then
' perform some code (the updating stuff?)
Else
' perform some other code (perhaps a message box)
End If

It would be interesting to know whether the previous suggestions worked
with regards to the initial errors.

--
Roy-Vidar


I don't know. Similar stuff seems to work when I test this. Here's a
quick and dirty type up (meaning air code) ;-)

private sub cmdDelete_clikc()

dim strSql as string
dim intAnswer as integer

on error goto myerr

' save if there are unsaved changes
if me.dirty then me.dirty = false

' if you've got another form with this record open, containing
' unsaved changes ...
' if forms("frmNameOfForm").dirty then
' forms("frmNameOfForm").dirty = false
' end if

if len(me!FilmID.value & vbNullString)>0 then
intAnswer = msgbox("Wanna delete?", _
vbyesno+vbquestion, "Delete?")
if intAnswer = vbyes then
' I don't know the table name of this one
strsql = "delete from yourtable where filmid = " & _
me!FilmID.value
currentdb.execute strSql, dbfailonerror
strsql = "UPDATE tblFilm SET tblFilm.Available = -1 " & _
WHERE FilmID=" & Me!FilmID.Value
currentdb.execute strSql, dbfailonerror
end if
Else
msgbox "no film selected...", vbinformation
End If
me.requery

myexit:
exit sub
myerr:
msgbox err.number & " " & err.description
resume myexit
end sub

If this doesn't work, perhaps remove/comment the "on error" line, to
make it halt on the line causing the error. Perhaps posting the full
code again, as you are using it, and highlight the offending line.

Sometimes, the 3197 can also relate to corruption. So if nothing works,
then you could take a look here (search for 3197)
http://www.granite.ab.ca/access/corruption/symptoms.htm
(Tony Toews pages on corruption symptoms) and read a bit.

--
Roy-Vidar
Feb 27 '06 #33

P: n/a
Why don't you and John Marshall go somewhere and kiss each other's A&$*##
!!! You're both A&$*##!!! who makes no contribution to this newsgroup.

"Arno R" <ar***********@tiscali.nl> wrote in message
news:44**********************@text.nova.planet.nl. ..

"PC Datasheet" <no****@nospam.spam> schreef in bericht
news:w5*****************@newsread2.news.atl.earthl ink.net...
Why don't you go and kiss John Marshall's A&$*##!!! You're an A&$*##!!!
who
makes no
contribution to this newsgroup.


Why don't you stop 'contributing' lies, name-calling and sheer utter
nonsense here?

Why don't you stop posting your false claim that you are a 'resource'?
Why don't you stop posting your idiotic claim 1100+ users requested your
'help'?

Arno R
Feb 27 '06 #34

P: n/a
"PC Datasheet" <no****@nospam.spam> wrote in message
news:h_*****************@newsread2.news.atl.earthl ink.net...
Why don't you and John Marshall go somewhere and kiss each other's A&$*##
!!! You're both A&$*##!!! who makes no contribution to this newsgroup.


The correct phrase is " who make no contributions to the newsgroup"

As usual you are wrong. Correcting erroneous information is helping the
newsgroup.

John... Visio MVP

PS: It's time to turn the page in the ""Trolls Guide to Flaming" and use a
different insult. Over the past few days, you have worn this one out.
Feb 27 '06 #35

P: n/a
DP
ok, i've gto an error at this line;

CurrentDb.Execute strSql, dbfailonerror strsql = "UPDATE tblFilm SET
tblFilm.Available = -1 " & _ WHERE FilmID = " & Me!FilmID.Value"

its all in red, however, i tried it with my update line, and it all works
correctly.

the whole form works exactly how it should. i've put some of my own code as
well, as validation. i've finalyl got it all working!! did u want to see wat
i've got??

thanx for being patient and helping me, i appreciate it. :)

dev

"RoyVidar" <ro*************@yahoo.no> wrote in message
news:mn***********************@yahoo.no...
DP wrote in message <L3*******************@newsfe4-win.ntli.net> :
sorry.
i've been trying them, but with no success. i've tried saving as u
mentioned. i saved after an update, after the record was changed, etc... i even tried saving the main form and subform. (when dirty, on load, etc...) i've tried the requery as well, but none of them work. it still comes out with that same error.

i have not tried it in single form mode, as i need it in continous.

i dont know wat else to do, as the error only comes after u try deleting a record straight away. If u delete it after a min, or after restarting, the error does not arise. however, thancx for all the help, i know u have not started arguing with other people. lol. (somone actually mentioed deleting a record through double clicking, which i dont want to do, as it does not feel correct, if u know wat i mean). thats bad HCI!!

i'll try the other bit of code u gave me now....

thanx

dev

"RoyVidar" <ro*************@yahoo.no> wrote in message
news:mn***********************@yahoo.no...
DP wrote in message <xn*******************@newsfe6-win.ntli.net> :
wat if i wanted it to display a message when there is NO content in the field?

thats wat u gave me;

if len(me!FilmID.value & vbNullString)>0 then
cos i have to use an update query to update another table, and i wanted to create a little validation. (and to stop an error popping up when there is no record active.)

(so i basically want it to display an error, when there is nothing to
delete).

thanx

dev

"RoyVidar" <ro*************@yahoo.no> wrote in message
news:mn***********************@yahoo.no...
> DP wrote in message <0g*******************@newsfe3-gui.ntli.net> :
>> ok, so i've tried wat u have gave me, and it still is not working. i
>> understand the problem but dont have a clue wat else to do. i think i've>> tried almost everything.
>>
>> this is wat i've got; it might look confusing cos i been messing around>> with it for a week now;
>>
>> Private Sub cmdDeleteRecord_Click()
>>
>> msg = "Are you sure you want to delete this film rental record?"
>> Style = vbYesNo + vbQuestion + vbDefaultButton2
>>
>> Response = MsgBox(msg, Style, "Film Rental Database")
>> If Response = vbYes Then
>> DeleteRecord
>>
>> Else
>> MsgBox "You cancelled the delete operation"
>>
>> End If
>>
>> End Sub
>>
>>
>> ================================================== ===========
>>
>> Private Sub DeleteRecord()
>>
>> DoCmd.SetWarnings False
>> 'DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available =True WHERE FilmID=">> & Me.FilmID
>> Update
>> DoCmd.SetWarnings False
>> DoCmd.RunCommand acCmdDeleteRecord
>>
>> DoCmd.SetWarnings True
>> End Sub
>>
>> ================================================== ===================>>
>> Private Sub Update()
>> DoCmd.SetWarnings False
>> If FilmID = Null Then
>>
>> MsgBox "Delete operation cannot be carried out"
>> 'End If
>> Else
>> 'DoCmd.SetWarnings False
>> DoCmd.RunSQL "UPDATE tblFilm SET tblFilm.Available =True WHERE FilmID=" &>> Me.FilmID
>> DoCmd.SetWarnings True
>> End If
>> End Sub
>> ================================================== ===========
>>
>> so, the code i've got works 'somtimes' as i get the error message
>> described before. (i've taken the double click event out, however that>> worked pretty well.)
>>
>> u know on the 'update' function, is it possible to check if the filmID is>> null (empty) and then the sql update runs. (cos if i do it now, i just>> get an error) i just wanted to create a little validation.
>>
>> thanx
>>
>> dev
>>
>>
>> "RoyVidar" <ro*************@yahoo.no> wrote in message
>> news:mn***********************@yahoo.no...
>>> Oh - and forgot, sorry - after deleting a record, you might want to do>>> a
>>>
>>> me.requery
>>>
>>> to freshen up the display.
>>>
>>> --
>>> Roy-Vidar
>
> I'm sorry, I can't see you've tried any of my suggestions from the code> you posted, so I don't know what to say, except to repeat
>
> Do try
> 1 - save the record explicitly before doing anything else
> 2 - run a requery of the form after the process is finished
>
> To test for contents in the control, you can for instance try
>
> if len(me!FilmID.value & vbNullString)>0 then
> ' only if there's contents in the control, continue with routine
>
>
> end if
>
> The next question I would ask, is whether this table/record is open,
> and
> contains unsaved changes in another form. If so, try explicitly saving> the record there too, either before opening this form, or before trying> to update/save from this form.
>
> --
> Roy-Vidar

Look at the Else clause of the If-Then-Else construct

If <some condition> Then
' perform some code (the updating stuff?)
Else
' perform some other code (perhaps a message box)
End If

It would be interesting to know whether the previous suggestions worked
with regards to the initial errors.

--
Roy-Vidar


I don't know. Similar stuff seems to work when I test this. Here's a
quick and dirty type up (meaning air code) ;-)

private sub cmdDelete_clikc()

dim strSql as string
dim intAnswer as integer

on error goto myerr

' save if there are unsaved changes
if me.dirty then me.dirty = false

' if you've got another form with this record open, containing
' unsaved changes ...
' if forms("frmNameOfForm").dirty then
' forms("frmNameOfForm").dirty = false
' end if

if len(me!FilmID.value & vbNullString)>0 then
intAnswer = msgbox("Wanna delete?", _
vbyesno+vbquestion, "Delete?")
if intAnswer = vbyes then
' I don't know the table name of this one
strsql = "delete from yourtable where filmid = " & _
me!FilmID.value
currentdb.execute strSql, dbfailonerror
strsql = "UPDATE tblFilm SET tblFilm.Available = -1 " & _
WHERE FilmID=" & Me!FilmID.Value
currentdb.execute strSql, dbfailonerror
end if
Else
msgbox "no film selected...", vbinformation
End If
me.requery

myexit:
exit sub
myerr:
msgbox err.number & " " & err.description
resume myexit
end sub

If this doesn't work, perhaps remove/comment the "on error" line, to
make it halt on the line causing the error. Perhaps posting the full
code again, as you are using it, and highlight the offending line.

Sometimes, the 3197 can also relate to corruption. So if nothing works,
then you could take a look here (search for 3197)
http://www.granite.ab.ca/access/corruption/symptoms.htm
(Tony Toews pages on corruption symptoms) and read a bit.

--
Roy-Vidar

Feb 27 '06 #36

P: n/a
DP wrote in message <Ud*******************@newsfe5-gui.ntli.net> :
ok, i've gto an error at this line;

CurrentDb.Execute strSql, dbfailonerror strsql = "UPDATE tblFilm SET
tblFilm.Available = -1 " & _ WHERE FilmID = " & Me!FilmID.Value"

its all in red, however, i tried it with my update line, and it all works
correctly.

the whole form works exactly how it should. i've put some of my own code as
well, as validation. i've finalyl got it all working!! did u want to see wat
i've got??

thanx for being patient and helping me, i appreciate it. :)

dev


Happy you got it working!

--
Roy-Vidar
Feb 27 '06 #37

P: n/a
Apparently kissing Arno's A&$*## isn't satisfying enough so you have to come
here and make an A&$*## of yourself. You're an A&$*## who makes no
contribution to this newsgroup. Go off somewhere else and get your
jollies!!!

"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:wd********************@magma.ca...
"PC Datasheet" <no****@nospam.spam> wrote in message
news:h_*****************@newsread2.news.atl.earthl ink.net...
Why don't you and John Marshall go somewhere and kiss each other's A&$*##
!!! You're both A&$*##!!! who makes no contribution to this newsgroup.


The correct phrase is " who make no contributions to the newsgroup"

As usual you are wrong. Correcting erroneous information is helping the
newsgroup.

John... Visio MVP

PS: It's time to turn the page in the ""Trolls Guide to Flaming" and use a
different insult. Over the past few days, you have worn this one out.

Feb 27 '06 #38

P: n/a
"DP" <DP@hotmail.com> wrote in
news:Ud*******************@newsfe5-gui.ntli.net:
ok, i've gto an error at this line;

CurrentDb.Execute strSql, dbfailonerror
strsql = "UPDATE tblFilm SET
tblFilm.Available = -1 " & _ WHERE FilmID = " & Me!FilmID.Value"

its all in red, however, i tried it with my update line, and it
all works correctly.


The problem is that you've got the quotes in the wrong place, and a
line continuation character without a line break. It should be:

strsql = "UPDATE tblFilm SET tblFilm.Available = -1 _
WHERE FilmID = " & Me!FilmID.Value

However, I don't like using line continuation characters in
circumstances like that, since it really messes with my head in
regard to how strings are concatenated. I would write it like this:

strsql = "UPDATE tblFilm SET tblFilm.Available = -1"
strsql = strsql & " WHERE FilmID = " & Me!FilmID.Value

That makes it more clear (to me, at least) what part is a literal
string and what part is being picked up from a control on your form.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Feb 27 '06 #39

P: n/a
"PC Datasheet" <no****@nospam.spam> wrote in message
news:qG*****************@newsread2.news.atl.earthl ink.net...
In fact with using double clicking the user knows explicitly which record
he is about to delete. In my opinion, a button on the main form to delete
a record in the subform is confusing.


I bet your apps are full of gaudy colours too.

Keith.
Feb 28 '06 #40

P: n/a
It must be time for a full moon; another A&%#$ has appeared!!! Why don't
you go kiss Arno's A&%#$.

"Keith Wilby" <he**@there.com> wrote in message
news:44********@glkas0286.greenlnk.net...
"PC Datasheet" <no****@nospam.spam> wrote in message
news:qG*****************@newsread2.news.atl.earthl ink.net...
In fact with using double clicking the user knows explicitly which record
he is about to delete. In my opinion, a button on the main form to delete
a record in the subform is confusing.


I bet your apps are full of gaudy colours too.

Keith.

Feb 28 '06 #41

P: n/a
"PC Datasheet" <no****@nospam.spam> wrote in message
news:%Z*****************@newsread1.news.atl.earthl ink.net...
It must be time for a full moon; another A&%#$ has appeared!!! Why don't
you go kiss Arno's A&%#$.


Two redundant exclamation marks and a full-stop where there should be a
question mark ... and a rather repetitive and predictable theme to boot.
Oh, and it's a new moon here in the UK today.
Feb 28 '06 #42

P: n/a
Since it's a new moon, why don't you go and moon Arno R. He loves seeing
that kind of stuff!!!
"Keith Wilby" <he**@there.com> wrote in message
news:44**********@glkas0286.greenlnk.net...
"PC Datasheet" <no****@nospam.spam> wrote in message
news:%Z*****************@newsread1.news.atl.earthl ink.net...
It must be time for a full moon; another A&%#$ has appeared!!! Why don't
you go kiss Arno's A&%#$.


Two redundant exclamation marks and a full-stop where there should be a
question mark ... and a rather repetitive and predictable theme to boot.
Oh, and it's a new moon here in the UK today.

Feb 28 '06 #43

P: n/a

"PC Datasheet" <no****@nospam.spam> schreef in bericht news:TD****************@newsread3.news.atl.earthli nk.net...
Since it's a new moon, why don't you go and moon Arno R. He loves seeing
that kind of stuff!!!


You don't have a clue of what I love Steve... but,
I would be surprized to see *only a few* of the 1125 users supporting you...
Maybe since it's full moon, a new (bad) Martin will show up ??

Please Steve, get a life and 'buzz off' with your nonsense sig-lines...

Arno R
Feb 28 '06 #44

P: n/a
Why don't you and Keith Wilby, Rob Oldfield and John Marshall go love each
other!!!
"Arno R" <ar***********@tiscali.nl> wrote in message
news:44**********************@text.nova.planet.nl. ..

"PC Datasheet" <no****@nospam.spam> schreef in bericht
news:TD****************@newsread3.news.atl.earthli nk.net...
Since it's a new moon, why don't you go and moon Arno R. He loves seeing
that kind of stuff!!!


You don't have a clue of what I love Steve... but,
I would be surprized to see *only a few* of the 1125 users supporting you...
Maybe since it's full moon, a new (bad) Martin will show up ??

Please Steve, get a life and 'buzz off' with your nonsense sig-lines...

Arno R
Feb 28 '06 #45

P: n/a
"PC Datasheet" <no****@nospam.spam> wrote in message
news:TD****************@newsread3.news.atl.earthli nk.net...
Since it's a new moon, why don't you go and moon Arno R. He loves seeing
that kind of stuff!!!


You're a very sad and insecure individual Steve and you have my pity.
Mar 1 '06 #46

P: n/a
Did you moon Arno R?
"Keith Wilby" <he**@there.com> wrote in message
news:44**********@glkas0286.greenlnk.net...
"PC Datasheet" <no****@nospam.spam> wrote in message
news:TD****************@newsread3.news.atl.earthli nk.net...
Since it's a new moon, why don't you go and moon Arno R. He loves seeing
that kind of stuff!!!


You're a very sad and insecure individual Steve and you have my pity.

Mar 1 '06 #47

This discussion thread is closed

Replies have been disabled for this discussion.