Solved

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

Posted on 1997-03-21
2
181 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
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

763 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

6 Experts available now in Live!

Get 1:1 Help Now