Allow non-list item

[alsoascientist]

Hi all,

I may be being completeley blind here, however I have been looking at this for two days now and can't for the life of me figure out what I am missing!

The purpose of the code below (you may think its a bit pointless!) is to allow the user to input a value that is not in the dropdown list for a combobox without using a seperate query holding the list control source information. The reason it has been set up this way is so that the combo box control source is essentially the same tble that the information goes into, requerying each time data is added so that the list is more used as a prompt to keep the same data rather than completely restricting this. Once a new value is added, because it is in the table and the source has been refreshed, this now shows up in the list, however (and this is why I am not using a seperate query for the source) if this is changed again (for example if there was an error or this value is no longer applicable) this will no longer show up as an option.The message box should pop up to warn the user that this is a new value and ask to confirm if they want to use it.

Anyway I digress - I am getting the error 'Arguement not optional' when I am calling the sub and I can't for the life of me figure out what it is looking for!

Any ideas?

'These give the user the option to add non-list items into the database.
'----------------------------------------------------------------------------------------------
Private Sub NewListItem(NewData As String, Response As Integer)
    'Recognises the item and asks if it should be kept
        intAnswer = MsgBox(Chr(34) & NewData & Chr(34) & " is not currently listed." & vbCrLf & _
        "Would you like to add it to the list now?", vbQuestion + vbYesNo, "Unrecognised Input")
    'For Yes...
        If intAnswer = vbYes Then
    'Turns of the messages, allows the input and updtes the value
        DoCmd.SetWarnings False
        Me.ActiveControl.LimitToList = False
        Me.ActiveControl.Value = NewData
    'Turns the messages back on and confirms with a message
        DoCmd.SetWarnings True
        MsgBox "List item added.", vbInformation, "List Updated"
    'Saves the record and updates the queries
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
        Me.ActiveControl.Requery
        Response = acDataErrContinue
    'Sets the properties back to limit to list
        Me.ActiveControl.LimitToList = True
    'For No...
        Else
    'Asks the user to try again
        MsgBox "Please choose a value from the list.", vbInformation, "Unrecognised Input"
        Response = acDataErrContinue
        End If
End Sub
Private Sub SFN01_NotInList(NewData As String, Response As Integer)
'Calls the sub
    Call NewListItem
End Sub

 

[bob fitz]

I have not read and tried to fully understand all that you are trying to do but the call to the sub routine needs to pass a string value. Try this:

Call NewListItem(NewData)

 

[alsoascientist]

Thanks Bob - works a charm if I change
Private Sub NewListItem(
NewData As String, Response As Integer
)
to
Private Sub NewListItem(ByRef NewData)

one other thing on this though - I still get the mesage 'The text you entered isn't an item in the list' before the sub ends, but the new value is now appearing in the list. Any ides why this is? I've tried moving aroung the requery and the set warnings but no change.

 

[alsoascientist]

Figured it out...

Just needed to pass the response too (I kinda get why but to be honest not the reasoning behind it)

Private Sub SFN01_NotInList(NewData As String, Response As Integer)
Call NewListItem(NewData, Response)

Private Sub NewListItem(ByRef NewData, Response)