NotInList popup form closing issue

[Fred Boer]

Greetings!

I am working using the NotInList event of a combobox to pop up a dialogue
form. I use the popup form to add records to the table upon which the
combobox is based. On the popup form I have two command buttons:
"Undo/Close" and "Insert/Close". Code is reproduced below. This has been
working for me, however, I have discovered a problem: if the user has
actually entered all the appropriate data and then chooses neither of these
command buttons, but instead clicks the control box "X", (and no, I never
thought to try this myself! <g>), the data is inserted into the table. This
isn't what I want, since, to me at least, "X"ing out of the window would
logically mean "discard the data". Is there any way to deal with this
besides disabling the control box? I'd like to avoid changing the standard
windows UI...

Thanks!
Fred Boer

P.S. (For you ADH owners...) I am looking in the Access Developers Handbook
(2002), and I see a section on "Creating Pop-up Forms" in Chapter 8. Is this
a better approach? I must confess that I find the ADH a *wee* bit cryptic;
but I'm willing to give it a go if it is a better solution...

Code to open popup form:

Dim mbrResponse As VbMsgBoxResult
Dim strMsg As String
Dim db As Database, sSQL As String

strMsg = NewData & " isn't an existing author. " & "Add a new author?"
mbrResponse = MsgBox(strMsg, vbYesNo + vbQuestion, "Invalid Author")

Select Case mbrResponse

Case vbYes
DoCmd.OpenForm "Frm_InsertAuthor", _
DataMode:=acFormAdd, _
WindowMode:=acDialog

If ISLOADED("Frm_InsertAuthor") Then
Response = acDataErrAdded
DoCmd.Close acForm, "Frm_InsertAuthor"

Else
Response = acDataErrContinue
End If

Me.cboAuthor.Requery
Case vbNo
Response = acDataErrContinue
End Select
End Sub




Undo/Close:

Private Sub cmdUndo_Click()
Me.Undo
DoCmd.Close
End Sub


Insert/Close:


Dim db As Database, sSQL As String
Dim intNewAuthorID As Integer

If Me.Dirty = True Then Me.Dirty = False
intNewAuthorID = Me.Author_ID
sSQL = "INSERT INTO Tbl_BookAuthor (Book_ID, Author_ID) SELECT " &
Forms!Frm_LibraryDataEdit.txtBookID & " AS Expr1," & intNewAuthorID & " AS
Expr2"
Set db = CurrentDb()
db.Execute sSQL, dbFailOnError
If db.RecordsAffected <> 1 Then
MsgBox "This is not a valid Book ID. Please try again.", _
vbOKOnly + vbInformation, "W. Ross Macdonald School"
Me.Undo
End If
Set db = Nothing
Forms!Frm_LibraryDataEdit!Frm_EditAuthorSubform.Form.Requery
Forms!Frm_LibraryDataEdit!Frm_EditAuthorSubform.Form!cboAuthor = Null
DoCmd.Close
End Sub

 

[Jeff Conrad]

Hi Fred,

I have a possible solution, but I've been reluctant to offer it because I
think it will only work if you do NOT use this form any other time to enter
records.

Will that condition be OK?

I created a very simple table and form to test. The form is bound to the
table which it sounds like your case as well.
I declared a Boolean (I think that's what it's called) when the form is
open. By changing the value at appropriate times in the code this simple
form does exactly what you desire. You'll have to test using your own code
and form.

This is all the code behind my form.
Just modify to your needs:

Dim CatchClose As Boolean
' Up in Declarations area

Private Sub cmdSave_Click()
' Save stuff here
CatchClose = True
DoCmd.Close
End Sub

Private Sub cmdUndo_Click()
Me.Undo
DoCmd.Close
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If CatchClose = True Then
' Do nothing
ElseIf CatchClose = False Then
Me.Form.Undo
End If
End Sub

Hope that helps a little,
Jeff Conrad
Bend, Oregon

 

[Fred Boer]

Hi Jeff! I've taken a quick look at this and it seems promising! However, I
have discovered (yet another!) flaw in my table design, which has put this
issue on hold. I've had to redo my table structure which, of course, means
that my forms all have to be redone!! Man, when they say get your tables and
relationships correct right from the beginning, they are soooo right!

So, I will have to leave this for a few days. I will try it and come back
with a report then...

Thanks a lot! Cheers!

Fred