Move directly to record in recordset Where PrimaryKey=text entered in txtbox. How?

VBKid04
04-15-2004, 10:36 PM
Does an ADODB recordset have any method to move directly to a record specified by the value of the primary key.

A recordset has already been opened returning all records in the Task table.

I have a search textbox where a user can enter a value...

Then What I want is a button which when pressed will do:
-Check value entered in the search textbox for a matching value in the table (the value the user enters will be for example TASK055).

-If value is found, move to and display the record in the recordset that matches the value (there will only be one match as the value is the primary key).


It is mainly the second part (moving to and displaying the record) that I am unsure of.
As the recordset is already open, and the user can browse backwards and forwards, is there any quick method of just shooting the user to that record by matching the value they typed?

Can anyone please help?

msmeth
04-16-2004, 05:30 AM
Does an ADODB recordset have any method to move directly to a record specified by the value of the primary key.

A recordset has already been opened returning all records in the Task table.

I have a search textbox where a user can enter a value...

Then What I want is a button which when pressed will do:
-Check value entered in the search textbox for a matching value in the table (the value the user enters will be for example TASK055).

-If value is found, move to and display the record in the recordset that matches the value (there will only be one match as the value is the primary key).


It is mainly the second part (moving to and displaying the record) that I am unsure of.
As the recordset is already open, and the user can browse backwards and forwards, is there any quick method of just shooting the user to that record by matching the value they typed?

Can anyone please help?

Maybe the Find method, but it can only be used on a recordset opened with a CursorType set to either adOpenKeyset or adOpenDynamic.

If not, you'd have to loop through the recordset.

MKoslof
04-16-2004, 07:16 AM
Does an ADODB recordset have any method to move directly to a record specified by the value of the primary key.

Yes, just use a query. Write a select statement that searches for all records, or individual fields within a record that match the primary key value you specify. This could be a comb box selection, text box, hard-coded, etc.

VBKid04
04-16-2004, 08:23 AM
MKoslof yes I had thought of that, but rather than use a query, I want to move directly to the matching record, so that the user still has access to browse backwards and forwards through the records with the recordset

If I use a Select statement, surely it will go to that one record and the recordset will not be browsable anymore ?

VBKid04
04-16-2004, 10:04 AM
Can anyone provide an example of matching the .Find value to a string specified in a textbox?

I have been messing with it but can't seem to get it working and ready to give up

Thanks

MKoslof
04-16-2004, 06:34 PM
If field1 is numeric, such as a primary key, and X is a dimmed numeric variable:



rs.Open sSQL, cn, adOpenKeyset, adLockOptimistic, adCmdText

rs.Find "Field1=" & x

If Not rs.EOF = 0 Then
msgbox "no records found"
Exit sub
End If

VBKid04
04-16-2004, 07:55 PM
MKoslof what if Field1 is text (and not numeric like you suggested)?


How would your suggested code change ?

MKoslof
04-19-2004, 06:21 AM
like this:



rs.Open sSQL, cn, adOpenKeyset, adLockOptimistic, adCmdText

rs.Find "Field1= '" & x & "'"

If Not rs.EOF = 0 Then
msgbox "no records found"
Exit Sub
End If

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum