Search?

[rinoaheartilly]

I'm designing a program to be used in bird shows. (If u don't kno, don't ask, it's a loooong story )
The entry information is saved in an access database, and i've linked it up. But now I need to search through the data for certain names/class numbers. How can I do this?

[Arbiter]

Either

a) SQL

b) Write some code to loop through each record individually scanning the data for the search string.

The latter is the more flexible, but more hassle to write.

[Wokawidget]

Ok, say you wanted to select all the types of birds in the show who are over 17 feet tall. The Select statement would be something like:

SELECT Birds.Type FROM Birds WHERE Birds.Height > 17

Where Birds is the table which contains details of the birds. Although the above statement would select the same type twice, so if we add the Distinct command into the above statement this will only return one entry for the type of bird, even if there are many birds of that type over 17 feet:

SELECT DISTINCT Birds.Type FROM Birds WHERE Birds.Height > 17

You could search for all all birds with the name Henry:

SELECT * FROM Birds WHERE Name = 'Henry'

Does this answer your question, or are you confused, I know I am

[Wokawidget]

Here's an example of selecting some birds from the database. Obviously I have no idea what your fields or table names are, so I made my own up:

Private Sub SearchDatabase()
Dim adoConnection       As ADODB.Connection
Dim adoRecordset        As ADODB.Recordset
Dim strDataSource       As String
Dim strSQL              As String
Dim strMessage          As String
On Error GoTo ErrorHandler
    strDatabaseLocation = "C:\Woof\Dog\Weasel\Birds.MDB"
    Set adoConnection = New ADODB.Connection
    With adoConnection
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDataSource & " ;Persist Security Info=False"
        .Open
        strSQL = "SELECT * FROM Birds WHERE Name = 'Henry'"
        Set adoRecordset = .Execute(strSQL)
    End With
    With adoRecordset
        If Not .EOF Then
            Do While Not .EOF
                strMessage = "Bird Details: " & vbCr & vbCr
                strMessage = strMessage & "Height: " & .Fields("Height") & vbCr
                strMessage = strMessage & "Color: " & .Fields("Color") & vbCr
                'etc
                MsgBox strMessage, vbCritical, "Details"
                .MoveNext
            Loop
        Else
            MsgBox "No birds found called Henry!", vbCritical, "Birds Called Henry"
        End If
        .Close
    End With
    Set adoRecordset = Nothing
    adoConnection.Close
    Set adoConnection = Nothing
    Exit Sub
ErrorHandler:
    strMessage = "Number: " & Err.Number & vbCr
    strMessage = strMessage & "Description: " & Err.Description & vbCr & vbCr
    strMessage = strMessage & "Source: " & Err.Source
On Error Resume Next
    adoRecordset.Close
    Set adoRecordset = Nothing
    adoConnection.Close
    Set adoConnection = Nothing
    MsgBox strMessage, vbCritical, "Error"
End Sub

This uses Microsoft ActiveX Data Objects 2.x

It basically connects to the database, queries it on birds called Henry then displays their details in a msgbox one by one. If no birds are found then a msgbox is display to notify the user.

Does this make sense?

[rinoaheartilly]

SQL??? I'm only a newbie don't forget. I don't think u understand, my fault. I'll upload what it looks like so far, if i can. I need to be able to search through the database. How???

[rinoaheartilly]

damn, it won't upload. I'll put it on my home page, gimme a few hours to upload it, lol, i'm using the skool's slow connection.

It's starting to make sense. The thing is, i missed a lot of work, cos i was ill, so i'm finshing it difficult to grasp some things. But I'm getting there slowly.

[Wokawidget]

If you post your database here I can see what the structure is then I can write a small bit of code to help you understand how to search the database for exactally what you want.

A-Level computing is like a bad fortnight in a hot air ballon

[Wokawidget]

When you add a post, like this one, just click on the browse button below this text box and select your MDB database file. That should upload it withy your next post...Nice web site by the way. What are those 2 awards all about?

[rinoaheartilly]

tell me about it.

I'm having major problems here, u should have brought my lappy in. Whenever i try to open the file, the blutty (don't ask) thing crashes The damn file won't upload, to angelfire cos they're having technical problems. I don't get home til 2.15, so i'll hafta post it up then Damn this college, the machines r hopeless I'll post it up later ok? There's too much for me to ty0pe out, besides, i can't remember all of it. Will u b on later?

[Wokawidget]

I will be on until 5pm, when I finish work
You could always email it to me at:

[email protected]

Speak to you soon.

[rinoaheartilly]

I've gotta redo it!!! I'm so mad The coll computer network picked up a virus, and it's damaged all of my work!!! I'm P!$$ed off! I can't believe it!!!

[Wokawidget]

I am going to write a V small database and code that will give you an idea of how to search for things using a database...
I'll post it here in about 30 minutes. Is that OK?

[rinoaheartilly]

yeah, thanks a lot , i'll e-mail u the code when i'm done ok? Shall i attatch my form?

[Wokawidget]

Here it is...Just open and run the project file.
There is a small database there too. I know the database is completely wrong and the stuff is made up, but it will show you how to search it...

[Behemoth]

If you're using Access, compose the query you require and then view it in SQL mode. It'll teach you a great deal...

[rinoaheartilly]

This is the code i've got so far, i'll post a screeny of it now.

Private Sub cmdAdd_Click()
    Dim iReply As Integer
    'let the user add a new record
 
    'confirm addition first
    iReply = MsgBox("Do you want to add a new entry?", vbYesNo + vbQuestion, "Add Entry")
    If iReply = vbNo Then
        Exit Sub
    End If
 
    'disable the add, delete and edit buttons
    cmdAdd.Enabled = False
    cmdDelete.Enabled = False
    cmdEdit.Enabled = False
 
    'enable the cancel and save buttons
    cmdCancel.Enabled = True
    cmdSave.Enabled = True
 
    'disable the navigation
    DisableNavigation
 
    'first unlock the text boxes
    UnlockFields
 
    'now add a new record to the table
    Adodc1.Recordset.AddNew
 
    'move to the owner text box for data input
    txtOwner.SetFocus
 
End Sub
 
Private Sub cmdCancel_Click()
    Dim iReply As Integer
    Dim IRecordPos As Long
 
    'confirm cancel before commiting
    iReply = MsgBox("Cancel changes made?", vbYesNo + vbQuestion, "Cancel edits")
    If iReply = vbNo Then
        Exit Sub
    End If
 
    'update the table
    Adodc1.Recordset.CancelUpdate
 
    'enable add, delete and edit
    cmdAdd.Enabled = True
    cmdDelete.Enabled = True
    cmdEdit.Enabled = True
 
    'disable the save and cancel buttons, as we've finished with them
    cmdSave.Enabled = False
    cmdCancel.Enabled = False
    
    'lock the textboxes
    LockFields
    
    'enable the navigation
    EnableNavigation
    
    'refresh
    IRecordPos = Adodc1.Recordset.AbsolutePosition - 1
    Adodc1.Refresh
    Adodc1.Recordset.Move IRecordPosition, adBookmarkFirst
 
End Sub
 
Private Sub cmdDelete_Click()
    Dim iReply As Integer
    Dim ITitles As Long
    
    'first confirm deletion
    iReply = MsgBox("Delete selected entry?", vbYesNo + vbQuestion, "Delete Entry?")
    If iReply = vbNo Then
        Exit Sub
    End If
    
    'if we get here, delete the entry
    Adodc1.Recordset.Delete
 
End Sub
 
Private Sub cmdEdit_Click()
    'Disable the Add, Edit and Delete buttons
    cmdAdd.Enabled = False
    cmdEdit.Enabled = False
    cmdDelete.Enabled = False
    
    'enable save and cancel buttons
    cmdSave.Enabled = True
    cmdSave.Enabled = True
    cmdCancel.Enabled = True
    
    'disable the navigation
    DisableNavigation
    
    'unlock the owner, class name, number and entry number txtboxes
    UnlockFields
    
    'move to the owner txtbox
    txtOwner.SetFocus
End Sub
 
Private Sub cmdFirst_Click()
    'move to the first record in the table
    Adodc1.Recordset.MoveFirst
End Sub
 
Private Sub cmdLast_Click()
    'move to the last record
    Adodc1.Recordset.MoveLast
End Sub
 
Private Sub cmdNext_Click()
    'check for the end of the file first
    If Adodc1.Recordset.EOF = True Then
        Adodc1.Recordset.MoveFirst
    Else
        Adodc1.Recordset.MoveNext
    End If
End Sub
 
Private Sub cmdPrevious_Click()
    'check for beginning of the file
    If Adodc1.Recordset.BOF = True Then
        Adodc1.Recordset.MoveLast
    Else
        Adodc1.Recordset.MovePrevious
    End If
End Sub
 
Public Sub DisableNavigation()
    'to disable the navigation
    cmdFirst.Enabled = False
    cmdPrevious.Enabled = False
    cmdNext.Enabled = False
    cmdLast.Enabled = False
End Sub
 
Public Sub EnableNavigation()
    cmdPrevious.Enabled = True
    cmdNext.Enabled = True
    cmdLast.Enabled = True
    cmdFirst.Enabled = True
End Sub
 
Private Sub cmdSave_Click()
    Dim iReply As Integer
 
    'confirm updates before saving
    iReply = MsgBox("Save changes made?", vbYesNo + vbQuestion, "save details")
    If iReply = vbNo Then
        Exit Sub
    End If
    
    'update the tables
    Adodc1.Recordset.Update
    
    'enable add, delete and edit
    cmdAdd.Enabled = True
    cmdEdit.Enabled = True
    cmdDelete.Enabled = True
    
    'disable cancel and save, cos we've finished with those
    cmdCancel.Enabled = flase
    cmdSave.Enabled = False
    
    'lock the text boxes
    LockFields
    'enable navigation
    EnableNavigation
 
End Sub
 
Private Sub Form_Load()
    'disable the save details and cancel edit buttons
    cmdSave.Enabled = False
    cmdCancel.Enabled = False
    LockFields
End Sub
 
Public Sub LockFields()
    'lock all of the fields, so none of the information will be edited by accident
    txtOwner.Locked = True
    txtClass.Locked = True
    txtClassNumber.Locked = True
    txtClassName.Locked = True
    txtEntryNumber.Locked = True
    txtprizes.Locked = True
End Sub
 
Public Sub UnlockFields()
    'unlock the fields
    txtOwner.Locked = False
    txtClass.Locked = False
    txtClassNumber.Locked = False
    txtClassName.Locked = False
    txtEntryNumber.Locked = False
    txtprizes.Locked = False
End Sub

[rinoaheartilly]

or maybe not. Anyway, I need something to search for class numbers and entry numbers

[rinoaheartilly]

sorry, the screeny won't load

[rinoaheartilly]

Originally posted by Wokawidget
Ok, say you wanted to select all the types of birds in the show who are over 17 feet tall. The Select statement would be something like:

SELECT Birds.Type FROM Birds WHERE Birds.Height > 17

Where Birds is the table which contains details of the birds. Although the above statement would select the same type twice, so if we add the Distinct command into the above statement this will only return one entry for the type of bird, even if there are many birds of that type over 17 feet:

SELECT DISTINCT Birds.Type FROM Birds WHERE Birds.Height > 17

You could search for all all birds with the name Henry:

SELECT * FROM Birds WHERE Name = 'Henry'

Does this answer your question, or are you confused, I know I am

Yup, I'm confused too, your pet bricks???