Bound Combo Box Question for the Access GURUS!
Stede Troisi
something not in the list a dialog box will appear asking the user to select
from the list. I want what the user selects to be added to the bound combo
box table and here is the tough part: change the value the user originally
typed into the combo box to the new value.
I am able to add a new record to the bound table of the combo box but when I
try to change the value to what is now in the list the limit_to_list
triggers again and I get stuck in this endless cycle. How can I change the
value of newdata to point to something else?
Stede
Sandra Daigle
Response = acDataErrAdded
Sandra Daigle
Stede Troisi <str...@morantug.com> wrote in message
news:#$gS#gyQ$GA....@cppssbbsa02.microsoft.com...
Stede Troisi
Sandra Daigle <smda...@mindspring.com> wrote in message
news:eBG5X1yQ$GA....@cppssbbsa02.microsoft.com...
Robert Nahas
Private Sub cboStock_ID_NotInList(NewData As String, Response As Integer)
'undo field
cboStock_ID.Undo 'this is probably the only part you forgot
DoCmd.RunCommand acCmdSaveRecord
Response = MsgBox("Would you like to add '" & NewData & "' ...
& " ...more text?", vbYesNo)
If Response = vbYes Then
DoCmd.OpenForm "frmName", acNormal, , , acFormAdd,
'do stuff here
...
...
...
cboStock_ID.Requery
cboStock_ID.Text = NewData
Else
Me.Stock_ID = ""
End If
End Sub
Andy Baron
currently in use -- it doesn't correctly handle the case where a user
enters a different value on the dialog form from what they originally
entered in the combo box.
One way to handle this is to use a static variable to keep track of
whether you are running the NotInList code a second time. (The
trickiest part actually is getting focus to move on to the next
control as the user probably expects. I have found SendKeys to be the
only reliable way to handle that.) A commented example follows:
Private Sub cboBuilding_NotInList( _
NewData As String, Response As Integer)
'Use a static variable to handle
' cases where the procedure is
' called recursively from within itself.
Static slngResponse As Long
'Allow new items to be added, using
' a dialog form.
If slngResponse = 0 Then
If MsgBox("Do you want to add " _
& NewData & " as a new building?", _
vbYesNo, _
NewData & " is not in the database") _
= vbYes Then
'Open dialog form for data entry.
'Assume form has:
' code to handle OpenArgs,
' cycle set to CurrentRecord,
' and code to hide/close based on OK/Cancel.
DoCmd.OpenForm "fdlgNewBuilding", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
If SysCmd(acSysCmdGetObjectState, _
acForm, "fdlgNewBuilding") _
= acObjStateOpen Then
'The user clicked OK in the dialog form.
'Set NewData to
' the actual new value entered
' (in case the user changed it)
NewData = _
Forms!fdlgNewBuilding!txtBuildingName
Response = acDataErrAdded
DoCmd.Close acForm, "fdlgNewBuilding"
Else
'The user Canceled data entry
Response = acDataErrContinue
Exit Sub
End If
Else
'The user chose not to add new data
Response = acDataErrContinue
Exit Sub
End If
'set the static variable
slngResponse = Response
'NewData may now hold a different value,
' if the user edited it on the form.
'Recursively trigger the procedure again.
Me!cboBuilding.Text = NewData
'Prevent an extra NotInList message.
'When the code gets here, the
' combo will already have the
' new data.
Response = acDataErrContinue
Else
'Second time through the procedure
Response = slngResponse
'The user added data, so
' tab to the next control.
'Using setfocus here causes errors,
' but Sendkeys works fine. Or skip it.
SendKeys "{Tab}"
End If
slngResponse = 0
End Sub
-- Andy
Stephen Lebans
> You have pointed out the Achilles heel of most NotInlist code that is
> currently in use -- it doesn't correctly handle the case where a user
> enters a different value on the dialog form from what they originally
> entered in the combo box.
>
> One way to handle this is to use a static variable to keep track of
> whether you are running the NotInList code a second time. (The
> trickiest part actually is getting focus to move on to the next
> control as the user probably expects. I have found SendKeys to be the
> only reliable way to handle that.) A commented example follows:
Great post Andy. Copied an Pasted into my bag of tricks. Thanks!
Up till now I have simply not allowed the user to enter a different
value on the dialog form from what they originally entered in the combo
box.
I realize that SendKeys "{Tab}" is going to work 99% of the time because
of where it is being called from but..well there is always a chance of
our app losing focus. An alternative is to use a general purpose
function to call SetFocus directly for the next Active Control in the
Tab order. For my inhouse apps I'll probably stick with your method. If
I was distributing an App I might consider adding the extra code.
Dev and I both had a go at such a function several months ago. Code
follows my Tagline.
--
Stephen Lebans
maca...@nbnet.nb.ca
http://www.lebans.com
'******* Code start ********
'Written by Dev Ashish
http://home.att.net/~dashish/
'Modified by Stephen Lebans
'http://www.geocities.com/~slebans/
Function fEnableNextInTab()
'Enable and Setfocus to the next incremental control
'in the form's TabIndex that is Enabled.
Dim ctlNew As Control, intTab As Integer
Dim ctlOld As Control, intNewTab As Integer
Dim ctlNext As Control
On Error Resume Next
'What's the current control's position in TabIndex
Set ctlOld = Screen.ActiveControl
'we want the next one
intNewTab = ctlOld.TabIndex + 1
For Each ctlNew In Me.Controls
Err = 0
intTab = ctlNew.TabIndex
If Err <> 438 And (intTab >= intNewTab) Then
If (ctlNew.Enabled <> False) Then
'if no error occurred and the current ctl is Enabled then
'If this Ctl.Tabindex < ctlNext.Tabindex then
'store the control's name for later use
' Debug.Print "Next:" & ctlNext.TabIndex & "New:" &
ctlNew.TabIndex
If ctlNew.TabIndex < ctlNext.TabIndex Then Set ctlNext =
ctlNew
End If
End If
Next
'Ok this Ctl is the closest incremental match
ctlNext.SetFocus
Set ctlOld = Nothing
Set ctlNew = Nothing
Set ctlNext = Nothing
End Function
'********** Code End **********
Andy Baron
Glad you found the post interesting. I too have written generic code
to set focus to the next control in the tab order. But the problem
here was that even hardcoding in MyNextControl.SetFocus didn't work at
this particular point in the form event structure.
-- Andy
Stephen Lebans
Andy Baron wrote
SNIP