Solved

VB,Access,SQL  Data controlor hard code?

Posted on 1997-03-21
1
191 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
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
to transfer string from C lanaguage to VBA 4 71
vb6 - Transfer from MSHFlexgrid1 to xls issue 8 54
Passing a Text Box name to a Sub 6 99
Added a column screws up code 5 63
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

809 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