Please HELP filtering a table & displaying result

[passmaster16]

I've been trying to make an application that will import an Access Database and give the user a chance to search the database according to a unique id number. I'm using ADO Data control and ADO Datagrid. I have one text box and a command button that performs the operation when it is clicked. So far I've been able to display the whole table in the datagrid but I want to search the records using the id number and display matching results in the datagrid. This has been a problem. I haven't been able to accomplish this. I used the filter property to search the records and I know that it is working because I can display the results to the immediate window; however, that is not very practical not to mention ugly so I want those results put into a grid. I've talked to many different people but have not yet got a correct solution. Some people suggested using an SQL statement to search instead of the filter property and update the data control but I get errors when using their syntax.

HERE is the code I have so far.

Option Explicit

Private Sub cmdclear_Click()
txtuutpart.Text = ""
txtaccpart.Text = ""
txtuutpart.SetFocus
End Sub
*****************************
Private Sub cmdquit_Click()
End
End Sub
*****************************
Private Sub cmdsubmitpart_Click()

Dim struutdata As String
Dim strfield As String
Dim junktwo As Recordset
Dim dbsbart As Database
Dim rstacc As Recordset


Set dbsbart = OpenDatabase("bartacc.mdb")
Set rstacc = dbsbart.OpenRecordset("BartAdtranz", dbOpenDynaset)

struutdata = txtuutpart.Text

Set junktwo = FilterField(rstacc, "UUTPartNumber", struutdata)

End Sub
***********************
Function FilterField(rsttemp As Recordset, strfield As String, strfilter As String) As Recordset

rsttemp.Filter = strfield & "='" & strfilter & "'"
Set FilterField = rsttemp.OpenRecordset


End Function

I would like to find a way to take that FilterField recordset and display it in the grid. Also I'm using VB 6.0 working model edition. ANy assistance is appreciated

[Gerald]

You say you are using the ADO data control and the ADO datagrid, yet the methods you are using to create you recordsets are using DAO methods. If you are truly using ADO then all you need to do is set your data control's recordset.filter property to a valid filter string and the ADO datagrid will "auto-magically" be refreshed. If you are using DAO you can set you data control's recordsource property to a valid SQL statement. Then use the data control's refresh method to update your grid.

ADO...

Adodc1.Recordset.Filter = "PartID=99"

DAO...

Data1.RecordSource = "SELECT * FROM Parts WHERE PartID=99"
Data1.Refresh

--Gerald

[jbuck]

Hi passmaster16'

The following code works for me! it can easily be adapted to any field in the Access Database.
Private Sub SearchCmd_Click()
Dim mybkmark As Variant
GlobalFound = "Yes"
' Unload CaseScr
' Unload PartScr

Do While True

'Set a bookmark to the current record.
mybkmark = datPrimaryRS.Recordset.Bookmark

strLastName = LastNameSrchText.Text
strPatIDNo = MedRecSrchText.Text

' can't Enter Both
If strLastName <> "" And strPatIDNo <> "" Then
MsgBox "Can't Enter Both Medical Record Number And Last Name", vbOKCancel
Exit Sub
End If

' both Can't Be Blank
If strLastName = "" And strPatIDNo = "" Then
MsgBox "Can't Search Must Have Either Last Name Or Medical Record Number To Search"
Exit Sub
End If

' If strSearch = "" Then Exit Do ' If No Value Exit!

If strLastName <> "" Then
'Search forward for the CustomerID
datPrimaryRS.Recordset.Find "LastName = '" & strLastName & "'", 0, adSearchForward

'If the record isn't found, we will be at the end of the recordset.
'So, reposition the recordset back to the record we came from and search backwards.
If datPrimaryRS.Recordset.EOF Then
datPrimaryRS.Recordset.Bookmark = mybkmark
datPrimaryRS.Recordset.Find "LastName = '" & strLastName & "'", 0, adSearchBackward

'If we don't find the record this time, it doesn't exist.
'So, reposition the recordset back to the record we came from and tell the user.
If datPrimaryRS.Recordset.BOF Then
datPrimaryRS.Recordset.Bookmark = mybkmark
MsgBox "Record Not Found"
End If
End If
End If

If strPatIDNo <> "" Then
'Search forward for the CustomerID
datPrimaryRS.Recordset.Find "PatientID = '" & strPatIDNo & "'", 0, adSearchForward

'If the record isn't found, we will be at the end of the recordset.
'So, reposition the recordset back to the record we came from and search backwards.
If datPrimaryRS.Recordset.EOF Then
datPrimaryRS.Recordset.Bookmark = mybkmark
datPrimaryRS.Recordset.Find "PatientID = '" & strPatIDNo & "'", 0, adSearchBackward

'If we don't find the record this time, it doesn't exist.
'So, reposition the recordset back to the record we came from and tell the user.
If datPrimaryRS.Recordset.BOF Then
datPrimaryRS.Recordset.Bookmark = mybkmark
MsgBox "Record Not Found"
End If
End If
End If

' Get The Cases Assigned and Load a Combo Box

EncounterCboBox_Load (txtFields(0).Text)

Exit Do
Loop
EnableTextBoxes ' Let Them change The record

' Clear The Search boxes

MedRecSrchText.Text = ""
LastNameSrchText.Text = ""
Exit Sub
AddErr:
MsgBox "Data Error"
End Sub


datPrimaryRS is an ADO Recordset. I got the basis of this code from a Microsoft tech. You'd think they would have something like this in their Illustrous MSDN online wouldn't you. I had to open a problem notification.
Hope this helps let me know.
JimB

[karunakaran]

hi,
If you are using VB6.Try this way.VB6 we have the environmetn,connection and command objects to do all these things.

make a connection to the database and test it. Then create a command using the SQL quiery .

Then set the command to the datagrid.

if you want still in detail please send me a mail.

thanks
karun

[This message has been edited by karunakaran (edited 01-05-2000).]