Adding to lookup field on fly in main form

[MichaelWaimauku]

I've got a lookup field on my main data entry form "Supplier Issue Detail" where a type of 'cause' for the issue can be selected. This is restricted to the records in the 'Cause' table. How can I add to the 'Cause' table on the fly if a particular cause doesn't exist in the drop down list?

Once added, reselecting from the dropdown field, the new record would be available to be selected.

 

[Isskint]

Have a look at the Not In List event/property. This allows you to determine what to do when a value is entered in a combobox that is not in the list, including adding a new value.

 

[John Big Booty]

The sample here demonstrates a method of managing combo box items.

 

[MichaelWaimauku]

Hey thanks for your input. John, thanks for the example, that's sorted it. Works a treat!!!

 

[MichaelWaimauku]

Just some clarification, if the user enters in a word that's not on the list, the following error occurs: The expression On Not in List you entered as the even property setting produced the following error: Object or class does not support the set of events.

How can I trap this so the user is not thrown? Not sure why this is happening. Code is as given:

Private Sub Cause_NotInList(NewData As String, Response As Integer)
MsgBox "Double-click this field to add an entry to the list."
Response = acDataErrContinue
End Sub

Private Sub Cause_DblClick(Cancel As Integer)
Dim lngcombo2 As Long
Dim strOpenArgs As String

If DCount("Cause", "Cause", "Cause = '" & Me.Cause.Text & "'") = 0 Then strOpenArgs = Me.Cause.Text

If IsNull(Me.Cause) Then
Me.Cause.Text = ""
Else
lngcause = Me![Cause]
Me.Cause = Null
End If

DoCmd.OpenForm "Cause", , , , , acDialog, "New#" & strOpenArgs
Me.Cause.Requery
If lngcause2 <> 0 Then
Me.Cause = lngcause2
ElseIf lngInfoXchg <> 0 Then
Me.Cause = lngInfoXchg
End If
End Sub

 

[John Big Booty]

Check he Not In List event, which should deal with that;

MsgBox "Double-click this field to add an entry to the list."
Response = acDataErrContinue

 

[MichaelWaimauku]

Yeah, that's the thing, I have checked that 'On Not on List' with the following, as above
Private Sub Cause_NotInList(NewData As String, Response As Integer)
MsgBox "Double-click this field to add an entry to the list."
Response = acDataErrContinue
End Sub

But still get that error...

 

[John Big Booty]

Are you able to post a copy of your DB?

 

[MichaelWaimauku]

I think I may have discovered what the prob is. When I open the db, it has the security warning below the ribbon - vba is disabled. Once enabled this works. Is there a way to have the db setup so any user that opens it will not have that security warning. As is, won't be user friendly.

 

[John Big Booty]

You will need to place the DB in a Trusted Location.