two combo boxes Access

[margot]

Hello, I have a "data entry form" with several text boxes and list boxes, combo boxes.The user is suppose to select a student first name from a combo box and a last name from another combo box and the Student_Id textbox should be updated after the user made the selections. I have written the code below but it does not seem to work properly. Can someone help? Thanks in advance.

[code= vb]
Private Sub txtStudent_ID_A fterUpdate(Canc el As Integer)
With Me![txtStudent_ID]
If IsNull(Me!cmbFi rst_Name) & IsNull(Me!cmbLa st_Name) Then
.RowSource = ""
Else
.RowSource = "SELECT [FirstName] & [LastName]" & _
"FROM tblSpecialist " & _
"WHERE [Student_ID]=" & Me!txtStudent_I D
End If
Call .Requery
End With
End Sub
[/End]

[Megalog]

Correct me if I'm wrong, but at a glance it appears that the code is trying to return a student's first and last name, based upon a match with a given Student ID.

But you said the opposite, that you want it to return a student ID, based upon an entered first and last name?

Also, watch where you use '&' vs 'AND' for starters...

[margot]

Correct me if I'm wrong, but at a glance it appears that the code is trying to return a student's first and last name, based upon a match with a given Student ID.

But you said the opposite, that you want it to return a student ID, based upon an entered first and last name?

Also, watch where you use '&' vs 'AND' for starters...

Yes, I want to do the opposite. So I guess my code is wrong. I want it to return a student ID, based upon an entered first and last name.

[Megalog]

Ok, this is close to what you're shooting for. You might have to play around a bit with the syntax a bit in the WHERE line.. I'm horrible at my commas and parens ;)

Also you probably want the logic in the If line to be changed so that if the first OR last name is empty, the ID comes up empty. So I changed it to if first AND last name are not null, then lookup the ID, else return nothing.

Hope it helps!

-Lee

Private Sub txtStudent_ID_AfterUpdate(Cancel As Integer)
With Me![txtStudent_ID]
    If IsNull(Me!cmbFirst_Name) = false AND IsNull(Me!cmbLast_Name) = false Then
      .RowSource = "SELECT [Student_ID] " & _
                   "FROM tblSpecialist " & _
                   "WHERE (((tblSpecialist.Firstname)=" & me!cmbFirst_Name &") AND (([tblsSpecialist.LastName)=" & Me!cmbLast_Name & "));
    Else
      .RowSource = ""
    End If
    Call .Requery
  End With
End Sub

[Megalog]

Ok I simplified the code I did earlier over lunch, and tested it.
I'm not sure how your form is laid out, but I'm assuming you want the returned student ID to be in a list or combo box, since I dont think you can pass a rowsource to a text field. if so this will work.

Also added a simple popup message box in the case that the First or Last name was empty, although there's a lot of error checking that could be done besides that. (If First and Last name are entered, but no resulting student ID found, etc)

Good luck,
-Lee

Private Sub txtStudent_ID_AfterUpdate(Cancel As Integer)
With Me![txtStudent_ID]
    If IsNull(Me!cmbFirst_Name) = false AND IsNull(Me!cmbLast_Name) = false Then
       .RowSource = "SELECT tblsSpecialist.Student_ID " & _
            "FROM tblsSpecialist " & _
            "WHERE (((tblsSpecialist.Student_ID) Is Not Null) AND ((tblsSpecialist.FirstName)=cmbFirst_Name) AND ((tblsSpecialist.LastName)=cmbLast_Name));"
    Else
      .RowSource = ""
    MsgBox "Please enter a first and last name", vbOKOnly, "Data Missing"
    End If
    Call .Requery
  End With
End Sub

[margot]

Ok I simplified the code I did earlier over lunch, and tested it.
I'm not sure how your form is laid out, but I'm assuming you want the returned student ID to be in a list or combo box, since I dont think you can pass a rowsource to a text field. if so this will work.

Also added a simple popup message box in the case that the First or Last name was empty, although there's a lot of error checking that could be done besides that. (If First and Last name are entered, but no resulting student ID found, etc)

Good luck,
-Lee

Private Sub txtStudent_ID_AfterUpdate(Cancel As Integer)
With Me![txtStudent_ID]
    If IsNull(Me!cmbFirst_Name) = false AND IsNull(Me!cmbLast_Name) = false Then
       .RowSource = "SELECT tblsSpecialist.Student_ID " & _
            "FROM tblsSpecialist " & _
            "WHERE (((tblsSpecialist.Student_ID) Is Not Null) AND ((tblsSpecialist.FirstName)=cmbFirst_Name) AND ((tblsSpecialist.LastName)=cmbLast_Name));"
    Else
      .RowSource = ""
    MsgBox "Please enter a first and last name", vbOKOnly, "Data Missing"
    End If
    Call .Requery
  End With
End Sub

Thank you so much!! I have not have time to test it, but I will get back to you as soon as I do it. Thanks again, you are the best.