Jorge-
In the subform, you're editing the VendorContacts table, not the contacts. I assume you have a combo box that pulls existing contacts from the Contacts table to set the ContactID in VendorContacts. That combo box should display the contact name (maybe last name, first name) but use the ContactID as the Bound Column. What you need is a Not In List event procedure that catches when the user enters a last name, first name that doesn't exist in the Contacts table. You can include code in that procedure to parse out the last and first names entered and either add a new record directly or pop open a Dialog form with what the user entered to fill in the other information.
Here's some sample code to get you started:
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 above is for a combo box for the manager on a Departments form. If the user enters a name not in the department, this code runs. It parses out a potential last name, first name, and builds a search string that is used later to verify that the record got added. The code then asks the user if they want to add this employee. If the user clicks yes, the code opens a form to add a new user and passes it the name the user entered and the current department ID. (You won't need the Department in your case.) There's code in the Load event of frmEmployeeAdd that parses out the name and pre-fills the last name and first name fields on a new record. The user can then enter other details such as address and telephone number. frmEmployeeAdd also has button to either cancel and not save anything or save and close. After the form closes, this code continues (it will halt after the OpenForm because it opened a dialog form). The code checks to see that the name was added and either tells Access that all is OK or go ahead and display an error message.
Hope that helps...
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
(Paris, France)
On Sep 25, 2014, at 7:50 PM, muralles_r12@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hi guys, I hope you are well.
I am back, working on access, but I am having trouble to get a simple job done.
Can you help me out?
I want to use a form and sub-form to display companies and its contacts.
The form should display the company info
The sub-form the contacts for the company
I have the 3 tables
vendors - primary key vendorID
contacts - primary key contactID
vendorContacts - primary key = vendorId and contactID
I want to be able to add new contacts to the company right on the sub form.
I build a query on the owners table to use it on the sub form.
I build a query on the vendors and vendorContacts table to use it on the form.
I want to embed the sub form into the form and set the master and child links.
Why does this not work?
Thank you.
Jorge Muralles
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar