Bound multiple columns to databse fields in datacombo box

[cheekykevwalker]

With the below code I populate a datacombo box from a recordset. I use 'allname' as a combined field so that I can have surname and forename in the datacombo box as a single value, I also select 2 more SQL fields that get the staff ID and staff team and staff group fields but these are not shown in the datacombo box (hidden from user).When the user selects a different name from the populated combo box the relevant staff_id changes automatically because it has been bound to the correct table value but how can I make the other two staff, group and team fields change in the table when the name is changed? Thanks in advance! Kev

'***Populate the name and Surname Combo Box*****
Set rsHmtStaff = New Recordset
strHmtStaffSQL = "select allname=surname + ' ' + forename, staff_id, short_directorate, short_team from tbl_staff order by allname"
rsHmtStaff.CursorLocation = adUseClient
rsHmtStaff.Open strHmtStaffSQL, cn, adOpenForwardOnly, adLockReadOnly
Set lsthmtstaff.RowSource = rsHmtStaff
lsthmtstaff.ListField = "allname"
lsthmtstaff.BoundColumn = "staff_ID"
Set lsthmtstaff.DataSource = rsContacts
lsthmtstaff.DataField = "hmtstaff"

[\code]

 

[CCLINT]

Requery the rsHmtStaff recordset

 

[cheekykevwalker]

Ok, but I dont know exactly how to do this, could you possibly give me a small example?? Thanks alot

Kev

 

[CCLINT]

Ok. I had to read it 3 times to understand what you want.
I understood that the values in the tbl_staff table have changed, and the combo box needs to reflect those changes.

You need to include those fields in the rsHmtStaff recordset (which you are)

Then you need to get the field values for the selected record by using:

rsContacts.Fields("short_team&quot.Value = rsHmtStaff.Fields("short_team&quot.Value

BUT, you do not do it this way.
You would not have the values in BOTH tables.
What if a value chamges in the main table? You then need to change it every else.
If you need the value, then you do a JOIN sql statement.

*********************************************************
So, the below example is a No, No:

Table1: Employees Fields: EmployeeID, LastName, FirstName
Table2: Vacation Fields: EmployeeID, LastName, FirstName, DaysAuthorized

SELECT * FROM Vacation

*********************************************************
However, this will help keep you from working even longer hours, and, keep from pulling your hair out, or whatever:

Table1: Employees Fields: EmployeeID, LastName, FirstName
Table2: Vacation Fields: EmployeeID, DaysAuthorized

SELECT Vacation.EmployeeID, Employees.LastName, Employees.FirstName, Vacation.DaysAuthorized FROM Vacation INNER JOIN Employees On Vacation.EmployeeID = Employees.EmployeeID

It's called normalizing your database....

 

[cheekykevwalker]

hi, ok thanks for that, I ll have another go now. cheers for your help Kev