Combo Box to both SEARCH for records and ADD NEW values to list

[robtyketto]

Greetings,

Using the button wizzard I am able to create a combo box that searchs for records and display data. However I cannot add NEW values to the list, I cannot change the properties of the box to allow this.

Is it possible to use a SINGLE combo box to allow a search facility and an ADD record?

Perhaps some VBA is required??
Hope this makes sense.

Cheers
Rob

 

[Uncle Gizmo]

you need to search the forum on the search term "not in list event"

I have done a quick search myself and here are the results

 

[robtyketto]

Thanks for the reply.

Well I read about another method and attempted to implement that.

Ive captured a screenshot of my form.

The problem is the USER must be able to search records by NAME and NOT ID. The combo box however is bound to the Module ID as the TWO subforms are based on the module ID value.

Ive attempted to allow the user to select an "ADD NEW MODULE" using a UNION in the query for the combo box.

SELECT tblModule.ModuleId,tblModule.ModuleName, tblModule.Archive 
FROM tblModule 
UNION 
SELECT -9999, "Add New Module", TRUE 
FROM tblModule
ORDER BY ModuleId;

I've put code behind the combo box AFTER UPDATE event

Private Sub cboModulename_AfterUpdate()
    
    Dim rs As DAO.Recordset
    
    If Not IsNull(Me.cboModuleName) Then
        'Save before move.
        If Me.Dirty Then
            Me.Dirty = False
        End If
        'Search in the clone set.
        Set rs = Me.RecordsetClone
        
        rs.FindFirst "[ModuleId] = """ & Me.cboModuleName & """"
        
        If rs.NoMatch Then
             DoCmd.GoToRecord , , acNewRec
        Else
            'Display the found record in the form.
            Me.Bookmark = rs.Bookmark
        End If
        
        Set rs = Nothing
    End If

End Sub

The problem being if I select an existing record then select "Add new module" option it save the value of the module id "-9999" into an existing record.

Difficult to explain.
Hope that makes sense

If there is a better/simpler way to allow a find and an add then Im all ears!!

Cheers
Rob

 

[Uncle Gizmo]

I'm not sure I've got the right end of the stick, but the fact that your combo box is bound set off warning bells in me.

I did a set of videos on searching for records using a combo box here: I wondered if this might throw some light on your problem.