Searching Databases

[Valley Boy]

i am trying to search a database using seek

this is the code i am using

Private Sub cmdfist_Click()
prompt$ = "Enter The Row Where You Would Like To Sit."
SearchStr$ = InputBox(prompt$, "Row Search")
datgrand.Recordset.Index = "Row Number"
datgrand.Recordset.Seek "=", SearchStr$

Prompt2 = "Enter The SDeat That You Would Like To Sit In"
searchstr2 = InputBox(Prompt2, "Seat Search")
datgrand.Recordset.Index = "Seat Number"
datgrand.Recordset.seek "=", searchstr2
If datgrand.Recordset.NoMatch Then
datgrand.Recordset.MoveFirst
End If
End Sub

my problem

i want to be able to enter a row number and then a
seat number and the text boxes so the results. the main reason this code is not working is that it is searching the database for the rows e.g row T finding that then seating for the seat number e.g 32 but it is showing the first seat
with the number 32 it comes to whihc is seat 32 but row A i want to be able to enter T 32 and the search displays T 32

thanx in advance

VB

Ok try an SQL statement


Dim sSQL as String
Dim rdatgrand as RecordSet
'
sSQL = "SELECT * FROM rdatgrand WHERE Row = '" _
& Searchstr& & "' Seat = '" & Searchstr2 & "'"
'
set rdatgrand = db.openrecordset(sSQL)
'
If rdatgrand.BOF and rdatGrand.EOF Then
MsgBox "Alias Row Seat doesn't exist"
Exit Sub
End if
.......

Ok the "*" in sSQL means get all fields, ya can modify this to only get the fields you require.
Hope it Helps

[Valley Boy]

I am getting a error message when I use this code
please can you help, i am using this when a press a
command button :-

Dim sSQL As String
Dim rdatgrand As Recordset


Private Sub Command1_Click()
prompt$ = "Enter The Row Where You Would Like To Sit."
Searchstr$ = InputBox(prompt$, "Row Search")

Prompt2 = "Enter The SDeat That You Would Like To Sit In"
Searchstr2 = InputBox(Prompt2, "Seat Search")

sSQL = "SELECT * FROM rdatgrand WHERE Row Number = '" _
& Searchstr$ & "' Seat Number = '" & Searchstr2 & "'"

Set rdatgrand = db.OpenRecordset(sSQL)

If rdatgrand.BOF And rdatgrand.EOF Then
MsgBox "Alias Row Seat doesn't exist"
Exit Sub
End If

If datgrand.Recordset.NoMatch Then
datgrand.Recordset.MoveFirst
End If
End Sub

I am getting a message saying
object required on the line saying

Set rdatgrand = db.OpenRecordset(sSQL)

thanx for your time
Vb

[LG]

What kind of error you receive?

Does Row Number and Seat Number are integers or strings?
If there are integer, than you don't need to surround them with '.
If it's string, than try this:

sSQL = "SELECT * FROM rdatgrand WHERE [Row Number] =" & chr(34) & Searchstr$ & chr(34) & " and [Seat Number] = " & chr(34) & Searchstr2 & chr(34)

The Field Titles "Row Number" and "Seat Number" should not include blanks in them. Therefore they must be either RowNumber or Row_Number????

If this ain't the problem, then post the error message. LG is correct numerics shouldn't be inclosed in brackets.

Persumably a correct value is something like A20, for row A seat 20. You would wrap the A in "", and the 20 would stand alone

[Valley Boy]

I am still getting an error message which says :-

object required

the error message is on this part of your code

set rdatgrand = db.openrecordset(sSQL)

please help

[LG]

This error message means that you forgot to declare an object. From your code I don't see you declaring a database.

Dim db As Database
Set db = OpenDatabase("Nwind.mdb") 'check all parameters

[Valley Boy]

Thanx for that but I am getting a new error message
when I put your code in saying:-
Syntax error (missing operator) in query expression 'Row = 'A' Seat = '23".

please help

Vb

If Seat is an integer or another numeric type...don't enclose it in quotations...will look for a string...wrong wrong wrong

[LG]

It should be:
Row = 'A' and Seat = 23 (don't forget AND keyword )

Good call LG, forgot to mention the AND word. Gee just spent the past few days building SQL statements too