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]

Hulm1 . . .

Indeed Contacts should be related to Offices.

[blue]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

zmtClients
**********
ClientID as long as PrimaryKey
OfficeID as Long as ForeignKey to zmtOffices. OfficeID
FirstName as String
LastName as string
Other Fields![/blue]

Your already aware how to change offices for a client.

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Hulm1]

Ok Thanks for the above.

Now are you able to advise how I can do the things I wanted to do from the beginning?

1) Open the customer form with the correct office and correct contact showing when accessed from the Contact_List form

2) Add a new contact record without using the nasty Access Navigation button. ie a button in the main Company form for "add new contact". Also "add new office for that matter"

 

[TheAceMan1]

[blue]Now are you able to advise how I can do the things I wanted to do from the beginning?[/blue]

Ya Think Not? . . .

In the [blue]zmtCompany[/blue] form, I see three listboxes or comboboxes (your choice):
[ol][li][blue]Companies[/blue]. Looks-up and displays the selected company.[/li]
[li][blue]Offices[/blue]. Looks-up and displays the selected office with proper associated company.[/li]
[li][blue]Contacts[/blue]. Looks-up and displays the proper contact, with proper associated office and proper associated company.[/li][/ol]
Since your in a rush, we'll handle the contacts list/combo 1st!

Darn! . . . a company emergency! I'll take my laptop and finish this later on this evening! . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Hulm1]

I waiting fingers at the ready!

 

[TheAceMan1]

Hulm1 . . .

OK! Rock n Roll Time!

[blue]Setup Combobox Query:[/blue]
[ol][li]Goto query design view and select the tables [blue]zmtOffice[/blue], [blue]zmtContact[/blue]. Be sure the primary/foreign keys are linked one to many in the query pane![/li]
[li]In the 1st field, copy/paste the following:

[blue]FullName:[FirstName] & " " & [LastName][/blue]

Be sure the field names match that you used in table [blue]zmtContact[/blue].[/li]
[li]Set sorting on this field.[/li]
[li]Drag/drop in order the following fields:
zmtOffice.[blue]CompanyID[/blue]
zmtOffice.[blue]OfficeID[/blue]
zmtContact.[blue]ContactID[/blue]
From Left to right you should have:
[blue]FullName, CompanyID, OfficeID, ContactID.[/blue] If not, make it so![/li]
[li]Save & name the query.[/li][/ol]
[blue]Setup Combobox[/blue]
[ol][li]Open [blue]zmtCompany_Test[/blue] in [blue]design view[/blue] and start the [blue]combobox wizard[/blue].[/li]
[li]Select the [blue]I want the comboboc to look up option[/blue].[/li]
[li]Parse thru the wizard making proper selections ([blue]be sure to select all fields in the query and that they are in order[/blue]) until done. Set the [blue]combobox name property[/blue] to [blue]Contacts[/blue].[/li]
[li]In the [blue]Column Widths[/blue] property of the combo, set the [blue]last three widths[/blue] to zero. Should look like:
[blue]1";0";0";0"[/blue][/li]
[li]Test the combo to make sure your getting the names.[/li]
[li]Done with the combobox.[/li][/ol]

[blue]Now the Code[/blue]
In the AfterUpdate event of the combo, copy/paste the following:

[blue]   Dim frmOffice As Form, frmContact As Form
   Dim CBx As ComboBox, Cri As String
   
   
   DoCmd.Hourglass True
   Set frmOffice = [zmtOffice_Edit].Form
   Set frmContact = frmOffice!zmtContact_Edit.Form
   Set CBx = Me!Contacts

   [green]'Company LookUp[/green]
   If Me!CompanyID <> CBx.Column(1) Then
      Cri = "[CompanyID] = " & CBx.Column(1)
      Me.Recordset.FindFirst Cri
   End If
   
   [green]'Office LookUp[/green]
   If frmOffice!OfficeID <> CBx.Column(2) Then
      Cri = "[OfficeID] = " & CBx.Column(2)
      frmOffice.Recordset.FindFirst Cri
   End If
   
   [green]'Contact LookUp[/green]
   If frmContact!OfficeID <> CBx.Column(3) Then
      Cri = "[ContactID] = " & CBx.Column(3)
      frmContact.Recordset.FindFirst Cri
   End If
   
   Set CBx = Nothing
   Set frmContact = Nothing
   Set frmOffice = Nothing
   DoCmd.Hourglass False
[/blue]

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

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Hulm1]

Yee Haa!

That worked!

I presume I can look up offices with a variation of that approach? I will have a go and come back to you if stuck!

