'How to Pass SQL Parameter to Form Using OpenArgs?

I have a Database (not built by me) that uses 3 separate forms to accomplish 1 thing.

I would instead like to pass a SQL string to the OpenArgs in order to utilize 1 form.

Original Code for form I'd like to utilize:

Private Sub Form_Open(Cancel As Integer)
Dim strSQL As String

If Not IsNull(Me.OpenArgs) Then

strSQL = "SELECT tbl_COMBINED.[First Name] AS [Name Badge], 'P' AS Logo, Format(Now(),""yyyy"") &  STOCKHOLDERS MEETING' AS MEETING " _
        & "FROM tbl_COMBINED " _
        & "GROUP BY tbl_COMBINED.[First Name], 'P', Format(Now(),""yyyy"") & ' STOCKHOLDERS MEETING', " _
        & "tbl_COMBINED.ACCOUNT, tbl_COMBINED.Came " _
        & "HAVING tbl_COMBINED.ACCOUNT = '" & CStr(Me.OpenArgs) & "' " _
        & "AND ((tbl_COMBINED.Came) Is Null Or (tbl_COMBINED.Came)) = 0"

    Me.RecordSource = strSQL
    

End If
End Sub

Each of the other forms is called by using

DoCmd.OpenForm "frm_newmanualnamebadge", "", "",, acNormal

from the Main form and has the SQL string in the row source. I would like to eliminate the row source and utilize the 1 form. I set the string from each button to:

strManuel = "SELECT tbl_manual_name_badge.NAMEBADGE1, tbl_manual_name_badge.MEETING, " _
    & "tbl_manual_name_badge.LOGO, tbl_manual_name_badge.Stockerholder " _
    & "FROM tbl_manual_name_badge"

DoCmd.OpenForm "frm_newmanualnamebadge", "", "",, acNormal, strManual

Passing the strManual to the form as a SQL string, however, every time I run it I get a "#Name?" in the name field instead of the name entered.

Here is the code I used on the form:

If Not IsNull(Me.OpenArgs) Then
    
strSQL = "SELECT tbl_COMBINED.[First Name] AS [Name Badge], 'P' AS Logo " _
        & "FROM tbl_COMBINED " _
        & "GROUP BY tbl_COMBINED.[First Name], 'P', " _
        & "tbl_COMBINED.ACCOUNT, tbl_COMBINED.Came " _
        & "HAVING tbl_COMBINED.ACCOUNT = '" & CStr(Me.OpenArgs) & "' " _
        & "AND ((tbl_COMBINED.Came) Is Null Or (tbl_COMBINED.Came)) = 0"


    Me.RecordSource = strSQL
    
ElseIf IsNull(Me.OpenArgs) Then

strSQL = "SELECT tbl_manual_name_badge.NAMEBADGE1, tbl_manual_name_badge.MEETING, " _
    & "tbl_manual_name_badge.LOGO, tbl_manual_name_badge.Stockerholder " _
    & "FROM tbl_manual_name_badge"
    
    Me.RecordSource = strSQL
    
End If
sqlstringms-accessopenargs


Solution 1:[1]

Well, you either pass one value, or you pass the whole sql string.

But, if you passing the WHOLE sql string for the form, then this makes no sense:

If Not IsNull(Me.OpenArgs) Then

    strSQL = "SELECT tbl_COMBINED.[First Name] AS [Name Badge], 'P' AS Logo " _
    & "FROM tbl_COMBINED " _
    & "GROUP BY tbl_COMBINED.[First Name], 'P', " _
    & "tbl_COMBINED.ACCOUNT, tbl_COMBINED.Came " _
    & "HAVING tbl_COMBINED.ACCOUNT = '" & CStr(Me.OpenArgs) & "' " _
    & "AND ((tbl_COMBINED.Came) Is Null Or (tbl_COMBINED.Came)) = 0"

Me.RecordSource = strSQL

I mean, OpenArgs is a WHOLE sel string, and I am VERY sure that ACCOUNT = " some huge sql string" will NEVER work.

So, you would want this:

Dim strSQL As String
If Not IsNull(Me.OpenArgs) Then
      strSQL = me.OpenArgs
else
    strSQL = "SELECT tbl_manual_name_badge.NAMEBADGE1, tbl_manual_name_badge.MEETING, " _
& "tbl_manual_name_badge.LOGO, tbl_manual_name_badge.Stockerholder " _
& "FROM tbl_manual_name_badge"
   
End If

Me.RecordSource = strSQL

So, our logic is now:

if passed sql string (openargs), then that becomes our sql

if no open arges, then use the defined sql we have in the on-load

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Albert D. Kallal

Related Questions