Noob Question

[oliveeue]

I know enough about Access to barely get by. I have designed a database for registering participants. I need to search as the last name is entered to see if they have a previous record, and then bring up a list if there is more than one person with the same last name.

When I get to the module set up with coding, I am reading Chinese.

I really need some help with this. I know there is code and I have found codes but I know I have to put my information into that code so the database knows what to search, but this is totally foreign as to what I need to code or replace as my code.

I would be greatful for any help, I know this is probably very basic

Thanks,

Ellen

 

[Molly Coddlez]

Ok, I think i know what you're after. I'd create a query on the table showing only records for people with the specified last name
So lets assume that your data is stored in "tblParticipants", that your input comes from a textbox called txtLastName and that you wish to output the list to a listbox called lstResults

Sub sCheckLastName()
   Dim strSQL As String, rstParticipants As Recordset, blnFlag as Boolean
   blnFlag = False
   'creating the SQL for the query, I used the LCase() function to avoid it not finding names due to case based input errors
   strSQL = "SELECT * FROM tblCustomer WHERE (((LCase([LastName]))=LCase(" & Chr(34) & txtLastName & Chr(34) & ")));"
   Set rstParticipants = CurrentDB.OpenRecordset(strSQL, dbOpenDynaset)
   If rstParticipants.RecordCount > 0 ' so if there are records with the specified last name.....
      lstResults.RowSource = strSQL ' so we load the SQL for the query into the listbox
      lstResults.Update
      'everything else you need to do goes here then


   Else ' so i guess you just want the textboxes on the form to populate the table now that there are no matching last names
      blnFlag = True ' i have put a boolean flag here instead of the code, the table you need to input to
                     ' is still being held at this point by rstRecords, you will need to close the query before
                     ' Access will allow you to enter new data into the table
   End If
   rstParticipants.Close           'this and the line after are important when dealing with recordset 
   Set rstParticipants = Nothing   'variables, to avoid clogging your memory and to stop Access holding tables
      
   If blnFlag=True ' now we populate the table i guess   
      Dim rstTable As Recordset
      Set rstTable = CurrentDB.OpenRecordset("tblParticipants")
      With rstTable
         If Not .Eof then .MoveLast
         .AddNew
         !FirstName = txtFirstName
         !LastName = txtLastName
         !FavoritePokemon = txtPokemon
         !BraSize = txtBraSize              'you get the idea..............
         .Update
         .Close
      End With
      set rstTable = Nothing
   End If

End Sub

The only real drawback to doing it this way is having to populate the table by code instead of relying on the auto forms and/or bound text boxes, I have assumed that you don't know how to do this too and included the neccesary code for you to work with in the "Else" statement.

Hope this is useful