update two fields based on criteria from one

[elektrik]

Hello All. I have written (rather sloppy) code to update two other fields on my form based on a selection from the first field. Example is below:

If Engineer_Name = "Mike" Then
Email_Address = "mike@nowhere.com"
Primary_Responsibility = "Hardware"

The thing is, I have tables already created with specific email addresses (the same applies to his responsibility) and I want to automatically insert his email address in one field and his responsibility in another field, based on the selection of his name. I'm still at a VERY BASIC understanding of coding at this point, and this code actually works, but I'd like to be able to write code that would refer to the actual table values and insert them into the fields. Could anyone help me figure out how to do this? I've seen a similar subject in this discussion, but to be honest, I haven't covered the me!xxxx chapter and I don't know what that is =]

Thanks in advance!

 

[BarkerD]

Sure. You need to use Recordset objects.

*Note: This code will only work if you have Microsoft DAO object library referenced. if you don't add it in the Editor window from Tools -> References *

Dim db as DAO.Database
Dim rst as DAO.Recordset
Dim strSQL as String

Set db=CurrentDB
strSQL = "SELECT * FROM [TableName] WHERE [Engineer_Name] = """ & Me.ControlName & """;"

Set rst = db.OpenRecordset(strSQL,dbOpenSnapshot)
If rst.EOF Then

'There is no match for this name

Else

'Set values of controls by referencing recordset values.
Me.[Email_Address] = rst![EMailFieldName]
Me.[Primary_Responsibility] = rst![ResponsibilityFieldName]

rst.close

End If

'Destroy objects
set rst=nothing
set db=nothing

You may find it more advantageous to use a Numeric field in your Names table to identify each person uniquely, since you will come across situations where you will have more than one "Mike" in your table. Identify them uniquely, and you will eliminate problems.

*Note: If you decide to use a numeric variable in the search instead of name, change the select statement to:


strSQL = "SELECT * FROM [TableName] WHERE [EngineerNumber] = " & Me.ControlName & ";"

HTH
Duane Barker



[This message has been edited by BarkerD (edited 01-26-2001).]

 

[elektrik]

Wow...thanks! That's gonna take a little time for me to digest exactly how that all works, but it gives me something to do (heh heh).

I really appreciate the help!

Regards,

Jerry Horgan

 

[elektrik]

Well, after looking at your code, I'm just as confused as when I looked at it the first time. Is there a simpler way to do this? I have the engineer list in one table and the primary responsibility list in another...

Maybe I'm not ready for this yet =[