Complex NotInList Event

[Robert Neville]

I have been developing a complex combo box for several weeks. On the
surface, it sounds like a straight forward NotinList event, yet the
combo box is more intricate. The combo box resides on the address
sub-form (sfrmCompAddr), which is on a Company form (frmComp). The
address sub-form's record source links the company and address table
through a relationship table (trelCompAddr).

The combo box should allow the user to select a previous entered
address, thus update the link; it should allow the user to edit the
data rather than update the link; or it should add a new address
record and update the new reference link to the new record. The
complexity rises from several objective. First, I do not open a
separate Edit form, yet one confirmation dialog is fine. Second, I
rather avoid adding toggle buttons and check boxes for the form to
carry out the desired action (this form should look and interact like
a traditional form). Finally, I rather avoid multiple prompts for the
user to press OK/Cancel.

Currently, the NotinLIst code asks "Do you want to add the New Data?"
- Yes or No If you select Yes, the code adds the new data and updates
the link in trelCompAddr. If you select No, then the code prompt the
user again with a message that says "Item not in List and will not be
added" - OK. Ideally, the Msgbox would prompt the user asking whether
he/she wants to ADD or EDIT data; then do the action and shut up. I
primarily use my form to fix inconsistencies in the data by picking
previously entered data and updating the link; basing the edit from
another previously entered address (like copy and paste); or adding a
need record and establishing a link.

The preliminary code lies below. This code only adds a new record and
updates the link; or updates the link. It does not edit the data as
desired.

******************Code Starts**************************
Private Sub cboAddr1_Change()

Dim strResponse As String
Dim strSQL As String
Dim rstAddr1 As DAO.Recordset
Dim lngAddrID As Long

If cboAddr1.Column(1) = "(add new)" Then
strResponse = InputBox("Enter new Address 1", "Create new
address", "")
If strResponse <> "" Then
cboAddr1.Undo
strSQL = "INSERT INTO tblAddr (Addr1) "
strSQL = strSQL & "SELECT '" & strResponse & "' AS Addr1 "
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "SELECT Max(tblAddr.AddrID) AS MaxOfAddrID FROM
tblAddr;"
Set rstAddr1 = CurrentDb.OpenRecordset(strSQL)
lngAddrID = rstAddr1!MaxOfAddrID
rstAddr1.Close
Set rstAddr1 = Nothing

cboAddr1.Requery
cboAddr1 = lngAddrID
End If
End If

End Sub
******************Code End**************************

******************Code Starts************************
Private Sub cboAddr1_Enter()

If Me.Dirty = True Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
End If

End Sub
******************Code End**************************

******************Code Starts************************
Private Sub cboAddr1_NotInList(NewData As String, Response As Integer)

If MsgBox("Do you wish to create a new address """ & NewData &
"""", vbYesNo) = vbYes Then
Dim strSQL As String
Dim rstAddr1 As DAO.Recordset
Dim lngAddrID As Long

Response = 0
cboAddr1.Undo

strSQL = "INSERT INTO tblAddr (Addr1) "
strSQL = strSQL & "SELECT '" & NewData & "' AS Addr1 "
CurrentDb.Execute strSQL, dbFailOnError

strSQL = "SELECT Max(tblAddr.AddrID) AS MaxOfAddrID FROM
tblAddr;"
Set rstAddr1 = CurrentDb.OpenRecordset(strSQL)
lngAddrID = rstAddr1!MaxOfAddrID
rstAddr1.Close
Set rstAddr1 = Nothing

cboAddr1.Requery
cboAddr1 = lngAddrID

End If

End Sub
******************Code End**************************

 

[Robert Neville]

I have tried several approaches and keep stumbling along the way. I
have tried using the ListIndex property. The logic behind the
ListIndex approach was to have the combo box assume whether the user
was updating the link to another address, adding new data; or editing
current record. The logic would accomplish this objective through
testing the listindex property; if the user enters text in the combo
box then the listindex should be -1 since no item was selected. But
the ListIndex property does not return -1 rather it returns the
ListIndex of the current record (I think). Here's an example of my
ListIndex approach. The approach has other flaws, yet I posted it to
generate comments in this thread. Maybe, the discussion will lead to
a distinct approach rather than rehash the standard NotinList
approach. I noticed while reading about the ListIndex that Access has
the ItemSelected and Selected property that may be worth exploring (or
not). Let me know any thoughts or ideas you may have, because I need
to resolve this situation with an intuitive solution.


******************Code Starts************************
If Me!cboAddrName.ListIndex = -1 Then
' Editing Mode
cboAddr1.Column(0) = lngAddrID
strSQL = "UPDATE tblAddr SET tblAddr.AddrName = " &
Me!cboAddrName
strSQL = strSQL & " WHERE tblAddr.AddrID = " & lngAddrID & ";"
CurrentDb.Execute strSQL, dbFailOnError

ElseIf Me!cboAddrName.ListIndex = 0 Then
' Add new data Mode
cboAddrName.Undo
strSQL = "INSERT INTO tblAddr (AddrName) "
strSQL = strSQL & "SELECT '" & strResponse & "' AS
AddrName "
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "SELECT Max(tblAddr.AddrID) AS MaxOfAddrID FROM
tblAddr;"
Set rstAddrName = CurrentDb.OpenRecordset(strSQL)
lngAddrID = rstAddrName!MaxOfAddrID
rstAddrName.Close
Set rstAddrName = Nothing

cboAddrName.Requery
cboAddrName = lngAddrID


ElseIf Me!cboAddrName.ListIndex > 0 Then
' Change Update Link
cboAddrName = lngAddrID


End If

******************Code End**************************

 

[Albert D. Kallal]

If your combo box is bound, then why not use:


If MsgBox("Do you wish to create a new address """ & NewData &
"""", vbYesNo) = vbYes Then

Dim strSQL As String
strSQL = "INSERT INTO tblAddr (Addr1) Values ('" & NewData & "')"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
End If


You don't need all that code go grab the id, and you don't need to re-query
the combo. Further, you don't need to use a append query with a "select" as
you had. You can and should use a standard sql insert as above. No need to
"fake" a select and use a "append" query.

Setting the Response correctly as above will do the requery. If you want to
suppress the not in list message, you could simply change the above to:

If MsgBox("Do you wish to create a new address """ & NewData &
"""", vbYesNo) = vbYes Then

Dim strSQL As String
strSQL = "INSERT INTO tblAddr (Addr1) Values ('" & NewData & "')"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
else
' Edit data?...just let the user go back to what they were doing....
' you might want to put in a undo here...but I don't see the need

' you can even give a custom error message
' msgbox "our custom not in list message"
Response = acDataErrContinue

End If

You could also make your own small dialog box, and use it in place of the
MsgBox. You could thus at the above point prompt for "Add new, or Edit?".

I would also consider using the after update event, and not the change event
for your other code you showed. That code is firing each time a character is
typed, and you really only need the code to fire *after* the user selects
something in the combo (hence, after update event). However, if your
"change" event works ok...then you can probably leave it.