Access 2003

[svn]

Hi,

I have not worked with MS Access at all before, so I apologize for asking a "simple" question. But this is what I am trying to do:
I made a form in Access where a user puts in a serial#. The hits a command button "next" which should do the following: open a calrecord table. Find this serial# typed by the user. If not found then this serial# is appended to the table. But if found, then i want to get more info from this record in the table. (The calrecord table has a bunch of fields with a bunch of info in it). I want all this info it found to appear in a text box.

So far I have:

Private Sub UF_Next_Click()
Dim TblCalRec As Recordset
Dim Cdb As Database
Dim SNbr As String
Dim a As String
Dim intFirst As Integer

SNbr = UCase(UF_SerN)
Set Cdb = CurrentDb
Set TblCalRec = Cdb.OpenRecordset("CalRecord", dbOpenTable)

intFirst = TblCalRec.RecordCount
With TblCalRec
.MoveFirst

Do While Not .EOF
If ![Serial #] = SNbr Then
MsgBox "Found"
End If
TblCalRec.MoveNext
Loop
End With

end sub

My questions are:
Is there a way to make this search go faster? I tried using the "findFirst" function but that kept giving me an error.
Do i use the bookmark function to find out more info from this record? What is the bext way to do that?

Thanks a ton. ~svn

[Laurel]

Make your recordset look for the ONE record with that serial #.

(I'm assuming you've guarded against duplicate serial #'s in this database and [Serial #] is text.)

Like this:

Private Sub UF_Next_Click()
Dim TblCalRec As Recordset
Dim Cdb As Database
Dim sql as String

sql = "Select CalRecord.* from CalRecord where CalRecord.[Serial #] = '" & UCase(Uf_SerN) & "';"

Set Cdb = CurrentDb
Set TblCalRec = Cdb.OpenRecordset(sql)
With TblCalRec
  If .Recordcount = 0 then
   .AddNew
   ![Serial #]
   .Update
  Else
    msgBox "Found"   
  End If
End With

End Sub

Hope this helps!
Laurel