Two questions regarding setting up a form

[Steve_work]

Can someone help me with a couple of questions I've got regarding my first
access database I'm setting up.

1. I've created a form that contains two textboxes, amongst other things.
One of these text boxes is username and the other is email. The email takes
the form of whatever they put in the username field plus the text
"@domain.co.uk". So at the moment for each record I have to type the
username then retype the username in the email textbox with the above text
appended on at the end. How can I get the text from the username to
automatically get copied over to the email text box with the "@domain.co.uk"
copied across? Ideally this would happened automatically when I exit the
username field.

2. The second question regards another two components on this form, two
comboboxes. The first combo contains a list of department heads while the
second combobox contains a list of departments. At the moment I have to
manually select the department and then select the department head form the
second dropdown. I want it so if I select a department, the department head
combo changes to reflect the head for that department, and vice versa. So I
know for instance if I select the 3 option under department then option 5
will need to be selected under department head.

Any help with this would be great. Many thanks

 

[freakazeud]

Hi,
for your first problem try this on the after update event of the username
control:

Me.YourEmailControl.Value = Me.YourUserNameControl & "@domain.co.uk"

Just have to make sure that each user really has such an email.
For your second problem you want something called cascading comboboxes.
You can find two great samples of this on candace tripps website.
Check this discussion for more:

http://www.utteraccess.com/forums/s...e=m&olderval=&oldertype=#Post759168&bodyprev=

HTH
Good luck

 

[Douglas J Steele]

If the two values are always the same (except for the extension), you
shouldn't be storing both. You can always create a query that derives the
email by adding the extension to it.

If there's a chance that they may not be the same, then that changes things.
In the AfterUpdate event of the first textbox (which I'll call txtUserName),
you can put code to set the value of the second textbox (which I'll call
txtEMail). In the code below, I'm assuming that if there's already something
in txtEMail, you don't want to overwrite it:

Private Sub txtUserName_AfterUpdate()

If Len(Me.txtEMail & vbNullString) = 0 Then
Me.txtEmail = Me.txtUserName & "@domain.co.uk"
End If

End Sub

For the second question, do you really need two combo boxes? Can you have
one combo box only, and determine the Department Head from the Department?
Note that it's possible to display more than one column in a combo box: your
combo box could display both department name and department head, to help
the users. Of course, after selecting a row, only one value can be displayed
in the text box area of the combo box. If you want to see the value of the
other column, you can put code in the combo box's AfterUpdate event to put
the value in another text box on the form. Assuming your combo box is named
cboDepartment, and its row source has 3 fields: DepartmentId, DepartmentNm
and DepartmentHeadNm (in that order, and presumably with DepartmentId as the
bound field, but hidden), and you have a text box named txtDepartmentHeadNm,
the AfterUpdate event would look something like:

Private Sub cboDepartment_AfterUpdate()

Me.txtDepartmentHeadNm = cboDepartment.Column(2)

End Sub

(columns start at 0, so the 3rd column is column 2). Your combo box could
also include the DepartmentHeadId as a hidden field, and you can retrieve
its value in the same way: the Column property doesn't care whether the
column is visible or not.

If you really feel that you need two different combo boxes, you can have one
automatically based on the other. Sal Ricciardi has an article on this at
http://office.microsoft.com/en-us/assistance/HA011730581033.aspx , or you
can take a look at http://www.mvps.org/access/forms/frm0028.htm at "The
Access Web"

 

[Al Camp]

Steve,
1. You could concatenate the two fields when you need to see, or use the
full email address.
Given examples of EName = JohnSmith and EISP = Comcast.net, you could create
a calculated field on your form (ex. EFull) with a ControlSource of...
= EName & "@" & EISP
That would display...
(e-mail address removed) (it won't appear as a hyperlink on your form,
this email is making it appear so)

Now... if you use the SendObject function to send an email, and you use
EFull as the TO:, that email address will work.

2. In the query behind your 2nd combo box, use the value in your 1st combo
as a filter. (cboDeptHeads and cboDepts)
Include (hidden) your DeptHeads in the cboDepts query. Give it a
criteria of...
= Forms!YourFormName!cboDeptHeads
Now, whenever you select a DeptHead, only those Depts that are
associated with it will appear in the Depts combo.
*** Requery cboDepts on the AfterUpdate event of cboDeptHeads, so they
will always be in synch.

 

[Steve_work]

Wow thanks for the fast reply guys. I'll take a good read through these
replies and see how I get on.

Many thanks

 

[Al Camp]

Steve,
In my post, I didn't include the "@" in either field, because it's
included for every address. I incorporated it during the concatenation.
Let us know how you make out... good luck.

 

[Steve_work]

Just wanted to get back to you guys and say thanks.

I've done what you suggested and used the AfterUpdate event to tag the
username onto the start of the email. Also Douglas I used your suggestion
and altered the combo box to have two columns and then displayed the
department head in a separate text box.

Thanks a lot for the help.

Steve