Open Record based on field in Subform 1

[Hulm1]

I have the following Tables:

zmtContactName
zmtCompany

I have the following forms:

ContactName_Edit
ContactName_List
Company_Edit
Company_List

Now each company may have several offices and several Contacts.

On the Main Form Company_Edit I actually have three subforms:
1) A subform of Company_List linked (Parent Child) by CompanyName (Shows all the offices - including the one on the main form - bit irritating but I do not know how to exclude it)

2) A subform of ContactName_Edit (as a continuous form)linked by CompanyID (I only want to show those contacts for that specific Company Office)

3) A subform showing the detail of the contact currently highlighted in the subform of ContactName_Edit including notes. I prefer the look of this with a large clear note field instead of the continuous form look for all contacts. This last subform is achieved by a non visible text box on the main form with the following code: =ContactName_Edit.Form!ContactNameID


When I am in Company_List I can easily use a button to go to the zmtCompany_Edit form with the specific CompanyID showing.

However, I want to achieve two things:

1) When I am in ContactName_List I want to use a button to go to the form Company_Edit with the correct CompanyID AND the correct Contact showing in the subform!

2) In the Mainform Company_Edit, I would like to have a button to add a new contact in contactName_Edit. I know I can simply add a name in the subform as it is continuous forms. I would prefer, however to have a pop up and not Allow edits in the continuous forms

Phew! I hope the anove is clearer than mud!

In summary therefore I am looking for code advice and where to put it (with the on-Click event for each button)? for each of the above two queries.

Thank YOU!

 

[TheAceMan1]

How are ya Hulm1 . . .

For starters, I'd get rid of the subform [blue]Company_List[/blue] and replace it with a combobox or listbox that looksup the company selected. You can easily exclude the company showing by including criteria in the rowsource that excludes the current company. Something like:

[blue]WHERE ([Company]<>Forms!Company_Edit!CompanyName)[/blue]

As for the ContactName_List button, as long as the primary key of company is included, the button code would look like:

[blue]   Dim frm As Form, sfrm As Form, Cri As String
   
   DoCmd.OpenForm "Company_Edit"
   DoEvents
   Set frm = Forms!Company_Edit
   Set sfrm = frm!ContactName_Edit.Form
   
   Cri = "[CompanyID] = " & Me!CompanyID
   frm.Recordset.FindFirst Cri
   
   Cri = "ContactID" = " & Me!ContactID"
   sfrm.Recordset.FindFirst Cri

   Set sfrm = Nothing
   Set frm = Nothing[/blue]

[blue]In the Mainform Company_Edit, I would like to have a button to add a new contact in contactName_Edit. I know I can simply add a name in the subform as it is continuous forms. I would prefer, however to have a pop up and not Allow edits in the continuous forms[/blue]

You really don't need the popup! You can set this subform to only allow editing when entering a new record! In the [blue]On Current[/blue] event of [blue]contactName_Edit[/blue], copy/paste the following:

[blue]   If Me.NewRecord Then
      Me.AllowEdits = True
   Else
      Me.AllowEdits = False
   End If[/blue]

With the above, you can get rid of the button! . . .

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Hulm1]

Part 1: Fantastic! That works a treat as it is at the moment. I absolutely get the point about the combo box for the contacts and will change that.

Part 2: Hmm. Would you have an "Add record" button within the subform ContactName_Edit? Is that what you meant?

 

[Hulm1]

On the matter of the combo box:

