too few parameters expected 1 with criteria in query.

[Homegrownandy]

I have some code that will place a field from a query into a word document. I can not get this to work how I want with the query I need.



I get this error when using the query with filtered results:

too few parameters expected 1

The criteria im using is:

[Forms]![SiteFullListF]![site_ID]

If i remove this criteria then the query returns every record BUT the code works to add feilds to the word document.

(When i say works what I mean is.. It will do the first record in the list and then error because the template docuement is already open.)

So i need to filter query results whilst being able to use the code.

Any suggestions are appreciated.

Thanks, Andy

[orange]

Too few parameters can often indicate a spelling error.

You haven't given us a lot of info, but here's a guess to consider.

Sometimes you have conditional logic regarding another application ( eg excel, outlook)

Code is along this line( air code)

if someFlag then
  createObject.... excel app --------------------->create an instance
else
  getobject.....  excel app -----------------------> instance exists so use it 
end if

[Homegrownandy]

Hi Orange, when troubleshooting I've edited the query to contain only one field for testing (one table also).

In access all results are shows with the criteria field being empty.. with the criteria being used then only the correct record is shown. So the query is correct with no spelling errors.

this is the whole code im using:

Private Sub Command92_Click()


'-----------------Set up word document to use recordset ------------------
Dim wrdApp As New Word.Application
Dim wrdDoc As Word.Document
Dim filepath As String
Dim docFile As String
Dim rst As DAO.Recordset
'-----------------Set up word document to use recordset ------------------

'                    --------------------------

'-----------------define bookmark variables as range -----------------
Dim test As Word.Bookmarks
'-----------------define bookmark variables as range ------------------


'------------------ define query -----------------------------
Set rst = CurrentDb.OpenRecordset("SITE_RAMS")
filepath = "C:\Users\ah\Desktop\RAMS AUTOMATION\RAM.docm"

Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True

Set wrdDoc = wrdApp.Documents.Open(filepath)

With wrdDoc

wrdApp.ActiveDocument.Bookmarks("test").Select
wrdApp.Selection.Text = rst("Site_ID") & " " & rst("Site_Name")

End With
Set rst = Nothing

End Sub

which generates the error on this line: (ONLY WHEN USING CRITERIA IN QUERY)

Set rst = CurrentDb.OpenRecordset("SITE_RAMS")

As I said earlier. If I remove the criteria then the word document will populate correctly for the first record in the recordset.

I dont wish to do this for multiple records anyway so it's just the problem with using criteria.

I'm not sure what you mean with your sugestion or if it applies still after my explination. I guess a way around this could be to use the query itself as a table in a new query and then use that query as a recordset... I can give that a go and report back.

[orange]

Andy,
No, my suggestion doesn't apply.

Good luck.

[Homegrownandy]

Sadly using this query as a table in query builder doesnt rectify the issue. I'm at a loss..

I dont want to give up because everything seems to be working fine except this error. Could you suggest anything else?

Andy

[Homegrownandy]

the SQL causing issue is:

WHERE (((SiteT.Site_ID)=[Forms]![SiteFullListF]![site_ID]))

this works within access for everything else i would do normally.

I have found this online:

https://stackoverflow.com/questions/...ecordset-issue

It doesnt mean much to me im trying to work it out.

[Minty]

Is the form with that control actually open when you are calling this code?
It needs to be.

[Homegrownandy]

Yes It's a button on the form I'm pressing.

[davegri]

Couple of shot in the dark ideas.
1. Is SiteFullListF a subform? If so, and the query is being built behind the main form, you need to adjust the WHERE code to account for the control holding the subform.
2. Try WHERE (((SiteT.Site_ID)=[Forms]![SiteFullListF].[site_ID]))
3. I assume Site_ID is numeric, if not then
WHERE SiteT.Site_ID='" & [Forms]![SiteFullListF].[site_ID] & "'"

[orange]

Andy
Can you show us the whole query for
"SITE_RAMS"?

[Homegrownandy]

Dave, Thanks for the replies.

I was really hopeful, especially about sugestion 2. Sadly nothing worked. As a note: here is no sub form.

Ill have a go setting up the function in the link I posted earlier. (took me a while to see what the sugestion actually was)
from the link:

A parameter like [Forms]![SurveyRegister_frm]![SurveyID] doesn't get evaluated automatically if you open a recordset in VBA.

I'll update shortly.

[Homegrownandy]

Orange:

SELECT SiteT.Site_ID, SiteT.Site_Name, SiteT.Asset_Type, SiteT.Address_1, SiteT.Address_2, SiteT.Address_3, SiteT.Postcode, ClientT.Company_Name, ClientT.Company_ID, HospitalT.Hospital_Name, HospitalT.Hospital_Postcode, HospitalT.Hospital_Address, HospitalT.Hospital_Telephone, SiteT.lat, SiteT.long
FROM HospitalT INNER JOIN (ClientT INNER JOIN SiteT ON ClientT.Company_ID = SiteT.Site_Owner) ON HospitalT.Hospital_ID = SiteT.Hospital_ID
WHERE ((([SiteT].[Site_ID])=[Forms]![SiteFullListF].[site_ID]))
ORDER BY SiteT.Site_Name;

This is the whole query SQL.

[Homegrownandy]

Just to provide some context, Ill be placing bookmarks in a risk assesement document. The user can go onto the database, select the location of works and itwill then produce a document as a basis to work on. The user will still need to add methodology but it would allow us to improve accuracy and save time.

[Homegrownandy]

Sadly I've been roped into doing some other works. I'll work on adding the other code on the weekend and update on monday. I'm assuming it will be okay once/if I understand it.

[orange]

Andy,
Is it possible to provide a copy of database with just a few dummy records eg Porky Pig, Donald Duck --Wales General Hospital sort of thing. Enough dummy data to "test".
It's failing in Access, right ----doesn't get to Word with the criteria?

It certainly seems it is not getting a value from the form.
Anyway you can try Debug.Print
[Forms]![SiteFullListF].[site_ID]
just to see what Access thinks???