Buttomn to run a Query

[seb]

Hello Friends
Access 2010
I have checking this Forum looking for a code to run a find button based in a Query
I can run this code when the code is like this
Private Sub cmdFind_Click()
DoCmd.OpenForm "FFind", acViewNormal
DoCmd.Close acForm, Me.Name
End Sub

I am trying another way with the following code:
Private Sub cmdFind_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
strSQL = "SELECT OBID, YY, AU, PK, CT, TT, RE, WN From QFind"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
DoCmd.OpenForm "FFind", acViewNormal
DoCmd.Close acForm, Me.Name
Else
MsgBox "No Records"
End If
rs.Close
Set rs = Nothing

End Sub

However, when I click de button to open the form FFind (baed on QFind) to display the records a message Error 3061 is displayed when I open the VBA the code line
Set rs = db.OpenRecordset(strSQL)
The execution stops in the above line and is coloured in Yellow
Could you help me to fix this error?



Many thanks in advance

[pbaldy]

If the query has parameters, it's probably this:

http://support.microsoft.com/default...b;en-us;209203

You can often use the Eval() function around each parameter in the query to get around the problem.

[seb]

If the query has parameters, it's probably this:

http://support.microsoft.com/default...b;en-us;209203

You can often use the Eval() function around each parameter in the query to get around the problem.

pbaldy. Many thanks
I read the article you suggested and I still confused since I just learning how to programming in Access database. On the other hand, I could not find the DAO in MS Access 64 I have.

Searching the web I read that Office 2010 simply does not support DAO 3.6 any more. I don't know is such opinion is true and I don't know how affect the query I am trying to run.

As you sad the query (named QFind in my Database) has parameters, and I am really sorry not mentioned in my post.
I don't know to use the Eval() function.

Is there another way to fix my problem? Please.
Thanks again

[pbaldy]

I assume the query has form parameters so in place of this in the criteria

Forms!FormName.ControlName

try

Eval('Forms!FormName.ControlName')

I am pretty certain DAO is still supported in 2010. I believe it's included by default in this library in Tools/References:

Microsoft Office 14.0 Access database engine Object Library

[seb]

pbaldy
My Query is based in the following parameters
Like [Forms].[FFind].[txtOID] & "*"
Between[Forms].[FFind].[txtStart] AND [Forms].[ FFind].[txtEnd]
Like "*" & [Forms].[FFind].[txtAUNM] & "*"
Like [Forms].[ FFind].[PKNM] & "*"
Like [Forms].[ FFind].[txtCTN] & "*"

If I include your idea in my parameter Eval(Like [Forms].[ FFind].[PKNM] & "*")
No records are displayed, however, without Evals recors aere displayed.
Eval(Like [Forms].[ FFind].[PKNM] & "*")

For additional information
Parameter 1 and 2 are form table tblOB
Parameter 3 is from table tblPkk
Parameter 4 is from table tblCTNN

On the other hand, I have a form where user may enter data in textboxes. If records are found they will be displayed in another form, else display a message “No records”

I tested the code below and works. I don’t know how to

Due I don’t know how to iterate I have 3 buttons to search
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
strSQL = "SELECT OBID, NM FROM tblOB WHERE NM LIKE 'Q'" _
& " ORDER BY NM;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me!lsbNM.RowSource = strSQL
Else
MsgBox "No records, try againg”
End If
rs.Close
Set rs = Nothing

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
strSQL = "SELECT OBID, NM FROM tblOB WHERE NM LIKE 'Y'" _
& " ORDER BY NM;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me!lsbNM.RowSource = strSQL
Else
MsgBox "No records, try againg”
End If
rs.Close
Set rs = Nothing

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
strSQL = "SELECT OBID, NM FROM tblOB WHERE NM LIKE 'U'" _
& " ORDER BY NM;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me!lsbNM.RowSource = strSQL
Else
MsgBox "No records, try againg”
End If
rs.Close
Set rs = Nothing

So in one case works as I need but, not in the other,
I would really appreciated if you can point me in the right direction
Many thanks again for your kindness

[pbaldy]

You included everything:

Eval(Like [Forms].[ FFind].[PKNM] & "*")

I said just the form reference:

Like Eval('[Forms].[ FFind].[PKNM]') & "*"

This type of thing would work:

strSQL = "SELECT OBID, NM FROM tblOB WHERE NM LIKE 'Q'" _

by letting the user type the letter into a form textbox:

strSQL = "SELECT OBID, NM FROM tblOB WHERE NM LIKE '" & Forms!FormName.ControlName & "'" _

[seb]

Baldy
I have been trying your code. However, I have no success until now. I think for my little acknowledgment in programming.
As I said I have form (FSearch) as filter for a parameter query that when user enter data in textboxes, and click the button Find in FSearch the query run.
When there are records to show the qry works fine. Nevertheless, if there aren’t records, then it opens a form F as a blank form.
My nightmare is that when no records are found, how I can get a message to say “No records Found” and then opening FFind after pressing Ok.

I have been testing the code

Private Sub cmdSearch_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
strSQL = "SELECT * FROM QFind"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No Records!"
DoCmd.Close acForm, Me.Name
DoCmd.OpenForm "FFind", acViewNormal
End If
End Sub

In Access help I read this code
Public Sub GetOrders()

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set dbs = CurrentDb
strSQL = "SELECT * FROM Orders WHERE OrderDate >#3-31-2006#;"
Set qdf = dbs.CreateQueryDef("SecondQuarter", strSQL)

End Sub

Unfortunately I have a Quey not a Table as in the above example. I also don’t know what means QueryDef and how to use the clauses Where.
My problem is that anytime I enter data and click the search button the code stops and the line srtSQL="SELECT * FROM QFind" is colored in yellow. I don’t know what I have to write after FROM. I tried the qry´s SQL but again no success.

I check from the Access Help

Could you point me in the right direction?

Thanks again

[seb]

Baldy
I have been trying your code. However, I have no success until now. I think for my little acknowledgment in programming.
As I said I have form (FSearch) as filter for a parameter query that when user enter data in textboxes, and click the button Find in FSearch the query run.
When there are records to show the qry works fine. Nevertheless, if there aren’t records, then it opens a form F as a blank form.
My nightmare is that when no records are found, how I can get a message to say “No records Found” and then opening FFind after pressing Ok.

I have been testing the code

Private Sub cmdSearch_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
strSQL = "SELECT * FROM QFind"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No Records!"
DoCmd.Close acForm, Me.Name
DoCmd.OpenForm "FFind", acViewNormal
End If
End Sub

In Access help I read this code
Public Sub GetOrders()

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set dbs = CurrentDb
strSQL = "SELECT * FROM Orders WHERE OrderDate >#3-31-2006#;"
Set qdf = dbs.CreateQueryDef("SecondQuarter", strSQL)

End Sub

Unfortunately I have a Quey not a Table as in the above example. I also don’t know what means QueryDef and how to use the clauses Where.
My problem is that anytime I enter data and click the search button the code stops and the line srtSQL="SELECT * FROM QFind" is colored in yellow. I don’t know what I have to write after FROM. I tried the qry´s SQL but again no success.

I check from the Access Help

Could you point me in the right direction?

Thanks again

After a many days checking the web and books finally I found a code to display the message. This is the code:

If DCount("*", "QSearch") = 0 Then
MsgBox "No records to show.Try again"
Else
DoCmd.OpenForm "FFind"
DoCmd.Close acForm, Me.Name
End If

Many thanks for your help

[pbaldy]

Glad you got it working; I'm actually traveling abroad right now, so not keeping up as well as I'd like.