need help - combobox controls table?

[RedGoneWILD]

I really need help with this

I have a detail form linked to a students table that displays all kinds of information about the student. for any info thats in related tables, i used a subform so that its easy to add info and have those tables updated automatically.

here's my problem.



each student can have one or two advisors. i have two comboboxes on the student detail form that are automatically filled with the names of all possible advisors. The default value for each combobox is set to whoever their advisor(s) are. So if the student only has one advisor, it is set to combobox 1, and the other one remains blank. But if that student gets a second advisor, or changes an advisor, whatever, then i can select the advisor from the combobox.

the question is how do i get the value of the combobox to update the table? since the student advisors are in a seperate table, the comboboxes are not bound like the other boxes on the form.

if a student has one advisor, and i change that combobox, i want it to change the related record. or if the student adds an advisor, i want to create a new record.

thanks in advance for any ideas or suggestions

[weekend00]

in the data page of property of combo box, the first line is "control source", select the fieldname of the table which link to your form. when thing in combo box changes, the table will be updated.

[RedGoneWILD]

all it's giving me is a list of fields in the students table, which is the same table that the form is linked to.

i have an advisor table which has an "AdvisorID" field, I have a student table which has a "StudentID" field...and then i have a table that links the two that's called tblStudentAdvisors which has only two fields, the AdvisorID & the StudentID.

I basically need the combobox to update or add a record to tblStudentAdvisors even though the form itself is linked to tblStudents

[weekend00]

just select the field in student table, that is where want to save the change, right?
item shows in the combo box is from advisor table, but save to student table when it changes.

[RedGoneWILD]

no, the table that needs updated is tblStudentAdvisors

[weekend00]

Oops...
you need VBA code to perform this update. write some code in combo box change event to update tblStudentAdvisors

[RedGoneWILD]

yeah, thats what im trying to do...except i dont know the vba code to write...i was hoping you could help me with that...lol

[weekend00]

How is you tblStudentAdvisors like?
is it like: studentID, advisor1,advisor2,advisor3..., or
studentID, advisorID ?

please provide more detail of the fields of you tables.

[RedGoneWILD]

tblStudentAdvisors has just two fields..."StudentID" & "AdvisorID"

a student can have up to two advisors, or two records in this table ...an advisor can have unlimited students

The "StudentID" is the primary key for tblStudents and "AdvisorID" is the primary key of tblAdvisors

furthermore, if it helps any...here is the code that i have so far that populates the two comboboxes with the current advisors for each student

'clear textboxes
    Me.cboAdvisor1.Value = ""
    Me.cboAdvisor2.Value = ""
    
    'declare variables
    Dim advisor1 As String
    Dim advisor2 As String
    Dim strSQL As String
    Dim rst As Recordset
    strSQL = "SELECT tblStudentAdvisors.*, tblFaculty.firstName, tblFaculty.LastName, tblFaculty.OfficePhone, tblFaculty.OfficeEmail, tblStudentAdvisors.studentID " & _
             "FROM tblFaculty INNER JOIN tblStudentAdvisors ON tblFaculty.FacultyID = tblStudentAdvisors.FacultyID " & _
             "WHERE tblStudentAdvisors.studentID = " & ID & ";"
    advisor1 = ""
    advisor2 = ""
    
    'display the results
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    If rst.RecordCount > 0 Then
        rst.MoveFirst
        Do While Not rst.EOF
            If IsNull(advisor1) Or advisor1 = "" Then
                advisor1 = rst("lastName") & ", " & rst("firstName")
            Else
                advisor2 = rst("lastName") & ", " & rst("firstName")
            End If
            rst.MoveNext
        Loop
        Me.cboAdvisor1.Value = advisor1
        Me.cboAdvisor2.Value = advisor2
    End If
    
    'Close and empty recordset
    rst.Close
    Set rst = Nothing

[weekend00]

If one student have at most 2 advisor, I suggest you change the tblStudentAdvisors to have: studentID, advisor1, advisor2

in your current table, if a student has 2 advisor and need to change one, how can we identify which to change?

(I am leaving at 5:00 o'clock, may come back tomorrow)

[RedGoneWILD]

the problem with doing that is it violates normalization rules

if i were to put an advisor1 and advisor2 field, then which one gets linked to the advisors table?

the way i think this is done is that an after update event is triggered when the combobox is changed...i want that event to contain code that either replaces the current record with a new advisor, or adds a new advisor record to the table

i just don't know what the correct SQL statement or VBA code would be

[weekend00]

if i were to put an advisor1 and advisor2 field, then which one gets linked to the advisors table?

My question is just on the contrary, you metioned you have 2 box in your form to show 2 advisors of the student, how do you put 2 advisors in each box if they are in 2 records? I believe your form is a single form.

[RedGoneWILD]

the two combo boxes are populated with a loop...the code i have to do that is posted above

[weekend00]

I could not find the old value of advisor which I need it to decide which advisor is to be updated.

[RedGoneWILD]

i really need help with this....can anybody explain this to me?