I will literally pay somebody to help me with this

[RedGoneWILD]

seriously, i will...but i dont have much money

i started a thread for this earlier, but it didnt get anywhere



https://www.accessforums.net/program...able-7653.html

i can upload an example database if that will help...but heres the situation

i have a bound form that contains student information...on this form is a bunch of text boxes and such relating to the table that the form is bound to

what i want is to have two combo boxes that are for a student advisor...the row source for the combo box is a list of all available advisors. a student can have one or two advisors, hence the two combo boxes. if an advisor is already assigned to the student, then the combobox displays that. if not, then an advisor can be selected from the combobox.

The problem I am having is that it won't update the advisors table when i change the record. I even tried putting it in a subform thinking that would work, but then its telling me the name is an expression (firstname & lastname) and that it couldn't be changed.

Ideally, i want the two comboboxes on the form...that looks the best and would work the best...but this is literally the last piece of the puzzle of this project I've been working on all summer, and when i get this part done, then I am done.

I REALLY REALLY REALLY NEED HELP! I will try ANY & ALL suggestions.

[weekend00]

I thought that you had it done

[RedGoneWILD]

ha, you've been a huge help dood!

but no, this needs to be presentable on Tuesday, and it basically is

this is literally the last thing i need to finish...as of right now, i have no way of updating a students advisor

[weekend00]

what I could not help is:
let's assume combo1 = advisor1, combo2=advisor2,
when the user change combo1 to advisor3, I can not find which record I should change because I can not get the value before change.

[weekend00]

following code is not tested,just give you some thought


private sub combo1_after_update(...) ' not sure the name and args of event
' studentid and advisorid are numbers, otherwise you need to change the code
if isnull(combo2.value) then
' no advisor2, just update the table
if dcount("*","tblStudentAdvisors","studentid=" & studentID)=0 then 'studentID should the textbox name holding
'no advisor for this student, add new record
currentdb.execute "insert tblstudentadvisors (studentid, advisorid) select " & studentID & "," & combo1
else
'one advisor for this student, just update
currentdb.execute "update tblstudentadvisors set advisorid=" & combo1 & " where studentid= " & studentID
endif
else
'advisor2 is not null, update the record with advisor<>combo2
'if advisor2 is not null and advisor1 is null, this will not work or you need to add more code to make it work
currentdb.execute "update tblstudentadvisors set advisorid=" & combo1 & " where studentid= " & studentID & " and advisorid<> " & combo2
end if

similiar code for combo2_after_update.

[RedGoneWILD]

okay, i started writing it for the first combobox but i haven't really tested it yet

quick question...my advisorID is the advisors initials, so 3 letters...but to me, that just means change i would have to change IsNull to ="" is that correct?

another thing...my combobox displays the advisors full name and not the advisorID...however, i'm thinking i can write a statement that would compare the combobox value to the table and find the appropriate ID? or is there a way to have the value of the combobox be the ID, yet only display the name?

i have done this with a list box, where the first column is the student ID and the second column is the students name, but when double clicked, the value returns the first column only as student id...can i do that with a combobox as well?

anyway, i appreciate your help regardless...i think you have started me on the right track

let me test this out and see what happens

[weekend00]

quick question...my advisorID is the advisors initials, so 3 letters...but to me, that just means change i would have to change IsNull to ="" is that correct?

not change null to "". I think isNull will work fine.
if studentID is not a number , you need to change all like studentid= " & com...
if advisorID is not a number , you need to change all like advisorID= " & com...

you need single quot ' around the value of the combo box. try to find all of them and change.

[weekend00]

if both studentID and advisorID are text:

private sub combo1_after_update(...) ' not sure the name and args of event
' studentid and advisorid are numbers, otherwise you need to change the code
if isnull(combo2.value) then
' no advisor2, just update the table
if dcount("*","tblStudentAdvisors","studentid='" & studentID & "'")=0 then 'studentID should the textbox name holding
'no advisor for this student, add new record
currentdb.execute "insert tblstudentadvisors (studentid, advisorid) select '" & studentID & "', '" & combo1 &"'"
else
'one advisor for this student, just update
currentdb.execute "update tblstudentadvisors set advisorid='" & combo1 & "' where studentid= '" & studentID & "'"
endif
else
'advisor2 is not null, update the record with advisor<>combo2
'if advisor2 is not null and advisor1 is null, this will not work or you need to add more code to make it work
currentdb.execute "update tblstudentadvisors set advisorid='" & combo1 & "' where studentid= '" & studentID & "' and advisorid<> '" & combo2 & "'"
end if

[weekend00]

I am leaving. may come back next tuesday.

[RedGoneWILD]

dood thanks a bunch...i think this is going to work...just need to change it around some

i will post on here how im doing, or i will be begging for more help

either way, really appreciate it