Hello Bill
There are two problems:
One is that your event procedure continues to run to completion after your
f_Clients form is opened, at which point the new client is still not in the
database - thus the message.
You can suspend execution of your code while the form is open by opening the
form in "dialog" mode:
DoCmd.OpenForm "f_Clients", acNormal, DataMode:=acFormAdd,
WindowMode:=acDialog
[Note that I am using named arguments in the call so you don't make mistakes
by miscounting the commas!]
The other problem is that you are not telling the event procedure that the
new client has been added. You do this by setting the Response argument:
Response = acDataErrAdded
Add this line to your procedure just after the OpenForm line. It will cause
Access to requery the list and "try again" when your procedure exits.
Also, you may wish to pass the new client name to the form so that the user
does not need to type the name again. For this, use the OpenArgs mechanism
when opening the form:
DoCmd.OpenForm "f_Clients", acNormal, DataMode:=acFormAdd, _
WindowMode:=acDialog, OpenArgs:=NewData
Then, in your form's Load event procedure, check if you are in DataEntry
mode and if you have an OpenArgs value, and if so, put it in the ClientName
field:
If Me.DataEntry and Not IsNull(Me.OpenArgs) Then
Me.ClientName = Me.OpenArgs
End If
Good luck!
Graham Mandeno
Microsoft Access MVP 1996 - 2012
> From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of agent1of6
> Sent: Thursday, 23 August 2012 14:18
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Not in list
>
>
> I am trying to adapt some code from another example for my combo box. I
believe Glenn helped me with the first version.
>
> If the user types a name into a combo box that is not on the list I want
them to get a message box and if they want to add the name (select yes) I
want another form to open. I entered the code below and it almost works.
>
> The message box works, lets me know, "Name is not currently in the list"
and ask the question "Do you want to add it?"
>
> when I answer yes I get an Access error message box that says
> "The text you entered isn't an item in the list"
> "Select an item from the list or enter text that matches one of the listed
items"
>
> When I hit OK, the correct form is open. I just do not know how to keep
this error box from showing.
>
> This combo box looks up a record on the form so there is now two events,
one on the After Update and on in the Not in List. Maybe the After Update
event is triggering this message. If that is the case how do I look up a
record on a form without the After Update event.
>
> Thanks,
> Bill
>
> Private Sub cboLookup_NotInList(NewData As String, Response As Integer)
> Dim strSQL As String
> Dim i As Integer
> Dim Msg As String
>
> 'Exit this sub if the combo box is cleared
> If NewData = "" Then Exit Sub
>
> Msg = "'" & NewData & "' Name is not currently in the list." & vbCr & vbCr
> Msg = Msg & "Do you want to add it?"
>
> i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Client Name")
> If i = vbYes Then
> DoCmd.OpenForm "f_Clients", acNormal, , , acFormAdd
>
> Else
> Response = acDataErrContinue
> End If
> End Sub
Tidak ada komentar:
Posting Komentar