Text Box Entry

[gsbatch1]

RESOLVED!!! Text Box Entry

I have a text box for account number entry. After update I have an even that populates another text box with the portion of the account number I need. I want to use this posrtion of the account number to populate an email address. This email address is specific to that portion of the account number. I can get the portion of the account number to show up using the OnClick Event, but the email address text box stays empty.

I want the email address to populate, but it can only be populated based on the second text box.

Acct.#
xxxxxxxxxxxxx -----> Acct Portion
xxxxxx ------> Email Address
xxxxxxxx@cccc.c

 

[vbaInet]

Can we see the code you are using to extract the relevant portion of the account number? Plus give us an example.

 

[gsbatch1]

Yes, below is the code used to pull the portion I need to use to determine the email address associated with that CorpSysprin.


Private Sub txtAcct_AfterUpdate()
If (txtAccount Like "?????????????") Then
txtCorpSysprin = Left(txtAcct, 5)
Else
txtCorpSysprin = Left(txtAcct, 6)
End If
End Sub

 

[vbaInet]

Put this in the Control Source of your e-mail address textbox:

=IIf(Len([[COLOR=Red]Acc.#[/COLOR]] & "")=13,Left([[COLOR=Red]Acc.#[/COLOR]],5),Left([[COLOR=Red]Acc.#[/COLOR]],6))+"@cccc.c"

Amend the bits in red if necessary.

 

[gsbatch1]

Well that works better than what I was getting, nothing. At least now I get the portion of the acct#.

I have a seperate table that lists Group Contacts based on that portion of the acct#.

The person is entering in information for a new record, they have the option to send an email to the group that handles that account requesting info. I want them to be able to see which group contact to use immediately instead of having to look it up on a 5000 record excel spreadsheet.

So the account portion is used to pull the group contact from a seperate table.

 

[vbaInet]

Well that works better than what I was getting, nothing. At least now I get the portion of the acct#.

So it's working as expected now?

I have a seperate table that lists Group Contacts based on that portion of the acct#.

The person is entering in information for a new record, they have the option to send an email to the group that handles that account requesting info. I want them to be able to see which group contact to use immediately instead of having to look it up on a 5000 record excel spreadsheet.

So the account portion is used to pull the group contact from a seperate table.

Is this a separate question?

 

[gsbatch1]

No, its working better than what i was getting, which was nothing.

I need it to actually pull the group contact from a seperate table based on that acct number portion.

 

[vbaInet]

Create a new query, join the account number in the group contact table to the part of the account number you've generated.

 

[gsbatch1]

I appreciate everything you have done.

I guess I am not being very clear, and my wife would agree.

I have an entry form. It updates a backend Table.

The Tech enters in an acct number as follows
8155200016528863

When they hit Tab, or Enter, it automatically pulls the first portion, 815520 and puts it into an unbound text box "txtCorp", and I then need it to go into a different table, not linked to the form, and use 815520 as Criteria in that table on field "CorpSysprin" to retrieve the "GroupContact" field that corresponds to it. The result needs to populate a Text box on the original form "Contact"

I am assuming that I need to use something like
=DLookup("[Addressability]","[tblContact]","[Corp/Sysprin]=" & Forms!CblCardDispoTool!txtCorpSysprin)

In the Control Source or in code to force the "GroupContact" to requery.

This is it, I htought it was simple enough to do, it may not be.

 

[vbaInet]

=DLookup("GroupContact", "Table Name","CorpSysprin = " & [txtCorp])

 

[gsbatch1]

I get #Name? error in the text box.

 

[vbaInet]

#Name because one of the field names or the table name you entered is incorrect. Also if txtCorp is not on the same form it will also show #Name.

 

[gsbatch1]

Thanks vbaInet, that worked. Now I seem to be having a problem with the account number.

the account number is either 13 or 16 numbers long.
If it is 13, then I need the first 5 numbers, if it is 16, then I need the first 6 numbers.

Right now, the 13 one works fine. I get the proper 5 first numbers and the correct email contact group populates.
When I use a 16 # acct it still pulls only the first 5, and then the contact email is blank because nothing matches.

I know it is in my If Then Else statement, but not sure where.

Private Sub txtAcct_AfterUpdate()
If (txtAccount Like "????????????????") Then
txtCorp = Left(txtAcct, 6)
Else
txtCorp = Left(txtAcct, 5)
End If
End Sub

 

[vbaInet]

In my second post, I gave you an IIF() statement to replace your function.

 

[gsbatch1]

Yes you did.

It is a beautiful thing now.

Thank you,

 

[vbaInet]

I'm sure it looks very beautiful now

You're welcome!