Solved

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

Posted on 1997-03-21
2
187 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



0
Comment
Question by:john_d@winmill.com
2 Comments
 

Accepted Solution

by:
limacher earned 100 total points
ID: 1424433
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
 

Author Comment

by:john_d@winmill.com
ID: 1424434
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now