trying to do something i am not sure can be done....

[jordancemery65]

Ok, I am almost done creating my DB.... I have run into a little bit of a problem.



I have a field called CellPhone this will be the members cell phone number. I also have a field called CellPhoneProvider this field will be their cell phone provider (US Cellular, AT&T, Verizon, etc.) So when they choose which Cell Phone provider they have, I want that to automatically select what their texting e-mail is going to be....

For instance if i use myself I choose US Cellular on the form, i want it to say ok so the phone number you input was 207-754-4657 so your e-mail is going to be 2077544657@email.uscc.net

I figured out how to use the code to add field 1 and field 2 so that i can get that. But i guess i am having the most trouble figuring out how to have it input the right email address...

I created a couple other fields such as CellPhoneProviderEmail which is currently a drop down lookup wizard list (contains @email.uscc.net, @vtext.com, etc)

I also created a field called CellPhoneEMailAddress which will be home to The full e-mail example: 2077544657@email.uscc.net

Does anyone have any ideas?

[Dal Jeanis]

Not sure what your question is.

If you have a list of cell phone providers, and each one has a standard format 1234567890@(thatprovidersemail.com), then in your onclick or afterupdate for the provider listbox (lstCarrier, for example), you can add vba code to calculate the standard email for that provider into the txtTextingEmail textbox.

You create a table that has the standard endings:

tblCarrierEmail
CarrName      CarrEmailSuff
ATT           @ATT.net                
US Cellular   @email.uscc.net

Dim strEmailSuff as String
   strEmailSuff = NZ(Dlookup("[CarrEmailSuff]","[tblCarrierEmail]","[CarrName]=" & lstCarrier),"Invalid Carrier")
   If strEmailStuff = "Invalid Carrier" Then 
      txtTextingEmail = "Invalid Carrier"
   Else
      txtTextingEmail = txtPhone & strEmailSuff
   End If

You could also code a formatting field into the table, in case some carriers use 123.456.7890 or 123_456_7890 or anything like that.

[jordancemery65]

Sorry for the confusion.
I have a field named CellPhone - This will be for their cell phone number
I have a field Named CellPhoneProvider - This will be a drop down list of different Cell Phone Providers. Each cell phone provider has a different e-mail associated with it. For instance to send a text or an email to verizon you must take the cell phone number XXX-XXX-XXXX and add @vtext.com. For US Cellular it would be XXX-XXX-XXXX@email.uscc.net

The problem is, I have no idea what to do to create it so when someone types in their cell number into the form and then clicks their cell phone provider, i dont know how to have it associate the cell phone proiver to the associated e-mail.

I am going to create a table and see if this works. As far as the code you provided... where would i enter that into?

[Dal Jeanis]

1) Build the table and populate it with at least two records for testing. (US Cellular, for example) If you use the names I gave you, then you can use the code below. If you change the names, then you must change the code below to use your names.

2) I'm assuming, below, that the text box for the email is called CellPhoneEmail.

3) In Design view, looking at your form, Right-click on the field CellPhoneProvider. Make sure the Properties pane is open. click the events tab. click in the afterupdate cell. Click the three dots. Choose "Code Builder".

It should open a window to a VBA subroutine called CellPhoneProvider_AfterUpdate.

Enter the following code after the header and before the line that says "End Sub"

Dim strEmailSuff as String
   strEmailSuff = NZ(Dlookup("[CarrEmailSuff]","[tblCarrierEmail]","[CarrName]=" & Me.CellPhoneProvider),"Invalid Carrier")
   If strEmailStuff = "Invalid Carrier" Then 
      Me.CellPhoneEmail = "Invalid Carrier"
   Else
      Me.CellPhoneEmail = Me.CellPhone & strEmailSuff
   End If

3) Change the cell phone provider in the listbox back and forth and see if the CellPhoneEmail changes.

[jordancemery65]

No Luck... I am sure i am doing or have done something completely wrong!

[June7]

Dal, not sure VBA and DLookup is needed.

Include the carrier email suffix as a separate column in combobox. Expression in a textbox can grab the suffix from combobox:

=[tbxCellPhone] & "@" & [cbxProvider].[Column](1)

[jordancemery65]

Mechanic Falls Fire & Rescue - Copy.zip

I have attached my database. When you open it you should see the table I have created for the Cell Phone Carrier... Since i created this table, should i remove all of the Cell Phone information from the other Member table?

Feel free to do whatever with this database as i still have a saved copy.

[jordancemery65]

June7,

Where do i put the code. When i put it in the expression builder for the default value it shows me a couple different errors....

[Dal Jeanis]

jordancemary65 - June7's way is vastly more elegant. You should use that.


Change the properties of the CellPhoneProvider field as follows:

column count      2
column width      1";0"
Row Source        SELECT CellPhoneProvider, CellPhoneProviderEMailAddress FROM tblCellPhoneCarrier; 
Row Source Type   Table/Query

Change the event code as follows

Private Sub CellPhoneProvider_AfterUpdate()
   
   Me.EMailAddress = Me.CellPhone & Me.CellPhoneProvider.Column(1)

End Sub

Never be afraid to notice and openly admit you were overcomplicating your life. Better to fix the problem and confess error than to remain a damn fool.

[June7]

Use the VBA AfterUpdate event if you want to save value into table but not sure there is any reason to save this concatenated value. It can always be constructed when needed. Saving calculated data is usually unnecessary and often bad idea.

The expression could go in the textbox ControlSource property, not in DefaultValue.

[Dal Jeanis]

June7 - I'm assuming that this code is creating the initial default email address, that can be later changed at the user's request, or based upon any additional information that may be collected while the person is signing up. As such, I put the code in the AfterUpdate of the provider selection box.

It seems to me like there might need to be additional checking going on as well, though. It may be that that code should only run when the email box is currently empty, or occasionally when the carrier is changing and the old email address is based on the carrier. But that code is beyond the original request, and this code meets his initial need for the new member data entry form.

[June7]

Yep, a number of unknowns. Hopefully OP has enough info now to get resolved, whichever way.

[jordancemery65]

I am 10X more confused than when i started. I have done two separate things to the database and i think they are kinda contradicting themselves.... I keep getting errors.... Is there any way someone can look at it and tell me what i need to do to it? I will have to attach a different file because changes have been made again.... I am sorry its just really confusing with two people giving two separate approaches... Not a bad thing, just super confusing! It would probably be alright if i had a vast knowledge of Access!

[June7]

Since neither of us knows your database, hard to say which approach you should use. What are you really trying to do? Are you trying to populate a table with the constructed full email address?

If you want to provide db for analysis, follow instructions at bottom of my post. You should not provide real phone numbers and emails in the data.

EDIT: I guess Dal does know your database (see next post).

[Dal Jeanis]

jordancemary65 - The code I put in #9 worked in your database as posted previously. If you don't have a backup, then you can download the version that you uploaded, and make only those changes, and it should work. And remember to always back up... and back up... and back up...