VBA DAO OpenRecordset Error

[Kirstin]

Hi,

Can anyone help me with this error?

I have this code: Set rs = db.OpenRecordset("Search")
where search is a parameterised query. When I try to run it I get this error: "Two few parameters, expected 2." I guess I have to put something else inside the brackets but I don't know what.


What I am trying to do is export the result of the query to word when a user clicks a command button. I have been at it all morning and made some progress but this latest error is doing my head in. Any help would be greatly appreciated!

Edit: Also 1 other question. What number do fields start at? I mean in this kind of code: Set fld = rs.Fields(?). I don't know if this second question makes sense but I can't think of any other way to ask it. Just ignore it if it doesn't!
TIA

Kirstin

[Kirstin]

I found this answer to the first one here.
Thankfully, that has been bugging me for ages now :)

Edit(again): now my query doesn't work at all (it was before I just couldn't export it using VBA how I wanted).
I have this now: "SELECT ProjectTitle, ProjectDescription
FROM Contracts
WHERE (Contracts.JobType=" & Forms!SearchForm!JobTypeQ & " And Contracts.Client=" & Forms!SearchForm!ClientQ & ") Or (Contracts.JobType=" & Forms!SearchForm!JobTypeQ & " And " & Forms!SearchForm!ClientQ & "=" N/A") Or (Contracts.Client=" & Forms!SearchForm!ClientQ & " And " & Forms!SearchForm!JobTypeQ & "=" N/A");"

Can anyone see any problems with that?
Besides a few syntax errors, what are the Data Types for the following Fields?

1. Contracts.JobType
2. Contracts.Client
3. Contracts.JobType

[Kirstin]

Besides a few syntax errors, what are the Data Types for the following Fields?

1. Contracts.JobType
2. Contracts.Client
3. Contracts.JobType

Contracts.JobType and Contracts.Client are Text

Also if it helps JobTypeQ and ClientQ are lookup comboboxes with predefined values.

It was working fine before when it was like this:
"SELECT ProjectTitle, ProjectDescription
FROM Contracts
WHERE (Contracts.JobType = Forms!SearchForm!JobTypeQ AND Contracts.Client = Forms!SearchForm!ClientQ) OR (Contracts.JobType = Forms!SearchForm!JobTypeQ AND Forms!SearchForm!ClientQ = " N/A") OR (Contracts.Client = Forms!SearchForm!ClientQ AND Forms!SearchForm!JobTypeQ =" N/A");"
but then I couldn't use it in VBA only reports etc. Infuriating!

Also I am a bit confused because of the way you answered me, was it okay to post this as a new reply or should I have edited my last one? I don't know who am talking to either, spooky!

[MMcCarthy]

Hi Kirsten
I'm not sure who added the edit to your second post however, there are a few rules to follow. For text fields you have to enclose them in single quotes.

Also you cannot use this statement in a query.

Forms!SearchForm!ClientQ & "=" N/A"

If you explain the logic behind this statement I will try to help further.

I've encluded a simplified version of the query below just to show you how the single quotes work

"SELECT ProjectTitle, ProjectDescription
FROM Contracts
WHERE (JobType='" & Forms!SearchForm!JobTypeQ & "' And Client='" & Forms!SearchForm!ClientQ & "')"
 

Just as a note for future reference dates would be enclosed with #

[Kirstin]

Thanks,

I eventually managed to solve it using this vba code:

Dim Search As QueryDef

...
Set Search = db.QueryDefs("Search")

Search.Parameters(0) = Forms!SearchForm!JobTypeQ
Search.Parameters(1) = Forms!SearchForm!ClientQ
Set rs = Search.OpenRecordset

Oh and why can't I use the code:

"Forms!SearchForm!ClientQ & "=" N/A" "?

It works(well actually
"Forms!SearchForm!ClientQ =" N/A") . The reason for it is so the user can choose to search for a certain job type for a certain client or a certain job type for all clients or all job types for a certain client. If that makes sense. I have a lookup up combobox from predefined tables of different jobtypes and clients that the user can't add to. I am pretty new to this and kinda making it up as I go along so maybe there is a better way.

[MMcCarthy]

Thanks,

I eventually managed to solve it using this vba code:

Dim Search As QueryDef

...
Set Search = db.QueryDefs("Search")

Search.Parameters(0) = Forms!SearchForm!JobTypeQ
Search.Parameters(1) = Forms!SearchForm!ClientQ
Set rs = Search.OpenRecordset

Oh and why can't I use the code:

"Forms!SearchForm!ClientQ & "=" N/A" "?

It works(well actually
"Forms!SearchForm!ClientQ =" N/A") . The reason for it is so the user can choose to search for a certain job type for a certain client or a certain job type for all clients or all job types for a certain client. If that makes sense. I have a lookup up combobox from predefined tables of different jobtypes and clients that the user can't add to. I am pretty new to this and kinda making it up as I go along so maybe there is a better way.

You are using this code in a query and that statement makes no sense in a query. Can you actually post the full code of this procedure.

[Kirstin]

Option Compare Database
 
Public WordApp As Word.Application
Public doc As Word.Document
Public sel As Word.Selection
Public Sub WordEx()
 
Set WordApp = New Word.Application
WordApp.Documents.Add
Set doc = WordApp.ActiveDocument
Set sel = WordApp.Selection
WordApp.Visible = True
 
End Sub
 
Public Sub WordXPort()
Dim db As DAO.Database
Dim Search As QueryDef
Dim rs As DAO.Recordset
Dim Title As DAO.Field
Dim Description As DAO.Field
 
Set db = CurrentDb()
Set Search = db.QueryDefs("Search")
 
Search.Parameters(0) = Forms!SearchForm!JobTypeQ
Search.Parameters(1) = Forms!SearchForm!ClientQ
 
Set rs = Search.OpenRecordset
 
If rs.RecordCount = 0 Then Exit Sub
 
Set Title = rs.Fields(0)
Set Description = rs.Fields(1)
 
WordEx
 
Do Until rs.EOF
 
sel.TypeText Text:="Job Title - " & Title.Value & vbCrLf 
sel.TypeText Text:="Job Description - " & Description.Value & vbCrLf 
 
rs.MoveNext
 
Loop
 
End Sub
 

The bit inside the loop is just me testing, I will be editing that to display it how I want (hopefully in a table if that is possible).

My SQL is:

SELECT Contracts.ProjectTitle, Contracts.ProjectDescription, Contracts.ProjectCost
FROM Contracts
WHERE (Contracts.JobType=Forms!SearchForm!JobTypeQ And Contracts.Client=Forms!SearchForm!ClientQ) Or (Contracts.JobType=Forms!SearchForm!JobTypeQ And (Forms!SearchForm!ClientQ=" N/A" or "Select Client")) Or (Contracts.Client=Forms!SearchForm!ClientQ And (Forms!SearchForm!JobTypeQ=" N/A" or "Select Job Type"));

It all works now anyway :) But if there is a better way to do it then please let me know!