CODE TO SEARCH DATAGRID FOR MATCHING RECORD

[Hiccup]

My setup in VB6 is a DataGrid connected via an Adodc to an Access2K Table "SortClients" and the Field I'm searching for matching Record(s) is "Clients."

This is the code I'm using that gives me a run-time error msg of "Object Required":

Private Sub Command1_Click()
prompt$ = "Enter Client's Name"
SearchStr$ = InputBox(prompt$, "Search for Possible Conflicts")
SortClients.Recordset.Seek "=", SearchStr$
If SortClients.Recordset.NoMatch Then
SortClients.Recordset.MoveFirst
End If
End Sub

The line that's highlighted when I debug is:

SortClients.Recordset.Seek "=", SearchStr$

Can anyone tell me what I might ave wrong with the code?

Thanks in advance!

 

[Golom]

A couple of possibilities

1. Your recordset does not support the "Seek" method.
You can check the "Supports" property to find out if it does.

2. You haven't set an index.
Normally, you must specify an index to use the "Seek" method.

3. Incorrect Seek Format
You have formatted the Seek for a DAO Seek command. ADO Seek has the format
recordset.Seek KeyValues, SeekOption

Try using the "Find" method rather than Seek.

 

[Hiccup]

Thanks, Golom; I'll return to the drafting table and try again with your suggestions!

 

[Hiccup]

Golom> Thanks for the info, but I tried the "Find" function in the code below and still get the "Object Required" run-time error:

Private Sub Command1_Click()
prompt$ = "Enter Client's Name"
SearchStr$ = InputBox(prompt$, "Search for Possible Conflicts")
SortClients.Recordset.Find "=", SearchStr$
If SortClients.Recordset.NoMatch Then
SortClients.Recordset.MoveFirst
End If
End Sub

I appreciate any other suggestions from you or anyone else!

 

[Hiccup]

I still can't get this search routine to work and would appreciate any suggestions.

The Access2k mdb Table Field (and the VB6 DataGrid Column) is Client. It seems to me that I need that Field name somewhere in the above code. Is that what is causing the "Object Required" run-time error?

If that Field name is what's missing in the code, can someone tell where it goes and how to set it up?

Thanks!

 

[Glasgow]

I don't know much about ADODC versus ADO but in ADO, you would say:

SortClients.Recordset.Find "ClientName ='", SearchStr$ & "'",,adbookmarkfirst

If SortClients.Recordset.eof then
'find failed

 

[Glasgow]

Oh - sorry, getting confused between ADODC & ADOX - you are just talking about a data control. Comments in my last post should still be appropriate.

 

[Glasgow]

Except it should read

SortClients.Recordset.Find "ClientName ='" & SearchStr$ & "'",,adbookmarkfirst

Sorry

 

[encata]

This is taken from MSDN Library, I think you have to Refer
the Field Name "Client" somewhere in your code.


Seek Method Example for DAO3.x

This example demonstrates the Seek method by allowing the user to search for a product based on an ID number.

Sub SeekX()

Dim dbsNorthwind As Database
Dim rstProducts As Recordset
Dim intFirst As Integer
Dim intLast As Integer
Dim strMessage As String
Dim strSeek As String
Dim varBookmark As Variant

Set dbsNorthwind = OpenDatabase("Northwind.mdb")
' You must open a table-type Recordset to use an index,
' and hence the Seek method.
Set rstProducts = _
dbsNorthwind.OpenRecordset("Products", dbOpenTable)

With rstProducts
' Set the index.
.Index = "PrimaryKey"

' Get the lowest and highest product IDs.
.MoveLast
intLast = !ProductID
.MoveFirst
intFirst = !ProductID

Do While True
' Display current record information and ask user
' for ID number.
strMessage = "Product ID: " & !ProductID & vbCr & _
"Name: " & !ProductName & vbCr & vbCr & _
"Enter a product ID between " & intFirst & _
" and " & intLast & "."
strSeek = InputBox(strMessage)

If strSeek = "" Then Exit Do

' Store current bookmark in case the Seek fails.
varBookmark = .Bookmark

.Seek "=", Val(strSeek)

