Problem opening subform when double click record from main form

[thayes5150]

I have an Access 2003 database used to research unemployment claims. The main form contains a subform in datasheet view listing the employee claim records, including employee Social security number. I would like them to be able to double Click a record in this datasheet and have it open a subform, populated from a Query that is passed the SSN from the recordset that was clicked. I have included the following code in the vba:

Private Sub Form_DblClick(Cancel As Integer)
  DoCmd.OpenForm "ClaimDetail", , , "[SSN] = " & Me!ImportSsN

End Sub

The form does open on double click, if I look at the for properties, the filter is set to the correct SSN, but the records will not display. I know I am missing something obvious, please help.

[thayes5150]

OK, Never mind - this was a stupid error, have to treat the SSN as a string, so code should be :

DoCmd.OpenForm "ClaimDetail", , , "[SSN] = '" & Me!ImportSsN & "'"

I hate dynamic sql...:)

[NeoPa]

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

[NeoPa]

I have moved your new question into its own, separate, thread (Continued Problems on Subforms).