Changing RecordSource with SQL string

[Someone]

Hello

I have designed a form with a listbox and two radio button option groups. I
have put code together to build a SQL string dependent on the choices made
on the form.

Near the end of the code, I entered a msgbox command to show the full SQL
command with the choices, and the SQL is correct (I have even compared the
SQL by creating the same query in Design View).

When I run the routine, it changes the RecordSource of the main form's
subform to the SQL I created. It returns the correct information.

I have a button on the main form which 'resets' the RecordSource on this
subform (I have various search routines which amend the RecordSource, so the
Reset button returns the RecordSource to its normal view).

However, when I click the Reset button for the SQL command I refer to above,
a new record has been created and it appears in the subform. The record is
added with the value of Operator (strOp) chosen on the form.

I have pasted below a slightly edited version of the code - can anyone
understand why this may be happening?

Private Sub btnExtensionSearch_Click()

Dim strOp As String 'Text value of Operator field
Dim strOrderPref As String 'Order by what field?
Dim strOrderBy As String 'Order Ascending or Descending?
Dim strTableName As String 'Name of table data is taken from
Dim strSQL As String 'Whole SQL string

strOp = Me.Operator.Value
strTableName = "tbl_Direction."

'Code to get values from first option group
If Me.fmeExtensionOption = 1 Then
strOrderPref = "ID"
Else
etc for rest of option group
End If

If Me.fmeOrderBy = 1 Then
strOrderBy = ""
Else
strOrderBy = "Desc"
End If

strSQL = "SELECT tbl_Direction.* " & _
"FROM tbl_Direction " & _
"WHERE (((" & strTableName + "Operator) = " + """" + strOp + """" & ")) " &
_
"ORDER BY " & strTableName + strOrderPref + " " + strOrderBy & ";"

Forms!frm_Main!frm_Main_Subform.Form.RecordSource = strSQL

Forms!frm_Extension.SetFocus
DoCmd.Close

End Sub

The Reset button just changes the RecordSource back to what it should be by
default.

Thanks
M

 

[John Welch]

I don't see anything in what you've sent that would create a record, though
you did say that the record is created when you click the reset button, and
you haven't given the code for the reset button. Am I missing something?

On a side note, you might consider using a list box or combo box for a list
of options for sort order. Then you could just say: (assuming the list or
combo box contained the names of the fields)
strSortOrderPref = nz(me.listSortOrder,"ID")
-John

 

[Someone]

Hi John

Thanks for your time in responding.

However, I've realised what I did wrong. I set the Record Source of the
form to the table that was inheriting the information I mentioned.

So, I'm very sorry for taking up your time on my silly mistake!

Thanks
M