' Return to the current record if the Seek fails.
If .NoMatch Then
MsgBox "ID not found!"
.Bookmark = varBookmark
End If
Loop

.Close
End With

dbsNorthwind.Close

End Sub


Why you worry? Life is too short to worry.

 

[Hiccup]

Thanks Glasgow, I entered:

SortClients.Recordset.Find "Client ='" & SearchStr$ & "'",,adbookmarkfirst

but that gives me the "Object Required" run-time error as well.

This seems like such a simple search to do on an adodc/DataGrid, but for a couple of months the many suggestions I've received haven't worked. I'm at a complete loss!!

Thanks anyway!

 

[Glasgow]

On further investigation...

Are you sure the data control has a recordset property!? Is it offered to you by intellisense after you type "SortClients.R"?

I just assumed from your code that such a property must exist but I don't use data controls so I just missed this possibility. Should it not be RecordSource? I also assume that SortClients is the name of the data control?

If you still have problems, it may be worth posting some more code showing how the recordsource is assigned - unless this is done at design time.

 

[Glasgow]

Another option is to try the grid's DataSource property:

DataGrid1.DataSource.Find "Client ='" & SearchStr$ & "'",,adbookmarkfirst

assuming your grid control is called DataGrid1

 

[Hiccup]

Thanks Glasgow; I think we're getting closer. I tried this suggestion of yours:

DataGrid1.DataSource.Find "Client ='" & SearchStr$ & "'",,adbookmarkfirst

and got a different message this time. It was the Compile Error: "Method or data member not found." and the word "Find" was highlighted when I debugged it.

It doesn't like "Find" or something else at this point.

To answer some of your questions:

The Data Connection to the Access2K mdb Sort Query "SortClients" is Adodc1. The "Test Connection" worked fine. The Adodc1 RecordSource is set to "SortClients."

To keep the Clients alphabetically sorted in my VB DataGrid by Client Name I created the Sort Query "SortClients" in Access and it is a Sort Query of the mdb Table "Clients" containing all the Fields of the Clients Table.

The DataGrid on my VB Form is DataGrid1 and its DataSource is Adodc1.

When viewing the DataGrid during run-time in VB, the Grid works fine and shows all of the Records in the mdb "Clients" Table of Access. And, when adding or deleting Records (from different VB "Add Client"/"Delete Client" Forms) as necessary, the VB Grid shows the changes and the Access Table is also updated accordingly. So I believe my Adodc and DataGrid are set up correctly and are working.

Now, at least, I'm not getting the "Object Required" run-time error with your suggestion of the:

DataGrid1.DataSource.Find "Client ='" & SearchStr$ & "'",,adbookmarkfirst

but I am getting this new Compile error about the Method/Data Member missing. Looking at the properties of my DataGrid1, however, I do see that there is no entry in the "DataMember" Field of the Properties Box. I didn't think I needed an entry there since everything seems to be working between the Adodc & DataGrid on my other "Non-Search" Forms in VB that contain the same Adodc1 and DataGrid1..

When creating my Search Form, I merely copied and pasted the Adodc1 and DataGrid1 Controls from my main "Data Viewing" Form and added three Command Buttons "Search" (which has the "Click" code in issue), "Print" (for printing the search results if I ever get the search to work) and a "Cancel" Button (for exiting the Search Form.)

Appreciate any further thoughts.

Thanks in advance!!

 

[Glasgow]

OK, sorry, I was hoping Datasource would be treated as a recordset object.

While I understand that SortClients is the name of a query in the access database, I assume that it is also declared within your VB code or you would not be able to reference it directly. Is it declared as ADODB.Recordset? If so then

SortClients.Find etc should do the trick without any reference to "Recordset".

e.g.

Dim SortClients as New ADODB.Recordset
With SortClients
.Open etc
.Find "Client ='" & SearchStr$ & "'",,adbookmarkfirst




If not, it would be interesting to see the code that declares and opens SortClients within VB.

Given the apparent timing of our posts, I suspect we are in very different time zones (I am in UK). I am going to be out of action for 4 days as of the end of today (back Tuesday) but may get a chance to check on things tomorrow.

Good luck.