Using Value in one text box to determine Value in another 2

[metalboy]

Sorry about the vague Subject but describing my problem confused me. I have a form which is used to enter data on calls recieved in a small call centre. I have a combo box which source is a table with sales persons details.the field is thier sales id. i have a text box also on this form which i want to be automatically filled with the correct sales person email which is also on the table which thier id is taken. i hope this makes sense.

Thanking you in advance...

Regards

Alex

 

[LittleSmudge]

Makes a lot of sense and it is an issue that a lot of people struggle with until they are shown the solution.

In the Row source of the combo box ( call it cboUser ) include the email address as one of the columns
( you can make this column NOT visible to the user by making it's column width = 0, but it must be there )

So cboUser.RowSource might look like
"SELECT UserId, UserName, UserEmail FROM tblUSER"

cboUser.ColumnWidths = 0;3cm;0

Then in the after_update event of the combo
( AND in the form's On_Current event )
you call a Sub called UpdateEmail

UpdateEmail is then

Private Sub UpdateEmail
txtEmail = cboUser.Column(2)
End Sub



'ope-that'elps.

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[metalboy]

Thank you for that it is great but i am stuck. i have created everything as follows:

SELECT salesagentid, email FROM WoodsTeamDetails

Private Sub UpdateEmail()
txtEmail = salesagentid.Column(2)
End Sub

but nothing happens!

Any ideas

Regards

Alex

 

[RoyVidar]

The column property of combos and lists are zero based. So to refer to the second column, you:

[tt]txtEmail = salesagentid.Column(1)[/tt]

Roy-Vidar

 

[LittleSmudge]

your select statement is missing the user name

You want the combo to SHOW the user's names so the second column needs to be the username

SELECT salesagentid, UserNameHere, email FROM WoodsTeamDetails

Then with the column widths set correctly the user doesn't see the SalesAgentId or the Email text but they are there.

'ope-that-'elps.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[metalboy]

Ok guys. got it set up like little smudge advised. but when you choose the correct sales person it does not update the email field as it should! sorry for being a pain!

Regards

Alex

 

[RoyVidar]

What errormsg, what happens?

Following LittleSmudge's description, you should have something like this within your forms module:

[tt]private sub salesagentid_afterupdate()
call uppdateemail
end sub

private sub form_current()
call uppdateemail
end sub

private sub uppdateemail()
me!txtEmail.value = me!salesagentid.column(2)
end sub[/tt]

To debug, place msgboxes around (and/or breakpoints - hit F9 on an executable line, then run), and check if the event triggers, the value of the combo...

Roy-Vidar

 

[metalboy]

Thanks guys works great!

Regards

Alex