Graham-
Good points all. I usually take it one step further and check to see that what
I picked up as NewData actually exists in the database after the return from the
add record form. Like this:
Private Sub ReferredBy_NotInList(NewData As String, Response As Integer)
Dim strName As String, strWhere As String
Dim intI As Integer
' User typed in a name that's not in the list
strName = NewData
' Look for comma separating last, first
intI = InStr(strName, ",")
If intI = 0 Then
' Assume only last name - set up the test predicate
strWhere = "[LastName] = '" & strName & "'"
Else
' Parse out last, first - build the predicate
strWhere = "[LastName] = '" & Left(strName, intI - 1) & "'"
strWhere = strWhere & " AND [FirstName] = '" & Trim(Mid(strName, intI +
1)) & "'"
End If
' Verify that they want to add the new Contact
If vbYes = MsgBox("Contact " & NewData & " is not defined. " & _
"Do you want to add this Contact?", vbYesNo + vbQuestion +
vbDefaultButton2) Then
DoCmd.OpenForm "frmContactAdd", DataMode:=acFormAdd,
WindowMode:=acDialog, _
OpenArgs:=strName
' Code will wait until "add" form closes - now verify that it got added!
If IsNull(DLookup("ContactID", "tblContacts", strWhere)) Then
' Ooops
MsgBox "You failed to add a Contact that matched what you entered.
Please try again.", _
vbInformation
' Tell Access we handled the error, but cancel the update
Response = acDataErrContinue
Else
' Tell Access new data was added
Response = acDataErrAdded
End If
Else
' Don't want to add what they typed - show standard error message
Response = acDataErrDisplay
End If
End Sub
Note that in this case, I'm expecting to enter a person name in the form: "Last
Name, First Name" - the existing names are displayed that way in the combo box.
I parse it out to set up a predicate before I call the add form, then use the
predicate to verify that what they entered got added. The user will get an
error message if, for example, they misspell the name and then correct it in the
"add record" form before saving the new record.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Graham Mandeno
Sent: Thursday, August 23, 2012 5:05 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Not in list
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
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar