VBA DAO OpenRecordset Error

[Kirstin]

Hi,

Can anyone help me with this error?

I have this code: Set rs = db.OpenRecordse t("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, ProjectDescript ion
FROM Contracts
WHERE (Contracts.JobT ype=" & Forms!SearchFor m!JobTypeQ & " And Contracts.Clien t=" & Forms!SearchFor m!ClientQ & ") Or (Contracts.JobT ype=" & Forms!SearchFor m!JobTypeQ & " And " & Forms!SearchFor m!ClientQ & "=" N/A") Or (Contracts.Clie nt=" & Forms!SearchFor m!ClientQ & " And " & Forms!SearchFor m!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.JobTy pe
2. Contracts.Clien t
3. Contracts.JobTy pe

[Kirstin]

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

1. Contracts.JobTy pe
2. Contracts.Clien t
3. Contracts.JobTy pe

Contracts.JobTy pe and Contracts.Clien t 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, ProjectDescript ion
FROM Contracts
WHERE (Contracts.JobT ype = Forms!SearchFor m!JobTypeQ AND Contracts.Clien t = Forms!SearchFor m!ClientQ) OR (Contracts.JobT ype = Forms!SearchFor m!JobTypeQ AND Forms!SearchFor m!ClientQ = " N/A") OR (Contracts.Clie nt = Forms!SearchFor m!ClientQ AND Forms!SearchFor m!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("S earch")

Search.Paramete rs(0) = Forms!SearchFor m!JobTypeQ
Search.Paramete rs(1) = Forms!SearchFor m!ClientQ
Set rs = Search.OpenReco rdset

Oh and why can't I use the code:

"Forms!SearchFo rm!ClientQ & "=" N/A" "?

It works(well actually
"Forms!SearchFo rm!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("S earch")

Search.Paramete rs(0) = Forms!SearchFor m!JobTypeQ
Search.Paramete rs(1) = Forms!SearchFor m!ClientQ
Set rs = Search.OpenReco rdset

Oh and why can't I use the code:

"Forms!SearchFo rm!ClientQ & "=" N/A" "?

It works(well actually
"Forms!SearchFo rm!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!