Another VB Problem (1 Viewer)

[Fiona]

Hey,

For my project I'm basically doing a database for a 2nd hand bookshop (because I get so frustrated when you go and say "do you have blahblah?" and the people that work there say "er... um... yeah maybe try looking over there"... so this is sort of like a library catalogue...) where you can search for books, add & delete book records, make requests, etc...

The only part of the prorgam that I haven't been able to figure out how to do is search the records - one of the main aims of the program

What I want to do is make the database searchable so that it displays the results as shown in the attached picture (note: doesn't actually have that colour cheme, but MSPaint has decided that it won't let me save things as JPEG any more, so this is a stuffed-up GIF )

Any ideas?

Thanks,

Fiona

 

[Fosweb]

If its a database (which i assume it is, because you said a 'database thing') then there is no need to do the above...

Are you using a built in control in Vb (DAO/ADO) or are you referencing DAO from VB References thingie?

Whichever way you are doing it, you need to use an SQL Query.
VB has good built in documentation on this. How you do this depends on the structure of your database.
After you search, either by Title or Author (as in your box) you will be returned a Recordset containing only the books that match your query.

You search by Opening a recordset in your database with a specified query. The query is set up as such: (it needs to be a string variable, assume its called SQLQuery)

Two lines for neatness:

SQLQuery = "SELECT * FROM tableName"
SQLQuery = SQLQuery & " WHERE Author = Whatever"

This will open a recordset containing ALL FIELDS (*) from the tableName in you database. It will be clarified to WHERE the Author = Whatever. You will need whatever to be the text entered from your search box, so that bit will be: Author = '" & varName & "'"

If you are using databound controls (i.e. you are using a built in control for your database and linking the text boxes to it), then that is basically it. The controls will update when you refresh the database (dBName.Refresh). Only books that were found in the search will be shown. YOu still scroll through by pressing prev/next.

If you have referenced your database stuff: (i.e. no built in controls and no databound text boxes):
You now need to open the recordset [i.e. rs = dBName.OpenRecordset(SQLQuery) ] and update your fields by adding, say: [ txtWhatever.text = rs.Fields("Fieldname").value ]

HTH.

If you need more - the best place for SQLhelp is by typing in SELECT * FROM (or any bit of SQL language) and pressing F1 !

 

[Fosweb]

sorry - in above - you still need to open the recordset with the ADO/DAO control...
dont remember how... probably just something like

ADO/DAOcontrolName.Recordset = SQLQuery (you may need some brackets in there somewhere too... i dunno...)

 

[Fiona]

Gosh, I have no idea about this DAO / ADO thing!

I've just done a database in Access and used the Data thing to stick it in the vb program!

So... do I need a new Access database for a new recordset for the search results? I don't really get it...

Ok here we go... a bunch of stuff that doesn't work...

Private Sub cmdTitle_Click()
datbase.Recordset = SQLQuery
SQLQuery = "SELECT * FROM table1"
SQLQuery = SQLQuery & " WHERE Author = txtName"
End Sub

where datbase is the name of my Access database... table1 is the table, and txtName is the text box where the name value is... if that makes sense...

and that just doesn't work

 

[del]

Set db = CurrentDB() //not 100% sure if that line is necessary in access, either way leaving it in does no harm

then do the SQLQuery bit

SQLQuery = "SELECT * FROM table1"
SQLQuery = SQLQuery & " WHERE Author = txtName"

then to execute

Set rs = db.OpenRecordset(SQLQuery) ' query database for results

you should check rs(0) to ensure it is not null

also need to ensure you have the Microsoft DAO Object Library checked - Tools > References

 

[Fiona]

Hmm... It doesn't like the .OpenRecordset bit...

"Compile error: method or data member not found."

 

[sunny]

I think you're really confused right now

The ideas about SQL so far are the right ways of going about doing it once you have all the necessary things in VB set up to do it. But before then none of the SQL stuff is going to work.

