Search Box

[rrueger]

Hello,
General Info.
I have a table with Account Number and Money in database backend

I have an unbound form for input in the front end. I need to add a button, that when pressed opens another form. This form has one text box which a user can enter an account number to see if the account is already in the table. I can't get it to work. Please help.

Private Sub CmdFind_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = DBEngine.Workspaces(0).OpenDatabase("path")
Set rst = db.OpenRecordset("tblSaveEntries")

If txtAccSearch = rst!AccountNumber Then

MsgBox ("A save has already been entered for this account"), , "Save Entered"
Else
MsgBox ("No Record Found")
Me!txtAccSearch = ""
rst.Close
Set rst = Nothing
db.Close
Exit Sub
End If

 

[apr pillai]

Modify the Code as below:

Private Sub CmdFind_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = DBEngine.Workspaces(0).OpenDatabase("path")
Set rst = db.OpenRecordset("tblSaveEntries")

rst.FindFirst "AccountNumber = '" & txtAccSearch & "'"

If Not rst.NoMatch Then

     MsgBox ("A save has already been entered for this account"), , "Save Entered"
Else
   MsgBox ("No Record Found")
   Me!txtAccSearch = ""
   rst.Close
   Set rst = Nothing
   db.Close
End If 
Exit Sub

 

[rrueger]

Thanks so much
So if I had for instance two different fields that the number could be under "Reference Number or Account Number" Would the code be this? (just guessing).. and I did mention I'm using Access 2003.

Private Sub CmdFind_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = DBEngine.Workspaces(0).OpenDatabase("path")
Set rst = db.OpenRecordset("tblSaveEntries")

rst.FindFirst "AccountNumber = "" & txtAccSearch & "'" Or _
rst.FindNext "ReferenceNumber = "" & txtAccSearch & ""

If Not rst.NoMatch Then

     MsgBox ("A save has already been entered for this account"), , "Save Entered"
Else
   MsgBox ("No Record Found")
   Me!txtAccSearch = ""
   rst.Close
   Set rst = Nothing
   db.Close
End If 
Exit Sub

Oh, and If I wanted to get fancy, and return a value in the MsgBox (for example "A save has already been entered of $3000 for this account", how would I write that to have it say the amount entered for the corresponding account (named Saved).

 

[rrueger]

I'm getting an object cannot be used with event error when I tried your code.