Noob Question

O

oliveeue

Joined
Aug 2, 2010
Messages
1
Reaction score
0
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
 
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

Code: 
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
 
Back
Top