There are several ways of setting up VB to use databases. The "data thing" you stuff into VB is one of way of doing it. It simplifies the process greatly, but doesn't give you much room for customisation. ADO is a lower level way to access databases but harder to use, and DAO is fast, but still good. Foster found some info on this before?

I use DAO myself. To start using it, you need to tell VB that you are going to be using things from the DAO library: Project -> References -> select Microsoft DAO xxx (where xxx is just some version you have installed) from the list and hit OK.

In the attached project, there is a small database, with one table called Main, and two fields, Name and Phone. Theres 4 records in the database. In the VB project, theres simply and textbox, and a command button that will search the database for the name you typed into the textbox....and this is the code and how it works.

Option Explicit

Dim DB As Database    ' need a database open to represent the database file
Dim RS As Recordset   ' need a recordset to represent the data returned from searches

Private Sub Form_Load()
    
    ' open our little database
    Set DB = OpenDatabase(App.Path & "\testDB.mdb")
    
End Sub

Private Sub Command1_Click()

    ' search the database with the SQL string
    ' looking for the person with the name entered
    ' in the textbox
    Set RS = DB.OpenRecordset("SELECT * FROM Main WHERE Name='" & Text1.Text & "'")
    
    ' this on error resume next will catch errors
    ' if there are no matches in the database
    On Error Resume Next
    
    ' try and show the second field of the search
    ' result (0 being the first, and 1 being the second)
    MsgBox RS.Fields(1).Value

    ' if there is nothing in the recordset, it means
    ' the search returned nothing. So let the user know
    If Err.Number = 3021 Then
        MsgBox "no in DB"
    End If
    
End Sub

There are 4 names in the database, Sunny, Fiona, Deline and Foster. Type those into the box and hit the button and try it out. Have a peek at the database too if you want to convince yourself that the code searched the database.

And presto! There is a simple search of the database. Of course using databases gets much more complicated and there is plenty of documentation in VB and MSDN online about these, probably more than you ever want to know about databases.

 

[Fiona]

Hey Sunny,

Ja, I am really confused...

Yay, isn't this lovely, so many people helping

I can't get the program to run...

"Compile error: User-defined type not defined"

and it his highlighting DB As Database...

hmm...

 

[Fiona]

Hmm ok well, I've been using bits of the code from your example even though I couldn't actually get the program to run...

so... I have this for the coding for this part of my program:

Option Explicit
    Dim db As Database
    Dim rs As Recordset
    
Private Sub Form_Load()

        ' open our little database
    Set db = OpenDatabase(App.Path & "\datbase.mdb")
End Sub



Private Sub cmdAuthor_Click()
    MsgBox ("This is where the Author Search module will be.")
End Sub

Private Sub cmdExit_Click()

Dim strPass As String
    strPass = InputBox("Please enter the password.", "Password Required")
    Select Case strPass
        Case "gaius"
            Main.Show
        Case Else
            MsgBox ("Incorrect password")
    End Select
End Sub

Private Sub cmdFirst_Click()
    datbase.Recordset.MoveFirst
End Sub

Private Sub cmdLast_Click()
    datbase.Recordset.MoveLast
End Sub

Private Sub cmdNext_Click()
    With datbase.Recordset
        .MoveNext
        If .EOF Then
            .MoveFirst
        End If
    End With
End Sub

Private Sub cmdPrevious_Click()
    With datbase.Recordset
        .MovePrevious
        If .BOF Then
            .MoveLast
        End If
    End With
End Sub

Private Sub cmdRequest_Click()
    Requests.Show
End Sub

