Lookup combo box

[AndrewG]

Hello,

I would like to create a combo box in which one could enter a student identification number(not the key field) and display the whole record if the student is in the database. If the student is not in the database, I would like to display a message box saying something such as "Student #... is not in the database, would you like to enter a new record for this student?"

The user would then be able to enter a new student record.

I have experimented with scripts to do this, however they stop working when I append new records to the table.

I would be thankful for any assistance.

 

[saharlan]

Suggestion

Off the top of my head...... try something like this...

1) In the properties of you combo box, go to the data tab and check limit to list.

2) On your event tab select "on got focus" and create an event procedure and paste something like this in it.

yourcomboboxname.requery

this will requery the combobox dataset after the new student is
updated so it will show up in the list.

3) On your event tab select "on not in list" and create an event procedure and paste something like this in it.

Dim intAnswer As Integer

intAnswer = MsgBox("Student not in list, Would you like to add a new StudentID?", vbYesNo + vbQuestion, _
"New Student")

Select Case intAnswer
Case vbYes
yourcombobox = Null
DoCmd.OpenForm "AddNewStudent"
Case vbNo

End Select

You will have to create and add new student form to
append a new student to your table.

When you close the new student form, set the focus back to
the combobox for the student number check.

Hope this helps