VB,Acces,SQL, Data controls or hard code?

I'm trying to create a VB front end for an access database of book titles.  I have a text box that accepts SQL code and searchs the Database and displays the correct records in a DB Grid.  I would like to be able to run one regular SQL Query

SELECT Titles.[Title] ,Titles.[Year Published] FROM Titles WHERE [Year Published] > YEAR

where year is a differnet value entered in a seperate text box.  I guess my immediate question is can I set up this SQL statement in code somewhere instead of entering it in the text box.  Then I would like to enter a value (year variable) that would execute my query for that particular year

suggestions?


Private Sub cmdExecute_Click()

    On Error GoTo CorrectSQL
   
    Data1.RecordSource = "" & txtSQLCode & ""
    Data1.Refresh

    On Error GoTo 0
    Exit Sub
   
CorrectSQL:
    If Err.Number = 3061 Then
        nresponse = MsgBox("Check your SQL Code!", vbOKOnly, "Error!")
        txtSQLCode.SetFocus
        txtSQLCode.SelStart = 0
        txtSQLCode.SelLength = Len(txtSQLCode.Text)
    End If
   
End Sub

Private Sub cmdQuit_Click()
    End
End Sub


Private Sub Form_Load()

    Data1.DatabaseName = "C:\Program Files\Microsoft Visual Basic\Biblio.MDB"
    Data1.RecordsetType = 1
    Data1.RecordSource = "Titles"
   
    Data1.Refresh
   
End Sub


Private Sub txtSQLCode_Change()
Dim year As Integer
year = 1992
    If txtSQLCode <> "" Then
        cmdExecute.Enabled = True
    Else: cmdExecute.Enabled = False
    End If
End Sub



john_d@winmill.comAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

limacherCommented:
Ok..........a quick cheat, do the following to your application.

1)  Set the RecordSetType property for the Data control to 1- Dynaset

2)  Change then cmdExecute click event to the following :

Private Sub cmdExecute_Click()
On Error GoTo CorrectSQL

    Dim ls_SQL As String
    Dim ld_YearFrom As Date

    ls_SQL = "SELECT * " & vbCrLf
    ls_SQL = ls_SQL & "FROM Titles" & vbCrLf
    ls_SQL = ls_SQL & "WHERE [Year Published] > " & Year(Text1.Text)

    Data1.RecordSource = ls_SQL
    Data1.Refresh

    On Error GoTo 0
    Exit Sub
CorrectSQL:
       If Err.Number = 3061 Then
       nresponse = MsgBox("Check your SQL Code!", vbOKOnly, "Error!")
       txtSQLCode.SetFocus
       txtSQLCode.SelStart = 0
       txtSQLCode.SelLength = Len(txtSQLCode.Text)
End If

End Sub

3)  Run the application, this shold work.

NOTE : The data control can be used to display Queries, Tables and
standard SQL statements that can be read by Jet (The Access Engine).
Queries may be more advanced for some of your application requirements.
You are not limited to Tables only, remeber that !!!!!!!

A more advanced option is to completely code the functionality of the system instead of using bound controls, but that may take more than 1 e-mail to tell you....
If you require any help with queries and Jet,
then mail me at limacher@netcomuk.co.uk

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
john_d@winmill.comAuthor Commented:
I think I may be using this wrong.

With your code my my application now reads like this:
Private Sub cmdExecute_Click()

   
On Error GoTo CorrectSQL

Dim ls_SQL As String
 Dim ld_YearFrom As Date

 ls_SQL = "SELECT * " & vbCrLf
 ls_SQL = ls_SQL & "FROM Titles" & vbCrLf
 ls_SQL = ls_SQL & "WHERE [Year Published] > " & year(Text1.Text)

 Data1.RecordSource = ls_SQL
 Data1.Refresh

 On Error GoTo 0
 Exit Sub
CorrectSQL:
 If Err.Number = 3061 Then
 nresponse = MsgBox("Check your SQL Code!", vbOKOnly, "Error!")
 txtSQLCode.SetFocus
 txtSQLCode.SelStart = 0
 txtSQLCode.SelLength = Len(txtSQLCode.Text)
End If

End Sub
   


Private Sub cmdQuit_Click()
    End
End Sub


Private Sub Form_Load()

    Data1.DatabaseName = "C:\Program Files\Microsoft Visual Basic\Biblio.MDB"
    Data1.RecordsetType = 1
    Data1.RecordSource = "Titles"
   
    Data1.Refresh
   
End Sub


Private Sub txtSQLCode_Change()
'Dim year As Integer
'year = 1992
    If txtSQLCode <> "" Then
        cmdExecute.Enabled = True
    Else: cmdExecute.Enabled = False
    End If
End Sub


I've changed the text property of txtSQLCode to:
SELECT Titles.[Title] ,Titles.[Year Published] FROM Titles WHERE [Year Published] >



and I amentering a year (1991?) into text1 (the year box).  The aplication then insists on me entering something (even a blank space) into txtSQLCode or it will not enable the execute button.  If Ido this it still doesn't seem to sort correctly.  I get all the fields in the database and they are not sorted by year.  What I am ultimetly trying to do is to create a program that displays titles published in a particular year and I want to take that yearfrom a textbox on the form.  I then want to beable to add some up and down buttons to cycle up and down in years.  Each time resubmitting the appropriate SQL statement to the database to view the resulting record set.  I've sent a zipped copy of the database to you by seperate cover.  Thanks, if you can help , double thanks.  

p.s do you know any good entry level SQL books?  VB is a little limtedwithout some SQL knowledge
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.