ACCESS FEED: RE: [MS_AccessPros] OnNotInLIst

Minggu, 12 Mei 2013

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]