Form closing cancelling

  • Thread starter Thread starter Luke Bellamy
  • Start date Start date
L

Luke Bellamy

Hi,
When a user closes a particular form (either by my "close" botton or
the 'x' in the top right corner) I have a question asking them are they sure
these settings are correct. If they choose No I wish to stop closing the
form.
I've found in my vb code if I use:

if varConfirm = vbNo then
Cancel = true
exit sub
end if

It does not seem to work and the form still closes when selecting No (for
the record
it is really going into this "if... then" block). But the funny thing is I
have another form
that does a similar thing it seems to work for. Anyone have any ideas on why
it wouldn't work under certain circumstances or know of a better way?

Thankyou
 
Luke,

Apart from not understanding the purpose of the Exit Sub line in your
code, I can think of no reason why it wouldn't work as expected. Maybe
someone might spot the problem if you post the entire code you have on
the Unload event.
 
Thanks Steve... this is from memory.
Your right though, I can't think of anything either why it wont work which
I why I asked here.

Private Sub Form_Unload(Cancel as Integer)
On Error Goto errHandler

Dim rsCheckPercentages as DAO.Recordset
set rsCheckPercentages = CurrentDB.OpenRecordset("SELECT Count(1) AS
Counter FROM blah blah etc")

' I have confirmed the Counter is > 0 and even replaced this with "IF
TRUE THEN"
if rsCheckPercentages![Counter] > 0 then
Cancel = true
' Exit Sub '' Had exit sub in here as a test as the first
thing after setting cancel may have been to stop processing in this Sub
'' As a test I didn't bother .closing the RS
end if
rsCheckPercentages.close
set rsCheckPercentages = nothing

set frmResize = nothing ' ADHResize object

Exit Sub
errHandler:
HandleError(Err) ' my error handling routine
End Sub
 
Have you checked the Unload Event row in the Properties Window of the Form
and made sure it has "[Event Procedure]"?

I am not sure about Count(1)? Do you have a Field named "1"?
 
Yep I'm sure the "[Event Procedure]" is set. I also have stepped through
the code as it executes to ensure I don't have any logic wrong. So I know
the event is fired. You wouldn't be able to enlighten me on how the Cancel =
True
registers to tell Access to leave the form open?

Count(1) will count the first field in the tabledefs and since I always have
the first field as the internal identifier, which is indexed, it's pretty
fast.

I'll have to keep playing with it tonight. Thanks Van.
--
Luke Bellamy
Newcastle, Australia


Van T. Dinh said:
Have you checked the Unload Event row in the Properties Window of the Form
and made sure it has "[Event Procedure]"?

I am not sure about Count(1)? Do you have a Field named "1"?

--
HTH
Van T. Dinh
MVP (Access)



Luke Bellamy said:
Thanks Steve... this is from memory.
Your right though, I can't think of anything either why it wont work which
I why I asked here.

Private Sub Form_Unload(Cancel as Integer)
On Error Goto errHandler

Dim rsCheckPercentages as DAO.Recordset
set rsCheckPercentages = CurrentDB.OpenRecordset("SELECT Count(1) AS
Counter FROM blah blah etc")

' I have confirmed the Counter is > 0 and even replaced this with "IF
TRUE THEN"
if rsCheckPercentages![Counter] > 0 then
Cancel = true
' Exit Sub '' Had exit sub in here as a test as the first
thing after setting cancel may have been to stop processing in this Sub
'' As a test I didn't bother .closing
Click to expand...
the
Click to expand...
 
Luke said:
Count(1) will count the first field in the tabledefs and since I always have
the first field as the internal identifier, which is indexed, it's pretty
fast.
Click to expand...


Side issue.

I always thought Count(1) counted the literal value 1 for
every record in the recordset. Since 1 is never Null, it
will count the number of records. Count(*) is supposed to
be the fastest way to count all the records regardless of
any values in a field.
 
1. Have you tried putting a break point on the statement Cancel = True to
see if this statement has ever been reached?

In fact, try putting the break point on both

Private Sub Form_Unload(Cancel as Integer)

and

Cancel = True

and see if Access tries to execute the Form_Unload and if it does, step
through the code and see if Cancel = True is reached.

2. If the statement Cancel = True is executed, Access cancel the unloading
action, i.e. leaving the Form open.

3. I wouldn't use the alias "Counter". "COUNTER" is a reserved word, i.e.
has special meaning in JET SQL.

If you want to use "Counter", make sure you enclose it in square brackets.

4. Try using Count(*) rather than Count(1). I don't think it is the same
as Count([FirstFieldName]) which only counts the non-Null value.

My tests confirmed what Marshall wrote. I tested with a Table that has 5
Fields and JET accepts Count(10) so the 10 definitely doesn't refer to the
Field index.
 
Marshall - what you are saying does sound correct when you
think about it logically. I remember the lecturer telling me about
it at Uni (the way I stated it worked) but could not get my head around
the overhead involved in working out the field (i.e index 1) details.
I was incorrectly informed and didn't bother to test or question it,
just accepted it.
 
Back
Top