auto fill form, or something

[ckra81]

I have a form with a list box where the user selects an employee name. After selecting the employee, I need the employee's current department and office to populate into a title and department field. This has to be done so that if/when an employee's title and department changes the data at the time the record was created does not.

I haven't done real coding since about 2001, so if it's required to do this, I'll need some detailed assistance. If it's beyond free advice, I understand that too. Any help is appreciated.

The employee table contains:
first name
last name
company via lookup


location via lookup
title

The injury table contains:
employee name, linked to employee table
company, need to get from employee.company
office, need to get from employee.location
title, need to get from employee.title

[pbaldy]

Does this fit?

http://www.baldyweb.com/Autofill.htm

[lacigol]

Idk exactly what your listbox is called or what employee info displays in said listbox, but maybe you could add an on update event to the employee selection list box and use the following code IF (1) your employee's table is named [employeesTable], (2) your employee listbox is named lstEmployees, (3) your listbox displays lastnames only, (4) your title and department fields are textboxes named txtTitle and txtDepartment, and (5) your department field is equivalent to location?

  dim sql, employeeName as string
  dim rstEmployees as recordset

  ' escape apostrophe's in names like O'Brien or O'Neil
  ' so the select query doesn't break on those names
  employeeName = Replace(lstEmployee.value, "'", "''")

  sql = "select * from employeesTable where lastName='" & employeeName & "'"
  set rstEmployees = currentdb.open(sql, dbopensnapshot, dbreadonly, dbreadonly)

  if not rstEmployees.eof
    txtTitle = rstEmployees.field("title")
    txtDepartment = rstEmployees.field("location")
  end if

P.S. I see your location field is a lookup, so what table is it in and what field (id or lastname?) links/relates to it in the employeesTable?

[pbaldy]

And (6) you don't have two employees with the same last name.

[lacigol]

Hehe it works, just not effectively - would select the first one found each time

I can't recall the last time i created a table without an id autonumber field and a creation date/time field default to =now()

[ckra81]

Thanks for the replies. I've been pulled away for a bit and will try this soon. I'll post what happens. Thanks again.

[ckra81]

PBaldy wins the award with his link to instructions. Seems so simple now. Thanks!

[pbaldy]

Woo hoo! Happy to help.