Another VB Problem (1 Viewer)

[Fiona]

Thankyou so much, Fosweb!

That would be wonderful!

 

[Fosweb]

ok.

so - i think we are making this all too complicated for you!
it is so simple when using bound text boxes and the data control.

Assuming your data control is called Data1

'Run the search.
Data1.RecordSource = "(" & SQLQuery & ")"
Data1.Refresh

That will load the recordset into the data control automatically.

You shouldnt need to worry about any of the lines of code like:
set rs = whatever, or set db = whatever.

To move between found records, just use this:

Data1.moveFirst/moveNext/MovePrevious/MoveLast etc...

To bring back all the records in the database, so that you can search again, you will need to refresh the data control with an sql query that loads all records again:

Data1.recordsource = ("SELECT * FROM table1")
Data1.refresh

Just remember, that after every sql query, you have to refresh the data control.

 

[Fiona]

Ah yes... That seems so much simpler! I'll have a go when I get back from work... Leaving soon, crap, should get ready...

Thankyou so much, Fosweb!

 

[Fosweb]

no probs

 

[Fiona]

Oh dear I must be so frustrating...

"Compile error: Variable not defined" - SQLQuery from the Data1.RecordSource = "(" & SQLQuery & ")" bit... Should I have kept something from previously?

 

[sunny]

SQLQuery should be whatever you want the search to be.

Like Foster already said if you want to get all the records back into the data control you should use

Data1.RecordSource = "(SELECT * FROM table1)"

In this case, SQLQuery would be "SELECT * FROM table1"

 

[Fiona]

Ok, new problemo

Run-time error 3075: Syntax error (missing operator) in query expression 'Title='Emma' '&'

where Emma is the item we are searching for in the Title field...

 

[Fiona]

In case you need it... Here is what I have:

Private Sub cmdTitle_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 table1 WHERE Title='" & txtSearch.Text & "'")
    datbase.RecordSource = ("SELECT * FROM table1 WHERE Title='" & txtSearch.Text & "' & ")
    datbase.Refresh
    ' this on error resume next will catch errors
    ' if there are no matches in the database
    On Error Resume Next


    ' 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
End Sub

 

[sunny]

datbase.RecordSource = ("SELECT * FROM table1 WHERE Title='" & txtSearch.Text & "' & ")

should be

datbase.RecordSource = ("SELECT * FROM table1 WHERE Title='" & txtSearch.Text & "'")

 

[anti]

when you get things like syntax errors, check that you haven't misspelled words. parameter errors mean that you haven't filled in all the required parameters (all the things you have to tell a function). you might be able to solve some of the errors on your own just by reading the error description

 

[Fiona]

Thanks everyone, it seems to be working now