How can I limit it to the Company Name? There may be several Company IDs for each Company Name (all the different offices.

Also: Could I use the combo box for the Contact also? So that ContactName_Edit simply shows whichever contact selected in the Combo box? Of course the combo box should be limited to contacts for the Company ID of the parent form.

Thanks v. much for your help

 

[Hulm1]

Uh Oh

I thought your code for the contact name was working. In fact it is not. It opens the correct Company ID, but if there are several Contacts for that Company ID it is NOT working, it just shows the first contact for the companyID.

Sorry to keep sending piecemeal comments

 

[TheAceMan1]

[blue]Part 2: Hmm. Would you have an "Add record" button within the subform ContactName_Edit? Is that what you meant?[/blue]

You don't need a button at all. Users can simply go to the add new line or page simply by clicking the add new navigation button

. If its a continuous form, they have the added option of scrolling down to the end of the list. If you still desire the button, just make it so.

[blue]On the matter of the combo box:

How can I limit it to the Company Name? There may be several Company IDs for each Company Name (all the different offices.[/blue]

You should have a seperate company table! If you don't, then your tables are no normalized. Its still possible to do using a subquery.

How are you performing this in the mainform which is based on CompanyName?

Provide some assemblance of the tables and their relationships. Need to see if we can get past this 1st!

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Hulm1]

For some reason I didn't get a notification about your last post. Sorry for the delay.

I would prefer not to have ContactName_Edit as a Continuous form. One reason is a memo field for notes. I would therefore prefer to have a combo populated with the contacts for that company ID. and then populate the subform ContactName_Edit from that.

Regarding the Company combo. Company_Edit is a separate table. I just don't know how to limit the contents of the combo box to all the different CompanyIDs which have the same Company NAME. This is the way I am dealing with multiple offices for one company. Each office has it's own Company ID

Regarding your code for opening the form Company_Edit from ContactName_List with the correct ContactName in the Subform. I cannot make this work. For example, for a companyID that has 4 contacts, it just shows the one that comes alphabetically first.

I am most grateful for you assistance on this.

Thanks

 

[TheAceMan1]

Hulm1 . . .

In my prior pos I asked for some assemblence of your table & relationships (at least as far as the forms you've mentioned). I need this in order to make determinations (initial code was without knowledge of this . . . and I see its not what I thought). I need:
[ol][li]Actual form names.[/li]
[li]Table Names[/li]
[li]Primary Key names of the tables and any fieldnames involved in this thread.[/li]
[li]The relationships of the tables and the fieldnames involved.[/li][/ol]
If your company table contains offices as well, then you have to solve a table issue. If you don't solve this issue, your headed for other problems down the road.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Hulm1]

Ok Thanks for your patience:

Tables:

zmtCompany
Primary Key (autonumber): Company ID
Other fields: Address; City; PostalCode; Country/Region; TelNumber; FaxNumber

zmtContactName
Primary Key (Autonumber: ContactID
Other fields: CompanyID (please note the initial set-up error in the zmtCompany Table which has a space between Company and ID), telNumber (this is direct line); MobileNo; EmailAddress; Notes.

The relationship between these tables is by Company ID as a primary key in zmtCompany and a foriegn key (CompanyID)in zmtContactName.


Forms:
zmtCompany_Edit
zmtContactName_Edit
Company_List
ContactName_List

Does this help explain the set-up?

 

[TheAceMan1]

Hulm1 . . .

That helps a great deal. Now if you could just clear up in what table(s) [blue]CompanyName[/blue] & the field for [blue]Offices[/blue] reside?

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Hulm1]

OK, sorry for the delay I guess we are in different time zones here.

CompanyName is in the table zmtCompany. There is no field called offices. I use the City field as follows:

I have a simplified Company_list form called Company_List_Subfrm. This is a subform in zmtCompany_Edit and shows the City. The parent child link is CompanyName. That way, in the subform, I can see all Companies with the same name as the one in the Mainform. It does mean that for companies with only one office (most) I see that company again. That is why I would prefer a Combo box which looks up Companies with the same name as the Company in the Main Form. Making a selection in the combo box could then change the company in the Main Form. At the moment that works by simply selecting Go to Record from the Company_list_Subfrm. The form simply refreshes effectively.

I realise that having multiple records with the same CompanyName probably breaches good practice. Perhaps I should have another entire table for Address. Howeever, it does work me in the way I have described. I would be anxious about making any major changes to the structure as it could entail knock on effects through Projects, Quotations, Estimates, Safety Method Statements etc. However, if I have to so be it.

For reference: I DO Have a field in the table zmtCompany called OfficeName. I did not mention it because it is not being used. In my earlier drafts it was used as follows:

The user manually entered the CompanyName, then a comma, then the City. The reason for it is that until now a new Contact record was created in zmtContactName_Edit (based on a query including zmtCompany and zmtContactName)and the user then selected which company the contact was connected with via a combo box showing OfficeName. I thought at the time that I needed this OfficeName field for the combo box because obviously Companyname was no good for a company with several offices (I now realise that as I was basing the combo box on CompanyID anyway and simply including the field OfficeName, I could have included the field CompanyName AND City)thus rendering OfficeName unecessary.

All this of course is comletely secondary as I woud prefer as discussed that by clicking an "Add New" button in zmtCompany, the Company ID will be passed to zmtCompanyName_Edit - and therefore no need for the user to select the CompanyID manually.

Long breath.

Any use?

 

[Hulm1]

I just realised that in my earlier post I had missed out CompanyName as one of the fields for zmtCompany. Oops! Sorry

 

[Hulm1]

I just realised that when I explained the fields in my tables yesterday evening, I missed off CompanyName. Can't think how I managed that, must have been that third glass of wine! Anyway, I hope it is clearer with my post this morning.

 

[TheAceMan1]

Hulm1 . . .

I imagine you realize some of the reasons why I asked! . . . and yes, it does seem like were in different time zones. For reference, AceMan is [blue]USA, New York, East Coast . . . Eastern Standard Time[/blue].

[blue]I would be anxious about making any major changes to the structure as it could entail knock on effects through Projects, Quotations, Estimates, Safety Method Statements etc.[/blue]

In an effort to keep the thread short, I present the following:

[purple]You have a definite table problem[/purple]. [red]Things that should be easy are complicated[/red], via current table structure, and will certainly cause additional complications down the line (if you had'nt noticed . . . your already there!).

As a first attempt to correct table structure, I suggest you include an [blue]Office[/blue] table, and modify your existing [blue]zmtCompany[/blue] table as follows:

[tt][blue]zmtCompany
**********
CompanyID as Long as PrimaryKey
CompanyName as Text

zmtOffices
**********
OfficeID as Long as PrimaryKey
CompanyID as Long as ForeignKey to zmtCompany.CompanyID
Name as Text
Address as Text
State as text
Zip as Text
Country as Text
Description as Text[/blue][/tt]

[blue]zmtOffices[/blue] is now what you would use for [blue]Company List[/blue], only now its called [blue]Office List![/blue], and base on the new table. This listing shows all offices of the company including the [blue]Main Headquarters[/blue], sub Headquarters, and any subsites as depicted by the [blue]description[/blue]. You'll find in time that this description is more powerful than you ever thought! . . . I Promise!

All said and done, what remains is how you intend to relate contacts ([blue]on a table relational basis![/blue]). If you relate them to the main table [blue]zmtCompany[/blue], no problem. However if you relate them to [blue]zmtOffices[/blue] table, you have the advantage of tracking employee movements within the company. AKA . . . [blue]an employee is promoted and is transferred to the main headquarters![/blue]

Your response will determine the rest of the table struture, so give it good thought! . . . It matters more than you realize right now. Id personlly prefer contacts realated to Offices, simply as a matter of following employee migration . . . The choice is yours!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Hulm1]

Yes I thought this was going to be needed. Darn. OK, I will make the changes with a copy of the databse. Maybe it will not be all that bad.

How would you use "Name" in Offices?

For example if the Company Name is "Environmental Smartass Ltd" and they have 4 offices, what would you call the Bristol Office, or the London office? Would you simply call it "Environmental Smartass Bristol"?? or is there a "good practice" approach here?

Thanks for sticking with me:

By the way, I am located in Bristol, UK,hence the time difference!

 

[Hulm1]

OK I have now set up exactly as you suggested. With an append query I copied all the details over from zmtCompany to zmtOffice. I had to so some manual re-linking of contacts, but all is now correct.

I then created a form based on zmtCompany. It is called zmtCompany_Test. It simply shows CompanyName. This has a subform called zmtOffice_Edit based on the table zmtOffice. This in turn has a subform called zmtContact_Edit based on the table zmtContact.

This all works perfectly. However, I really don't like this method of scrolling through contacts and adding new ones. I would much rather zmtCompany_Edit had a list of the other offices and that zmtOffice_Edit has a list of all the contacts. By selecting each one, it would then populate a nice neat box in the top half of the form.

Equally I want a nice clever button on the Office list subform to add a new Office and a similar one on the contact List subform to add an a new contact. Possible?

Finally, as previously discussed (and for which you provided some code), the ability to go from a MAIN contact list of ALL CONTACTS straight to zmtCompany_Edit and the Correct Contact Name!!! Of course it will not work due the to nested subforms.

Thanks!

 

[TheAceMan1]

[blue]How would you use "Name" in Offices?

For example if the Company Name is "Environmental Smartass Ltd" and they have 4 offices, what would you call the Bristol Office, or the London office?[/blue]

You've just given descriptions for the Description field. Just don't forget [blue]HeadQuarters[/blue] or [blue]Main Office![/blue]. Descriptions are what ever makes sense to to you. Imagine a lookup listbox based on Descriptions instead of long drawnout company names used in addressing!

I have to get back to work so I'll wrap this up later this evening . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Hulm1]

OK now you have me baffled. What is Name for? I have a separate table called zmtContact because in some offices we have several contacts. Hence the layout I described.

 

[TheAceMan1]

Hulm1 . . .

Sorry for the confusion about name.

[blue]Name[/blue] is that you would use in the [purple]address of the company![/purple]

Consider your example of [blue]Environmental Smartass Ltd[/blue]. The name here would be involved in all office addresses, but descriptions would vary like:

Main HeadQuarters
USA HeadQuarters
USA NewYork, Brooklyn Office
USA NewYork, Manhattan Office
USA NewYork, Queens Office
USA Chicago Office
United Kingdom HeadQuarters
Japan Office

Of course addresses would vary as far as contact is concerned.

[blue]Is this any clearer? . . .[/blue]

BTW: I hope this opens up the window as to wether you relate contacts to the [blue]Company[/blue] table or the [blue]Offices[/blue] table.

Also, since your post origination, just what does the prefix [blue]zmt[/blue] stand for?

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Hulm1]

OK That is clear.

Contacts is not though. I have assumed that one would connect the contact to the office via OfficeID. Does that allow you to move them between offices? or would you link them to Company. I don't know how I would otherwise link the individual to a particular office if Office and Contact are independently connect to Company via CompanyID.

Regarding zmt. Beat me! I was following the naming structure of a programmer who developed a database for our company some time ago. I never really understood what it was for! I rather wish I had simply used tbl and frm and subfrm. Oh well!

I realise that how we link the contact is pivotal to how you advise me to proceed, however, this time zone difference is killing me!

I look forward to your advice!


used zmt as a means of identifying tables and forms that form the basis of