Does LeaseDocumentQ have any other criteria? If so, that's your problem.

No other criteria within that query

Are you referring to the correct Combo Box in the criteria of your Query?

Yes have double check names and spelling

What is it you are trying to accomplish? What happens if you remove the criteria from the query? Screenshots?

First i'm trying to save data into a Contact table, then use the recordset to populate a report and email

Let's see the SQL for your query

Hi Warren, the reason for this happening is quite involved, but the short answer is that when you run db.OpenRecordset, etc., the VBA code has no awareness of the form's field value of [TenantCombo], hence the "missing parameter" error.  To fix this, you need:

STEP ONE: Combine everything into one query.  You need to rewrite LeaseDocumentQ to include the other criterion: WHERE DocumentID=" & DocumentCombo .

STEP TWO: Rewrite LeaseDocumentQ to include an actual parameter, because [Forms]![LeaseReviewF]![TenantCombo] is *not* a parameter per se.  It is the value of a form's combo box.  An actual query parameter is a unique name enclosed in square brackets, such as:

     SELECT * FROM MyTable WHERE TenantID = [Tenant parameter]

Access says "missing parameter" because it thinks that any unknown name enclosed in square brackets is a parameter.  But as I said, [Forms]![LeaseReviewF]![TenantCombo] is not a parameter per se.  It is merely a user entry on a form whose value is unknown to your VBA code.

STEP FOUR:  This is how you pass a parameter value to a query in VBA, and create a recordset from it:

    Dim db As Database, q As QueryDef, r As Recordset
    Set db = CurrentDb
    Set q = db.QueryDefs("LeaseDocumentQ")
    q.Parameters("Tenant parameter") = [Forms]![LeaseReviewF]![TenantCombo]
    Set r = q.OpenRecordset

Note that the parameter referred to in such a manner does *not* have square brackets, i.e. "Tenant parameter".

Thanks all, I'll run Kevin's solution and see how I get on.

I covered this in one of my Developer lessons somewhere... unfortunately I didn't put "too few parameters" in the keywords, so I can't tell you which one. There are a couple of different ways you can handle this, including Kevin Yip's method. I'll add this to my TechHelp list. It could use a video.

That would be awesome, thanks Richard

Hi Kevin,
Sorry i'm not understanding some of what you are saying.  I can understand what you are saying regarding the combo box not being a parameter as such, however i'm not getting what to replace it with, and assume it'll apply to both parameters relating to the combo boxs.
If it's easier can anyone direct me to Developer lesson Richard refers to?

Hi Warren, basically you need to rewrite LeaseDocumentQ to incorporate both combo boxes' values (TenantCombo and DocumentCombo).  If you show me the SQL for LeaseDocumentQ I may be able to show you how to rewrite it.  If you want to keep LeaseDocumentQ unchanged (since you may want to use it elsewhere), then you need to construct the whole SQL statement in VBA and use it with OpenRecordset.

Hi Kevin,

That would be very much appreciated.  I've included both combo boxs and would like to retain LeaseDocumentQ as it is used elsewhere, however could copy and rename if necessary.

SELECT DocumentT.DocumentID, DocumentT.TenantID, DocumentT.Description, DocumentT.Notes, DocumentT.Cover, DocumentT.DocumentDate, TenantT.TenantName, TenantT.CommencementDate, DateSerial(Year(Date()),Month([CommencementDate]),Day([CommencementDate])) AS Anniversary, TenantT.SubLease, PropertyT.Unit, PropertyT.StreetName, PropertyT.[Town/City], IIf([SubLease] Is Null,IIf([Unit] Is Null,[StreetName] & ", " & [Town/City],"Unit " & [Unit] & ", " & [StreetName] & ", " & [Town/City]),"Unit " & [Unit] & [SubLease] & ", " & [StreetName] & ", " & [Town/City]) AS TenantAddress, AssociateT.FirstName, AssociateT.LastName, AssociateT.FullName, AssociateT.EmailAddress
FROM ((DocumentT INNER JOIN TenantT ON DocumentT.TenantID = TenantT.TenantID) INNER JOIN PropertyT ON TenantT.PropertyID = PropertyT.PropertyID) INNER JOIN AssociateT ON TenantT.AssociateID = AssociateT.AssociateID
WHERE (((DocumentT.DocumentID)=Forms!LeaseReviewF!DocumentCombo) And ((DocumentT.TenantID)=forms!LeaseReviewF!TenantCombo));

Thanks

Hi Warren, you create a new query, and name it, say, LeaseDocumentQ2.  In it, rewrite the SQL as below.  Note the two newly added parameters near the end: [TenantCombo parameter] and [DocumentCombo parameter].  My picture below shows what it should look like.  

SELECT DocumentT.DocumentID, DocumentT.TenantID, DocumentT.Description, DocumentT.Notes, DocumentT.Cover, DocumentT.DocumentDate, TenantT.TenantName, TenantT.CommencementDate, DateSerial(Year(Date()),Month([CommencementDate]),Day([CommencementDate])) AS Anniversary, TenantT.SubLease, PropertyT.Unit, PropertyT.StreetName, PropertyT.[Town/City], IIf([SubLease] Is Null,IIf([Unit] Is Null,[StreetName] & ", " & [Town/City],"Unit " & [Unit] & ", " & [StreetName] & ", " & [Town/City]),"Unit " & [Unit] & [SubLease] & ", " & [StreetName] & ", " & [Town/City]) AS TenantAddress, AssociateT.FirstName, AssociateT.LastName, AssociateT.FullName, AssociateT.EmailAddress
FROM ((DocumentT INNER JOIN TenantT ON DocumentT.TenantID = TenantT.TenantID) INNER JOIN PropertyT ON TenantT.PropertyID = PropertyT.PropertyID) INNER JOIN AssociateT ON TenantT.AssociateID = AssociateT.AssociateID
WHERE (((DocumentT.DocumentID)=[DocumentCombo parameter]) And ((DocumentT.TenantID)=[TenantCombo parameter]));


Then you write VBA code in the manner I showed earlier:

    Dim db As Database, q As QueryDef, r As Recordset
    Set db = CurrentDb
    Set q = db.QueryDefs("LeaseDocumentQ2")
    q.Parameters("TenantCombo parameter") = Forms!LeaseReviewF!TenantCombo
    q.Parameters("DocumentCombo parameter") = Forms!LeaseReviewF!DocumentCombo
    Set r = q.OpenRecordset

r the recordset should have all the data you need.

Just want to add that OpenRecordset belongs to the Database object, which has no access to information about forms.  And that is what caused Warren's original problem.

Only the "Application" object has access to forms.

When you write Forms!LeaseReviewF!DocumentCombo, it implicitly means:

     Application.Forms!LeaseReviewF!DocumentCombo

When you run queries manually on the navigation pane, it is the "Application" that runs them, and that's why expressions like "Forms!LeaseReviewF!DocumentCombo" work.

When you run queries with OpenRecordset in VBA, it is the Database object that runs them, so no info on forms is available to it.

The Database object has access to tables and queries, but not forms.

Hi Kevin,

Thanks very much for this, all makes sense and is now up and running.

Warren