moving focus from a combo box

[Paul Cooke]

Hi all another problem which seem to be taking hours for me to work out !!

I have a combo box Called 'Name' and another called 'details'. A value must be entered in one of them but not always both of them - which is where my problem is...

If I enter a value in cboName and it is not in the list, I have a Msgbox set up...

MsgResponse = MsgBox("There is no record for the Name" & vbCrLf & vbCrLf & Chr(34) & NewData & Chr(34) & _
    vbCrLf & vbCrLf & "Please click ok to continue.", vbInformation + vbOKOnly, "No Record found")

The user clicks 'ok'.

At the moment when ok is clicked the usual default error message comes up "the text you entered isn't an item in the list"...

I have tried all methods to stop this without success.

What I need to happen is for the new entry that was typed to be undone and for the focus to be set to the next control cboDetails. What I have tried is

If MsgResponse = vbOK Then
     If Me.Dirty Then
     Me.cboName.Undo
     End If
     me.cboDetails.SetFocus

This is just one example of loads I have tried!

As a footnote there is also code in the lostfocus event of cboName as below but this does not seem to be causing me any problems and works fine I'm just posting it in case it may be causing a conflict,

If IsNull(Me.cboName) = True Then
    Me.cboDetails.Enabled = True
    Me.cboDetails.SetFocus

I would be really grateful for your expert advice and help

Many thanks

 

[DCrake]

Have you looked into the On Not In List event?

 

[Paul Cooke]

Sorry David I should of said (doh!) this event is in the Not In List !

sorry !

 

[DCrake]

Your response is vbOkOnly you need a vbYesNo to determine if the user wants to add the new item to the list or not?

click on the property of the control for On Not In List then hit F1 for an example of the code.

 

[Paul Cooke]

thanks David I will change it now and see what happens but in hindsight that makes sense !

thanks again - I will post back

 

[Paul Cooke]

Can I just confirm this please as it does not seem to be working !....

MsgResponse = MsgBox("There is no record for the Name" & vbCrLf & vbCrLf & Chr(34) & NewData & Chr(34) & _
    vbCrLf & vbCrLf & "Please click Yes to continue.", vbQuestion + vbYesNo, "No Record Found")
    If MsgResponse = vbYes Then
     If Me.Dirty Then
     Me.cboName.Undo
     End If
    Me.cboDetails.SetFocus
End If

I am still getting the default item not in list error message? Should me.dirty be = true?

any ideas?

many thanks

 

[DCrake]

Take a look at this Access example (F1)

Private Sub Colors_NotInList(NewData As String, _
        Response As Integer)
    Dim ctl As Control
    
    ' Return Control object that points to combo box.
    Set ctl = Me!Colors
    ' Prompt user to verify they wish to add new value.
    If MsgBox("Value is not in list. Add it?", _
         vbOKCancel) = vbOK Then
        ' Set Response argument to indicate that data
        ' is being added.
        Response = acDataErrAdded
        ' Add string in NewData argument to row source.
        ctl.RowSource = ctl.RowSource & ";" & NewData
    Else
    ' If user chooses Cancel, suppress error message
    ' and undo changes.
        Response = acDataErrContinue
        ctl.Undo
    End If
End Sub

 

[Paul Cooke]

ok thats has helped a lot as the dreaded default message has now stopped but (there always is a but!) when I select 'Cancel' it goes back to the same cbo - I assuming because it has acDataErrcontinue.

What I need it to do is go to the next control - cboDetails. Is there a way to add something at the bottom to the code to 'Cancel' out what was typed in the box - like a re-set on the control ??

Sorry I grasping at straws here I know !!

Many thanks

 

[DCrake]

Can you simply move focus to the next control using the .Setfocus command.

 

[Paul Cooke]

At last !! not sure if this is 'correct protocol' in VB but I added a line disabling the cboName after ctl.undo ( with the set focus cmd as well) and it works

Many thanks David for you assistance much appriciated !