Graham-
Yes, I think my code in the "new record" form just sets Default Values, not the
control value.
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 8:13 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Not in list
Hi John
Yes - good point - I just didn't want to make it too complicated :-)
Actually, what I usually do is have the OK button on the dialog form hide
(rather than close) the form, and then have NotInList reconstruct the name
string and compare it with NewData. If it is different then you can change
ComboBox.Text so that the requery+retry is satisfied. Only problem is that
changing .Text triggers another NotInList (!!) so you need a static boolean
to ignore the re-entry.
Having gone to the trouble of parsing out the FirstName/LastName, I would
pass them separately through OpenArgs:
DoCmd.OpenForm "frmContactAdd", DataMode:=acFormAdd,
WindowMode:=acDialog, _
OpenArgs:=strLastName & vbTab & strFirstName
That would make it easier for Form_Load to populate the correct fields.
Another way in which my post differed from my habit is that I would normally
populate the field(s) in the dialog form by setting the DefaultValue
property rather than the Value so as not to dirty the form (in case the user
wants to bail without adding the new record.
Salut!
Graham
> From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
> Sent: Thursday, 23 August 2012 17:14
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Not in list
>
>
> 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