Open form with 2 stLinkCriteria get error

What datatypes are Week and Assigned To?

If Week is a Date, then you need to enclose it in hash marks, and if Assigned To is a Text value, you'd need to enclose that in single quotes:

stLinkCriteria = "[Week]=#" & Me![Week] & "# And [Assigned To]='" & Me![Assigned To] & "'"

Week is a number ... tried and now get "Syntax error in date in query expression.

Then you don't need anything enclosing that value. The only ones you must treat differently are Dates (enclose them in hash marks: #) and Text (enclose them in single or double quotes).

Is Assigned To a Text value?

Sorry confused...
Week is a number like 24  and AssignedTo is a person or text... everything I have tried gets me another error.
any other suggestions?
Thanks SS

SusanSSS,

Here is a sample:
Presumptions:
"Week" is a number
"AssingnedTo" is text

Just FYI:
Do yourself a favor and adopt a naming convention like the Lezinski Naming Convention (LNC)
http://en.wikipedia.org/wiki/Leszynski_naming_convention

So it is clear what you are referring to.
In your case you are referring to a Field called Week on the form to be opened, and a "control" called Week on the current form.

I have used such a convention in my sample.

The final code ended up being:
Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmMRepoComplete"
    stLinkCriteria = "[Week]=" & Me![cboWeek] & " And [AssignedTo]=" & "'" & Me![cboAssignedTo] & "'"
   
    DoCmd.OpenForm stDocName, , , stLinkCriteria

...which worked fine

JeffCoachman
Access-EEQ23580285OpenFormWithWT.mdb

JeffCoachman,
Thank you.... but when I try this it pop up " Enter Parameter Value" for Assignedto .. still not getting it to pull by Week and AssignedTo
SS

SusanSSS,

First things first...

Does the sample I submitted work?
Yes or no?

Jeff

Jeff,
No..the sample
 stLinkCriteria = "[Week]=" & Me![Week] & " And [AssignedTo]=" & "'" & Me![AssignedTo] & "'"
Linked the week but not the AssingedTo ... Get "Enter Parameter Value" when I attempt to open the form.

SS  

SusanSSS,

That IS NOT the code from my sample.

When you downloded "my" sample and tested it, it should have worked.
I tested it before I uploded it. And I just now downloaded it from the link here, and retested it.
It works fine.
I have attached a screeenshot (Week 2, for pete)

Can you confirm that my sample, when downloaded, works as specified?

JeffCoachman
peteweek2.JPG

Jeff,
If I use your code
stLinkCriteria = "[Week]=" & Me![cboWeek] & " And [AssignedTo]=" & "'" & Me![cboAssignedTo] & "'"
I get and error - Microsoft Access can't find field 'cboWeek' refered to in your expression.
I am happy it worked for you... but I am right back were I started.

Susan

This would indicate a problem with your object/field names. The Field Names in your Link criteria must match field names on the form/report you're trying to open. Does your Form/Report actually have a field named [Assigned To] and [Week] (without the square brackets)? Note that I said Field, and not control ... you'd need to look in the Recordsource of whatever object you're trying to open.

Thank you so much Jeff!
After stepping back from it for awhile I noticed that [AssignedTo] should have had a space [Assigned To] .... I am very alert to spacing but getting so frustrated with this in building this massive database on a deadline, I missed it till this morning. The solution work perfectly this morning once I noticed my error.
Susan