Using Domain Names and Changing a Domain Name

[d9pierce1]

Hi all, I promise this is my last question on this db....
This is a two issue post!
I am trying to create a pop up form when entering a new email address that checks to see if a domain is present on another form
and then if domain changes it changes all my other addresses that have that domain. db Attached but not a lot of data in as have to keep small in order
to make it small enough to upload it


Db will open to CompanyProfileFrm. On that form i have a ChkDomain check box and TxtDomainName text box.
If you click on Contacts button the contact form will open up, then click on Manage Emails button on on ContactsFrm.


On the after update event on the CboEmailType I need it to check to see if the ChkDomain is True and that the TxtDomainName is not null on the CompanyProfileFrm.
If Use Domain is True, and Domain is not null then
msgbox "Would you like to use the company domain for this email address? Y/N
If Yes, then put the Domain Name from the TxtDomainName from Company Form in the TxtDomain and ChkUseDomain = True on CompanyEmailFrm
If No, then just leave the Domain Name blank for me to fill in the Domain and ChkUseDomain = False on CompanyEmailFrm




Now, If I change the TxtDomainName (Domain Name) on CompanyProfileFrm I need it to change the CompanyEmailFrm Emails address that are CheckUseDomain = True
so that it changes all the emails for that domain that the ChkUseDomain = True


Reasons for doing this are:
1. If a company has a domain as many do these days, then i want to be able to use it and have it automatically fill in. In some cases even when they have
a domain there are emails that dont use it or are differnt form that of that domain. Need the option to use or not use!
2. If a company changes their domain as they do... i dont have to go through 500 email names and change the domain to a new domain.


Thank you all,
Dave

Copy Profile Test.zip

[June7]

Not quite sure what the issue is.

1. More than one way to approach. Since forms are open, code can directly reference to get value to check. Or code can use OpenArgs to pass value to opening form. Can use DLookup() domain aggregate function. You already have basic logic needed now try building code that applies the logic.

2. Run an UPDATE action SQL to change field value - what table? Why are you repeating company info in EmailTbl? Why not just save CompanyID as foreign key? Why is Parkway a reference when company name is Parkway C & A, LLC?

[d9pierce1]

Hi June7
Yes, only way to get to it when finished will be through open forms. EmailTbl holds the Domain field on the CompanyEmailFrm, CompanyTbl holds the DomainName field on CompanyProfileFrm!
I dont see i am repeating company info on email page other than the locationID? Parkway is referenced in the email as just a reference....I just added some data (Just temp Data for testing out). A contact can have multi email addresses and i want a reference to that is the purpuse of that as it will give me all emails for that contact in the contact screen by combo so its a reference when in the combo. There are means to my methods. Maybe easier ways to do so but? And companies can have multi locations that have different contacts, emails, and such. I dont want to see 500 contacts in a list and using the location to break it up. Foristance, I dont want 10000 types of emails so i have a email type that has suchy as Office, Home, and such.... I use the reference txtbox to let me know what that type refers to. Like...Home , Gmail Accout and Work, Parkway and Personal, Bellsouth... so when i decide to email someone and want to send to specific email address, i can just see the type and reference on that contact form by using combo. Hope that makes sence....

[June7]

Still seems odd that domain is in multiple tables. Disregarding my confusion about data structure, my suggestions still apply.

[d9pierce1]

I will try some things this weekend and see if i can get something to work.

[d9pierce1]

Just wondering if i am on the correct track here. Way over my paygrade for sure but trying to figure out where to go with this?

