New data in combo box? Please show us the way!

  • Thread starter Thread starter Sandra
  • Start date Start date
S

Sandra

Based on the number of posts I have read concerning this
problem and the number of different solutions (none of
which I have been able to get to work), it is surely one
of the greatest Access mysteries on this Forum! Would
someone please, PLEASE!, address this problem in some
straight forward way with some sample code that works
(surely this is a no-brainer for you Gurus but I am
stumped and more confused than ever).

Here's what I want:
I have a combo box on a Main form to look up a patron's
name. If Patron isn't on the list, I want to add it.
After I add the new Patron to the underlying table/form,
I want the new Patron's name to show up in the combo
box's field on the Main form, then with no further Ado I
can move on to the rest of the fields.

Here's what I have so far using an Event Proc:

Private Sub GetName_NotInList(NewData As String, Response
As Integer)

Dim Result
Dim Msg As String
Dim CR As String

CR = Chr$(13)
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not in the list." & CR &
CR
Msg = Msg & "Do you want to add this name?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
DoCmd.OpenForm "PeoplePlaces", , , , acAdd,
acDialog, NewData

End If

End Sub

If the Patron's name isn't on the list, my custom message
box pops up, I answer Yes, the Patron's form comes up and
I add the new Patron to the table/form. So far so good,
the new patron has been added to the underlying table.
BUT, when I exit the Patron form back to the Main form, I
still have the message box asking me to enter a new name,
and although my new Patron IS in the table, it does not
appear in the field of the Main form, I have to scroll
down to the bottom of my list and select the name again.
This does not seem one bit elegant to me, and I have
tried every suggestion I have found on this Forum to no
avail. I even tried the suggested code from the "Access
Web" and didn't get past the first line without an error.
Can someone please fill in the blanks on this proc so it
will work as expected?

Thanks a million,
Sandra
"Accidental IT Manager" at a Ronald McDonald House
 
I'm not a Guru (maybe in 30 yrs), but this might work for
you. Watch for Line wrap....

The Limit to list under the DATA tab must be set to 'YES'.


Private Sub GetName_NotInList(NewData As String, Response
As Integer)

Dim Result
Dim Msg As String
Dim CR As String

CR = Chr$(13)

If NewData = "" Then
' don't add & continue
Response = acDataErrContinue
' clear the combo box -just in case
Me.GetName.Undo
Exit Sub
End If

Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add this name?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
' Remove new data from combo box so control can be
requeried
' after the AddCategory form is closed.
Me.GetName.Undo

DoCmd.OpenForm "PeoplePlaces", , , , acAdd,
acDialog, NewData
' Continue without displaying default error
message.
' This also does an automatic requery
Response = acDataErrAdded
Else
' don't add & continue
Response = acDataErrContinue
' clear the combo box
Me.GetName.Undo
End If
End Sub

HTH

Steve
 
Thank you Steve!
-----Original Message-----
I'm not a Guru (maybe in 30 yrs), but this might work for
you. Watch for Line wrap....

The Limit to list under the DATA tab must be set to 'YES'.


Private Sub GetName_NotInList(NewData As String, Response
As Integer)

Dim Result
Dim Msg As String
Dim CR As String

CR = Chr$(13)

If NewData = "" Then
' don't add & continue
Response = acDataErrContinue
' clear the combo box -just in case
Me.GetName.Undo
Exit Sub
End If

Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add this name?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
' Remove new data from combo box so control can be
requeried
' after the AddCategory form is closed.
Me.GetName.Undo

DoCmd.OpenForm "PeoplePlaces", , , , acAdd,
acDialog, NewData
' Continue without displaying default error
message.
' This also does an automatic requery
Response = acDataErrAdded
Else
' don't add & continue
Response = acDataErrContinue
' clear the combo box
Me.GetName.Undo
End If
End Sub

HTH

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

.
Click to expand...
 
Back
Top