ACCESS FEED: Re: [MS_AccessPros] Form and subform not working

Kamis, 25 September 2014

Re: [MS_AccessPros] Form and subform not working - not display information well

Hi John, thank you for getting back to me.
I do not think I explain you well, what I am trying to do.
I am using two different entities, but I do not think it makes a difference:
1. resorts - resortID = primary key
2. owners - ownerID = primary key
3. ownerresorts - linking table. ownerID and resortID = primary key
I can make a query and join these three tables.Then I can make a form base on it and the wizard creates a form and subform.
The form display the resort information
The subform displays the owners at that resort.
I am attaching the database, I do not know if you might have a chance to take a look.
What I wanted to do is similar to how you do the form and subforms in the advance section of the book, Access Inside Out.
In that example you build the querries, then the subform, form, and main form using your separate queries and then you link them together in the form properties.
So I thought it would work as follows:
1. Make query that gives me a list of owners.
2. Make a form base on it.
3. Make a query based on resorts and owner resort.
4. Make a form based on the query above
5. Embed the form from step to into the form from step 4
6. Set the link master fields and Link child field properties.
　
Is this the correct way of doing this?
In the sub form, I would like to include the owner ID as a combo box, that way I can ads owners to a resort.

On Thursday, September 25, 2014 12:53 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

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
http://www.viescas.com/
(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

----------

BUSINESSOFFICE

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