Before we get to the bit about adding contacts or offices, is it possible to be really clever and let the operator have the option of simply typing "john sm.." etc. into the combox box and have it go straight there? It's just that we might have 2000 people. Seriously large combo list!

 

[TheAceMan1]

Hulm1 . . .

Actually there's an error! . . . you'd just never see it in operation!

[blue]If frmContact!OfficeID <> CBx.Column(3) Then
   should be
If frmContact![purple][b]ContactID[/b][/purple] <> CBx.Column(3) Then[/blue]

The idea of the IF statments is not wasting time to lookup whats already in view.

[blue] . . . is it possible to be really clever and let the operator have the option of simply typing "john sm.." etc. into the combox box and have it go straight there?[/blue]

Be sure the combobox [blue]Auto Expand[/blue] property is set to [blue]Yes[/blue]. This makes typing in the combo equivalent to [blue]find as you type![/blue].

Also, since your moving on to the Office combo, migh I suggest a two field combo. CompanyName & Description, sorted in that order. You probably havn't updated the description field as yet, but I hope you see how much more [blue]user friendly[/blue] it will be compared to say . . . pinging on adresses!

Finally, to maintain a good reputation here at the forums, would you please [blue]read at least one of the links[/blue] at the bottom of any of my posts.

[blue]Cheers! . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Hulm1]

Thanks very very much. Could you just give me the code for creating a new contact from main Company form?

Thanks

 

[TheAceMan1]

Hulm1 . . .

To make it easier for others to follow what your doing, start a new thread. This one is already a little too long. I'll be sure to pick it up!

Be sure to explain well, what you have in mind . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Hulm1]

I was successfull with the Combo box for office.

One thing you may be able to help me with.

If I search for say "Environmental Smartass Ltd" and find that record, and then seach for John Smith, who happens to work for "Contamination Dumbass Ltd." the Office look-up combo is still showing Environmental Smartass ltd, London Office (for example). I would prefer that it just went blank.

Can you advise on that?

As you said, I will start a thread on creating a new contact
Thanks for all your help

 

[TheAceMan1]

Hulm1 . . .

At the end of your routines for the comboboxes:

[blue]Me![PURPLE][B][i]ComboboxName[/i][/b][/purple] = Null[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Hulm1]

Thanks again. One last thing, if possible! Some of the companies do not have company names. Is it possible to adjust the combo sort order so the blank ones are at the end?

Thanks!

 

[Hulm1]

Oh and one other small problem. My office seach is looking at Office Name and of course I get Duplicates. Possible to remove the dups?

 

[TheAceMan1]

[blue]My office seach is looking at Office Name and of course I get Duplicates.[/blue]

Earlier on in the thread you said:

[blue] . . . 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.[/blue]

So you copied the duplicate data from the origional! [purple]How did you take care of foreign keys when you did this?[/purple]

Offices that belong to a particular company should all have the [blue]same CompanyID[/blue] as [blue]foreign key[/blue]. However, there should only be one entry per office that exist in a company!

Contacts that belong to a particular Office should all have the [blue]same OfficeID[/blue] as [blue]foreign key[/blue].

Correcting this will correct your office problem.

As for the blank companies, remove them!

Once again: [purple]How did you take care of foreign keys when you did this?[/purple]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Hulm1]

Durr... I fixed the problem of office dups. It was not that I copied any over it was that the query on which I based the Combo box included zmtCompanyTest.

Strange thing though. To finalise things, I changed the name of the tables and forms, removing the "test" extension. I then went into the code for the after update event of the combo boxes and changes the names of the forms. Does not work though. Interestingly when I type in for example zmtOfficeTest, it clearly recognises that as a form. When I change it to zmtOffice, it does not. Funny goings on here that I don't understand

 

[TheAceMan1]

[blue]I changed the name of the tables and forms, removing the "test" extension.[/blue]

As far as the table names are concerned, realize your changing referencing in [blue]queries[/blue], [blue]SQL's[/blue] and the [blue]Record Source[/blue] of forms.

As for forms names, you've changed the [blue]Source Object Name[/blue], but not the [blue]Name[/blue] property used in VBA!

Open the mainform id design view, then click on the border of the 1st subform. The Source Object & Name properties probably do not match . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheAceMan1]

Out of curiousity post the [blue]SQL[/blue] for the [blue]Office Combobox![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Hulm1]

That does explain it I guess

Here is the SQL for the office combo box

SELECT qrCompanyOffice.Office, qrCompanyOffice.CompanyID, qrCompanyOffice.OfficeID
FROM qrCompanyOffice
ORDER BY qrCompanyOffice.Office;