ACCESS FEED: RE: [MS_AccessPros] OnNotInLIst

Minggu, 12 Mei 2013

RE: [MS_AccessPros] OnNotInLIst - Acc2007

Zjubias-

The code is from one of the sample databases in my 2007 book. You'll find
all the code examples are presented with a liberal dose of comments.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
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 ZJUbias
Sent: Sunday, May 12, 2013 1:13 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] OnNotInLIst - Acc2007

John,

Thank you very much, appreciate your kind help and time.

Though am not a good coder, will try to digest your code provided. And I
like the way you present your code with explanation each line..

Thank you.

zjubias

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Saturday, May 11, 2013 10:55 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] OnNotInLIst - Acc2007

zjubias-

When I want to have a user enter more info than is simply displayed in a
combo box as the result of typing something not in the list, I create a
special version of my normal edit form to do a single record add. Here's
some sample code to add an employee:

Private Sub ManagerNumber_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("Employee "
& NewData & " is not defined. " & _ "Do you want to add this Employee?",
vbYesNo + vbQuestion + vbDefaultButton2, _
gstrAppTitle) Then
' Open the add form and pass the name and current department DoCmd.OpenForm
"frmEmployeeAdd", DataMode:=acFormAdd, WindowMode:=acDialog, _
OpenArgs:=strName & ";" & Me.DepartmentID ' Code will wait until "add" form
closes - now verify that it got added!
If IsNull(DLookup("EmployeeID", "tblEmployees", strWhere)) Then ' Ooops
MsgBox "You failed to add an Employee that matched what you entered. Please
try again.", _ vbInformation, gstrAppTitle ' 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

The combo box normally displays LastName, FirstName, so I parse it out to
set up a field comparison. This combo box happens to be on a form for
editing departments, so I also pass the current department ID to ensure the
new employee gets added into the correct department.

The Load event of frmEmployeeAdd parses out the OpenArgs to set up correct
starting values as shown below. Note that the code above checks to make sure
the entered data exists in a new record when frmEmployeeAdd closes.

Private Sub Form_Load()
Dim intI As Integer, strArgs As String
' Save the openargs - protect against null strArgs = Nz(Me.OpenArgs, "") '
First, find the semi-colon before department intI = InStr(strArgs, ";") '
Should always have one, but make sure If intI <> 0 Then Me.DepartmentID =
CLng(Mid(strArgs, intI + 1)) ' Strip off the Department strArgs =
Left(strArgs, intI - 1) End If ' Now, look for name If Not
IsNothing(strArgs) Then ' Look for comma separating last, first intI =
InStr(strArgs, ",") If intI = 0 Then ' Assume only last name Me.LastName =
strArgs Else ' Parse out last, first Me.LastName = Left(strArgs, intI - 1)
Me.FirstName = Trim(Mid(strArgs, intI + 1)) End If End If End Sub

HTH...

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of moi
Sent: Saturday, May 11, 2013 1:11 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: [MS_AccessPros] OnNotInLIst - Acc2007

Can someone please help me on OnNotInList..

I have a code to add a new employee name, but what i wanted is to open my
employee form and complete the other info/detail.

The code am using is good for 2 fields only (id-auto, name).

Thank you for any help.

zjubias

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links