Full Version: Select Case or Table??
UtterAccess Forums > Microsoft® Access > Access Forms
samohtwerdna
Hello Again!
thought I would start a new thread for the question.
Now I have my email form poping up and filling in the appropriate feilds - however, I would like to use a Select Case Statement or something like to fill in the email address text box. I have a text box for the Dealer ID which has about 37 possibilties and a unique email address for each of these dealers. So rather than fill in the email address every time - I would like to fill in this feild automatically for the user.
Should I do this with a Select Case in VBA some where - or would Access prefer using a 'look-up" table??
How would I go about either one?
p.s. I already have a table with the appropriate info, and I also have a Select Case Function writen out with the same info.
freakazeud
Hi,
do you mean you want to fill a textbox control on your e-mail form with a specific e-mail? How to you determine which e-mail to use?
Can you describe a little more what your form looks like. I'm not following you with: "I have a text box for the Dealer ID which has about 37 possibilties and a unique email address for each of these dealers."
HTH
Good luck
fkegley
I would go with the table lookup---as you add to the table, the new addresses will "automatically" be put in the list, whereas the Select Case would have to be modified.

I would not use a text box, it should really be a combo box.
Edited by: fkegley on Wed Dec 28 13:52:35 EST 2005.
dannyseager
I second using a lookup table...
It makes it easier to edit/add values and as you only have to store an ID it saves space
samohtwerdna
Sorry for being vague,
I have several textboxes on my email form. These will supply the pertantent info for the email itself (To: , Subject: and Message) I have a select group of email address that I will be using based on the name of the Dealer - which is actualy a three letter abrv. for the dealer. example 'Ali Kriste' = "ALK" and the email address for ALK = "generic@theiremail.com"
So I tried a combo box that was linked to my look up table - but this requires the user to select the dealer?
fkegley
Yes, the user would have to select the dealer. However, you could fill in a text box with the associated Email. Two columns in the combo box, one the DealerID, the other the DealerEmail. The user could pick the DealerID and in the AfterUpdate event of the combo box, you could put some code to set the text box to the associated Email address.
samohtwerdna
Yep - that is what I did! thanks Frank.
already select the dealer from a combo box on the main form - so I was hoping not to have to select that again on the email form - but I guess thats ok.
Thanks!
NoahP
If the main form is still open, you can refer to the hidden column from that combo box as well. You don't have to have it selected again.
oah
samohtwerdna
First, how do you hide a column in a combo box?
ext, how dou you update the text box on the pop up form by what is selected in the combo box from the main form??
Thanks?
fkegley
To hide a column, set its width to 0 in the Column Widths property so to hide the first column and set the second column's width to 2 inches, and hide the third column this would be the setting:

0;2;0

To update the text box, you need a touch of code in the AfterUpdate event in the combo box. Open the form in design view and set the properties sheet to the combo box. Click the Event page, the AfterUpdate row, the ... at the right end, Code Builder from the list. The code window should open, you should be in the correct place to type:

Me.[NameOfTextBox] = [Forms]![NameOfForm]![NameOfComboBox].Column(1)

This assumes that the value you want to display is in the SECOND column of the selected row. The Column collection is zero-based.

It also assumes the the [NameOfForm] form is open.

Edited by: fkegley on Thu Dec 29 11:27:08 EST 2005.
Edited by: fkegley on Thu Dec 29 11:27:41 EST 2005.
NoahP
There is no code required here. You need only use:
Forms!NameofForm.NameofComboBox.Column(1)
Has the control source of the text box.
Noah
samohtwerdna
The [NameOfForm] form is not open until after the ship date is entered - which is the last thing on the Main form to be filled in.
My cboDealer is on my MainForm my txtEmailTo is on my EmailForm - so maybe I should pu the event procedure on the Email form On Open or something?
samohtwerdna
Noah
Thanks! you were right! that works wonderfully !
NoahP
You are welcome!
oah
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.