Solved

VB,Access,SQL  Data controlor hard code?

Posted on 1997-03-21
1
192 Views
Last Modified: 2008-02-26
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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 2

Accepted Solution

by:
dirkmartin earned 100 total points
ID: 1424532
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…

726 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