Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Apr 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Simple Access Question about Forms (Access 2000)

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simple Access Question about Forms (Access 2000)

    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.

  3. #3
    New Lounger
    Join Date
    Apr 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Access Question about Forms (Access 2000)

    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!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simple Access Question about Forms (Access 2000)

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •