Cascading Combo Boxes on Subform, NotInList

[TXChem]

I have looked through the method Pat Hartman has used by putting a textbox
over the combo boxes in the subform. However if I do this, is there a way to
add records to the combo box, for example using the NotInList event?

Let me know

Thanks in advance

John

 

[Pat Hartman\(MVP\)]

If the LimitToList property is set to Yes, you should be able to add code to
the NotInList event to open a form or to just add the new item. I don't
normally allow users to add items to lists this way. It is rare that
unanticipated new values come up in the normal course of business. Usually,
there is some planning and management approval first.

 

[strive4peace]

Hi John,

Pat has a good point about possibly needing to validate before new
entries are made... never-the-less, here is some NotInList code that
does what you want -- you may want to add a msgbox to prompt the user if
this is what they really want to do...

on your form, here is an example with the properties you need to set for
a combobox that stores the ID and displays the text

combobox control

Name --> SomeID
ControlSource --> SomeID
RowSource -->
SELECT
SomeID,
SomeName
FROM Tablename
ORDER BY SomeName

BoundColumn --> 1
ColumnCount --> 2

columnWidths --> 0;2
(etc for however many columns you have
-- the ID column will be hidden since its width is zero)

ListWidth --> 2
(should add up to the sum of the column widths)

LinkID will be stored in the form RecordSource while showing you
information from another table...

for the NotInList event of the combobox, here is code behind the form:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Code:


Private Sub ControlName_NotInList( _
NewData As String, _
Response As Integer)

'assumption:
'the combobox controlname is
'RecordID_controlname
'and its first column (hidden)
'is the Autonumber record ID for the source table

Dim s As String
Dim mRecordID As Long
Dim mText As String

If Len(Trim(NewData)) = 0 Then
me.activecontrol = null
Response = acDataErrContinue
Exit Sub
end if

'if NewData needs to be parsed,
'seperate it and
'modify the SQL accordingly

'~~~~~~~~~~~~~~~~~~~~~~~~
'Choose ONE of these code blocks

'--------------------------------------------------------

'if you want to convert to ProperCase
mText = StrConv(NewData, vbProperCase)

s = "INSERT INTO Tablename(SomeName) " _
& " SELECT '" & mText & "';"

'--------------------------------------------------------

s = "INSERT INTO Tablename(SomeName) " _
& " SELECT '" & NewData & "';"

'--------------------------------------------------------
'~~~~~~~~~~~~~~~~~~~~~~~~


'comment next line after this works correctly
Debug.Print s

CurrentDb.Execute s

CurrentDb.TableDefs.Refresh
DoEvents

mRecordID = Nz(DMax("SomeID", "Tablename"))

If mRecordID > 0 Then
Response = acDataErrAdded

'assuming the first column of the listbox
'is the RecordID, SomeID and is a Long Integer

Me.SomeID_controlname = mRecordID
Else
Response = acDataErrContinue
End If

End Sub

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*