We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

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

Medium Priority
250 Views
Last Modified: 2008-03-06
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



Comment
Watch Question

Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.