Private Sub cmdTitle_Click()
    ' search the database with the SQL string
    ' looking for the person with the name entered
    ' in the textbox
    Set rs.OpenRecordset = ("SELECT * FROM table1 WHERE txtTitle.text='" & txtSearch.Text & "'")
    
    ' this on error resume next will catch errors
    ' if there are no matches in the database
    On Error Resume Next
    
    ' try and show the second field of the search
    ' result (0 being the first, and 1 being the second)
    MsgBox rs.Fields(1).Value

    ' if there is nothing in the recordset, it means
    ' the search returned nothing. So let the user know
    If Err.Number = 3021 Then
        MsgBox "There are no matches."
    End If

    ' MsgBox ("This is where the Title Search module will be.") (previous code)
End Sub

But then I get "Compile error: Invalid use of property." for the bit .OpenRecordset =



Gosh darnit!

 

[anti]

I can hardly remember VB. I was so glad to remove it from my brain when I was done.

I think you need something like

Set rs = db.OpenRecordset = ("SELECT * FROM table1 WHERE txtTitle.text='" & txtSearch.Text & "'")

what you're doing is setting the recordset to the entry of the database which corresponds to the txtTitle.text and txtSearch.text.

at the moment you're not using the OpenRecordSet function correctly

hth

 

[Fiona]

anti,

With that one... I get "Compile Error: argument not optional."

 

[anti]

oh.
try it without the second '='

 

[Fiona]

Ooh... That would make sense

Now I get... "Runtime Error 3061: too few parameters. expected 1."

Oh poo!

 

[sunny]

If VB is telling you it doesn't know what "database" is like you said, double check that you've added reference to the DAO library in Project -> References described before. This is very important, otherwise nothing will work!!

Which line is the 3061 occuring?

 

[Fiona]

From Set rs = db.OpenRecordset("SELECT * FROM table1 WHERE txtTitle.text='" & txtSearch.Text & "'")

 

[Fosweb]

Fiona:

1.
You have:
Set rs.OpenRecordset = ("SELECT * FROM table1 WHERE txtTitle.text='" & txtSearch.Text & "'")

You should have: (you need to type the field name, as in the database, of the Title field.)
Set rs.OpenRecordset("SELECT * FROM table1 WHERE titlefieldname ='" & txtSearch.Text & "'")

(this should fix the parameters error... because, with what you had typed above, you dont appear to have a field name that you are actually searching, and this is the parameter in this particular query)


2.
You should check out this book: (you can download the first 6 chapters for free off their site...) its really good, and importantly - VERY simple to understand, taking you through tutorial like stuff...
http://www.softpile.com/Development/Tutorials/Review_02272_index.html

3.
On DAO/ADO:
You should you DAO if you are using a Jet/Access database... That is what these types of databases are designed around. If you use ADO for access databases, it can slow things down...
(Basically, for a simple program though, it makes little difference...)

HTH...

 

[Fiona]

Ah... Well that seems to have worked, soemwhat!

It now comes up with a tiny little box that has the Author's name (the second field) in it... and an OK button... er...

Ok, so how do I get it to display it in the text boxes I have set up for just flipping through the next, previous, first, last buttons etc?

 

[Fosweb]

here is where i am confused again as to how you are doing it.
You said you were simplifying it and using the Data Control? (the one you DRAG onto your form?)
And therefore when you added your textboxes, you 'Bound' certain fields to them by choosing options such as 'Datasource' - where you chose the data control, then the 'DataField' where you added the certain field you wanted to display in that text box.)
If this is how you did it, then try typing:
data1.refresh
after you run the query.
Otherwise, give me two more minutes, and i will get another simple program working for you.

(btw - you are getting a msgbox displaying the result, because you have used sunny's little example, and put in msgbox 'whatever ' in there. take that out, and put the code to update the data control there, and it should work...
(give me a minute to confirm exactly what this code is...)

 

[Fiona]

Yes, I am using the data control and then 'binding' text boxes to certain fields Sorry, I should have explained that earlier...

Aah.... I see! MsgBox rs.Fields(1).Value That bit is putting it in the message box Now to figure out how to make it go in the text boxes... Hmm...

 

[Fosweb]

i have to go - but i'll have a look tomorrow if you still need help...