HELP confused beginner with VBA (1 Viewer)

Ste4en · Dec 5, 2005

I am trying to learn a little VBA and am stumped again...
I am trying to build a SQL statement and query from an input form. I am using a book and I am not understanding or it is not explaining well.

In the VBA form module I have 2 functions and then one function in a module called module 1. On clicking a button on the form a query and sql statement should be created based on the data in some fields (only one right now).

When I click the button nothing happens - I just dont see how one the click function calls the SQL builder or query writer.

If anyone can point me right I would appreciate it.
Thanks

Code:

Private Sub Command17_Click()

Dim StrSQL As String

If Not EntriesValid Then Exit Sub

If Not BuildSQLString(StrSQL) Then
    MsgBox "There was a problem Building the SQL string"
    Exit Sub
End If
    
MsgBox StrSQL

CurrentDb.QueryDefs("qryExample").SQL = StrSQL

End Sub
__________________________________________
Function BuildSQLString(StrSQL As String) As Boolean
 
 Dim strSELECT As String
 Dim strFROM As String
 Dim strWHERE As String
 
 strSELECT = " s.* "
 
 strFROM = "tblUnit4Data s "
 
 If CheckClass Then
    strWHERE = strWHERE & " AND s.CLASS = " & ComboClass1
 End If
 
 StrSQL = "SELECT " & strSELECT
 StrSQL = StrSQL & "FROM " & strFROM
 If srtWHERE <> " " Then StrSQL = StrSQL & "WHERE " & Mid$(strWHERE, 6)
 
 MsgBox StrSQL
 
BuildSQLString = True
 
 
End Function

and in module1 I have

Code:

Function MakeQueryDef(StrSQL As String) As Boolean

Dim qdf As QueryDef

If StrSQL = "" Then Exit Function

Set qdf = CurrentDb.CreateQueryDef("qryExample")
qdf.SQL = StrSQL
qdf.Close
RefreshDatabaseWindow

MakeQueryDef = True

End Function

tkpstock · Dec 5, 2005

What is the code for EntriesValid?

Since Functions default to ByRef, strSql should hold it's reference in the function call, but I would put ByRef just in case.

Ste4en · Dec 5, 2005

Thanks for helping;

EntriesValid is from the book, not sure what it is testing. I removed it and I got a SQL statement msgbox so the click function and BuildsqlString is working ; when I click Ok I get an error Run-time error 3265: Item not found in this collection.
Debug highlights the line
CurrentDb.QueryDefs("qryExample").SQL = StrSQL

Not sure what you mean ByRef?

thanks

tkpstock · Dec 5, 2005

Is qryExample a query in your database? If not, that is your problem. CurrentDb.QueryDefs(item) refers to a query in the current database.

You can add a reference (under Tools > References) to the latest Microsoft DAO. Then add this:

Code:

'Add in your declarations
Dim qdf as DAO.QueryDef

'Add in your code where appropriate
qdf.Name = "qryExample"
qdf.SQL = (your SQL code here)
CurrentDb.QueryDefs.Append qdf

This will add the query to your database once. If you are wanting to do it over and over, you'll need to delete it, then re-add it. (and compact your database too!) - use this:

Code:

On Error Resume Next
CurrentDb.QueryDefs.Delete "qryExample"
On Error Resume 0
qdf.Name = "qryExample"
qdf.SQL = (your SQL code here)
CurrentDb.QueryDefs.Append qdf

Hope this points you in the right direction!

Ste4en · Dec 6, 2005

Thanks for the pointers, it seems i left out a changeQueryDef function and once the qryExample was created it worked just fine.

thanks again

HELP confused beginner with VBA (1 Viewer)

Ste4en

Registered User.

tkpstock

Cubicle Warrior

Ste4en

Registered User.

tkpstock

Cubicle Warrior

Ste4en

Registered User.

Similar threads

Users who are viewing this thread