Searching Database Records

[KizMar]

Pop Quiz: What's wrong with this code? When I compile and run it, it tells me "Object variable or With block variable not set, then it points at the ">>>>>"'s.

Private Sub cmdSearch_Click()

Dim strSQL As String
Dim strType As String
Dim strName As String
Dim strColor As String

strName = txtAName.Text
strType = lstType.Text
strColor = lstColor.Text

If strName = "" And strType = "" And strColor = "" Then

MsgBox "Please enter search criteria.", ... vbExclamation, "Search Results"
lstType.SetFocus

Else

strSQL = "SELECT Animal.type, Animal.name, ... Animal.color " & _
"FROM Animal " & _
"WHERE Animal.type = " & strType & _
" OR Animal.name = " & Trim(strName) & _
" OR Animal.color = " & Trim(strColor)

datAnimal.RecordSource = strSQL
datAnimal.Refresh

>>>>>>> If datAnimal.Recordset.EOF Then
MsgBox "There are no records that match your criteria.", vbExclamation, "Search Results"
End If

End If

End Sub
___________________________

I'm trying to write a form that I can search by either animal name, color, or type and have it display the results in a box next to it. I'm not sure what object to use for the box... I need help!!! =)

 

[zgtrman]

The answer to this question requires a bit more input from you.
1. How are you accessing the database?
2. Have you declared a connection string in the form load event?
3. where is the recordset.open event?

zgtrman To effectively improve in the efficiency of your performance, one must be proficient in the implementation of a positive mental attitude.

 

[CCLINT]

Add a data control to your form and name it "datAnimal"

Then, in the properties window, create a connection to your database by setting the connection string property (or clicking on the build button next to it). [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[KizMar]

I'm accessing the database with a Data Control called "datAnimal". It's an Access Database that I created. The properties of the Data Control are set as such: DatabaseName: "D:\ITT\Visual Basic II\Group Project\humane_society.mdb", Connect: "Access 2000;", RecordSource: Animal.

Do I have to create a connection string even though I'm connecting that way?

 

[CCLINT]

>Do I have to create a connection string even though I'm connecting that way?

Not with a DAO data control, which is apparently what you are using (the connection string property mentioned was for an ADO data control).
You have set the needed properties to make the connection. [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[Merlijn]

I have to agree with zgtrman.
Where is the .open statement?
If you do not open a recordset, you can't test its .EOF property.

Merlijn is my name and logic is my game...

 

[CCLINT]

>I have to agree with zgtrman.
>Where is the .open statement?
>If you do not open a recordset, you can't test its .EOF property.


uh, Merlijn, you do not use the .Open statement with the data control.
It opens the recordset for you when refreshed.... [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}