Searching through MS Access Db

[Guest_imported]

Here's my problem:
I have written some code for a search command button that when pressed prompts a user to enter a person's name, anything else prompts and error message and returns to the Input box. Once the user enters the person's name and clicks OK, the program must access a database from MS Access, named "USER". It must then search through the records for the name entered into the Input box. If found then return "Person Found" otherwise "Person not found" Now, I am having difficulty writing the code for the search to take place. Can you help?

 

[tane]

hi

you could use
recordset.findfirst "User=" &inputbox.text
**** Keep it simple ***

 

[Guest_imported]

Thanks Tane for your help.

 

[Guest_imported]

The code did not seem to work. Here is the complete function, could you verify it.
Private Sub cmdSearch_Click()
Dim ok As Boolean
Dim marc As Integer

Do
userInput = InputBox("Enter Name of Person:", "DATABASE SEARCH&quot
ok = False
If userInput = "" Then
MsgBox "Please enter a name!"
ElseIf IsNumeric(userInput) Then
MsgBox "Invaild Character!"
Else
ok = True
End If
Loop Until ok

Data1.RecordSource = "Select * from Students where Name = UserInput"

Do

If Data1.Recordset = userInput Then
MsgBox "match found"
marc = 1
Else
Data1.Recordset.MoveNext
End If
If Data1.Recordset = EOF Then
Data1.Recordset.MoveFirst
MsgBox "Match not Found"
marc = 1
Else
Data1.Recordset.MoveNext
End If
Loop Until marc = 1

MsgBox "You entered:" + userInput
End Sub

 

[CajunCenturion]

May I suggest changing

Data1.RecordSource = "Select * from Students where Name = UserInput"

to
Data1.RecordSource = "Select * from Students where Name = '" & UserInput & "'"

Good Luck

 

[Guest_imported]

CajunCenturion or whomever,

I seem to be having difficulty searching through my database. The MoveNext command causes an error and I do not know why. Maybe you can help, here is my code.


marc = 0

MyString = Data1.Recordset.Fields("Name&quot.Value
Do Until marc = 1
If MyString = userInput Then
MsgBox "Match Found"
marc = 1
Else
Bin = Data1.Recordset.MoveNext
MyString = Bin
End If

If Data1.Recordset.EOF = False Then
Data1.Recordset.MoveFirst
MsgBox "Match not Found"
marc = 1
Else

End If
Loop

If this problem with MoveNext continues, can you suggest another method for searching through a database to compare each field to data in an Inputbox. Thanks a bunch!

 

[Foada]

You have several ways to do what you are trying to do.

If you open a recordsorce with a specific SQL statement such as

Data1.RecordSource = "Select * from Students where Name = '" & UserInput & "'"

and it doesn't return anything there is no need to move through it as the requested field was not present.

If you open the recordsource with a more general SQL statement such as

Data1.Recordset.MoveFirst
Do Until Data1.Recordset.EOF
MyString = Data1.Recordset.Fields("Name&quot.value
If MyString = userInput Then
MsgBox "Match Found"
Exit Sub
Else
Data1.Recordset.MoveNext
End If
Loop
MsgBox "Match Not Found"

I hope this helps.
Data1.RecordSource = "Select Name from Students"

It will return all the data in the Name field as your recordset. You could then move through the recordset and attempt to find your requested Name. Anything is possible, the problem is I only have one lifetime.

 

[Foada]

Sorry that post got messed up it should read:

You have several ways to do what you are trying to do.

If you open a recordsorce with a specific SQL statement such as

Data1.RecordSource = "Select * from Students where Name = '" & UserInput & "'"

and it doesn't return anything there is no need to move through it as the requested field was not present.

If you open the recordsource with a more general SQL statement such as

Data1.RecordSource = "Select Name from Students"

It will return all the data in the Name field as your recordset. You could then move through the recordset and attempt to find your requested Name.

Data1.Recordset.MoveFirst
Do Until Data1.Recordset.EOF
MyString = Data1.Recordset.Fields("Name&quot.value
If MyString = userInput Then
MsgBox "Match Found"
Exit Sub
Else
Data1.Recordset.MoveNext
End If
Loop
MsgBox "Match Not Found"

I hope this helps. Anything is possible, the problem is I only have one lifetime.

 

[Guest_imported]

Hey foada, the code worked great. Thanks so much for your help.