Issue W/ Qry Functionality In A Form

G

Guest

I have created the following Form from a sample database I found that queries
information in my table. Unfortunately, I cannot get the "Run Query" button
to work properly. My table name is master_tbl and I have validated the names
of my cbo boxes.

The error that I am getting is "Compile Error: User-Defined type not defined
and it is pointing at the 3rd line of code below. Since I am not a VB
expert, I have no idea what Dim is trying to accomplish. Do I need to give
this line of code specific information regarding my database? Remember this
is a sample db from someone else.
-----------------
Private Sub cmdRunQuery_Click()
On Error GoTo Err_cmdRunQuery_Click

Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String

Set db = CurrentDb

'*** create the query based on the information on the form
strSQL = "SELECT Master_tbl.* FROM Master_tbl WHERE "
strSQL = strSQL & "[Supplier] like """ & Me![txtSupplier] & """"
strSQL = strSQL & Me![cboLogical]
strSQL = strSQL & " [Year] like """ & Me![txtYear] & """"
strSQL = strSQL & Me![cbological2]
strSQL = strSQL & " [Quarter] like """ & Me![txtQuarter] & """"
strSQL = strSQL & Me![cbological3]
strSQL = strSQL & " [Phase] like """ & Me![txtPhase] & """"

'*** delete the previous query
db.QueryDefs.Delete "TestSupplierAve_qry"
Set qdf = db.CreateQueryDef("TestSupplierAve_qry", strSQL)

'*** open the query
DoCmd.OpenQuery "TestSupplierAve_qry", acNormal, acEdit

Exit_cmdRunQuery_Click:
Exit Sub

Err_cmdRunQuery_Click:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on
the next line
Else
MsgBox Err.Description '*** write out the error and exit the sub
Resume Exit_cmdRunQuery_Click
End If
End Sub

Private Sub cmdExit_Click()
DoCmd.Close

End Sub
Private Sub cmdExplain_Click()
On Error GoTo Err_cmdExplain_Click
Dim strExplain As String

strExplain = " This form illustrates how to create and execute a select
query "
strExplain = strExplain & "via code. It creates the query 'qryMyQuery'
based on "
strExplain = strExplain & "the choices in the form, including the 'AND' and
'OR' "
strExplain = strExplain & "operators. This differs from the parameter query
in "
strExplain = strExplain & "it re-creates the query each time it is run. The
query "
strExplain = strExplain & "is based on the table 'master_tbl'."

MsgBox strExplain

Exit_cmdExplain_Click:
Exit Sub

Err_cmdExplain_Click:
MsgBox Err.Description
Resume Exit_cmdExplain_Click

End Sub
 
N

Naresh Nichani MVP

Hi:

Your VBA Project needs a reference to DAO - the Database and Recordset
objects are from DAO Type Library.

To do this in the Visual Basic Editor click Tools | References and check
"Microsoft DAO 3.6 Object Library" and click OK.

Changes these lines from
Dim db As Database
Dim qdf As QueryDef
To
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Click to expand...

Regards,

Naresh Nichani
Microsoft Access MVP
..




Kevin said:
I have created the following Form from a sample database I found that queries
information in my table. Unfortunately, I cannot get the "Run Query" button
to work properly. My table name is master_tbl and I have validated the names
of my cbo boxes.

The error that I am getting is "Compile Error: User-Defined type not defined
and it is pointing at the 3rd line of code below. Since I am not a VB
expert, I have no idea what Dim is trying to accomplish. Do I need to give
this line of code specific information regarding my database? Remember this
is a sample db from someone else.
-----------------
Private Sub cmdRunQuery_Click()
On Error GoTo Err_cmdRunQuery_Click

Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String

Set db = CurrentDb

'*** create the query based on the information on the form
strSQL = "SELECT Master_tbl.* FROM Master_tbl WHERE "
strSQL = strSQL & "[Supplier] like """ & Me![txtSupplier] & """"
strSQL = strSQL & Me![cboLogical]
strSQL = strSQL & " [Year] like """ & Me![txtYear] & """"
strSQL = strSQL & Me![cbological2]
strSQL = strSQL & " [Quarter] like """ & Me![txtQuarter] & """"
strSQL = strSQL & Me![cbological3]
strSQL = strSQL & " [Phase] like """ & Me![txtPhase] & """"

'*** delete the previous query
db.QueryDefs.Delete "TestSupplierAve_qry"
Set qdf = db.CreateQueryDef("TestSupplierAve_qry", strSQL)

'*** open the query
DoCmd.OpenQuery "TestSupplierAve_qry", acNormal, acEdit

Exit_cmdRunQuery_Click:
Exit Sub

Err_cmdRunQuery_Click:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on
the next line
Else
MsgBox Err.Description '*** write out the error and exit the sub
Resume Exit_cmdRunQuery_Click
End If
End Sub

Private Sub cmdExit_Click()
DoCmd.Close

End Sub
Private Sub cmdExplain_Click()
On Error GoTo Err_cmdExplain_Click
Dim strExplain As String

strExplain = " This form illustrates how to create and execute a select
query "
strExplain = strExplain & "via code. It creates the query 'qryMyQuery'
based on "
strExplain = strExplain & "the choices in the form, including the 'AND' and
'OR' "
strExplain = strExplain & "operators. This differs from the parameter query
in "
strExplain = strExplain & "it re-creates the query each time it is run. The
query "
strExplain = strExplain & "is based on the table 'master_tbl'."

MsgBox strExplain

Exit_cmdExplain_Click:
Exit Sub

Err_cmdExplain_Click:
MsgBox Err.Description
Resume Exit_cmdExplain_Click

End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Export Filtered Form Data To Excel 0
Currenct issue with a text box 1
Select AS query problem 3
Generic Error Handling 3
Msg: You are about to append..... 5
Cascading Combo Box BIG issue 11
strSQL in Search Form 4
Update the subform or refresh based on multiple selection in a lis 1

Top