Private Sub CboEmailType_AfterUpdate()   
         'Check to see if new record
    If Me.NewRecord Then 
          'See if UseDomain = True and Is Not Null DomainName in CompanyTbl or on CompanyProfileFrm
    DLookUp ("DomainName", "CompanyTbl", "CompanyID=" & Forms!CompanyProfileFrm![CompanyID] & "UseDomain=True")
          'If True then message
    Msgbox ((Would you like to use the Company Domain for this email address?", vbYesNo) 
    If vbYes then
          ' Put DomainName into the EmailFrm field [Domain] and make ChkUseDomain=True on EmailFrm or EmailTbl
    DoCmd.SQL INSERT INTO EmailAddressTbl ([Domain]) values (DomainName from CompanyTbl) 
    If vbNo Then
        'Do Nothing
End IF
    Me.Forms!EmailFrm.Requery
End Sub

[June7]

Need to set the DLookup result to a variable or use as part of If Then conditional. DLookup returns Null if no match. Missing AND in the criteria.

MsgBox function also needs to either set a variable or be in If Then conditional. Missing quote mark in the MsgBox.

I prefer CurrentDb.Execute "INSERT …".

EmailAddressTbl and EmailFrm are not in the posted db so these have been added since?

However, I am not sure why you are using INSERT action SQL. If form is open to record you want to edit (new or existing) just populate field.

Dim strDom As String
If Me.NewRecord Then
    strDom = Nz(DLookUp("DomainName", "CompanyTbl", "CompanyID=" & Forms!CompanyProfileFrm![CompanyID] & " AND UseDomain=True"),"")
    If strDom <> "" Then
        If MsgBox("Would you like to use the Company Domain for this email address?", vbYesNo) = vbYes Then Me!Domain = strDom
    End If
End If

[d9pierce1]

Thank you so much,
The EmailAddressTbl you spoke of is Just EmailTbl and the EmailFrm is CompanyContactEmailFrm Just for reference...
I used several junction tables in this to create what i needed or wanted.
Example:
CompanyTbl is related to CompanyLocationTbl by Junction Table Company2LocationTbl so one company can have one name and multi locations such as AMC Theatres, Company is AMC and they have locations LIke First Colony or Rio 24 and that is used in reference for the location. Now ContactsTbl are related to CompanyLocationsTbl by Junction Table by Contact2LocationTbl so that one location can have multi contacts and i dont see all company contacts, just those that are related to that location. Now a Contact for that location can have multi email addresses and multi phone numbers. ContactTbl is related to EmailTbl by Junction Table Contact2EmailByLocationTbl

I broke this down this way so that i could have specific data per company, location, and contacts. Bank of America is a company, They have 1000+ locations, Locations have multi Addresses and Phone Numbers, and each location has Contacts , each Contact has multi Emails, Phone Numbers, and so on. I hope this makes some sence, may not have been the ideal way to do it but it does work and its the only way i know how to do it. This has taken me a long time to create and get to work correctly. I only want to store one company name per company, as many locations they have along with the address and phone info for that each location, and contacts for each location along with their emails and phone numbers to each individual contact. This was the only way i could get this to work like i wanted it to.

YOU all have been so much help and it is so appriciated to say the least as i am not a programmer by far and struggle with how to get this info to here all the time. Beleive me, i struggle really hard with these complicated things So i just want to say Thank you all so much.

Dave

[June7]

So, does code work?

[d9pierce1]

Hi June7,
This worked well. I had some errors on my part but fixed them, set relationship window, and it works like a charm. Just wondering if I have the code for that in correct method. Such as should the Dim be first or does it matter?

Private Sub CboContactEmailType_AfterUpdate()    If Me.NewRecord Then
        DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO Contact2EmailByLocationTbl (ContactID, EmailID) " & _
        "VALUES (" & CboContact & ", " & TxtEmailID & ")"
    DoCmd.SetWarnings True


    Dim strDom As String
    
        strDom = Nz(DLookup("DomainName", "CompanyTbl", "CompanyID=" & Forms!CompanyProfileFrm![CompanyID] & " AND UseDomain=True"), "")
    If strDom <> "" Then
        If MsgBox("Would you like to use the Company Domain for this email address?", vbYesNo) = vbYes Then Me!TxtDomain = strDom
    End If
  End If


Forms!ContactFrm.Refresh
End Sub

Again, thanks so much
Pretty sure I can do the update qry, may take me a few attempts but it normally does when i create one of them, LOL....Practice

[June7]

As long as variable declared before it's used, all is good. But many say all Dim statements should be at beginning of procedure just because it is more orderly and "best practice".

If you use CurrentDb.Execute, SetWarnings is not needed.

[d9pierce1]

Test123.zip

Very Nice June7,
I have never seen CurrentDb.Execute Used before. I will try it out in the near future.

I did run into one issue is that when the code runs, it doesnt tic the ChkUseDomain check box in the ContactEmailFrm? Everything else works great.

I had one more question which i uploaded this with the changes to a fresh db called Test 123, and that was on the update qry. I made an update qry for this called for now Query1 so its easy to find, i will change that name later. I was able to get the query to update the domain in the EmailTbl however, it makes every domain that is UseDomain=True to the change. I just cant figure out how to get it to only change the CompanyProfileFrm i have open that i made the change on. And will I put that Update qry into the OnChange property or ? I have never used OnChange before so not really sure.

Thank you
Dave

[June7]

I have NEVER used OnChange event.

DLookup retrieves DomainName yet you are populating DomainID field. DomainName and DomainID are synonymous?

Why run an UPDATE action? Again, if form is open to record that needs edit, just populate field on form.

If MsgBox("Would you like to use the Company Domain for this email address?", vbYesNo) = vbYes Then
Me!TxtDomain = strDom
Me!ChkUseDomain = True
End If

[d9pierce1]

HI,
That worked great, i tried Me.Chk.... but never tried the Me! so that solved that issue.
The DomainName is in the CompanyTbl and the DomainID is in the EmailTbl. These are joined by junction tbls. they are basically the same field yet i named them different to acknowledge they are different fields. I need to keep them separate due to I have email addresses that are not the company domain and need to be able to have a domain field in EmailTbl. I dont actually put in the email address, its a ConCat of the email name and the domain. So the update qry is most likely the way to go. The Qry i made works to change the DomainName, yet it changes not only the company i have open, it changes all emails that have a company DomainName to that domain.
And thank so much
dave