Proper syntax of DoCmd.OpenForm statement with "Where" arguments 3

[08211987]

What is the proper syntax of the DoCmd.OpenForm Statement? I have a subform (subform_List) and when any field in the row of the subform is clicked, it should open a form (form_Update) and display the record that contains the values of the 4 fields listed below in the subform - DName, SNum, VNum, PNum. These values make the record unique. The rows in the subform are from the same table that form_Update was created from. The subform is actually a subset of all the fields provided in the "form_Update" which is to be used to edit additional information.

Below is what I tried the fields on the left are the fields on form_Update and the Forms!form_Main!subform_List are the subform field. I'm getting a runtime error of 2465 and it doesn't know what I am referencing.

Private Sub DName_Click()
DoCmd.OpenForm "form_Update", , , _
"DName = " & Forms!form_Main!subform_List.Form.DName _
And "SNUM = " & Forms!form_Main!subform_List.Form.SNum _
And "VNum = " & Forms!form_Main!subform_List.VNum _
And "PName = " & Forms!form_Main!subform_List.Form.PName
End Sub

Appreciate your help.

 

[PHV]

Assumptions: DName and PName are string, SNum and VNum are numbers.

Private Sub DName_Click()
DoCmd.OpenForm "form_Update", , , _
 "DName='" & Replace(Me!DName, "'", "''") & "'" _
 & " AND SNum=" & Me!SNum _
 & " AND VNum=" & Me!VNum _
 & " AND PName='" & Peplace(Me!PName, "'", "''") & "'"
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[08211987]

Perfect....Thanks so much! Syntax gets me everytime....
So everytime you place text in a text field, you should code a Replace?

 

[Andrzejek]

If you have a problem with the syntax, try this approach:

Private Sub DName_Click()[blue]
Dim str As String

str = "DName='" & Replace(Me!DName, "'", "''") & "'" _
 & " AND SNum=" & Me!SNum _
 & " AND VNum=" & Me!VNum _
 & " AND PName='" & Peplace(Me!PName, "'", "''") & "'"

Debug.Print str[/blue]

DoCmd.OpenForm "form_Update", , ,[blue]str[/blue]
End Sub

So everytime you place text in a text field[blue] where you have the potential of a single quote in the text[/blue], you should code a Replace

Have fun.

---- Andy

 

[MajP]

Here is technique to make it clean, readable, and debuggable. I never try to do any resolution of values in the sql string. This is one of the biggest mistakes I see in Access programming, where people try to bite off everything in at once. I always use variables. This makes it readable, less prone to mistakes, and more importantly easily debuggable.

Private Sub DName_Click()
  dim strWhere as string
  dim SNUM as long
  dim VNum as long
  dim PName as string
  dim dName as string
  
  SNum = Me.SNUM
  Vnum = Me.Vnum
  PName = SqlText(me.Pname)
  DName = SqlText(me.Dname)
  
  strWhere = "DName = " & DName & " And SNUM = " & SNum & " And VNum = " & VNum & " And PName = " & PName
  debug.print strWhere  'Verify it gives you what you want.
  DoCmd.OpenForm "form_Update", , , strWhere
End Sub

Public Function sqlTxt(varItem As Variant) As Variant
  If Not IsNull(varItem) Then
    varItem = Replace(varItem, "'", "''")
    sqlTxt = "'" & varItem & "'"
  End If
End Function

Function SQLDate(varDate As Variant) As Variant
     If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

 

[08211987]

Great ideas, Thanks!