Where Condition

[Oliversmo]

I am attempting to open a form on a {on_click} event where the Project # on the form to be opened will be that of the Project # on the form currently open and have set the following procedure:

Private Sub cmdTeams_Click()
On Error GoTo Err_cmdTeams_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Teams"
DoCmd.OpenForm stDocName, , [ProjectNo] = Forms![Projects]![ProjectNo], stLinkCriteria

Exit_cmdTeams_Click:
Exit Sub

Err_cmdTeams_Click:
MsgBox Err.Description
Resume Exit_cmdTeams_Click

End Sub

The error I receive is "Access cannot find the field ProjectNo referred to in your expression"! I have checked the spelling of the control on the form and underlying table and both are spelled in that manner. The field that it is getting the text string from is reading fine but it doesn't like something in the 1st [fieldname] in the where condition. Any suggestions?

 

[LarryDeLaruelle]

Oliver:

I think the string should look like this:


DoCmd.OpenForm stDocName, , ,[ProjectNo] = Forms![Projects]![ProjectNo]

The linking criteria is the fourth argument and goes after the third comma. Also, you still had the stLinkCriteria as part of the line (since you had not assigned a value to it, it would be empty)

If ProjectNo is a text field, you may need to enclose it in quotes.

See if that does it for you.
Larry De Laruelle
larry1de@yahoo.com

 

[seaport]

Assuming the projectNo is a number field, try this:
DoCmd.OpenForm stDocName, , ,"[ProjectNo] =" & Forms![Projects]![ProjectNo]

If the projectNo is a text field, try that:
DoCmd.OpenForm stDocName, , ,"[ProjectNo] ='" & Forms![Projects]![ProjectNo] & "'"

Seaport

 

[Oliversmo]

I attempted all suggestions but to no avail. The ProjectNo is a text field and the control source is a query. When I used the last suggestion for the 'text' field I didn't receive an error and the subform did appear. The record appearing in the subform was not the same as in the main form. When I set a breakpoint for that line and view the code, it acknowledges the correct ProjectNo on the main form but doesn't seem to recognize the subform ProjectNo at all. Any suggestions?