Combobox Events onchange + Notinlist

[Mohsin Malik]

Hi there,

I have a combo box and I have 02 events to fire in sequence but it won't worked any idea.

First event is Onchange event which I am using to fill out the other combo box values as and when I press key the second combo box will show the values pressed and I want to display the pressed key values in realtime and it works great.

If this combo box value is not in the list then I would like to prompt for the message box that the value is not in the list but it is not working, I am not sure why but after adding the event_change on this combo box it is surpassing the message of not in list and I have properly set the Limit to list property = Yes and AllowValueList=No?

Is there any way that on lost focus I could run an event NotInlist, Can I call combobox_NotInlist on lost focus or something?

Thank you
Mohsin

 

[EternalMyrtle]

Do you realize that you marked your thread "solved"?

Anyway, yes you can call whatever subroutine you need (in the same class or in a public module) like this:

Call Combobo_NotInList

 

[Mohsin Malik]

Do you realize that you marked your thread "solved"?

Anyway, yes you can call whatever subroutine you need (in the same class or in a public module) like this:

Call Combobo_NotInList

Hi EternalMyrtle,

Thank you for the reply and I am trying to call the combobox event on lostfocus and it says

Argument Not optional.....

Private Sub cmbSong_Lostfocus()
Call cmbSong_NotInList
End Sub

 

[EternalMyrtle]

Can you post all your code for both subroutines (it is best to use code tags, by the way)? Maybe that will help.

 

[Mohsin Malik]

Can you post all your code for both subroutines (it is best to use code tags, by the way)? Maybe that will help.

This is the code for cmbSong combobox change event!

Private Sub cmbSong_Change()
    Dim vSearchString As String
    vSearchString = cmbSong.Text
    Song.Value = vSearchString
    cmbSong.SetFocus
    cmbSong.SelStart = Nz(Len(cmbSong), 0)
    cmbSong.SelLength = Nz(Len(cmbSong), 0)
    Exit Sub
End Sub

This is the CmbSong Not InList code which won't fired with the combination of onchange event and I would like to call this event if the value is not in the combobox selection ( I have already set the Limitto list property=Yes, Allow Value Edit = False)

Public Sub cmbSong_NotInList(NewData As String, Response As Integer)

'Add New Song to the tblSongLists if the Song is not in the list through frmSongAdd Form
Response = acDataErrContinue
If MsgBox("The Song  " & NewData & " is not in Database. Add it?", vbYesNo, _
              "Add Song into the Database!") = vbYes Then
              
Dim Songtmp As String
Dim Artisttmp As String

Artisttmp = Nz(Me.Artist)
Songtmp = Nz(Me.Song)

TempVars.Add "SongName", NewData
TempVars.Add "ArtistName", Artisttmp
TempVars.Add "CD", 2
'
'=[TempVars]![CD]
DoCmd.OpenForm "frmSongAdd", acNormal, , , acFormAdd, acDialog

Response = acDataErrAdded

End If
End Sub

Looking forward to hear from you

Thank you
Mohsin

 

[EternalMyrtle]

The problem is that you have an argument in your subroutine for NotInList (NewData As Strong, Response As Integer) so you have pass the arguments to the subroutine

To call it you would have to write:

Call cmbSong_NotInList (Arg1, Arg2)

but you don't have any arguments to pass, right?

I think you can probably remove the arguments from the subroutine declaration and substitute blank parentheses and still get the same results.

 

[EternalMyrtle]

But I want to make a disclaimer that my VBA skills are not stellar so take that advice for what its worth

 

[Mohsin Malik]

Hi EternalMyrtle,

Thank you for the reply, Is there any way if I could first check if the value is not in the combo box list, if cmbSong.notinlist like that?

Thank you
Mohsin

 

[EternalMyrtle]

The NotInList event should handle that. If the control loses the focus and the selected value is not in the list, it should automatically call the NotInList event.

If you really want to do it by writing code I guess you could check if the selected value exists in the combo box row source but I don't have time to figure out the exact code for you and cannot think of a simple way off the top of my head. Maybe someone else can help with that.

Did my previous post about the arguments make sense?? If you do not need to pass arguments to the subroutine, you should not declare any parameters. it should be like this:

Public Sub cmbSong_NotInList ()

 

[Mohsin Malik]

The NotInList event should handle that. If the control loses the focus and the selected value is not in the list, it should automatically call the NotInList event.

If you really want to do it by writing code I guess you could check if the selected value exists in the combo box row source but I don't have time to figure out the exact code for you and cannot think of a simple way off the top of my head. Maybe someone else can help with that.

Did my previous post about the arguments make sense?? If you do not need to pass arguments to the subroutine, you should not declare any parameters. it should be like this:

Public Sub cmbSong_NotInList ()

Thank you for your time and suggestion but without argument to notinlist event and calling it was not worked yet.