VB,Access,SQL Data controlor 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?
 
dirkmartinConnect With a Mentor Commented:
The best way of doing this is to create your query right over in Access, in the Query QBE grid.  Make Year a parameter.  I don't know if you've worked with parameter queries or not, but this is bar far the best way to do it.

Make Year a parameter over in Access.  Run the query to make sure that Access asks you for the year each time the query is run. (look up parameter queries in Access).

Then go back to VB and do something like this:

Dim MyDB As Database, MyQuery As QueryDef, MySet As Recordset
Set MyDB = Workspaces(0).OpenDatabase("BIBLIO.MDB")
Set MyQuery = MyDB.OpenQueryDef![NameOfMyQuery]      ' Open QueryDef.MyQuery.Parameters![Year]] = Textbox.text      ' Set parameter.
' Create snapshot-type Recordset.
Set MySet = MyQuery.OpenRecordset(dbOpenSnapshot)

0
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.

All Courses

From novice to tech pro — start learning today.