Simple Access Question about Forms (Access 2000)

[agrablewski]

Can anyone answer this question? I have a form which displays student information. The primary key is the student's SS number. When I enter the SS #, I want the form to display information in the other fields if the student is already in the database. Do you do this with a macro?

[HansV]

Welcome to Woody's Lounge!

You shouldn't do this by typing into the text box bound to the SSN field - you would be changing the SSN for a student.
Instead, use a separate text box or combo box to locate a student. the easiest way to do this is the Combo Box Wizard:

<UL><LI>Open the form in design view.
<LI>Make sure that the Toolbox is visible.
<LI>Make sure that the Wizard button is "down" (the one with the "magic wand"; by default this is the second button on the Toolbox).
<LI>Add a combo box to the form.
<LI>Select the last (third) option in the first step of the Wizard, then click Next and follow the instructions.[/list]Post back if you need more instructions, or want something else.

[agrablewski]

Hans!
Thank you for your answer, I feel I am being a huge jerk, but unfortunately, this does not solve my problem.
The combo box works fine as long as the student is already in the database. But let's say I have a new student, and I enter their SS# into the combo box, it displays the first student record in the database. I can not erase the information on this student to enter the information for the new student. Is there a field that would look up to see if a student is already in the database and if they are, displays their information, or if not they are not, leaves the fields blank so that new information could be added. I don't know if this is a ridiculously easy or a ridiculously hard question. But thank you in advance!

[HansV]

You don't have to feel like a jerk, many problems aren't solved immediately.

The code generated by the wizard probably looks like this, with the appropriate names substituted:

Private Sub cboSearch_AfterUpdate()
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSN] = '" & Me![cboSearch] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

In this code, cboSearch is the name of the combo box, and SSN is the name of the, uh, SSN field. The code can easily be modified to create a new record if the SSN is not found. I built in a message box to give the user the chance to get out if there is a typo (otherwise each mistake would lead to a new record.)

Private Sub cboSearch_AfterUpdate()
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSN] = '" & Me![cboSearch] & "'"
If rs.NoMatch = False Then
Me.Bookmark = rs.Bookmark
ElseIf MsgBox("Student with SSN " & Me![cboSearch] & " not found." & vbCrLf & _
"Do you want to create a new record with this SSN?", vbQuestion + vbYesNo) = vbYes Then
RunCommand acCmdRecordsGoToNew
[SSN] = Me![cboSearch]
End If
End Sub

You'll have to substitute the name of your combo box and the name of your SSN field.
Note: the underscore _ is a line continuation character. There must be a space before it.

Don't hesitate to post back if this doesn't solve your problem or if you have more questions. That's what the Lounge is for!